SQL & Databases Querying Data with SQL Syllabus

Querying Data with SQL syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Querying Data with SQL in SQL & Databases, not a summary of it.

1. 1

   Single-Table Queries

   5 topics

   • SELECT and FROM Basics
   • Filtering with WHERE
   • Sorting with ORDER BY
   • Limiting Results
   • DISTINCT and Column Aliases
2. 2

   Joining Tables

   6 topics

   • INNER JOIN
   • LEFT, RIGHT, and FULL OUTER JOIN
   • CROSS JOIN and Cartesian Products
   • Self Joins
   • Multi-Table Joins and Join Order
   • USING and Natural Joins
3. 3

   Aggregation and Grouping

   4 topics

   • Aggregate Functions
   • GROUP BY Semantics
   • Filtering Groups with HAVING
   • GROUPING SETS, ROLLUP, CUBE
4. 4

   Subqueries

   5 topics

   • Scalar and Row Subqueries
   • Correlated Subqueries
   • EXISTS and NOT EXISTS
   • IN, ANY, ALL Predicates
   • Subqueries in FROM (Derived Tables)
5. 5

   Set Operations

   3 topics

   • UNION and UNION ALL
   • INTERSECT
   • EXCEPT / MINUS
6. 6

   Functions and Expressions

   5 topics

   • String Functions
   • Numeric and Math Functions
   • Date/Time Functions
   • Conditional Logic
   • Type Casting and Conversion

Querying Data with SQL flashcards for SQL & Databases

24 of 51 cards from the Querying Data with SQL deck — real questions with worked answers.

1. In a basic SQL query, what do the SELECT and FROM clauses each specify?

   SELECT specifies which columns (or expressions) to return in the result set; FROM specifies the table(s) that supply the rows. Example: SELECT name, age FROM employees.

2. What does the wildcard SELECT * return, and why is it discouraged in production queries?

   SELECT * returns all columns of the source table(s). It is discouraged because it can retrieve unneeded data (hurting performance), breaks if the schema changes, and makes queries less explicit and harder to maintain.

3. What is the logical order of evaluation of the main SQL clauses (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT)?

   FROM (and JOINs) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET. This explains why column aliases from SELECT usually can't be used in WHERE, but can be used in ORDER BY.

4. What is the purpose of the WHERE clause, and at what stage does it filter?

   WHERE filters individual rows before grouping and aggregation. Only rows for which the predicate evaluates to TRUE are passed on (rows evaluating to FALSE or UNKNOWN are excluded).

5. How does SQL's three-valued logic treat comparisons with NULL, and how must you test for NULL?

   Any comparison with NULL (e.g. x = NULL) yields UNKNOWN, not TRUE, so such rows are excluded by WHERE. You must use IS NULL or IS NOT NULL to test for NULL.

6. List the common WHERE operators for ranges, sets, and pattern matching.

   Range: BETWEEN a AND b (inclusive). Set membership: IN (v1, v2, ...). Pattern matching: LIKE with wildcards % (any sequence) and _ (single character). Also =, <>, <, >, <=, >=, AND, OR, NOT.

7. In SQL's LIKE operator, what do the wildcards % and _ match?

   % matches any sequence of zero or more characters; _ matches exactly one single character. Example: LIKE 'A%' matches strings starting with A; LIKE '_a%' requires 'a' as the second character.

8. What does the ORDER BY clause do, and what is its default sort direction?

   ORDER BY sorts the final result set by one or more columns/expressions. Default direction is ASC (ascending); use DESC for descending. Multiple keys are applied left to right as tie-breakers.

9. In ORDER BY, how do NULLs sort, and how can you control their placement?

   NULL ordering is implementation-defined by default (e.g. PostgreSQL treats NULLs as largest, so last in ASC). You can control it explicitly with NULLS FIRST or NULLS LAST (where supported).

10. Can you sort by a column position or by a SELECT alias in ORDER BY?

    Yes. ORDER BY can reference a column's ordinal position (e.g. ORDER BY 2) and can reference SELECT-list aliases, because ORDER BY is logically evaluated after SELECT.

11. What does the LIMIT clause (and its standard equivalent FETCH FIRST) do?

    LIMIT n restricts the result to at most n rows. The SQL-standard form is FETCH FIRST n ROWS ONLY. Without an ORDER BY, which rows are returned is nondeterministic.

12. How do you implement pagination using LIMIT and OFFSET?

    LIMIT n OFFSET m skips the first m rows and returns the next n. For page $p$ (1-indexed) with page size $s$: OFFSET $(p-1)\times s$ LIMIT $s$. A stable ORDER BY is required for correct paging.

13. Why can large OFFSET values be inefficient for pagination, and what is an alternative?

    OFFSET m still scans and discards the first m rows, so cost grows with m. Keyset (seek) pagination is faster: instead of OFFSET, filter WHERE key > last_seen_key ORDER BY key LIMIT n.

14. What does the DISTINCT keyword do in a SELECT statement?

    DISTINCT removes duplicate rows from the result, keeping only unique combinations of the selected columns. It is applied after SELECT computes the output columns.

15. What is the difference between SELECT DISTINCT a, b and grouping? How does DISTINCT treat NULLs?

    SELECT DISTINCT a, b returns unique (a,b) pairs. For duplicate elimination, two NULLs are treated as equal (not distinct), so multiple all-NULL rows collapse to one — unlike the = comparison which returns UNKNOWN for NULLs.

16. What is a column alias, how is it defined, and what is the role of the AS keyword?

    A column alias renames a column/expression in the output, e.g. SELECT price * quantity AS total. AS is optional in most dialects (SELECT price*quantity total). Aliases with spaces/special characters need quotes: AS "Total Cost".

17. Why can't you usually reference a SELECT column alias inside the WHERE clause?

    Because WHERE is logically evaluated before SELECT, so the alias does not yet exist. You must repeat the full expression in WHERE, or use a subquery/CTE. Aliases are, however, usable in ORDER BY and often GROUP BY.

18. Define an INNER JOIN and state which rows it returns.

    An INNER JOIN returns only rows where the join condition (ON predicate) is satisfied in BOTH tables — the intersection of matching rows. Unmatched rows from either side are excluded.

19. Write the general syntax of an INNER JOIN with an ON clause.

    SELECT ... FROM A INNER JOIN B ON A.key = B.key. The keyword INNER is optional (JOIN alone defaults to INNER JOIN). The ON clause specifies the matching predicate.

20. Define a LEFT OUTER JOIN and describe its result for unmatched left rows.

    A LEFT (OUTER) JOIN returns all rows from the left table plus matching rows from the right. Where no right-side match exists, right columns are filled with NULL. It preserves every left row.

21. 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.

22. 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.

23. 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.

24. 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.

See more Querying Data with SQL flashcards →

Planning Querying Data with SQL for SQL & Databases

Querying Data with SQL (SQL & Databases) FAQ