SQL & Databases Advanced SQL and Programmability Syllabus

Give a classic use case for a recursive CTE.

Traversing hierarchical or graph data, such as an employee/manager org chart, a bill-of-materials, or generating a sequence of numbers/dates.

How can you prevent infinite recursion in a recursive CTE?

Ensure the recursive member eventually returns no rows (a proper termination condition), and use options like a depth counter, a cycle-detection clause, or SQL Server's MAXRECURSION hint.

Compare CTEs, subqueries, and temp tables on scope and reuse.

A subquery is inline and used once in its enclosing statement. A CTE is named, readable, can be referenced multiple times in the same statement, and supports recursion. A temp table persists across multiple statements in a session and can be indexed.

When is a temp table preferable to a CTE?

When the intermediate result is large, reused across several separate statements, or benefits from indexing/statistics. Many CTEs are re-evaluated each reference (not materialized), so a temp table avoids recomputation.

Write the syntax to insert a single row with explicit column values into table t(a, b).

INSERT INTO t (a, b) VALUES (1, 'x');

How do you perform a multi-row (bulk) insert in a single INSERT statement?

List multiple value tuples: INSERT INTO t (a, b) VALUES (1,'x'), (2,'y'), (3,'z');

How can you insert rows into a table from the result of a query?

INSERT INTO t (a, b) SELECT a, b FROM other_table WHERE condition;

What is the standard syntax for UPDATE with a join in PostgreSQL?

UPDATE t SET col = s.val FROM source s WHERE t.id = s.id; (PostgreSQL uses FROM; SQL Server uses UPDATE t SET ... FROM t JOIN source s ON ...).

Why is a WHERE clause critical in an UPDATE or DELETE statement?

Without WHERE, the operation applies to every row in the table, updating or deleting all rows unintentionally.

Compare DELETE and TRUNCATE across four dimensions: WHERE support, logging, triggers, and identity reset.

DELETE: supports WHERE, is row-by-row (fully logged), fires triggers, keeps identity seed. TRUNCATE: removes all rows, minimally logged/faster, no WHERE, usually fires no row triggers, and resets identity/auto-increment.

Is TRUNCATE typically DDL or DML, and what implication does that have?

TRUNCATE is generally treated as DDL. It is faster because it deallocates data pages rather than deleting rows individually, and in some databases it cannot be rolled back or is auto-committed.

What does UPSERT mean?

An operation that inserts a new row if it does not exist, or updates the existing row if a key conflict occurs ("update or insert"), performed atomically in one statement.

Write the PostgreSQL UPSERT syntax to insert into t(id, v) and update v on conflict.

INSERT INTO t (id, v) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;

What is the MySQL equivalent of an UPSERT?

INSERT INTO t (id, v) VALUES (1,'a') ON DUPLICATE KEY UPDATE v = VALUES(v); (newer syntax uses an alias instead of VALUES()).

Which SQL Server statement provides UPSERT-style conditional insert/update/delete?

The MERGE statement, which matches a target against a source and runs WHEN MATCHED / WHEN NOT MATCHED clauses.

What does the PostgreSQL RETURNING clause do?

RETURNING lets an INSERT, UPDATE, or DELETE return columns from the affected rows (e.g., a generated id) without a separate SELECT: INSERT INTO t (v) VALUES ('a') RETURNING id;

What is SQL Server's equivalent of RETURNING, and how does it identify old vs new values?

The OUTPUT clause. It exposes the INSERTED pseudo-table (new values) and the DELETED pseudo-table (old values); UPDATE can reference both.

What is a view in SQL?

A named, stored query that acts as a virtual table. It stores no data itself (except materialized views); it re-runs its defining SELECT each time it is queried.

Write the syntax to create a view named active_users.

CREATE VIEW active_users AS SELECT id, name FROM users WHERE active = true;

Name three benefits of using views.

They simplify complex queries (reusable abstraction), provide security by exposing only selected columns/rows, and give a stable interface that hides underlying schema changes.

What conditions generally make a view updatable?

It must map to a single base table, with no aggregation (GROUP BY, HAVING), no DISTINCT, no window functions, no set operations (UNION), and typically no computed columns being modified — so each view row corresponds to exactly one base-table row.

What does WITH CHECK OPTION do on an updatable view?

It forces INSERTs and UPDATEs through the view to satisfy the view's WHERE condition, preventing rows from being modified so they would disappear from (or never appear in) the view.

How can you make a non-updatable view support modifications?

Define an INSTEAD OF trigger (INSTEAD OF INSERT/UPDATE/DELETE) that translates the DML on the view into the appropriate operations on the underlying base tables.

What is a materialized view and how does it differ from a regular view?

A materialized view physically stores the query's result set on disk, so reads are fast but data can be stale. A regular view stores only the query definition and recomputes results on each access (always current).

What are the two broad refresh strategies for materialized views?

Complete (full) refresh, which recomputes the entire result set, and incremental/fast refresh, which applies only the changes since the last refresh using logs. Refresh can be scheduled/on-demand or, in some systems, on commit.

How do you refresh a materialized view in PostgreSQL without locking out readers?

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name; (requires a unique index on the materialized view).

What are procedural extensions to SQL, and give two examples by vendor.

Procedural extensions add imperative programming constructs (variables, loops, conditionals, error handling) to declarative SQL. Examples: Oracle PL/SQL, SQL Server T-SQL, PostgreSQL PL/pgSQL.

What is the difference between a stored procedure and a user-defined function?

A function must return a value and is usually callable within a query (often no side effects). A stored procedure is invoked with CALL/EXEC, can perform actions and return zero or many result sets, and typically cannot be embedded in a SELECT.

What are the three common parameter modes in procedural SQL, and what does each do?

IN passes a value into the routine (read-only), OUT returns a value back to the caller, and IN OUT (INOUT) passes a value in and returns a possibly modified value out.

By what two mechanisms can a routine return values to its caller?

Via a RETURN statement/value (functions and scalar returns) or via OUT / INOUT parameters. Procedures may also return result sets. RETURN also immediately exits the routine.