-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path03-sql.html
More file actions
432 lines (412 loc) · 35.5 KB
/
Copy path03-sql.html
File metadata and controls
432 lines (412 loc) · 35.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
<script>try{var t=localStorage.getItem("dbms-theme")||"dark";document.documentElement.setAttribute("data-theme",t)}catch(e){document.documentElement.setAttribute("data-theme","dark")}</script>
<title>SQL Fundamentals — DBMS Illustrated</title>
<link rel="stylesheet" href="../css/style.css">
<style>:root{--topic-color:#10B981}</style>
</head>
<body>
<div class="reading-progress" id="reading-progress"></div>
<nav class="navbar">
<div class="navbar-inner">
<a class="navbar-brand" href="../index.html"><span class="brand-icon" style="color:var(--primary-soft)">◆</span>DBMS Illustrated</a>
<div class="navbar-links"><a href="../index.html#topics">All Topics</a></div>
<div class="navbar-actions"><button class="btn-icon" id="theme-toggle" title="Toggle theme">☼</button></div>
</div>
</nav>
<div class="container">
<div class="breadcrumb">
<a href="../index.html">Home</a><span class="breadcrumb-sep">›</span>
<a href="../index.html#topics">Course</a><span class="breadcrumb-sep">›</span>
<span>SQL Fundamentals</span>
</div>
<div class="topic-header">
<div class="topic-badge">Topic 03</div>
<h1>SQL <span class="accent">Fundamentals</span></h1>
<p class="subtitle">SQL is the language you use to ask questions of a database. Think of it like talking to a very precise librarian who can fetch any combination of records, sort them, group them, or summarize them — as long as you phrase your question correctly. This topic covers how to write those questions well.</p>
<div class="company-badges">
<span class="badge">PostgreSQL</span><span class="badge">MySQL</span><span class="badge">SQLite</span>
<span class="badge">BigQuery</span><span class="badge">Snowflake</span><span class="badge">DuckDB</span>
</div>
</div>
<div class="section-label">Core Concepts</div>
<div class="mini-cards">
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>SELECT Pipeline</h4>
<p>A SQL query is not run in the order you write it. The database first figures out which table to read (FROM), then filters rows (WHERE), then groups and counts (GROUP BY), then filters groups (HAVING), then selects the columns you want (SELECT), then sorts (ORDER BY), then limits the output (LIMIT). This is the logical execution order — and it explains why you cannot use a column alias from SELECT inside a WHERE clause. The WHERE runs before SELECT, so the alias does not exist yet.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Window Functions</h4>
<p>Imagine you want each employee's salary AND the average salary for their department in the same row. GROUP BY cannot do this — it collapses all employees into one row per department. Window functions solve this: they calculate across a group of rows but keep every individual row intact. Think of PARTITION BY as "draw a box around each department," and ORDER BY as "rank people within that box." The result: every employee's row now also includes a department average, a rank, or a running total.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>CTEs (WITH clause)</h4>
<p>A CTE (Common Table Expression) is a way to give a subquery a name and define it at the top of your query, before the main SELECT. Think of it like assigning a variable in code — instead of burying a complex subquery inside your SELECT, you write it once at the top with a clear name and then use that name in the query below. This makes complex queries much easier to read. CTEs also unlock recursive queries, which can walk through tree structures like an org chart or a set of folder paths.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>EXPLAIN / EXPLAIN ANALYZE</h4>
<p>When a query is slow, you need to see what the database is actually doing. EXPLAIN shows the plan the database intends to follow — like the blueprint before the building. EXPLAIN ANALYZE actually runs the query and shows what really happened: how many rows were read, how long each step took, and whether the database's guesses about row counts were accurate. This is the most important tool for diagnosing why a query is slow.</p>
</div>
</div>
<div class="section-label">How It Works</div>
<h2 class="section-title">Logical Query Execution Order</h2>
<div class="steps">
<div class="step-item"><div class="step-num">1</div><div><h4>FROM + JOIN</h4><p>The very first thing the database does is figure out which tables to read and stitch them together. All the joins happen here, creating one big combined table in memory. This is why you can reference tables from a JOIN in your WHERE clause — they already exist. But a column alias you define in SELECT does not exist yet — SELECT has not happened yet.</p></div></div>
<div class="step-item"><div class="step-num">2</div><div><h4>WHERE</h4><p>Now the database goes through every row and throws out ones that do not match your filter condition. The sooner you filter, the less work the later steps have to do. This is the most impactful place to speed up a query — a good WHERE clause with a good index can reduce 10 million rows to 100 before any grouping happens. Note: you cannot filter on counts or averages here — that is what HAVING is for.</p></div></div>
<div class="step-item"><div class="step-num">3</div><div><h4>GROUP BY + Aggregates</h4><p>When you want totals, counts, or averages — this is the step where rows get bucketed into groups. All employees in the same department become one group. Then COUNT(*), SUM(salary), AVG(salary) and similar functions calculate one result per group. In SELECT, every column must either be part of the grouping or be inside an aggregate — otherwise the database would not know which row's value to show.</p></div></div>
<div class="step-item"><div class="step-num">4</div><div><h4>HAVING</h4><p>WHERE filters individual rows. HAVING filters groups — it runs after GROUP BY, so it can see the results of aggregations. For example, "show me only departments where the average salary is above $70,000" — that average does not exist until after GROUP BY, so it belongs in HAVING, not WHERE.</p></div></div>
<div class="step-item"><div class="step-num">5</div><div><h4>SELECT + Window Functions</h4><p>Only now does the database pick which columns to include in the result. Window functions also run at this stage — they can see all the grouped data and add things like "rank within group" or "running total" to each row without collapsing them into fewer rows. This is why window functions are so powerful: they add context to each row without losing the row.</p></div></div>
<div class="step-item"><div class="step-num">6</div><div><h4>ORDER BY + LIMIT</h4><p>Finally, the result is sorted and trimmed to the requested size. LIMIT without ORDER BY is dangerous — the database picks rows in whatever order is convenient, which can change between runs. Always pair LIMIT with ORDER BY if you care about which rows you get. And if you are paginating through many pages, avoid skipping thousands of rows — that is slow. Instead, remember the last ID you saw and filter from there next time.</p></div></div>
</div>
<div class="section-label">JOIN Types</div>
<div class="comparison-grid">
<div class="comparison-card">
<h4>INNER JOIN</h4>
<ul>
<li>Only matching rows from both tables</li>
<li>Drops non-matching rows silently</li>
<li>Equivalent to intersection on join key</li>
<li>Most common join type in OLTP queries</li>
</ul>
</div>
<div class="comparison-card">
<h4>LEFT JOIN</h4>
<ul>
<li>All rows from left, matching from right</li>
<li>NULLs for right cols when no match</li>
<li>Use for optional relationships</li>
<li>More common than RIGHT JOIN</li>
</ul>
</div>
<div class="comparison-card">
<h4>FULL OUTER JOIN</h4>
<ul>
<li>All rows from both tables</li>
<li>NULLs where no match on either side</li>
<li>Good for reconciliation queries</li>
<li>Expensive — full scan of both sides</li>
</ul>
</div>
<div class="comparison-card">
<h4>SELF JOIN</h4>
<ul>
<li>Table joined to itself via aliases</li>
<li>Classic use: hierarchies (employee/manager)</li>
<li>Also for finding pairs within the same table</li>
<li>Same as any join — one table used twice</li>
</ul>
</div>
</div>
<div class="section-label">Subqueries vs CTEs</div>
<h2 class="section-title">When to Use Which</h2>
<div class="comparison-grid">
<div class="comparison-card">
<h4>Correlated Subquery</h4>
<ul>
<li>References outer query columns</li>
<li>Re-executes once per outer row — O(n)</li>
<li>Use: EXISTS checks, scalar lookups per row</li>
<li>Optimizer may rewrite as JOIN automatically</li>
</ul>
</div>
<div class="comparison-card">
<h4>Uncorrelated Subquery</h4>
<ul>
<li>Self-contained, runs once</li>
<li>Result cached and reused</li>
<li>Use: filtering against a derived set</li>
<li>Example: WHERE id IN (SELECT id FROM ...)</li>
</ul>
</div>
<div class="comparison-card">
<h4>CTE (WITH clause)</h4>
<ul>
<li>Named, readable, defined before main query</li>
<li>Can be referenced multiple times</li>
<li>Supports WITH RECURSIVE for trees and graphs</li>
<li>PostgreSQL: optimization fence (may inhibit pushdown)</li>
</ul>
</div>
<div class="comparison-card">
<h4>Derived Table (inline view)</h4>
<ul>
<li>Subquery in FROM clause</li>
<li>Not named — one-time use</li>
<li>Optimizer can often merge with outer query</li>
<li>Use: simple one-off transformations</li>
</ul>
</div>
</div>
<div class="code-block">
<div class="cb-header"><span class="cb-lang">SQL — Window Functions and CTEs</span></div>
<pre><span class="cmt">Running salary total per department, rank within each dept</span>
<span class="kw">SELECT</span>
emp_id,
name,
dept,
salary,
<span class="fn">SUM</span>(salary) <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> dept <span class="kw">ORDER BY</span> hire_date) <span class="kw">AS</span> running_total,
<span class="fn">RANK</span>() <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> dept <span class="kw">ORDER BY</span> salary <span class="kw">DESC</span>) <span class="kw">AS</span> salary_rank,
<span class="fn">LAG</span>(salary,<span class="num">1</span>) <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> dept <span class="kw">ORDER BY</span> hire_date) <span class="kw">AS</span> prev_salary,
salary - <span class="fn">AVG</span>(salary) <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> dept) <span class="kw">AS</span> diff_from_avg
<span class="kw">FROM</span> employees;
<span class="cmt">CTE + recursive (org chart — depth-first traversal)</span>
<span class="kw">WITH RECURSIVE</span> org <span class="kw">AS</span> (
<span class="kw">SELECT</span> emp_id, name, manager_id, <span class="num">0</span> <span class="kw">AS</span> depth
<span class="kw">FROM</span> employees <span class="kw">WHERE</span> manager_id <span class="kw">IS NULL</span> <span class="cmt">anchor: root</span>
<span class="kw">UNION ALL</span>
<span class="kw">SELECT</span> e.emp_id, e.name, e.manager_id, o.depth + <span class="num">1</span>
<span class="kw">FROM</span> employees e
<span class="kw">JOIN</span> org o <span class="kw">ON</span> e.manager_id = o.emp_id <span class="cmt">recursive step</span>
)
<span class="kw">SELECT</span> <span class="fn">repeat</span>(<span class="str">' '</span>, depth) || name <span class="kw">AS</span> org_tree, depth
<span class="kw">FROM</span> org <span class="kw">ORDER BY</span> depth, name;
<span class="cmt">EXISTS: customers who made a high-value order (correlated)</span>
<span class="kw">SELECT</span> * <span class="kw">FROM</span> customers c
<span class="kw">WHERE EXISTS</span> (
<span class="kw">SELECT</span> <span class="num">1</span> <span class="kw">FROM</span> orders o
<span class="kw">WHERE</span> o.customer_id = c.id <span class="cmt">references outer query: correlated</span>
<span class="kw">AND</span> o.total > <span class="num">1000</span>
);
<span class="cmt">Keyset pagination: O(log n) at any page depth</span>
<span class="kw">SELECT</span> id, name, created_at
<span class="kw">FROM</span> orders
<span class="kw">WHERE</span> id > :last_seen_id <span class="cmt">index seek</span>
<span class="kw">ORDER BY</span> id
<span class="kw">LIMIT</span> <span class="num">20</span>;</pre>
</div>
<div class="at-a-glance reveal">
<h3>At a Glance</h3>
<ul>
<li><strong>SQL has three jobs:</strong> DDL defines the shape of your tables (CREATE, ALTER). DML reads and writes data (SELECT, INSERT, UPDATE, DELETE). DCL controls who can do what (GRANT, REVOKE).</li>
<li><strong>Window functions</strong> (ROW_NUMBER, RANK, LAG) let you rank or compare rows within a group without losing the individual rows. GROUP BY collapses rows; window functions keep them.</li>
<li><strong>EXPLAIN ANALYZE</strong> shows you exactly what the database did to answer your query. If a query is slow, run this first — it shows where the time went and whether an index was used.</li>
<li><strong>CTEs (WITH clause)</strong> let you name a subquery and reuse it. WITH RECURSIVE lets you walk tree structures like org charts or category hierarchies.</li>
<li><strong>Avoid OFFSET for large tables.</strong> Skipping 10,000 rows to show "page 500" makes the database read all those rows just to throw them away. Instead, track the last ID you saw and filter from there.</li>
</ul>
</div>
<div class="section-label">Reading EXPLAIN Output</div>
<h2 class="section-title">Diagnosing Slow Queries</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Read Bottom-Up</h4>
<p>The EXPLAIN output is a tree structure. The deepest, most-indented lines are the first steps — usually reading from a table or using an index. The top line is the last step — returning the final result. Execution flows from the bottom up. When looking for where time is being wasted, start at the bottom and look for steps that read many more rows than expected, or that have a long execution time.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Compare Estimated vs Actual Rows</h4>
<p>EXPLAIN ANALYZE shows two numbers for each step: how many rows the database guessed it would process (estimated), and how many it actually processed. If the estimate is 100 but the actual is 95,000, the database was wildly wrong — and it probably made bad decisions because of that. The fix is to update the statistics the database uses to make estimates, by running ANALYZE on the table. Fresh statistics help the optimizer make better plans.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Spot Seq Scans on Large Tables</h4>
<p>A "Seq Scan" means the database is reading every single row in the table from start to finish — like reading an entire book to find one sentence. On a small table this is fine. On a table with millions of rows, this is slow. If you see a Seq Scan on a large table, it usually means there is no index for the column in your WHERE clause, or you are using a function on the column that prevents the index from being used. Adding an appropriate index typically fixes this.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>Check Join Types</h4>
<p>The database has a few strategies for joining two tables. A Hash Join is good when both tables are large — it builds an in-memory hash table and looks things up quickly. A Nested Loop join is efficient when one side is small and there is an index on the join column. If you see a Nested Loop join between two large tables with no index, that is usually the source of a slow query. Adding an index on the join column is the fix.</p>
</div>
</div>
</div>
<div class="diagram-box">
<svg viewBox="0 0 700 200" role="img" aria-label="SQL query execution pipeline: parse, rewrite, optimize, execute, return">
<!-- Pipeline boxes -->
<rect x="20" y="76" width="100" height="48" rx="8" class="svg-box-c"/>
<text x="70" y="96" text-anchor="middle" class="svg-label">PARSE</text>
<text x="70" y="113" text-anchor="middle" class="svg-soft">SQL to AST</text>
<rect x="150" y="76" width="100" height="48" rx="8" class="svg-box-p"/>
<text x="200" y="96" text-anchor="middle" class="svg-label">REWRITE</text>
<text x="200" y="113" text-anchor="middle" class="svg-soft">View expand</text>
<rect x="280" y="76" width="100" height="48" rx="8" class="svg-box-p"/>
<text x="330" y="96" text-anchor="middle" class="svg-label">OPTIMIZE</text>
<text x="330" y="113" text-anchor="middle" class="svg-soft">Cost-based plan</text>
<rect x="410" y="76" width="100" height="48" rx="8" class="svg-box-a"/>
<text x="460" y="96" text-anchor="middle" class="svg-label">EXECUTE</text>
<text x="460" y="113" text-anchor="middle" class="svg-soft">Volcano model</text>
<rect x="540" y="76" width="120" height="48" rx="8" class="svg-box-g"/>
<text x="600" y="96" text-anchor="middle" class="svg-label">RESULT</text>
<text x="600" y="113" text-anchor="middle" class="svg-soft">Rows to client</text>
<!-- Query text at top -->
<text x="350" y="38" text-anchor="middle" class="svg-label" style="fill:#8B5CF6">SELECT name, dept FROM employees WHERE salary > 80000</text>
<line x1="350" y1="44" x2="350" y2="76" class="svg-line" style="stroke:#8B5CF6"/>
<!-- Connecting lines -->
<line x1="120" y1="100" x2="150" y2="100" class="svg-line"/>
<line x1="250" y1="100" x2="280" y2="100" class="svg-line"/>
<line x1="380" y1="100" x2="410" y2="100" class="svg-line"/>
<line x1="510" y1="100" x2="540" y2="100" class="svg-line"/>
<!-- Catalog reference -->
<text x="330" y="165" text-anchor="middle" class="svg-soft">Statistics Catalog</text>
<rect x="270" y="152" width="120" height="22" rx="5" class="svg-box"/>
<line x1="330" y1="124" x2="330" y2="152" class="svg-line svg-dash" style="stroke:#F59E0B"/>
<!-- Animated packets -->
<circle class="pkt" cx="120" cy="100" r="5" fill="#0EA5E9"/>
<circle class="pkt" cx="250" cy="100" r="5" fill="#7C3AED"/>
<circle class="pkt" cx="380" cy="100" r="5" fill="#F59E0B"/>
<circle class="pkt" cx="510" cy="100" r="5" fill="#10B981"/>
</svg>
<p class="diagram-caption">Every SQL statement passes through five stages before returning rows. The optimizer consults the statistics catalog — row counts, histograms, index metadata — to estimate the cheapest plan.</p>
</div>
<div class="demo-section" id="demo-sql">
<div class="demo-header">
<h3>Live SQL Filter</h3>
<div class="demo-controls">
<button class="demo-btn" data-preset="0">WHERE dept='Engineering'</button>
<button class="demo-btn" data-preset="1">WHERE salary>80000</button>
<button class="demo-btn" data-preset="2">OR clause</button>
<button class="demo-btn" data-preset="3">ORDER BY salary</button>
</div>
</div>
<div class="demo-canvas-wrap"></div>
<div class="demo-input-row">
<input class="demo-input" type="text" placeholder="SELECT * FROM employees WHERE ...">
<button class="demo-btn" data-action="run-sql" style="background:var(--topic-color);color:#fff;border-color:var(--topic-color)">Run</button>
</div>
<div class="demo-hint">Rows animate one-by-one as the engine scans. Green = matches predicate.</div>
</div>
<div class="section-label">Anti-patterns</div>
<div class="antipatterns">
<div class="antipattern">
<h4>SELECT * in production queries</h4>
<p>Fetches all columns including BLOBs and unused fields. Breaks when columns are added or removed. The optimizer can use covering indexes only if it knows which columns you need. Always list explicit columns.</p>
</div>
<div class="antipattern">
<h4>The N+1 query problem</h4>
<p>Fetching N rows then running one query per row in application code. Replace with a single JOIN or a batch IN clause. N+1 is almost always the top performance issue in ORMs — add ORM query logging to detect it.</p>
</div>
<div class="antipattern">
<h4>Using OFFSET for pagination on large tables</h4>
<p>OFFSET N scans and discards N rows every time. For page 1000 with 20 rows/page, you discard 20,000 rows. Use keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT 20.</p>
</div>
<div class="antipattern">
<h4>Implicit type conversions in WHERE</h4>
<p>WHERE user_id = '123' when user_id is INT causes a cast on every row, disabling index usage. Match parameter types to column types exactly — use bind parameters with the correct types in your ORM or driver.</p>
</div>
<div class="antipattern">
<h4>Functions on indexed columns in WHERE</h4>
<p>WHERE UPPER(email) = 'FOO@EXAMPLE.COM' prevents index usage on the email column. The index stores original values. Use expression indexes (<code>CREATE INDEX ON users (UPPER(email))</code>) or store data in the correct case to begin with.</p>
</div>
</div>
<div class="section-label">Quiz</div>
<div class="quiz-section">
<div class="quiz-question">
<div class="quiz-q-num">Question 1 of 5</div>
<div class="quiz-q-text">Which SQL clause filters results AFTER GROUP BY (on aggregated values)?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="WHERE filters before GROUP BY and cannot reference aggregate results."><span class="opt-letter">A</span>WHERE</div>
<div class="quiz-option" data-correct="false" data-explanation="GROUP BY groups rows; it does not filter them."><span class="opt-letter">B</span>GROUP BY</div>
<div class="quiz-option" data-correct="true" data-explanation="HAVING filters grouped results. Example: HAVING COUNT(*) > 5 keeps only groups with more than 5 rows."><span class="opt-letter">C</span>HAVING</div>
<div class="quiz-option" data-correct="false" data-explanation="ORDER BY sorts the final result set; it does not filter."><span class="opt-letter">D</span>ORDER BY</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 2 of 5</div>
<div class="quiz-q-text">A correlated subquery differs from a regular subquery in that it:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="true" data-explanation="A correlated subquery references a column from the outer query, forcing re-execution for each outer row. This makes it O(n) subquery executions vs one for a regular subquery."><span class="opt-letter">A</span>References a column from the outer query, re-executing per outer row</div>
<div class="quiz-option" data-correct="false" data-explanation="Regular subqueries run once and cache. Correlated ones re-run each time."><span class="opt-letter">B</span>Runs once and its result is cached</div>
<div class="quiz-option" data-correct="false" data-explanation="A correlated subquery is typically less efficient than a JOIN for large datasets."><span class="opt-letter">C</span>Is always faster than an equivalent JOIN</div>
<div class="quiz-option" data-correct="false" data-explanation="Correlated subqueries can return multiple rows (with EXISTS) or single values."><span class="opt-letter">D</span>Cannot return multiple rows</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 3 of 5</div>
<div class="quiz-q-text">EXPLAIN ANALYZE in PostgreSQL differs from EXPLAIN in that it:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="EXPLAIN shows estimated costs; EXPLAIN ANALYZE shows actual execution."><span class="opt-letter">A</span>Only shows estimated costs, not actual execution</div>
<div class="quiz-option" data-correct="true" data-explanation="EXPLAIN ANALYZE actually executes the query and shows actual row counts, execution times, and loop counts — vs EXPLAIN which only estimates."><span class="opt-letter">B</span>Actually executes the query and shows real vs estimated statistics</div>
<div class="quiz-option" data-correct="false" data-explanation="EXPLAIN ANALYZE executes fully; wrap in a transaction and rollback if you do not want side effects on DML."><span class="opt-letter">C</span>Runs the query but discards result rows to avoid side effects</div>
<div class="quiz-option" data-correct="false" data-explanation="EXPLAIN ANALYZE does not modify the query plan; it reveals what the planner chose."><span class="opt-letter">D</span>Forces the optimizer to choose a different plan</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 4 of 5</div>
<div class="quiz-q-text">What is the difference between RANK() and DENSE_RANK() window functions?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Both return numbers starting from 1 within a partition."><span class="opt-letter">A</span>RANK() starts from 0; DENSE_RANK() starts from 1</div>
<div class="quiz-option" data-correct="true" data-explanation="If two rows tie for rank 2, RANK() gives both rank 2 and skips rank 3 (next row gets 4). DENSE_RANK() gives both rank 2 and the next row gets rank 3 — no gaps."><span class="opt-letter">B</span>RANK() leaves gaps after ties; DENSE_RANK() does not leave gaps</div>
<div class="quiz-option" data-correct="false" data-explanation="Both work with PARTITION BY."><span class="opt-letter">C</span>DENSE_RANK() cannot use PARTITION BY</div>
<div class="quiz-option" data-correct="false" data-explanation="Both handle ties — they just handle the numbering after ties differently."><span class="opt-letter">D</span>RANK() handles ties; DENSE_RANK() does not</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 5 of 5</div>
<div class="quiz-q-text">Which statement about WITH RECURSIVE CTEs is correct?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="WITH RECURSIVE is for graphs and trees — not aggregations."><span class="opt-letter">A</span>WITH RECURSIVE is only useful for aggregations</div>
<div class="quiz-option" data-correct="false" data-explanation="WITH RECURSIVE terminates when the recursive step returns no rows — it has a natural termination."><span class="opt-letter">B</span>WITH RECURSIVE runs indefinitely unless you add LIMIT</div>
<div class="quiz-option" data-correct="true" data-explanation="WITH RECURSIVE consists of an anchor query (base case) joined via UNION ALL to a recursive step that references the CTE itself. It terminates when the recursive step returns zero rows."><span class="opt-letter">C</span>It has an anchor query and a recursive step; terminates when recursive step returns no rows</div>
<div class="quiz-option" data-correct="false" data-explanation="PostgreSQL, MySQL 8+, SQL Server, and others all support WITH RECURSIVE."><span class="opt-letter">D</span>WITH RECURSIVE is not supported by standard SQL databases</div>
</div>
<div class="quiz-feedback"></div>
</div>
</div>
<div class="section-label">Interview Q&A</div>
<div class="qa-section">
<div class="qa-item">
<div class="qa-q">What is the difference between WHERE and HAVING?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">WHERE filters rows <em>before</em> they are grouped. It can reference only raw column values, not aggregate functions. HAVING filters groups <em>after</em> GROUP BY aggregation. It can reference aggregate expressions like <code>COUNT(*)</code> or <code>AVG(salary)</code>. A common mistake: <code>WHERE COUNT(*) > 5</code> fails because WHERE runs before aggregation. The correct form is <code>HAVING COUNT(*) > 5</code>. Performance tip: push filters into WHERE whenever possible so fewer rows are grouped.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Explain window functions and when you would use them instead of GROUP BY.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Window functions compute a value across a set of rows related to the current row, without collapsing them. Syntax: <code>AGG() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame)</code>. Use window functions when you need both individual row data AND aggregate data in the same result — e.g., each employee's salary alongside the department average. GROUP BY collapses to one row per group; window functions keep all rows. Common functions: ROW_NUMBER (unique sequential), RANK (gaps on ties), DENSE_RANK (no gaps), LAG/LEAD (prev/next row value), SUM/AVG OVER (running totals).</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is a CTE and how does it differ from a subquery?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A CTE (Common Table Expression) is a named temporary result set defined with <code>WITH name AS (...)</code> before the main query. Key differences from subqueries: (1) CTEs can be referenced multiple times in the same query without re-execution (in most DBMS). (2) CTEs support recursion (<code>WITH RECURSIVE</code>) for hierarchical data — impossible with regular subqueries. (3) CTEs improve readability for complex queries. (4) In PostgreSQL, CTEs are optimization fences (cannot be pushed into or merged with the outer query) — this can hurt performance. Use <code>WITH ... AS MATERIALIZED/NOT MATERIALIZED</code> to control this behavior in PG 12+.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">How do you solve the N+1 query problem?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">N+1 occurs when you fetch N rows and then execute one query per row to fetch related data (1 + N queries total). Solutions: (1) <strong>JOIN</strong> — fetch parent and children in one query. (2) <strong>IN clause</strong> — fetch all parents, collect IDs, do one <code>WHERE parent_id IN (...)</code> query for children. (3) <strong>Eager loading</strong> in ORMs (e.g., <code>includes</code> in ActiveRecord, <code>joinedload</code> in SQLAlchemy). (4) For very large ID lists, a JOIN is usually faster than a big IN clause. Always verify with EXPLAIN ANALYZE and ORM debug logging.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is keyset pagination and why is it better than OFFSET?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">OFFSET n skips n rows, which requires scanning and discarding them. On page 1000 of 20 rows/page you throw away 20,000 rows — O(n) cost that grows with page depth. Keyset pagination uses the last-seen value: <code>WHERE id > :last_id ORDER BY id LIMIT 20</code>. The index seeks directly to last_id — O(log n) cost regardless of page depth. Limitation: you cannot jump to an arbitrary page number — only prev/next. For most feeds and lists this is fine. For UIs needing "go to page X" use hybrid approaches or accept OFFSET's cost for the rare arbitrary-page case.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is a non-sargable predicate?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">SARG = Search ARGument able. A sargable predicate allows the optimizer to use a B+tree index to seek directly to matching rows. Non-sargable predicates force a full scan. Common non-sargable patterns: (1) Function on column — <code>WHERE YEAR(order_date) = 2024</code> — rewrite as <code>WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'</code>. (2) Leading wildcard — <code>WHERE name LIKE '%smith'</code> forces a full scan unless a GIN/trigram index exists. (3) Arithmetic — <code>WHERE price * 1.1 > 100</code> — rewrite as <code>WHERE price > 90.9</code>. The rule: keep indexed columns bare (unmodified) on the left side of the predicate.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Explain SQL's three-valued logic and how NULL affects queries.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL returns UNKNOWN, not TRUE or FALSE. UNKNOWN in a WHERE clause means the row is filtered out. Key consequences: (1) <code>NULL = NULL</code> is UNKNOWN — use IS NULL / IS NOT NULL. (2) NOT IN with a list containing NULL returns no rows (UNKNOWN propagates). (3) COUNT(*) counts all rows; COUNT(col) ignores NULLs. (4) COALESCE(val, default) is the standard NULL replacement. Always be explicit about NULL handling — it is the most common source of subtle bugs in SQL.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">How do you write a query to find the second-highest salary?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Multiple approaches: (1) <strong>DENSE_RANK window function</strong> (best): <code>SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) sub WHERE rnk = 2</code>. (2) <strong>Subquery</strong>: <code>SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)</code>. (3) <strong>OFFSET</strong>: <code>SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1</code>. The DENSE_RANK approach is most general and handles ties correctly — multiple employees with the same salary all rank the same. The subquery approach is intuitive but requires a nested scan.</div></div>
</div>
</div>
<div class="section-label">Further Reading</div>
<div class="reading-links">
<a class="reading-link" href="https://use-the-index-luke.com/sql/explain-plan" target="_blank">Reading EXPLAIN Plans</a>
<a class="reading-link" href="https://www.postgresql.org/docs/current/functions-window.html" target="_blank">PostgreSQL Window Functions</a>
<a class="reading-link" href="https://modern-sql.com/" target="_blank">Modern SQL</a>
<a class="reading-link" href="https://explain.depesz.com/" target="_blank">Explain Depesz Visualizer</a>
</div>
<div class="topic-nav">
<a href="02-relational-model.html" class="topic-nav-link"><div class="topic-nav-arrow">←</div><div><div class="topic-nav-label">Previous</div><div class="topic-nav-title">Relational Model</div></div></a>
<a href="04-normalization.html" class="topic-nav-link next"><div class="topic-nav-arrow">→</div><div><div class="topic-nav-label">Next</div><div class="topic-nav-title">Normalization</div></div></a>
</div>
</div>
<script src="../js/main.js"></script>
<script src="../js/demos.js"></script>
</body>
</html>