GATE_DPP 2 [Link]
GATE
CS & IT
Database Management System DPP: 2
Query Languages
Q1 Consider the following keywords. Assume that PID is a primary key of relation.
A. SELECT Which SELECT statement should we used to limit
B. TOP the display of product information to the product
C. DISTINCT having price/cost less than 50?
D. FROM (A) SELECT PID, PName FROM Products WHERE
E. WHERE Cost < 50;
F. GROUP BY (B) SELECT PID, PName FROM Products WHERE
G. HAVING Cost< = 50;
H. ORDER BY (C) SELECT PID, PName FROM Products WHERE
The above keywords are used in the given SQL PID IN (SELECT PID FROM Products WHERE
query below. Cost <50);
SLECT TOP NumberOfRows DISTINCT Col1, Col2 (D) SELECT PID, PName FROM Products GROUP
FROM TableNameX, TableNameY BY PID Having Cost < 50;
GROUP BY ColumnName
Q4 The Employees table contains these columns
HAVING expression
empID NUMBERS (4)
ORDER BY ColumnName;
LastName VARCHAR (25)
Which of the following is the correct query
JobID VARCHAR (10)
execution order according to SQL Standard?
Suppose that, you want to search for string that
(A) D E F G A H B C
contains ‘Negi’ in the LastName column which
(B) D E F G A C H B
SQL statement will be used?
(C) D E F G A B C H
(A) SELECT empID, LastName, JobID FROM
(D) A D E F G H C B
employees WHERE LastName LIKE ‘%Negi’;
Q2 Consider the following employee table (B) SELECT empID, lastName, JobID FROM
Employees (EMPID, EmpName, Sal, DeptID, employees WHERE LastName = ‘Negi_%’;
ManagerID) assume that EMPID is primary key of (C) SELECT empID, lastName, JobID FROM
relation. which of the following SELECT employees WHERE LastName LIKE ‘Negi’;
statements is/are invalid? (D) None of these
(A) SELECT ManagerID, DeptID FROM employees;
Q5 Consider a relation A(P,Q) currently has tuples {(1,
(B) SELECT ManagerID, DISTINCT DeptID FROM
2), (1, 3), (3, 4) } and relation B(Q, R) currently has
employees;
{(2, 5), (4, 6), (7, 8)}. Then the number of tuples in
(C) SELECT DISTINCT ManagerID, DISTINCT
the result of the SQL query: SELECT * FROM A
DeptID FROM employees;
NATURAL OUTER JOIN B; is ?
(D) SELECT DISTINCT ManagerID, DeptID FROM
employees; Q6 Which of the following statement is/are true
about constraints?
Q3 Consider the following product relation
(A) The constraints is applied only to INSERT
Products (PID,PName, Cost)
operation into table.
Android App | iOS App | PW Website
1 of 5 02/09/24, 11:14
GATE_DPP 2 [Link]
GATE
(B) A foreign key can’t contain NULL values. (D) Both S1 and S2 are false
(C) A column with the unique constraint can store
Q9 Which of the below statement are true regarding
NULLS.
the WHERE and HAVING clause in a SQL
(D) We can have more than one column in a table
statement?
as a part of primary key.
(A) WHERE and HAVHIG clause can’t be used
Q7 Consider the following statements together in SQL Statement.
S1: An INSERT statement can add multiple rows (B) The HAVING clause condition can have
per execution to a table. aggregate function.
S2: An UPDATE Statement can modify multiple (C) The WHERE clause is used to exclude rows
rows based on multiple condition on a table. before the grouping of data.
Choose the correct statements. (D) The HAVING clause is used to exclude one or
(A) Only S1 is true more aggregated results after grouping data.
(B) Only S2 is true
Q10 Given the database schema A(P,Q,R) which of the
(C) Both S1 is S2 are true
following SQL query can be used to test whether
(D) Both S1 and S2 are false
the functional dependency P→R holds on relation
Q8 Consider the following statements. A?
S1: A DELETE statement can remove rows based (A) Select P from A group by P having count
on a single condition on a table (distinct R) >1
S2: An INSERT statement can add a single row (B) Selects P from A group by A having count
based on multiple condition on a table. (distinct R) >1
Choose the correct statements (C) Select R from A group by P having count
(A) Only S1 is true (distinct R) >1
(B) Only S2 is true (D) None of the above
(C) Both S1 and S2 are true
Android App | iOS App | PW Website
2 of 5 02/09/24, 11:14
GATE_DPP 2 [Link]
GATE
Answer Key
Q1 (B) Q6 (C, D)
Q2 (B, C) Q7 (C)
Q3 (A, C) Q8 (C)
Q4 (C) Q9 (B, C, D)
Q5 (4 to 4) Q10 (A)
Android App | iOS App | PW Website
3 of 5 02/09/24, 11:14
GATE_DPP 2 [Link]
GATE
Hints & Solutions
Q1 Text Solution: Option (b):
The correct query execution order. The following SQL selects all Lastname starting
FROM → D with "Negi".
WHERE → E Option (c):
GROUP BY → F The following SQL selects all Lastname contains
HAVING → G with "Negi".
SELECT → A Or
DISTINCT → C The equivalent SQL query:
ORDER BY → H SELECT empID, lastName, JobID FROM
TOP → B employees WHERE LastName LIKE ‘%Negi%’;
So, correct order of execution is DEFGACHB i.e…
Q5 Text Solution:
option (b).
A B A⋈B
Q2 Text Solution: P QQ R P Q R
Option b & c are having invalid SELECT 1 2 2 5 1 2 5
statement, because we cannot apply DISTINCT
1 3 4 6 3 4 6
keyword on attribute basis, DISTINCT keyword
3 4 7 8 1 3 -
chooses a distinct row.
- 7 8
Q3 Text Solution:
Therefore the number of tuples in the results are
Option (a) is correct because this SQL statement
4.
displays the product information of product with
Q6 Text Solution:
cost less than 50.
(a) False; we can also apply for an update
Option (b) is incorrect because it will return
operation into table.
product information of product with cost equal
(b) False; A foreign key can contain NULL values
to 50.
as well
Option (c) is correct SQL statement because in
(c) True; A column with the UNIQUE constraint
this we used nested SQL query.
can store NULL values but not duplicate value.
First, we find PID of product whose cost is less
(d) True; a primary key can also be a composite
than 50, and then we compare PID with the
key.
result of inner query.
Option (d) is incorrect SQL statement because it Q7 Text Solution:
cannot select non aggregate column PName in S1: True; An INSERT statement can add multiple,
SELECT clause. rows per execution to a table by using the
following SQL query.
Q4 Text Solution:
INSERT INTO table 2 (col1, col2, col3, ….)
The LIKE command is used in a WHERE clause to
SELECT col1, col2, col3,…
search for a specified pattern in a column.
S2: True; An UPDATE statement can modify
You can use two wildcards with LIKE:
multiple rows based on multiple conditions on
• % Represents zero, one, or multiple characters
table.
• -Represents a single character
Option (a) Q8 Text Solution:
The following SQL selects all Lastname ending S1: True; DELETE statement can remove rows
with "Negi". based no/single/multiple condition on a table.
Android App | iOS App | PW Website
4 of 5 02/09/24, 11:14
GATE_DPP 2 [Link]
GATE
S2: True; An insert statement can add a single before the grouping of data.
row based on multiple conditions on a table. (d) True; The HAVING clause is used to exclude
Q9 Text Solution: aggregated results after grouping
(a) False; A query can have both WHERE and Q10 Text Solution:
HAVING clauses. If the query in option a returns non null output,
(b) True; The HAVING clause condition can then the dependency does not hold. Hence (a)
have aggregate function. option is correct.
(c) True; WHERE clause is used to exclude rows
Android App | iOS App | PW Website
5 of 5 02/09/24, 11:14