CS50x - Lecture 7 - SQL

Main ideas and lessons

• SQL introduces a new programming paradigm (declarative vs. procedural)

  • Languages like C and Python are largely procedural: you specify step-by-step instructions (loops, conditionals, etc.).
  • SQL is declarative: you specify what you want (e.g., “select these rows/columns”), and the SQL engine determines how to compute it.
  • Motivation: learning SQL helps you learn future languages and appreciate different paradigms.

• Start with real-world data (CSV from Google Forms)

  • Students vote anonymously via a Google Form (e.g., favorite language and favorite problem set).
  • The instructor then:
    • Views results in a Google Sheet
    • Downloads responses as CSV (Comma-Separated Values)
  • The CSV is treated like a flat file database: one text file storing rows/columns via comma delimiters.
  • Example conceptual layout:
    • Column 0: timestamp
    • Column 1: chosen language
    • Column 2: chosen problem

• Use Python to analyze CSV (foundations before SQL)

  • Reads CSV line-by-line using Python’s CSV library:
    • csv.reader produces lists per row (numeric indexes like [0], [1], [2])
    • csv.DictReader produces dictionaries per row (named keys like "language")
  • Key robustness lesson:
    • List indexing by position (row[1]) can break if the column order changes.
    • Dictionary indexing by column name (row["language"]) is more stable.

• Counting frequencies: multiple ways to get the same result

  • Approach 1: separate variables
    • e.g., python_count, c_count, scratch_count
  • Approach 2: dictionary-based counting with conditionals
    • Initialize missing keys to avoid KeyError, using either:
      • if key in counts: ... else ...
      • if key not in counts: counts[key] = 0 then increment
    • Mentions try/except as an alternative
  • Approach 3: simple dictionary increment pattern
    • Use dictionary keys dynamically and increment counts per row.

• Why SQL helps: replace long analysis code with queries

  • What took many lines in Python becomes much more compact in SQL.
  • Core SQL tools mentioned:
    • SELECT (read data)
    • Aggregate functions: COUNT, COUNT(DISTINCT ...), etc.
    • WHERE (filtering; similar to if conditions)
    • GROUP BY (compute counts per category)
    • ORDER BY (sort results)
    • LIMIT (restrict returned rows)
    • string utilities like LOWER, UPPER, etc. (mentioned broadly)
    • pattern matching using LIKE with % wildcards
  • Example emphasis:
    • “How many people prefer each language?”
    • SQL can do it directly via GROUP BY language, then ORDER BY COUNT(*) DESC.

• Relational databases and CRUD

  • A database is software that stores data (locally or in the cloud).
  • A relational database stores data in tables and models relationships between tables.
  • SQL fundamentals are summarized as CRUD:
    • Create, Read, Update, Delete
  • In practice:
    • SELECT → Read
    • Writing/changing:
      • INSERT
      • UPDATE
      • DELETE
      • DROP TABLE (destructive)

• Hands-on: CSV → SQLite (SQL Light / SQLite 3)

  • SQLite is used as a lightweight SQL engine.
  • Instructor flow:
    • Creates a database file (e.g., favorites.db)
    • Imports CSV using SQLite’s .mode csv and .import ...
    • Inspects the schema using .schema
  • Key concept: SQL stores data in structured tables, not just raw text.

• SQL with real schemas: modeling data properly

  • The lecture escalates from one-table favorites to a realistic multi-table dataset (IMDB-style).
  • Common modeling pitfalls highlighted:
    • Redundant “star” columns (star, star, star…) → sparsity and redundancy
    • Repeated show titles across star rows → misspelling redundancy
  • Normalized relational design uses IDs:
    • Separate tables like shows, people, and a linking table (e.g., stars)
  • Key relational concepts:
    • Primary key: uniquely identifies a row in a table
    • Foreign key: references a primary key in another table
    • Constraints:
      • NOT NULL
      • UNIQUE
  • Relationship types:
    • 1-to-1: shows ↔ ratings (uniqueness on the rating’s show ID)
    • 1-to-many: shows ↔ genres
    • many-to-many: shows ↔ people through stars

• Querying relationships (nested queries and joins)

  • For linked tables, SQL can retrieve related data via:
    • Nested subqueries (often easier for beginners)
    • JOIN operations (powerful, sometimes more verbose)
  • Examples:
    • Shows with ratings ≥ 6.0 (nested IN (subquery))
    • Join shows + ratings to show titles and ratings together
    • Genres for a particular show via WHERE show_id = ... or nested ID lookup
    • Many-to-many (“stars”):
      • Who starred in “The Office” (2005)
      • Other shows a person (Steve Carell) starred in

• Performance and indexing

  • SQL can be optimized.
  • Timing demo:
    • Without an index: searches are slower (closer to scan/linear behavior)
    • With an index on the searched column: much faster
  • Indexes introduced conceptually as structures like balanced trees (e.g., B-trees).

• Combine SQL + Python

  • Instead of rewriting grouping logic in Python, SQL can handle it.
  • Uses CS50’s Python SQL helper:
    • Open DB in Python
    • Run SQL via db.execute("SELECT ...")
    • Receive results as a list of dictionaries
  • Example: counts by language can be done in one SQL query executed from Python.

• Security: SQL injection

  • Danger: building SQL queries by directly formatting user input into an SQL string (e.g., f-strings).
  • Malicious input containing quotes/SQL keywords/operators can:
    • Break the query
    • Or enable actions like commenting out parts or injecting DELETE/DROP
  • Mitigation:
    • Use parameterized queries with placeholders (e.g., ?) so the library escapes user input safely.
  • Core rule: Never trust user input to be safe.

• Concurrency issue: race conditions + transactions

  • In multi-user systems, simultaneous updates can produce incorrect results.
  • Example: a “likes” update might compute based on stale current values and overwrite increments.
  • Solutions:
    • Locks and/or
    • Transactions to ensure multiple steps happen atomically (commit or rollback).

• Closing tone

  • Ends with classic CS jokes (OCR/toll booth and “Little Bobby Tables”).

---

Methodologies / structured instruction bullets (where applicable)

A) Python approach to analyze CSV frequency counts

• Download CSV from Google Forms/Sheets.
• In Python:
  • Import the csv library.
  • Open the CSV file in read mode.
  • Create a CSV reader:
    • Prefer csv.DictReader so you can reference columns by name.
  • For counting:
    • Initialize counts (either separate variables or a dictionary).
    • Iterate rows:
      • Extract the relevant field (e.g., favorite or language).
      • Increment that category’s counter.
    • Handle missing keys to avoid KeyError:
      • If key not in dict: initialize it to 0 (or 1) before increment.

B) SQL approach to answer the same counting question

• Ensure the CSV is imported into SQLite:
  • .mode csv
  • .import favorites.csv favorites
• Use SQL queries such as:
  • Count total submissions
    • SELECT COUNT(*) FROM favorites;
  • Count distinct languages
    • SELECT COUNT(DISTINCT language) FROM favorites;
  • Count per language
    • SELECT language, COUNT(*) AS count FROM favorites GROUP BY language;
  • Sort by popularity
    • ... GROUP BY language ORDER BY count DESC;
  • Limit results
    • ... ORDER BY count DESC LIMIT 1;

C) SQL CRUD operations (as taught)

• Create (conceptual)
  • CREATE TABLE ...
• Read
  • SELECT ...
• Update
  • UPDATE table SET column=value WHERE condition;
  • Emphasis: include a correct WHERE clause to avoid updating everything.
• Delete
  • DELETE FROM table WHERE condition;
  • Emphasis: include a correct WHERE clause (missing it can wipe the table).
• Drop (destructive)
  • DROP TABLE table_name;

D) Relational modeling and querying approach

• Normalize relational data:
  • Create separate tables for each entity type (e.g., shows, people)
  • Use linking tables for many-to-many (e.g., stars)
• Define constraints:
  • PRIMARY KEY on unique identifiers
  • FOREIGN KEY referencing related tables
  • Optionally NOT NULL and UNIQUE
• Query relationships:
  • Nested query style:
    • Use subqueries to get IDs first, then use those IDs in outer queries
  • Join style:
    • JOIN ... ON table1.key = table2.foreign_key
    • Apply filters with WHERE

E) Avoid SQL injection in Python

• Don’t:
  • Interpolate user input directly into SQL strings (e.g., f-strings).
• Do instead:
  • Use placeholders (often ? in CS50 helpers / SQLite usage)
  • Pass user values as separate arguments to db.execute(...)
• This ensures dangerous characters are escaped safely.

F) Avoid race conditions with transactions (conceptual steps)

• When multiple dependent database operations must stay consistent:
  • Start a transaction
  • Perform reads/updates inside the transaction
  • Commit at the end
  • Roll back if anything fails

---

Speakers / sources featured

• Speaker: Harvard CS50 instructor (unnamed in subtitles; standard CS50x Week 7 lecture format)
• Primary course content source: CS50x Lecture 7 – SQL
• Tools/services referenced:
  • Google Forms / Google Sheets (for collecting and exporting CSV data)
  • SQLite 3 / SQLite Light 3 (lecture’s SQL engine in CodeSpaces)
  • Python CSV library (csv.reader, csv.DictReader)
  • CS50 Python SQL helper library (for executing SQL from Python)