SQL: Computing Results by Hand

“How many rows does this query return?” That is the GATE DA question, almost word for word. Not “write a query,” not “design a schema” — just stare at a small SQL snippet and a tiny table and tell us the count.

The trick is to read SQL like a tiny pipeline: tables in, filter, group, project, rows out. Six clauses do almost all the work, and once you know the order in which they fire, counting becomes mechanical.

Reading SQL as a pipeline

A query is six steps. They run in this order — not the order you write them:

• SELECT picks which columns come out (and runs aggregates).
• FROM picks the tables. Multiple tables separated by commas form a cross product — every row of A paired with every row of B.
• WHERE filters individual rows.
• JOIN … ON combines two tables on a matching condition — same as cross product plus a WHERE, but cleaner.
• GROUP BY buckets rows that share a value, and aggregates collapse each bucket to one row.
• HAVING filters those grouped buckets (use it instead of WHERE when the test involves an aggregate).
• DISTINCT removes duplicate output rows at the end.

The cross-product mental model

The most common GATE pattern uses the old “comma join” — FROM Raider, Team. That’s a cross product: if Raider has 5 rows and Team has 5 rows, the intermediate result is 25 rows. The WHERE clause then prunes it.

Worked example — GATE DA 2024 Q21

This is the actual exam question, stated nearly verbatim:

Schema: Raider(ID, City, ...) and Team(ID, RaidPoints, ...). How many rows does the following SQL query return?

SELECT *
FROM   Raider, Team
WHERE  Raider.ID = Team.ID
   AND City = 'Jaipur'
   AND RaidPoints > 200;

The answer is 3. Let’s see why with a concrete tiny dataset. We have six raiders and the same six teams (one per raider, joined on ID):

Walk through the WHERE filter row by row after the ID match collapses 36 cross-product rows down to 6:

• ID 1: City = Jaipur, RaidPoints = 250 > 200. Keep.
• ID 2: Jaipur, 210 > 200. Keep.
• ID 3: Mumbai — wrong city. Drop.
• ID 4: Jaipur, 180 — not above 200. Drop.
• ID 5: Jaipur, 220 > 200. Keep.
• ID 6: Patna — wrong city. Drop.

Three rows survive. Run it yourself — edit the data, change the threshold, watch the count move:

Try removing AND RaidPoints > 200 — you get 4 rows (every Jaipur raider). Drop the ID-equality condition and you get 24 (4 Jaipur raiders cross 6 teams). The WHERE clause is doing all the work.

Aggregates and GROUP BY

COUNT, SUM, AVG, MIN, MAX collapse many rows into one. Add GROUP BY and you get one collapsed row per group instead of one for the whole table.

SELECT   City, COUNT(*) AS raiders
FROM     Raider
GROUP BY City;

Output rows = number of distinct cities = 3 (Jaipur, Mumbai, Patna) — not 6.

HAVING filters those groups after aggregation:

SELECT   City, COUNT(*) AS raiders
FROM     Raider
GROUP BY City
HAVING   COUNT(*) >= 2;

Now only Jaipur (4 raiders) survives — 1 row.

How GATE asks this

A NAT: a tiny schema (two or three tables, a handful of rows each), a query that uses some mix of cross-product join, WHERE, GROUP BY, HAVING, and maybe DISTINCT — and you must type the exact row count. GATE DA 2024 Q21 was exactly this pattern with two tables. The way to beat the question every time: walk the six-clause pipeline above with a pencil, one clause at a time, and write down the row count after each step.

Quick check