GATE Questions & Answers of SQL

What is the Weightage of SQL in GATE Exam?

Total 17 Questions have been asked from SQL topic of Databases subject in previous GATE papers. Average marks 1.65.

Consider the following two tables and four queries in SQL.

Book (isbn, bname), Stock (isbn, copies)
Query 1:       SELECT B.isbn, S.copies
                      FROM Book B INNER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 2:        SELECT B.isbn, S.copies
                      FROM Book B LEFT OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 3:        SELECT B.isbn, S.copies
                      FROM Book B RIGHT OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 4:        SELECT B.isbn, S.copies
                      FROM Book B FULL OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

Consider a database that has the relation schema EMP (EmpID, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are below.

EMP
EmpId EmpName DeeptName
1 XYA AA
2 XYB AA
3 XYC AA
4 XYD AA
5 XYE AB
6 XYF AB
7 XYG AB
8 XYH AC
9 XYI AC
10 XYJ AC
11 XYK AD
12 XYL AD
13 XYM AE

 

SELECT AVG(EC.Num)
FROM EC
WHERE(DeptName,Num) IN
       (SELECT DeptName,COUNT(EmpId) AS
                                    EC(DeptName,Num)
       FROM EMP
         GROUP BY DeptName)

 

The output of executing the SQL query is_______________.

Consider the following database table named top_scorer

top_scorer
player country goals
Klose  Germany  16 
Ronaldo  Brazil  15
G Miiller Germany  14
Fontaine  France  13
Pele  Brazil  12
Klinsmam Germany  11
Kocsis  Hungary  11
Batistuta  Argentina  10
Cubillas  Peru  10
Lato  Poland  10
Lineker  England  10
T Mulle Germany  10
Rahn  Germany  10

Consider the following SQL query:

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals >ALL (SELECT tb.goals
            FROM top_scorer AS tb
            WHERE tb.country = 'Spain')
AND ta.goals >ANY (SELECT tc.goals
            FROM top_scorer AS tc
            WHERE tc.country = 'Germany')
 
The number of tuples returned by the above SQL query is _________.

Consider the following database table named water_schemes :

 water_schemes
scheme_no district_name capacity
1 Ajmer 20
1 Bikaner 10
2 Bikaner 10
3 Bikaner 20
1 Churu 10
2 Churu 20
1 Dungargarh 10
The number of tuples returned by the following SQL query is ________ .

 

with total(name, capacity) as

 

     select district_name, sum(capacity)
     from water_schemes
     group by district_name
with total_avg(capacity) as
     select avg(capacity)
     from total
select name
     from total, total_avg
     where total.capacity $ \style{font-family:'Courier New'}\geq $ total_avg.capacity

Consider the following relations:

Student   Performance
Roll_No Student _Name           Roll_No Course Marks
1 Raj   1 Math 80
2 Rohit   1 English 70
3 Raj   2 Math 75
      3 English 80
      2 Physics 65
      3 Math 80

Consider the following SQL query.

SELECT S. Student_Name, sum (P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name
 

The number of rows that will be returned by the SQL query is _______.

Consider the following relation
        Cinema (theater, addresscapacity)
Which of the following options will be needed at the end of the SQL query
        SELECT P1. address
        FROM Cinema P1
Such that it always finds the addresses of theaters with maximum capacity?

Given the following statements:

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.

S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
CREATE TABLE S (

      a INTEGER,
      d INTEGER,
      e INTEGER,
      PRIMARY KEY (d),
      FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

Given the following schema:
                 employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
                 departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
                 SQL>SELECT last-name, hire-date
                                FROM employees
                                WHERE (dept-id, hire-date) IN
                                (SELECT dept-id, MAX(hire-date)
                                FROM employees JOIN departments USING(dept-id)
                                WHERE location-id = 1700
                                GROUP BY dept-id);

What is the outcome?

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

select * from R where a in (select S.a from S)

Consider the following relational schema:

employee(empId,empName,empDept)
customer(custId,custName,salesRepId,rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> ’GOOD’);

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Consider the following relations A, B and C:

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AB is the same as that of A.

$\style{font-family:Verdana}{\left(\mathrm A\cup\mathrm B\right)\bowtie_{\mathrm A.\mathrm{Id}>40\vee\mathrm C.\mathrm{Id}<15}\mathrm C}$

Consider the following relations A, B and C:

How many tuples does the result of the following SQL query contain?

SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age
                   FROM B
                   WHERE B.Name = ‘Arun’)

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table.

Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change

What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;

Database table by name Loan_Records is given below.

Borrower Bank_Manager Loan_Amount
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00

What is the output of the following SQL query?

SELECT count(*)
FROM (
            (SELECT Borrower,Bank_Manager FROM Loan Records)AS S
             NATURAL JOIN
            (SELECT Bank_Manager,Loan_Amount FROM Loan Records)AS T
);

A relational schema for a train reservation database is given below

Passenger (pid, pname, age)
Reservation (pid, cass, tid)

Table: Passenger
pid pname Age
0 ‘Sachin’ 65
1 ‘Rahul’ 66
2 ‘Sourav’ 67
3 ‘Anil’ 69
Table: Reservation
Pid Class Tid
0 ‘AC’ 8200
1 ‘AC’ 8201
2 ‘SC’ 8201
5 ‘AC’ 8203
1 ‘SC’ 8204
3 ‘AC’ 8202

What pids are returned by the following SQL query for the above instance of the tables?

SELECT pid
FROM Reservation
WHERE class = 'AC' AND
      EXISTS (SELECT *
              FROM Passenger
              WHERE age 65 AND
              Passenger.pid Reservation.pid)

Consider the table employee(empId, name, department, salary) and the two  queries Q1, Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?

Q1 : Select e.empId
       From employee e
       Where not exists
            (Select * From employee s Where s.department = “5” and s.salary >=e.salary)
Q2:  Select e.empId
        From employee e
       Where e.salary > Any
            (Select distinct salary From employee s Where s.department = “5”)