SQL & Databases Querying Data with SQL Syllabus

Define a RIGHT OUTER JOIN and how it relates to a LEFT JOIN.

A RIGHT (OUTER) JOIN returns all rows from the right table plus matching left rows, filling left columns with NULL when unmatched. A RIGHT JOIN of A to B is equivalent to a LEFT JOIN of B to A with operands swapped.

Define a FULL OUTER JOIN and what its result contains.

A FULL OUTER JOIN returns all rows from both tables: matched rows combined, plus unmatched left rows (right columns NULL) and unmatched right rows (left columns NULL). It is the union of LEFT and RIGHT joins.

How can you find rows in table A that have no match in table B using an outer join (anti-join)?

Use a LEFT JOIN and filter for NULL on the right key: SELECT A.* FROM A LEFT JOIN B ON A.id = B.a_id WHERE B.a_id IS NULL. This returns left rows with no matching right row.

What is a CROSS JOIN and what is the size of its result (Cartesian product)?

A CROSS JOIN pairs every row of A with every row of B, producing the Cartesian product with no ON condition. If A has $m$ rows and B has $n$ rows, the result has $m \times n$ rows.

How can an accidental Cartesian product occur, and how do you avoid it?

It occurs when tables are listed comma-separated in FROM (or joined) without a join predicate, e.g. FROM A, B with no WHERE linking them. Avoid it by always supplying a proper ON/WHERE join condition between related keys.

What is a self join, and why is table aliasing required for it?

A self join is a join of a table to itself, used for hierarchical or comparative relationships (e.g. employee to manager). Aliases are required to give the two instances distinct names, e.g. FROM employees e JOIN employees m ON e.manager_id = m.id.

Give a typical use case for a self join with an example predicate.

Finding employee-manager pairs: SELECT e.name AS emp, m.name AS mgr FROM employees e JOIN employees m ON e.manager_id = m.id. Also used to find duplicate/related rows, e.g. pairs where a.value = b.value AND a.id < b.id.

When joining three or more tables, how are the joins evaluated, and does textual order matter for INNER joins?

Joins are logically evaluated left to right, each JOIN combining the accumulated result with the next table. For INNER joins the optimizer may reorder them freely (they are associative/commutative), so textual order doesn't change results. For OUTER joins, order matters.

Why does join order matter when mixing INNER and OUTER joins?

OUTER joins are not freely reorderable: an OUTER join preserves unmatched rows that a later INNER join (or WHERE on the null-able side) can silently discard, effectively turning the outer join into an inner one. Order and placement of predicates must be chosen carefully.

What does the USING clause do in a join, and how does it differ from ON?

USING (col) joins on columns with the same name in both tables (equivalent to ON A.col = B.col) and collapses the joined column into a single output column (not qualified by table). ON is more general and keeps both columns separately.

What is a NATURAL JOIN, and why is it considered risky?

A NATURAL JOIN automatically joins on ALL columns sharing the same name in both tables, with no explicit condition. It is risky because adding a same-named column later silently changes the join semantics, producing unexpected results; explicit ON/USING is safer.

List the five standard SQL aggregate functions and what each computes.

COUNT (number of rows/non-null values), SUM (total of values), AVG (arithmetic mean), MIN (minimum), MAX (maximum). They collapse a set of rows into a single scalar value per group.

How do aggregate functions (other than COUNT(*)) treat NULL values?

SUM, AVG, MIN, MAX, and COUNT(column) ignore NULLs — they operate only on non-null values. COUNT(*) counts all rows including those with NULLs. This means AVG divides by the count of non-null values, not total rows.

What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

COUNT(*) counts all rows. COUNT(column) counts rows where column IS NOT NULL. COUNT(DISTINCT column) counts distinct non-null values of the column.

What does the GROUP BY clause do, and how does it relate to aggregate functions?

GROUP BY partitions rows into groups sharing the same values of the listed columns; aggregate functions then compute one summary value per group. The result has one row per distinct group.

What is the fundamental rule about non-aggregated columns in the SELECT list when using GROUP BY?

Every column in SELECT that is not inside an aggregate function must appear in the GROUP BY clause (functional-dependency exceptions aside). Otherwise the value would be ambiguous. Violating this is a standard SQL error (though MySQL historically allowed it).

How does GROUP BY treat NULL values in the grouping columns?

All NULLs in a grouping column are treated as a single group (NULLs are considered equal for grouping purposes), producing one group containing all rows with NULL in that column.

What does the HAVING clause do, and how does it differ from WHERE?

HAVING filters groups after aggregation (it can reference aggregate functions like SUM > 100), whereas WHERE filters individual rows before grouping and cannot reference aggregates. WHERE runs first, then GROUP BY, then HAVING.

Can HAVING be used without GROUP BY? What happens?

Yes. Without GROUP BY, the entire result set is treated as one group, so HAVING filters that single aggregate group — the query returns rows only if the whole-table aggregate condition is met.

For performance, why is it preferable to filter with WHERE rather than HAVING when possible?

WHERE eliminates rows before grouping/aggregation, reducing the number of rows the aggregation must process. HAVING filters after aggregation. Non-aggregate conditions should go in WHERE so fewer rows are grouped, improving efficiency.

What do GROUPING SETS let you compute in a single query?

GROUPING SETS specify multiple grouping combinations in one query, producing the union of several GROUP BY results. E.g. GROUP BY GROUPING SETS ((a,b),(a),()) yields groupings by (a,b), by a alone, and a grand total.

What does ROLLUP generate, and for GROUP BY ROLLUP(a, b) which grouping sets are produced?

ROLLUP produces hierarchical subtotals from most detailed to grand total. ROLLUP(a, b) generates the grouping sets (a,b), (a), and () — i.e. subtotals per (a,b), subtotals per a, and a grand total. For $n$ columns it yields $n+1$ sets.

What does CUBE generate, and how many grouping sets does CUBE(a, b) produce?

CUBE produces all possible combinations (the power set) of the grouping columns. CUBE(a, b) generates (a,b), (a), (b), and () — $2^{2}=4$ grouping sets. In general CUBE of $n$ columns yields $2^{n}$ grouping sets.

What is the GROUPING() function used for with ROLLUP/CUBE?

GROUPING(col) returns 1 if the column was aggregated away (rolled up) for that row and 0 otherwise. It distinguishes a genuine NULL value in the data from a NULL that represents a subtotal/grand-total row.

What is a scalar subquery, and where can it be used?

A scalar subquery returns exactly one row and one column (a single value). It can be used anywhere a scalar expression is allowed — in SELECT, WHERE, or as a value in comparisons, e.g. WHERE salary > (SELECT AVG(salary) FROM employees).

What is a row subquery, and give an example comparison using one.

A row subquery returns a single row with multiple columns, compared as a tuple. Example: WHERE (dept_id, salary) = (SELECT dept_id, MAX(salary) FROM ...). The comparison matches the ordered set of column values.

What happens if a scalar subquery unexpectedly returns more than one row?

It raises a runtime error (e.g. 'more than one row returned by a subquery used as an expression'). A scalar subquery must return at most one row; returning zero rows yields NULL.

What is a correlated subquery, and how does its execution differ from an uncorrelated one?

A correlated subquery references columns from the outer query, so it is (conceptually) re-evaluated once per outer row. An uncorrelated subquery is independent and can be evaluated once. Correlation makes it depend on the current outer row's values.

Write a correlated subquery that selects employees earning more than their own department's average salary.

SELECT e.* FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id). The inner query references e.dept_id from the outer row, making it correlated.

What does the EXISTS predicate do, and what does its subquery's SELECT list matter?

EXISTS (subquery) returns TRUE if the subquery produces at least one row, else FALSE. It tests for existence only, so the SELECT list is irrelevant — conventionally SELECT 1 is used. It typically short-circuits on the first matching row.

What does NOT EXISTS test, and why is it often preferred over NOT IN when NULLs are possible?

NOT EXISTS returns TRUE if the subquery returns no rows (an anti-join). It is preferred over NOT IN because NOT IN with a NULL in the value list returns UNKNOWN for all comparisons, yielding no rows; NOT EXISTS handles NULLs correctly and gives the expected results.