SQL formulas

SQL. 7 formulas for GATE DA Database Management and Warehousing, each with when to use it. Each is explained in the topic notes.

All 7 SQL formulas

LOGICAL EVALUATION ORDER OF A QUERY BLOCK

FROM \to WHERE \to GROUP BY \to HAVING \to SELECT \to DISTINCT \to ORDER BY

Rows are filtered by WHERE before grouping; groups are filtered by HAVING after aggregation; ORDER BY runs last and does not change which rows are returned.

BAG COUNTS FOR SET OPERATIONS

UNION ALL: INTERSECT ALL: EXCEPT ALL: m + n min (m, n) max (m - n, 0)

Here m and n are the number of copies of one particular tuple in the first and second operand. Without ALL, each of these results has at most one copy of every tuple.

AGGREGATES AND NULL

AVG (c) = \frac{SUM ( c )}{COUNT ( c )}

Both SUM and COUNT here skip NULLs, so AVG divides by the number of non-NULL values, not by the number of rows; when c has NULLs, AVG(c) differs from SUM(c)/COUNT(). If every value of c is NULL (or there are no rows), SUM, AVG, MIN and MAX return NULL and COUNT(c) returns 0, while COUNT() returns the number of rows (0 only when there are no rows).

ROW COUNT OF AN EQUI-JOIN ON ONE COLUMN

∣ R ⋈_{R . k = S . k} S ∣ = v \neq = NULL \sum c_{R} (v) c_{S} (v)

Here c_R(v) and c_S(v) are the numbers of rows of R and S with key value v. Sum over non-NULL values v only: a NULL key never satisfies equality, so it matches nothing.

ROW COUNTS OF OUTER JOINS

∣ R LEFT JOIN S ∣ ∣ R FULL JOIN S ∣ = ∣ R ⋈ S ∣ + # {unmatched rows of R} = ∣ R ⋈ S ∣ + # {unmatched rows of R} + # {unmatched rows of S}

A row is unmatched if no row of the other table satisfies the ON condition; rows with a NULL join key are always unmatched. Every row of R appears at least once in R LEFT JOIN S, so the left join has at least |R| rows.

IN, ANY AND ALL

x IN S \equiv x = ANY S, x NOT IN S \equiv x \neq = ALL S

x op ANY S is TRUE if the comparison is TRUE for at least one value in S; x op ALL S is TRUE if it is TRUE for every value in S. On an empty S, ANY is FALSE and ALL is TRUE.

NEGATION IN THREE-VALUED LOGIC

NOT T = F, NOT F = T, NOT U = U

Because NOT UNKNOWN is still UNKNOWN, a row failing both p and NOT p can exist: WHERE p OR NOT p does not return every row when p can be UNKNOWN.

Where this was asked: 2024 (1), 2025 (1), 2026 (2). See the 4 previous-year questions on SQL. This shows what was asked, not what will be asked.

KEEP GOING

SQL: the notes and 4 solved previous-year questions