Lesson 5: Advanced SQL

Lesson 0005 · Silberschatz Ch. 5 · ~11 min read

Lessons 3 and 4 covered SQL as a standalone language: queries, joins, views, transactions, constraints. This chapter (the book's Chapter 5) answers a question every working engineer hits in week one on the job: SQL is declarative and powerful, but your application is written in Java, Python, or Go — how do the two worlds connect? From there the chapter climbs through everything SQL gained once it stopped being just a query language: stored procedures and functions, triggers that fire automatically, recursive queries over hierarchies, and the analytics-grade aggregation toolkit (ranking, windowing, pivot, rollup/cube).

The organizing idea: SQL started as a declarative language you talked at, and grew into a programmable environment you build in — while every one of those additions came with an escape hatch back to a general-purpose language when SQL's expressiveness runs out.

Accessing SQL from a Programming Language

Why leave SQL at all? The book gives two reasons. First, SQL does not have the full expressive power of a general-purpose language — some computable queries simply can't be written in it. Second, non-declarative actions (printing a report, talking to a user, feeding a GUI) can't happen inside SQL; applications have many components and querying data is only one of them.

Dynamic SQL won. JDBC is the Java standard, ODBC (and the nearly identical SQL CLI standard) is the C original now wrapped by most languages, and the Python Database API is what psycopg2, MySQLdb, and friends implement. Embedded SQL survives mainly as history and as Microsoft's LINQ, which takes a different route: extending the host language itself rather than preprocessing it.

JDBC in One Pass

1. Connect: DriverManager.getConnection(url, user, passwd) opens a Connection; the URL encodes protocol, server, port, and database, and a vendor driver translates portable calls into product-specific ones.
2. Ship SQL: a Statement object runs executeQuery() (returns a ResultSet) or executeUpdate() (returns a count of affected tuples; zero for DDL).
3. Fetch: rset.next() advances tuple by tuple; getter methods pull attribute values out of the current row.
4. Clean up: connections and statements hold real resources — exhaust them and the database stalls until time-out. Use try-with-resources so exceptions can't leak them.

JDBC also exposes metadata: ResultSetMetaData describes a result's columns (count, names, types), and DatabaseMetaData describes the database itself (tables, keys, supported features) — enough to write schema-agnostic browsers and generic result printers. Large objects come back as locators (getBlob()/getClob()) that stream rather than materialize.

Embedded SQL

In embedded SQL, statements marked EXEC SQL ...; are rewritten by a preprocessor into host-language procedure calls, then compiled normally. Host variables appear inside SQL prefixed with a colon (:name) to distinguish them from SQL identifiers, and multi-row results require declaring a cursor, which you open, then FETCH row by row — the cursor can even update or delete the current row. Embedded SQL's payoff was compile-time checking of types and syntax; its cost was preprocessor complexity and brittle coupling to host-language evolution, which is why virtually all current systems use dynamic SQL instead.

Don't confuse embedded SQL with an embedded database: the latter (SQLite, HSQLDB, Java DB) is a full database engine running inside your application process rather than behind a server.

Functions and Procedures

SQL's procedural extensions (standardized as PSM, Persistent Storage Module) let business logic live inside the database where every application shares one copy and one point of change:

create function dept_count(dept_name varchar(20))
  returns integer
begin
  declare d_count integer;
  select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count.dept_name;
  return d_count;
end

• Table functions return whole relations — select * from table(instructor_of('Finance')). Think of them as parameterized views.
• Procedures use in/out parameters and are invoked with call; functions return values and can be used anywhere an expression is.
• Overloading works: same name is fine if the argument count (or, for functions, a type) differs.
• PSM control flow: begin atomic ... end (compound statement run as one transaction), while/repeat/for loops, if-then-elseif-else, declare ... handler for exceptions (sqlexception, sqlwarning, not found), and signal to raise conditions.

Caveats worth remembering: invoking complex user-defined functions per-tuple has wrecked performance on many systems, and procedural syntax is aggressively nonstandard in the wild (Oracle PL/SQL, SQL Server T-SQL, PostgreSQL PL/pgSQL all diverge from the standard shown here).

External Language Routines

Instead of writing logic in a SQL dialect, you can register procedures written in Java, C, C#, and invoke them from queries and triggers. The safety/performance spectrum: run the routine in a separate process (safe, but interprocess communication costs tens to hundreds of thousands of instructions per call), load it into the database process (fast, but a bug corrupts internals and bypasses access control), or — for safe languages — run it in a sandbox inside the query process, which is what Oracle/DB2 (Java), SQL Server (CLR), and PostgreSQL (Perl/Python/Tcl) actually do.

Triggers

A trigger is a statement the system executes automatically as a side effect of a modification — the event-condition-action model. You specify when it fires (an event plus a condition) and what it does.

create trigger credits_earned after update of takes on grade
referencing new row as nrow
             old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null
     and (orow.grade = 'F' or orow.grade is null)
begin atomic
  update student set tot_cred = tot_cred +
    (select credits from course
     where course.course_id = nrow.course_id)
  where student.id = nrow.id;
end;

• Timing: after fires post-modification (validation, logging); before can repair or reject the change — e.g. converting a blank grade to null via set nrow.grade = null so the insert becomes valid.
• Granularity: for each row iterates affected tuples with transition variables (referencing new row as nrow); for each statement acts once per statement, using transition tables (referencing old/new table as) holding all affected rows. Transition tables can't be used with before triggers.
• Lifecycle: enabled by default; disable with alter trigger ... disable (important during bulk loads and replication, or the triggered action runs twice), drop permanently with drop trigger.

Recursive Queries

Classic problem: given prereq(course_id, prereq_id), find all direct-and-indirect prerequisites of CS-347 — the transitive closure of the relation. The same shape appears everywhere hierarchies live: org charts, bill-of-materials subparts, flight reachability.

The iterative solution uses create temporary table (per-transaction, so concurrent callers don't collide): seed with direct prerequisites, then repeatedly expand one more level, using except against already-found courses both to terminate and to survive cycles.

SQL expresses this directly with with recursive:

with recursive rec_prereq(course_id, prereq_id) as (
  select course_id, prereq_id from prereq
  union
  select rec_prereq.course_id, prereq.prereq_id
    from rec_prereq, prereq
    where rec_prereq.prereq_id = prereq.course_id
)
select * from rec_prereq;

A recursive view must be the union of a base query (nonrecursive) and a recursive query that mentions the view. Its meaning is defined by iteration: compute the base, add the recursive step's results repeatedly, stop when nothing new appears — the final instance is called a fixed point. That definition only makes sense if the recursion is monotonic (more input tuples never shrink the output), which bans three constructs inside the recursive part: aggregation on the recursive view, not exists over it, and except whose right side uses it. Oracle's legacy start with ... connect by prior is the famous nonstandard alternative.

Advanced Aggregation Features

Ranking

select ID, rank() over (order by GPA desc nulls last) as s_rank
from student_grades
order by s_rank;

• rank() gives ties the same rank and leaves gaps (two at rank 1, next gets 3); dense_rank() leaves no gaps. percent_rank(), cume_dist(), and row_number() round out the family.
• partition by ranks within groups (per-department ranks in one pass), and multiple rank expressions coexist in one select.
• Nulls default to highest; say nulls first/nulls last explicitly.
• Top-N: wrap the ranked query and filter where s_rank <= 5 — ties mean you may get six students. System-specific shortcuts (limit n, fetch first, top n) break ties arbitrarily; only ranking honors ties and partitions.
• ntile(n) splits each partition's ordered tuples into n equal buckets — instant quartiles for histograms.

Windowing

select year, avg(num_credits)
       over (order by year rows 3 preceding) as avg_total_credits
from tot_credits;

Window aggregates run over sliding ranges of the sort order: rows 3 preceding, rows unbounded preceding (running totals), between 3 preceding and 2 following, combined freely with partition by. Unlike partitions, windows overlap — a tuple contributes to several windows — which is exactly what moving averages need. range substitutes value-based extents for tuple counts.

Pivoting

A cross-tab turns an attribute's values into columns: from sales(item_name, color, clothes_size, quantity), make dark, pastel, white into attributes:

select * from sales
pivot (sum(quantity) for color in ('dark', 'pastel', 'white'));

Supported directly by SQL Server and Oracle; expressible (clumsily) with basic SQL elsewhere.

Rollup and Cube

Analysts want the same data aggregated many ways at once. group by rollup(item_name, color) generates one grouping per prefix — {(item_name, color), (item_name), ()} — while group by cube(item_name, color, clothes_size) generates all subsets: 8 groupings. Missing attributes appear as null in the unified result, and multiple rollups in one clause multiply (Cartesian product of their grouping sets). For exact control, grouping sets ((color, clothes_size), (clothes_size, item_name)) lists precisely the groupings you want.

One trap: those generated nulls are indistinguishable from real data nulls. The grouping(attr) function returns 1 exactly for rollup/cube-generated nulls — use it (not coalesce, which would also rewrite genuine nulls) to display 'all'.

Notes

Primary source: Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts, 7th ed., Chapter 5: “Advanced SQL.” McGraw-Hill.
Note on sequencing: authorization, integrity constraints, and views belong to the book's Chapter 4 and are covered in Lesson 4; this lesson follows Chapter 5's own arc from application connectivity to analytics SQL.

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this maps onto a SQL whiteboard question.