SQL

  • Last Updated : 03 Aug, 2021

1234567
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

SQL    50 DBMS MCQs with Answers    
Discuss it


Question 1 Explanation: 

According to standard SQL answer should be option (C) which is answer key given by GATE authority. 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

Alternative way - Statement (P) "An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause" is correct because Having clause is applied after the aggregation phase and must be used if you want to filter aggregate results and Having doesn't require Group By clause. A HAVING clause without a GROUP BY clause is valid and (arguably) useful syntax in Standard SQL. Consider this example, which is valid Standard SQL:

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

Statement (S) "Not all attributes used in the GROUP BY clause need to appear in the SELECT clause" is correct but if we use Group By clause must, there are limitations on what we can put into the Select clause.

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    50 DBMS MCQs with Answers    
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    50 DBMS MCQs with Answers    
Discuss it


Question 4 Explanation: 

See Question 3 of http://www.geeksforgeeks.org/database-management-systems-set-4/

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.
 

E

None
 

GATE-CS-2009    SQL    50 DBMS MCQs with Answers    
Discuss it


Question 7 Explanation: 

(D) option matched because given query returns suppliers who have not supplied any blue parts. That means it can include other than blue parts. 

(A): False, as this may include blue parts and may not include "null" parts. 

(B): Obviously false because it returning other than any blue part. 

(C): Obviously false because it does not return this. 

(D): Correct. Please try here: http://sqlfiddle.com/#!9/9ae12d/1/0 

This explanation is contributed by Archit Garg.
 

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
GATE-CS-2007    SQL    
Discuss it


Question 8 Explanation: 
First note that they asked for Anyone (= All) not for Any. 
Here, Everyone means all of the group. 
Anyone means all or any part of the group. 

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 Q1 is the correct query. 

Note that if we use ALL in place of Any in second query then this will be correct. 

Option (A) is correct.

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.
GATE-CS-2014-(Set-1)    SQL    50 DBMS MCQs with Answers    
Discuss it


Question 9 Explanation: 
Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion. 

Using a check condition we can have the same effect as Foreign key while adding elements to the child table. But when we delete an element from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.

So, we cannot replace it with a single check.

 

    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
GATE-CS-2014-(Set-1)    SQL    
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 66 questions to complete.
1234567
My Personal Notes arrow_drop_up