SQL is the most reliably tested part of DBMS in GATE and PSU papers, and it punishes half-knowledge: duplicates that SELECT keeps but relational projection drops, HAVING with no GROUP BY, ALL over an empty set, and correlated subqueries that quietly do the ranking. Below are 12 solved MCQs from KnowledgeGate's published question bank, most of them previous-year questions with the exam and year marked. Try each before the explanation, which is deliberately short. If the underlying idea slips, the full treatment is in the DBMS learn module. A note on the deep links: GATE PYQs carried inside the course link to their exact solved page; the remaining questions live in the same module's practice sets, so the module link above is your entry point for those.
SQL basics: DDL, DML and SELECT
Q1. Which of the following is or are correct? (GATE 1999)
(a) an SQL query automatically eliminates duplicates
(b) an SQL query will not work if there are no indexes on the relations
(c) SQL permits attribute names to be repeated in the same relation
(d) none of the above
Answer: (d). SELECT returns every qualifying row, duplicates included, unless you add DISTINCT, so (a) is wrong. Queries run without any index, only slower, and a relation cannot repeat an attribute name. None of the three claims holds (see the solved page).
Q2. The SELECT operation in SQL is equivalent to (GATE 2015)
(a) the selection operation in relational algebra
(b) selection in relational algebra, except that SQL SELECT retains duplicates
(c) the projection operation in relational algebra
(d) projection in relational algebra, except that SQL SELECT retains duplicates
Answer: (d). SQL's SELECT list chooses columns, which is projection, not the row-filtering that relational selection performs. The one difference is duplicates: relational projection removes them, while SQL keeps them unless DISTINCT is used. That makes (d) exact (see the solved page).
Q3. Given r(w, x) and s(y, z), the query SELECT DISTINCT w, x FROM r, s is guaranteed to equal r provided (GATE 2000)
(a) r has no duplicates and s is non-empty
(b) r and s have no duplicates
(c) s has no duplicates and r is non-empty
(d) r and s have the same number of tuples
Answer: (a). FROM r, s forms the Cartesian product, so every tuple of r appears at least once only if s is non-empty. Projecting w, x with DISTINCT then collapses the copies, but it also merges any duplicate rows already in r. So r must itself be duplicate-free and s non-empty (see the solved page).
Q4. Which one is a DCL command in SQL? (Bihar STET 2019)
(a) UPDATE
(b) SELECT
(c) DELETE
(d) GRANT
Answer: (d). Data Control Language governs permissions, and its two commands are GRANT and REVOKE. UPDATE and DELETE are DML, while SELECT is a query command. Only GRANT controls access, so it is the DCL command.
Joins, grouping and functions
Q5. Which statements are true about an SQL query? P: it can contain HAVING without GROUP BY. Q: it can contain HAVING only with GROUP BY. R: all GROUP BY attributes must appear in SELECT. S: not all GROUP BY attributes need appear in SELECT. (GATE 2012)
(a) P and R
(b) P and S
(c) Q and R
(d) Q and S
Answer: (b). HAVING can run without GROUP BY, treating the whole result as one group, so P is true and Q false. And GROUP BY attributes need not all appear in the SELECT list, so S is true and R false. The valid pair is P and S (see the solved page).
Q6. With Students(Roll, Name, DOB), Courses(Cno, Cname, Instructor) and Grades(Roll, Cno, Grade), the query selects distinct Name where the tables are joined on Roll and Cno, Instructor = Korth and Grade = A. It returns (GATE 2003)
(a) students with an A in all courses taught by Korth
(b) students with an A in all courses
(c) students with an A in at least one course taught by Korth
(d) none of the above
Answer: (c). The joins link a student to a specific course and grade, and the filters keep only Korth's courses where the grade is A. A single qualifying row is enough for the student's name to appear, so the query captures anyone with at least one A under Korth, not all of them (see the solved page).
Q7. With Book(isbn, bname) and Stock(isbn, copies), which join query is certain to produce a superset of the other three? (GATE 2018)
(a) inner join
(b) left outer join
(c) right outer join
(d) full outer join
Answer: (d). A full outer join returns all matching pairs plus unmatched rows from both tables. The inner join gives only matches, and each one-sided outer join adds only its own side's extras. Only the full outer join contains everything the others can, so it is the superset (see the solved page).
Q8. In SQL, which is an aggregate function? (RSSB 2022)
(a) SELECT
(b) CREATE
(c) AVG
(d) MODIFY
Answer: (c). Aggregate functions collapse many rows into one summary value, and the standard set is COUNT, SUM, AVG, MIN and MAX. SELECT retrieves rows and CREATE defines schema objects; neither aggregates. AVG is the only aggregate in the list.
Subqueries: nested and correlated
Q9. For Cinema(theater, address, capacity), which clause added to SELECT P1.address FROM Cinema P1 always returns the addresses of theaters with maximum capacity? (GATE 2015)
(a) WHERE P1.capacity >= ALL (SELECT P2.capacity FROM Cinema P2)
(b) WHERE P1.capacity >= ANY (SELECT P2.capacity FROM Cinema P2)
(c) WHERE P1.capacity > ALL (SELECT MAX(P2.capacity) FROM Cinema P2)
(d) WHERE P1.capacity > ANY (SELECT MAX(P2.capacity) FROM Cinema P2)
Answer: (a). A row is a maximum only if its capacity is at least as large as every capacity in the table, which is exactly what >= ALL expresses, and it correctly keeps ties. ANY is too weak, and > ALL(MAX ...) can never be satisfied since nothing exceeds the maximum. So (a) is the one that works (see the solved page).
Q10. Which query always gives the same answer as: SELECT * FROM R WHERE a IN (SELECT S.a FROM S)? (GATE 2014)
(a) SELECT R.* FROM R, S WHERE R.a = S.a
(b) SELECT DISTINCT R.* FROM R, S WHERE R.a = S.a
(c) SELECT R.* FROM R, (SELECT DISTINCT a FROM S) AS S1 WHERE R.a = S1.a
(d) SELECT R.* FROM R, S WHERE R.a = S.a AND is unique R
Answer: (c). The IN version keeps each row of R once if its a value appears anywhere in S, without multiplying. A plain join duplicates an R row for every match in S, and DISTINCT would wrongly strip genuine duplicates already in R. Joining against the distinct a values of S reproduces IN exactly (see the solved page).
Q11. For book(title, price) with distinct prices, what does this list: SELECT title FROM book B WHERE (SELECT COUNT(*) FROM book T WHERE T.price > B.price) < 5? (GATE 2005)
(a) titles of the four most expensive books
(b) title of the fifth cheapest book
(c) title of the fifth most expensive book
(d) titles of the five most expensive books
Answer: (d). The correlated subquery counts how many books cost more than the current one. Requiring that count below 5 means at most four books are pricier, so the current book ranks first through fifth by price. That selects the five most expensive titles (see the solved page).
Q12. For Employee(name, sex, salary, deptName), the query selects deptName where sex = 'M', groups by deptName, and keeps groups whose AVG(salary) exceeds the company-wide AVG(salary). It returns departments where (GATE 2004)
(a) the average salary exceeds the company average
(b) the average male salary exceeds the average of all male employees
(c) the average male salary exceeds the department average
(d) the average male salary exceeds the company average
Answer: (d). The WHERE clause limits rows to male employees, so the grouped AVG(salary) is the average male salary per department. The subquery in HAVING has no filter, so it computes the average over all employees company-wide. The result is departments where male average pay beats the overall company average (see the solved page).
How SQL is examined
The pattern is stable across years. The basics block (Q1 to Q4) tests the duplicate semantics of SELECT and the DDL, DML, DCL split, and these are quick one-mark scores if you never confuse projection with selection. The join and grouping block (Q5 to Q8) rewards knowing that HAVING can stand without GROUP BY and that a full outer join dominates the others. The subquery block (Q9 to Q12) is the two-mark territory: master ALL versus ANY, the empty-set trap, correlated counting for ranking, and IN versus a join, and most nested-query variants become mechanical.
For the reasoning behind joins and subqueries, work through our SQL Queries and Joins in DBMS deep dive, then drill the practice sets in the DBMS learn module. GATE aspirants get the full DBMS sequence inside GATE Guidance by Sanchit Sir. Solve, revisit the ones you missed, and come back a week later; the second pass is where accuracy under time is built.