Learning SQL the Hard Way (On Purpose): Notes from CS50 + Practice

CS50 SQL — Lecture Notes + Mini Query Cheatsheet

Last updated: 2025-10-14

These notes summarize key topics from CS50’s Introduction to Databases with SQL and pair each topic with a tiny, runnable query.
The examples assume a sample table longlist with columns like "title", "author", "year", "format", "translator", "rating", "votes", and "pages".

---

1) Foundations (DB vs Spreadsheet, CRUD, SQL Dialects)

• Idea: Databases enable reliable Create, Read, Update, Delete at scale; SQL has vendor-specific dialects (SQLite, PostgreSQL, MySQL).
• Try: a trivial query to confirm your SQL engine is alive.

-- Portable sanity check
SELECT 1 AS one;

---

2) Tools & Setup (sqlite3, VS Code, leaving the shell)

• Idea: Use the sqlite3 CLI or a client; exit with .quit (SQLite).
• Try: Check your SQLite version (replace with SELECT version(); on Postgres).

-- SQLite
SELECT sqlite_version() AS "sqlite version";

---

3) SQL Basics (SELECT columns, identifiers vs strings)

• Idea: Double-quotes for identifiers (tables/columns), single-quotes for string literals.
• Try: Select specific columns.

SELECT "title", "author"
FROM "longlist";

---

4) Peeking at Data (LIMIT)

• Idea: Preview a small slice.
• Try:

SELECT "title"
FROM "longlist"
LIMIT 5;

---

5) Filtering Rows (WHERE, AND/OR/NOT, NULLs, case rules)

• Idea: Use comparisons and logical operators; LIKE is case‑insensitive in SQLite, = is case‑sensitive for strings.
• Try A: Exact year

SELECT "title"
FROM "longlist"
WHERE "year" = 2023;

• Try B: Combined conditions

SELECT "title", "format"
FROM "longlist"
WHERE ("year" = 2022 OR "year" = 2023)
  AND "format" <> 'hardcover';

• Try C: NULL checks

SELECT "title"
FROM "longlist"
WHERE "translator" IS NULL;

---

6) Pattern Matching (LIKE with % and _)

• Idea: % = any string; _ = any single char.
• Try A: Word anywhere in title

SELECT "title"
FROM "longlist"
WHERE "title" LIKE '%love%';

• Try B: Unknown character

SELECT "title"
FROM "longlist"
WHERE "title" LIKE 'P_re';

---

7) Ranges (>, <, >=, <=, BETWEEN)

• Idea: Prefer concise range syntax.
• Try:

SELECT "title", "year"
FROM "longlist"
WHERE "year" BETWEEN 2019 AND 2022;

---

8) Sorting (ORDER BY ASC/DESC, multi-column tiebreaks)

• Idea: Default is ASC; add DESC; chain multiple columns.
• Try:

SELECT "title", "rating", "votes"
FROM "longlist"
ORDER BY "rating" DESC, "votes" DESC
LIMIT 10;

---

9) Aggregations (AVG, MIN, MAX, SUM, COUNT) + ROUND + AS

• Idea: Aggregate rows to one value; alias for readability.
• Try:

SELECT ROUND(AVG("rating"), 2) AS "average rating"
FROM "longlist";

---

10) Distinct Values (DISTINCT, COUNT DISTINCT)

• Idea: De-duplicate before counting.
• Try:

SELECT COUNT(DISTINCT "publisher") AS "publisher_count"
FROM "longlist";

---

11) Data Types & Style Notes (readability, comparisons)

• Idea: Uppercase SQL keywords; be mindful of type + case rules.
• Try (case behavior demo in SQLite):

-- Case-sensitive equality (likely returns 0 rows if stored as 'Pyre')
SELECT "title"
FROM "longlist"
WHERE "title" = 'pyre';

-- Case-insensitive LIKE
SELECT "title"
FROM "longlist"
WHERE "title" LIKE 'pyre';

---

12) Next Steps (Inspect schema; moving to multi-table models)

• Idea: Learn table shapes, then model relations (authors, books, publishers).
• Try (SQLite schema peek):

PRAGMA table_info("longlist");

• Try (PostgreSQL variant):

-- PostgreSQL: view columns for a table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'longlist';

---

Notes

• Replace "longlist"/column names with your real schema.
• For PostgreSQL, prefer single-quoted identifiers only when you’ve created them quoted; otherwise use unquoted snake_case identifiers.

Happy querying!