SQL

Question 1
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 
A
P and R
B
P and S
C
Q and R
D
Q and S
GATE CS 2012    SQL    
Discuss it


Question 1 Explanation: 
According to standard SQL answer should be C. Refer If we talk about different SQL implementations like MySQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details. S is correct . To verify S, try following queries in SQL.
CREATE TABLE temp 
  ( 
     id   INT, 
     name VARCHAR(100) 
  ); 

INSERT INTO temp VALUES (1, "abc"); 
INSERT INTO temp VALUES (2, "abc"); 
INSERT INTO temp VALUES (3, "bcd"); 
INSERT INTO temp VALUES (4, "cde"); 

SELECT Count(*) 
FROM   temp 
GROUP  BY name; 
Output:
count(*)
--------
2
1
1
Question 2
Table A
Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11


Table B
Id   Name   Age
----------------
15   Shreya  24
25   Hari    40
98   Rohit   20
99   Rohit   11


Table C
Id   Phone  Area
-----------------
10   2200   02  
99   2100   01
Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?
SELECT A.id 
FROM   A 
WHERE  A.age > ALL (SELECT B.age 
                    FROM   B 
                    WHERE  B. name = "arun") 
A
4
B
3
C
0
D
1
GATE CS 2012    SQL    
Discuss it


Question 2 Explanation: 
The meaning of “ALL” is the A.Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected. Source: http://www.geeksforgeeks.org/database-management-system-set-3/
Question 3
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;
A
127
B
255
C
129
D
257
GATE CS 2011    SQL    
Discuss it


Question 4
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
3
B
9
C
5
D
6
GATE CS 2011    SQL    
Discuss it


Question 4 Explanation: 
Question 5
A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, 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?
SLECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
    EXISTS (SELECT *
       FROM Passenger
       WHERE age > 65 AND
       Passenger. pid = Reservation.pid)
A
1, 0
B
1, 2
C
1, 3
D
1, 5
GATE CS 2010    SQL    
Discuss it


Question 5 Explanation: 
When a subquery uses values from outer query, the subquery is called correlated subquery. The correlated subquery is evaluated once for each row processed by the outer query. The outer query selects 4 entries (with pids as 0, 1, 5, 3) from Reservation table. Out of these selected entries, the subquery returns Non-Null values only for 1 and 3.
Question 6
Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database: gateqa
IV) SELECT R.a, R.b
       FROM R,S
            WHERE R.c=S.c
Which of the above queries are equivalent?
A
I and II
B
I and III
C
II and IV
D
III and IV
GATE-CS-2009    SQL    
Discuss it


Question 6 Explanation: 
I and II describe the division operator in Relational Algebra and Tuple Relational Calculus respectively. See Page 3 of this and slide numbers 9,10 of this for more details.
Question 7
Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Consider the following relational query on the above database:
SELECT S.sname
    FROM Suppliers S
        WHERE S.sid NOT IN (SELECT C.sid
                            FROM Catalog C
                            WHERE C.pid NOT IN (SELECT P.pid  
                                                FROM Parts P
                                                WHERE P.color<> 'blue'))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
A
Find the names of all suppliers who have supplied a non-blue part.
B
Find the names of all suppliers who have not supplied a non-blue part.
C
Find the names of all suppliers who have supplied only blue parts.
D
Find the names of all suppliers who have not supplied only blue parts.
GATE-CS-2009    SQL    
Discuss it


Question 7 Explanation: 

The subquery “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue. The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts. The complete query gives the names of all suppliers who have supplied a non-blue part

Question 8
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”)
A
Q1 is the correct query
B
Q2 is the correct query
C
Both Q1 and Q2 produce the same answer.
D
Neither Q1 nor Q2 is the correct query
SQL    GATE-CS-2007    
Discuss it


Question 8 Explanation: 
Let the employee(empId, name, department, salary) have the following instance. empId name department salary ----------------------------------
e1 ------- A-------- 1---------10000
e2 -------B ------- 5 ---------5000
e3 -------C ------- 5----------7000
e4 -------D ------- 2----------2000
e5 -------E ------- 3----------6000
Now the actual result should contain empId : e1 , e3 and e5 ( because they have salary greater than anyone employee in the department '5') -------------------------------------------------------- Now Q1 : Note : EXISTS(empty set) gives FALSE, and NOT EXISTS(empty set) gives TRUE.
Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and
s.salary >=e.salary)
Q1 will result only empId e1. --------------------------------------------------------- whereas Q2 :
Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary From employee s Where s.department = “5”)
Q2 will result empId e1, e3 and e5. -------------------------------------------------------- Hence Q2 is the correct query.
Question 9
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?
A
S1 is TRUE and S2 is FALSE.
B
Both S1 and S2 are TRUE.
C
S1 is FALSE and S2 is TRUE.
D
Both S1 and S2 are FALSE.
SQL    GATE-CS-2014-(Set-1)    
Discuss it


Question 9 Explanation: 
    S1: A foreign key declaration can always
        be replaced by an equivalent check
        assertion in SQL. 
False: Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.
    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) 
False: Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field 'a' of R. The field 'a' in table S doesn't uniquely identify a row in table R.
Question 10
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?
A
It executes but does not give the correct result.
B
It executes and gives the correct result.
C
It generates an error because of pairwise comparison.
D
It generates an error because the GROUP BY clause cannot be used with table joins in a subquery
SQL    GATE-CS-2014-(Set-1)    
Discuss it


Question 10 Explanation: 
The given query uses below inner query.
SELECT dept-id, MAX(hire-date)
     FROM employees JOIN departments USING(dept-id)
     WHERE location-id = 1700
     GROUP BY dept-id
The inner query produces last max hire-date in every department located at location id 1700. The outer query simply picks all pairs of inner query. Therefore, the query produces correct result.
SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN
     (Inner-Query); 
There are 35 questions to complete.

Company Wise Coding Practice    Topic Wise Coding Practice