SQL: Computing Results by Hand

The last lesson named SQL as the declarative idea made practical — so here it is, doing real work. And GATE’s question about it is almost always the same five words: “How many rows does this query return?” 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 is a cross product: if Raider has 6 rows and Team has 6 rows, the intermediate result is 36 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 us see why with a concrete tiny dataset — six raiders and the same six teams (one per raider, joined on ID):

Walk 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, and here is exactly what the query returns:

Raider.ID | City   | Team.ID | RaidPoints
--------- | ------ | ------- | ----------
1         | Jaipur | 1       | 250
2         | Jaipur | 2       | 210
5         | Jaipur | 5       | 220
                  (3 rows)

Two quick variations confirm where the work happens. Remove AND RaidPoints > 200 and you get 4 rows (every Jaipur raider). Drop the ID-equality condition and you get 24 (4 Jaipur raiders crossed with 6 teams, filtered only by city). The WHERE clause is doing all the pruning.

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.

A question to carry forward

You can now query any schema and count its rows by hand. But notice we have never asked whether the schema was worth querying. Imagine cramming student, course, and instructor all into one wide table: the instructor’s name would repeat in every single enrolment row — and the day one of those copies is updated and the others are not, the database starts contradicting itself. That rot is a property of the design, invisible to any query. Here is the thread onward: what is the formal rule that captures “this column’s value is fixed by that one” — student_id determines name, course_id determines title — and how does that rule let you diagnose a bad design, and find a table’s keys, before a single bad update corrupts your data?

In one breath

• GATE’s SQL question is almost always “how many rows?” — read the query as a pipeline and count after each clause.
• Logical order (not written order): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• FROM A, B is a cross product (|A|·|B| rows); a missing join condition is the classic count-balloon trap. WHERE then prunes.
• Aggregates (COUNT/SUM/AVG/MIN/MAX) collapse rows; GROUP BY → one row per group; HAVING filters groups (WHERE cannot test an aggregate).
• GATE DA 2024 Q21: Raider, Team joined on ID, then City='Jaipur' and RaidPoints>200 → 3 rows.

Practice