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 standing alone without GROUP BY, a Cartesian product that collapses the moment one relation is empty, and correlated subqueries that quietly do the ranking. The 12 previous-year questions here run from SELECT's duplicate semantics and the DCL commands, through joins and grouping, to correlated subqueries. Attempt each one before reading the answer; if the idea underneath it slips, the DBMS learn module carries the full treatment.
SQL basics: SELECT, DISTINCT and the DDL, DML, DCL split
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 full solution).
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 full solution).
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 full solution).
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, which changes rows; CREATE, ALTER and DROP are DDL, which defines the schema; SELECT only reads. Only GRANT controls access, so it is the DCL command.
SQL joins, GROUP BY and aggregate 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 full solution).
Q6. Students(Roll_number, Name, Date_of_birth), Courses(Course_number, Course_name, Instructor) and Grades(Roll_number, Course_number, Grade). Which set does this query compute? (GATE 2003)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students.Roll_number = Grades.Roll_number
AND Courses.Instructor = 'Korth'
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = 'A';(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 full solution).
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 full solution).
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.
SQL 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 full solution).
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 full solution).
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 full solution).
Q12. Employee(name, sex, salary, deptName) holds a company's employee records. What does this query return? (GATE 2004)
SELECT deptName
FROM Employee
WHERE sex = 'M'
GROUP BY deptName
HAVING AVG(salary) > (SELECT AVG(salary) FROM Employee);(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 full solution).
How SQL queries are examined
The pattern is stable across the years these questions span. The basics block (Q1 to Q4) tests the duplicate semantics of SELECT, the empty-relation trap in a Cartesian product, and the DDL, DML, DCL split; those are quick scores once you stop confusing 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 other three. The subquery block (Q9 to Q12) is where the longer, multi-step questions sit: master ALL versus ANY, correlated counting for ranking, and the way a plain join multiplies rows where IN does not, 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.
