SQL Query Questions in GATE CSE: Patterns and Traps

SQL questions in GATE CSE are almost always the same exercise: a small table is printed, a query is applied to it, and you count the rows in the result. The difficulty is never the syntax. It is that the intended meaning of a query and its actual evaluation diverge, usually because of a NULL, an empty subquery or a duplicate. This guide works the traps on one tiny instance you can carry in your head.

Key takeaways

• The answer is produced by evaluation, not by intent. Work row by row on the printed instance.
• Clause order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. HAVING filters groups, WHERE filters rows.
• Any comparison involving NULL is UNKNOWN, and WHERE keeps only rows that are TRUE.
• NOT IN with a NULL anywhere in the subquery returns nothing. NOT EXISTS does not behave that way.
• A comparison with ALL over an empty subquery is true for every row; with ANY it is false for every row.

Where SQL sits in the paper

Third-party analyst compilations place Databases at 3 to 11 marks per paper from 2009 to 2026, and at 7 to 8 marks in most papers from 2019 onward, which is four to five questions. The overall average is about 6.9 marks with a median of 7. These figures are unofficial and borderline questions are classified differently by different analysts. For 2026 Shift 1 one analyst counted three one-mark and two two-mark Databases questions and listed the topics as SQL, normalization and transactions.

Within the subject, SQL row counting is the highest-frequency item: the reconstruction places it in essentially every paper since 2010, usually as a two-mark numerical answer. The full subject ranking, including serializability, normal forms and B+ trees, is in the DBMS important topics guide.

One instance to reason on

Every example below uses these two tables. The NULLs are deliberate, because that is where the questions live.

emp(eid, name, dept, salary)          work(pid, eid)
 1  Asha    CS    60                   101  1
 2  Bimal   CS    45                   101  2
 3  Chitra  EE    NULL                  102  1
 4  Dev     NULL  55                    102  3
 5  Esha    EE    45                    103  4
 6  Farid   NULL  NULL

Bag semantics and duplicates

SQL works on multisets. SELECT salary FROM emp returns six rows including repeats, while SELECT DISTINCT salary FROM emp returns four: 60, 45, 55 and one NULL. DISTINCT keeps a single NULL row, which is a different rule from the one used by COUNT. A row-count question that omits DISTINCT is usually testing exactly this.

NULL and three-valued logic

WHERE salary = 45 returns 2 rows and WHERE salary <> 45 also returns 2 rows. Together that is 4, not 6, because the two rows with a NULL salary satisfy neither condition: the comparison evaluates to UNKNOWN and WHERE keeps only TRUE. The only way to reach those rows is IS NULL.

This one fact generates most of the SQL traps in the paper. Whenever you see a column that contains a NULL in the printed instance, check every predicate that touches it before counting anything.

Aggregates, GROUP BY and HAVING

On the same table:

Grouping by a nullable column produces three groups here, not four: CS, EE and one group holding both NULL departments. GROUP BY treats NULLs as belonging together even though NULL = NULL is UNKNOWN, and that asymmetry is a favourite one-mark item.

Now add HAVING. SELECT dept FROM emp GROUP BY dept HAVING COUNT(salary) = 1 returns two groups: the EE group, whose two rows carry one non-NULL salary, and the NULL-department group, which is the same shape. The CS group has two non-NULL salaries and is filtered out. Had the condition been COUNT(*) = 1, the answer would have been zero groups, since every group has two rows.

One more distinction worth rehearsing. A subquery computing an average can be correlated or not. WHERE salary > (SELECT AVG(salary) FROM emp) compares each row against 51.25 and returns Asha and Dev. The correlated version, comparing each row against the average of its own department, returns only Asha: the CS average is 52.5 and Asha's 60 clears it, Esha's 45 does not clear the EE average of 45, and Dev's department is NULL so the correlated subquery matches no rows, returns NULL, and the comparison is UNKNOWN.

NOT IN versus NOT EXISTS

These two are often described as interchangeable. On real data with NULLs they are not.

SELECT * FROM emp e
WHERE e.salary NOT IN (SELECT salary FROM emp WHERE dept = 'EE');

The subquery returns NULL and 45. For any row, NOT IN needs the salary to differ from every value in that list, and the comparison against NULL is UNKNOWN, so no row is ever TRUE. The query returns 0 rows, even for Asha, whose salary of 60 is obviously not 45.

The NOT EXISTS form asks a different question and answers it sensibly:

SELECT * FROM emp e
WHERE NOT EXISTS (SELECT 1 FROM emp f
                  WHERE f.dept = 'EE' AND f.salary = e.salary);

This returns 4 rows: Asha, Chitra, Dev and Farid. NOT EXISTS is about whether a matching row can be found, and an unmatched NULL simply fails to match rather than poisoning the whole condition.

The same construction handles division queries, which ask for rows related to every member of a set. Doubly nested NOT EXISTS is the standard translation, and on this instance "employees who work on every project that employee 3 works on" returns Asha and Chitra.

ALL and ANY over an empty subquery

Suppose no employee is in department ME, so SELECT salary FROM emp WHERE dept = 'ME' is empty. Then:

• salary > ALL (...) is TRUE for every one of the 6 rows, because there is no counterexample. Vacuous truth.
• salary > ANY (...) is FALSE for every row, so 0 rows come back.
• salary > (SELECT MAX(salary) FROM emp WHERE dept = 'ME') also returns 0 rows, because MAX over an empty set is NULL and the comparison becomes UNKNOWN.

Those three lines look like the same query and give 6, 0 and 0. This device has been used more than once in past papers, and it is worth practising until the emptiness of the inner result is the first thing you check.

Joins and row counts

On the instance above, the inner join of emp and work on eid gives 5 rows, one per assignment. The left outer join gives 7 rows: employee 1 contributes two rows because she has two assignments, employees 2, 3 and 4 contribute one each, and employees 5 and 6 contribute one padded row each with NULLs on the work side.

The general bounds are worth knowing. For a natural join without constraints the maximum is the product of the two cardinalities and the minimum is zero. If the join column is a foreign key with no NULLs, the count is exactly the size of the referencing relation. For a full outer join the minimum is the larger of the two cardinalities.

The traps in one list

• NOT IN with any NULL in the subquery returns nothing.
• <> does not reach NULL rows; only IS NULL does.
• COUNT(*) counts rows, COUNT(col) skips NULLs, and DISTINCT keeps one NULL row while COUNT DISTINCT ignores it.
• All rows with a NULL grouping key form one group.
• > ALL over an empty subquery is true; > ANY and > (SELECT MAX ...) are not.
• Missing DISTINCT leaves duplicates in the result, and the answer is a row count, not a value count.
• HAVING filters groups after aggregation; WHERE filters rows before it.
• A correlated subquery is recomputed per row; an uncorrelated one is computed once.

The cross-subject version of this list is in common mistakes that cost marks in GATE CSE, and the short formulas for join bounds and key counting are in the GATE CSE formula sheet.

How to practise

Write out one six-row table with two nullable columns and keep reusing it. Run each device against it on paper: the NOT IN and NOT EXISTS pair, the COUNT variants, the group with a NULL key, the empty-subquery comparison. If a database client is available, type the queries and compare your hand answer with the engine's; a mismatch is more instructive than any explanation.

Then practise reading a query bottom-up, innermost subquery first, and writing the surviving rows in a column. That single habit converts a two-mark trap into a two-mark gift. The method for deciding which topics deserve that drilling is covered in the guide to what the evidence says about important questions, and the Databases chapter of the GATE CSE 2027 book works a full bank of these instance-driven queries with step-by-step solutions.