SQL & Databases Advanced SQL and Programmability Syllabus

Advanced SQL and Programmability syllabus — full chapter and topic list

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

1. 1

   Window Functions

   5 topics

   • OVER, PARTITION BY, ORDER BY
   • Ranking Functions
   • Offset Functions
   • Window Frames (ROWS/RANGE)
   • Running Totals and Moving Averages
2. 2

   Common Table Expressions

   3 topics

   • WITH Clause Basics
   • Recursive CTEs
   • CTEs vs Subqueries vs Temp Tables
3. 3

   Modifying Data

   5 topics

   • INSERT and Bulk Inserts
   • UPDATE with Joins
   • DELETE and TRUNCATE
   • UPSERT
   • RETURNING / OUTPUT Clauses
4. 4

   Views and Materialized Views

   3 topics

   • Creating and Using Views
   • Updatable Views
   • Materialized Views and Refresh Strategies
5. 5

   Stored Procedures and Functions

   4 topics

   • Procedural Extensions
   • Parameters and Return Values
   • Control Flow and Loops
   • Error Handling and Exceptions
6. 6

   Triggers and Automation

   3 topics

   • BEFORE and AFTER Triggers
   • Row vs Statement Triggers
   • Use Cases and Anti-Patterns
7. 7

   Pivoting and Advanced Patterns

   3 topics

   • PIVOT and UNPIVOT
   • Conditional Aggregation
   • Gaps and Islands

Advanced SQL and Programmability flashcards for SQL & Databases

18 of 50 cards from the Advanced SQL and Programmability deck — real questions with worked answers.

1. What does the OVER clause do in SQL, and how does it differ from GROUP BY?

   OVER defines a window (set of rows) over which a window function operates. Unlike GROUP BY, it does not collapse rows: each input row is retained and gets a computed value alongside it.

2. In a window function, what is the role of PARTITION BY?

   PARTITION BY divides the rows into groups (partitions); the window function restarts and is computed independently within each partition. Without it, the entire result set is one partition.

3. Within OVER(), what does adding ORDER BY change for a window function?

   ORDER BY defines the logical order of rows inside each partition, which determines ranking, offsets, and the default running window frame (from start of partition to the current row).

4. Compare the three ranking functions ROW_NUMBER, RANK, and DENSE_RANK on tie handling.

   ROW_NUMBER gives every row a unique sequential number (ties broken arbitrarily). RANK assigns equal rank to ties then skips numbers (1,1,3). DENSE_RANK assigns equal rank to ties with no gaps (1,1,2).

5. What does NTILE(n) do as a window function?

   NTILE(n) distributes the ordered rows of each partition into n roughly equal buckets and returns the bucket number (1..n) for each row. Earlier buckets get the extra rows when the count is not evenly divisible.

6. For the ranking values 90, 90, 80, 70, give ROW_NUMBER, RANK, and DENSE_RANK (descending).

   ROW_NUMBER: 1,2,3,4. RANK: 1,1,3,4. DENSE_RANK: 1,1,2,3.

7. What do the offset functions LAG and LEAD return?

   LAG accesses a value from a previous row in the ordered partition; LEAD accesses a value from a following row. Syntax: LAG(expr, offset, default) OVER(... ORDER BY ...).

8. What is the default offset for LAG and LEAD, and what happens past the partition boundary?

   The default offset is 1. When the requested row is outside the partition, they return NULL unless a default argument is supplied.

9. What do FIRST_VALUE and LAST_VALUE return, and what is the common pitfall with LAST_VALUE?

   FIRST_VALUE returns the first row's value in the window frame; LAST_VALUE returns the last. Pitfall: with an ORDER BY the default frame ends at the current row, so LAST_VALUE returns the current row's value unless the frame is set to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

10. What are the two window-frame units, ROWS and RANGE, and how do they differ?

    ROWS counts a physical number of rows relative to the current row. RANGE works on logical value ranges, treating all peer rows (equal ORDER BY values) as one unit. ROWS gives distinct counts; RANGE lumps ties together.

11. Write the frame clause for a running total from the start of the partition to the current row.

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

12. What is the default window frame when ORDER BY is present but no explicit frame is specified?

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

13. Write the frame for a centered 3-row moving average (previous, current, next row).

    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

14. How do you compute a running total of a column named amount ordered by date?

    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

15. Give the formula for a simple moving average over the last $n$ observations $x_i$.

    $$\text{SMA}_t = \frac{1}{n}\sum_{i=0}^{n-1} x_{t-i}$$

16. How would you write a trailing 7-row moving average of amount ordered by date?

    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

17. What is a CTE (Common Table Expression) and how is it introduced?

    A CTE is a named temporary result set defined with the WITH clause that exists only for the duration of a single query. Syntax: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name.

18. Can a single WITH clause define multiple CTEs, and can they reference each other?

    Yes. Separate multiple CTEs with commas after a single WITH. A later CTE may reference any CTE defined earlier in the same WITH clause.

See more Advanced SQL and Programmability flashcards →

Planning Advanced SQL and Programmability for SQL & Databases

Advanced SQL and Programmability (SQL & Databases) FAQ