SQL Syntax Quick Reference

Reference · Print-friendly

Query Shape

select [distinct] A1, A2, ...        -- projection (keeps dups unless distinct)
from r1 [join type] r2 on pred       -- meaning order: from -> where -> group by -> having -> select
where predicate                      -- written order differs; unknown rows dropped like false
group by A1 [, A2]
having group_predicate               -- filters groups after grouping
order by A [asc|desc] [nulls first|last]

Joins

Form	Behavior
`natural join`	equates ALL shared attributes — risky if schema gains columns
`join ... using (A)`	equates only named attributes
`join ... on pred`	arbitrary predicate; filters before outer-padding
`left/right/full outer join`	pads unmatched rows with nulls

Set Operations

union / intersect / except — deduplicate automatically
union all / intersect all / except all — keep duplicates (bag semantics)
Inputs must be union-compatible: same arity, matching types per position

Null Handling

Comparisons with null yield unknown; test with is null / is not null
Three-valued logic: true and unknown = unknown, false or unknown = unknown
where/having keep only true — unknown is dropped like false
Aggregates except count(*) ignore nulls; sum() of empty/all-null is null, count(*) is 0
coalesce(x, y) — first non-null value

Aggregation

select dept_name, avg(salary)
from instructor
group by dept_name
having avg(salary) > 50000;

Non-aggregated select columns must appear in group by (else error)
Ranking/windowing can be combined with group by: grouping happens first

Subqueries & CTEs

-- membership, comparison, existence, set containment
where name in (select ...)          -- not exists (A except B) = "A contains all of B"
where salary > some/all (select ...)
where exists (select ... )          -- correlated: re-evaluated per outer row

with dept_totals(dept_name, total) as (
  select dept_name, sum(salary) from instructor group by dept_name
)
select * from dept_totals where total > 100000;   -- scope: this query only

DDL Essentials

Statement	Effect
`create table t (... primary key, foreign key ... references ...)`	schema + integrity constraints; violations reject the change
`alter table t add/drop A`	add fills old rows with null; drop column support varies
`drop table t`	deletes data AND schema (`delete from` empties only)
`create index i on t(A)`	physical schema; optional for correctness, speeds queries
`create view v as query`	virtual relation, recomputed at use; `create materialized view` stores it

Integrity Constraints

not null · unique(A1,A2) (nulls allowed) · check(P) (unknown = pass)
foreign key ... references r(...) [on delete cascade | set null]
create assertion A check (db-wide-predicate) — standard, rarely implemented; use triggers

Transactions & Authorization

begin;  -- or set autocommit off
update accounts set bal = bal - 100 where id = 'A';
update accounts set bal = bal + 100 where id = 'B';
commit;            -- or rollback;

grant select, update ON TABLE takes TO user1 [with grant option];
revoke update ON TABLE takes FROM user1 [restrict|cascade];  -- cascade default

Privileges: select / insert / update / delete (+ column scope); roles bundle privileges

Functions, Procedures & Triggers (PSM)

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

create procedure p(in x int, out y int) begin ... end;
call p(5, ?);

-- control flow: begin atomic ... end · while/repeat/for · if/elseif/else
-- declare cond condition; declare exit handler for cond ...; signal cond;

create trigger T after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (select time_slot_id from time_slot))
begin atomic rollback end;

Timing: after validates/logs; before can repair (set nrow.col = ...)
Granularity: for each row (transition vars) vs for each statement (transition tables — after-only)
Disable during bulk load/replication: alter trigger T disable

Recursive Queries

with recursive rec_prereq(course_id, prereq_id) as (
  select course_id, prereq_id from prereq              -- base query
  union
  select rp.course_id, p.prereq_id                     -- recursive step
    from rec_prereq rp, prereq p
    where rp.prereq_id = p.course_id
)
select * from rec_prereq;

Semantics: iterate to fixed point; recursion must be monotonic — no aggregation, not exists, or except over the recursive view

Ranking & Windowing

select ID,
       rank() over (partition by dept_name
                    order by GPA desc nulls last) as dept_rank
from dept_grades
order by dept_name, dept_rank;

-- dense_rank() no gaps · percent_rank() · cume_dist() · row_number() · ntile(4)

select year, avg(num_credits)
       over (order by year rows between 3 preceding and current row)
from tot_credits;                       -- moving average window

Top-N with ties kept: wrap ranked query, filter rank <= N; vendor shortcuts (limit, fetch first, top) break ties arbitrarily

Pivot, Rollup, Cube

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

select item_name, color, sum(quantity)
from sales group by rollup(item_name, color);   -- prefixes: {(i,c),(i),()}
select ... from sales group by cube(a, b, c);   -- all subsets: 8 groupings
select ... from sales group by grouping sets ((a,c),(b,c));  -- exact list

select case when grouping(item_name)=1 then 'all' else item_name end
from sales group by rollup(item_name, color);   -- grouping() flags generated nulls

Covers Silberschatz, Korth & Sudarshan, Database System Concepts, 7th ed., Chapters 3–5 (SQL introduction through advanced SQL). Companion to the DBMS Glossary.

← Lesson 5: Advanced SQL Course Dashboard →