SQL / CAPSTONE PROJECTS
Where to go next as a SQL developer
Plan your next stage as a SQL developer: separate portable relational semantics from vendor syntax, and settle behaviour questions with the engine itself.
What you will learn
- Tell portable relational semantics apart from vendor-specific syntax
- Settle a behaviour question with a four-row scratch table instead of guessing
- Query information_schema to audit nullability, keys and types in your schemas
- Name your next depth topics: isolation levels, planner behaviour, recursive queries
Understanding Where to go next as a SQL developer
After 143 lessons you know a language and one or two implementations of it. Sort that knowledge into two piles: relational semantics (set operations, three-valued logic, join algebra, grouping, transactions, indexes), which is the same everywhere, and surface syntax, which is not: LIMIT versus FETCH FIRST, string_agg versus GROUP_CONCAT versus LISTAGG, INSERT ... ON CONFLICT versus MERGE. Extra study compounds in the first pile; the second is a lookup you do once per engine and forget. The piles do leak into each other, though: MySQL evaluates 5/2 as 2.5 while PostgreSQL truncates it to 2, so a port can change your numbers without raising a single error.
The habit that replaces a tutorial is a two-minute loop: question, four-row table, answer. Window frames, NULL comparison, collation ordering, duplicate handling in UNION - all of these are decidable in seconds if you build the smallest table that contains the interesting case, which usually means one tie, one NULL, or one duplicate. The database also documents itself: information_schema tells you what a column really allows, and EXPLAIN tells you what the planner really chose, and both outrank your memory of what a page said.
For depth, the highest-payoff area is not more syntax but concurrency: a query that is correct when run alone can lose an update or read a row that vanishes when two sessions run it at once, and the fix lives in isolation levels, locking and constraints rather than in the SELECT. After that comes cost-based execution - cardinality estimates, index selectivity, join order - and then the operational side, where a schema change takes locks and a restore you have never rehearsed is not a backup. Set-based reporting features such as window frames, recursive CTEs and GROUPING SETS are worth learning too, but they mostly make you faster, whereas concurrency and operations keep you from being wrong.
-- runs on PostgreSQL 11+ and SQLite 3.28+
CREATE TABLE sales (d date, amt integer);
INSERT INTO sales VALUES
('2024-01-01', 10),
('2024-01-02', 20),
('2024-01-02', 30),
('2024-01-03', 40);
-- one aggregate, two frames: the implicit default, and an explicit ROWS frame
SELECT d,
amt,
SUM(amt) OVER (ORDER BY d) AS range_total,
SUM(amt) OVER (ORDER BY d, amt
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM sales
ORDER BY d, amt;The relational core transfers between engines and the syntax does not, so keep growing by making the database answer your questions instead of memorising dialects.
Worked examples
Ask the catalog what your schema really says
Reading column facts out of information_schema instead of trusting the CREATE TABLE you remember writing.
-- PostgreSQL (also works on MySQL and SQL Server)
CREATE TABLE booking (
id integer PRIMARY KEY,
guest text NOT NULL,
room_id integer NOT NULL,
note text
);
SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'booking'
ORDER BY ordinal_position;Example explained
Line 1id reports is_nullable = NO even though nobody wrote NOT NULL: PRIMARY KEY implies it.
Line 2note is the only YES, and that is the kind of fact worth checking rather than recalling.
Line 3ORDER BY ordinal_position is required because catalog views have no inherent row order.
Line 4information_schema is standard SQL; SQLite has no such schema and exposes the same facts through pragma_table_info('booking').
Generating rows that are not in the data
A recursive CTE builds a date spine so a report shows days with no activity, one entry point into the standard features beyond SELECT, JOIN and GROUP BY.
-- PostgreSQL
CREATE TABLE hits (d date, n integer);
INSERT INTO hits VALUES ('2024-03-01', 4), ('2024-03-03', 7);
WITH RECURSIVE spine(d) AS (
SELECT DATE '2024-03-01'
UNION ALL
SELECT d + 1 FROM spine WHERE d < DATE '2024-03-04'
)
SELECT s.d, COALESCE(h.n, 0) AS n
FROM spine s
LEFT JOIN hits h ON h.d = s.d
ORDER BY s.d;Example explained
Line 1The branch before UNION ALL seeds one row; the branch after it reads only the rows the previous step produced.
Line 2The WHERE d < DATE '2024-03-04' test is the terminating condition - drop it and the engine generates rows until you cancel the query.
Line 3d + 1 is date arithmetic in PostgreSQL; SQLite needs date(d, '+1 day'), which is exactly the syntax-versus-semantics split.
Line 4The LEFT JOIN is what puts 2024-03-02 in the result: no row in hits could ever produce that date.
Important notes
Version decides availability as much as vendor does: window functions arrived in MySQL 8.0 and SQLite 3.25, FETCH FIRST ... WITH TIES in PostgreSQL 13, so check the version before concluding a feature is missing.
A behaviour confirmed on your laptop can differ in production when a setting differs - isolation level, ONLY_FULL_GROUP_BY, collation - so store the settings next to the result you recorded.
Common mistakes
Treating SQL as one language and pasting MySQL answers into PostgreSQL: backticks, GROUP_CONCAT and LIMIT 10, 20 fail loudly, but integer division and looser GROUP BY rules produce different numbers with no error at all.
Collecting features (window functions, CTEs, JSON columns) while never studying transactions, so the first read-then-write race in production silently overwrites someone's update and nothing in the code looks wrong.
Trusting a remembered default instead of testing it: assuming SUM(amt) OVER (ORDER BY d) is a row-by-row running total gives duplicate dates the same total, and the wrong figure reaches a report nobody re-checks.
Try it yourself
Change, predict, then run
In a browser editor, create a five-row table where two rows share the same date, then select SUM(amt) OVER (ORDER BY d) beside SUM(amt) OVER (ORDER BY d, amt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Write one sentence explaining why the two tied rows disagree.
Open the SQL workspaceCheck your understanding
You are moving a working reporting query from MySQL 8 to PostgreSQL 15. Which difference is most likely to reach production undetected?
- GROUP_CONCAT(name), because PostgreSQL calls that aggregate string_agg
- Integer division, because MySQL's / yields a decimal while PostgreSQL truncates integer / integer
- Backtick-quoted identifiers, because PostgreSQL quotes identifiers with double quotes
- The LIMIT 10, 20 offset form, which PostgreSQL does not accept
Show answer
Integer division is the silent one: 5/2 is 2.5 in MySQL and 2 in PostgreSQL, so a rate or per-unit calculation keeps running and simply returns different numbers. GROUP_CONCAT is tempting because it is the most visible difference, but a missing function raises "function does not exist" on the first run, and the same immediate failure applies to backticks and to MySQL's two-argument LIMIT.