My progress
Menu

Level 5 · Databases and Data Management · Course 5.2 SQL

Module 55: SQL Fundamentals

Goal: write SQL to create tables, add, read, change and remove data, filter and sort results, and summarise data with simple calculations.

You'll need: DB Browser for SQLite and your crm.db with sample data from Module 54. (The SQL here works in SQLite and PostgreSQL; differences are noted.)

Lessons

  1. 1What SQL is15–25 minutes✓
  2. 2Creating and changing tables15–25 minutes✓
  3. 3INSERT, UPDATE and DELETE15–25 minutes✓
  4. 4SELECT, filtering and sorting15–25 minutes✓
  5. 5Counting and summarising15–25 minutes✓

🛠️Module practice: Project B, part 1, everyday SQL

Using crm.db, write and save in queries.sql (with a comment above each):

  1. Insert 10 more customers, 5 products and 20 orders with items (generate realistic fake data with AI, and read it).
  2. Update and delete using the SELECT-first habit.
  3. Five filtering and sorting queries a CRM user would ask.
  4. Five summary queries: totals, averages, counts per group.

Commit queries.sql.

✅Module check

Explain in a few sentences, in your own words:

What are the main SQL statements, and what's the most important safety habit when changing data?

Show a good answer

A good answer mentions: CREATE/ALTER/DROP define tables; INSERT adds rows, UPDATE changes them, DELETE removes them; SELECT reads, with WHERE to filter, ORDER BY to sort, LIMIT to cap results, and aggregates like COUNT, SUM and AVG with GROUP BY to summarise. The key safety habit: always use WHERE on updates and deletes, run the matching SELECT first to check which rows are affected, and use transactions and backups for important changes.

📝Module quiz

10 questions · 10 marks each · 100 marks in all · pass mark 70% · retry as often as you like ·

1. Is SQL declarative or step-by-step?

Why each option is right or wrong

  • “Step-by-step: you write each loop the database should run” (Incorrect): SQL describes the result; the database decides the steps.
  • “Step-by-step, but only for queries that change data” (Incorrect): Changes are described the same way, by what should change.
  • “Neither; SQL just stores files on the disk” (Incorrect): SQL is a query language, not a file store.
  • “Declarative: you describe the result and the database finds it” is correct.

    You write what you want, like "customers in Patna, sorted by name", not how to loop through the table. The database's query planner chooses the fastest way, such as using an index.

    • Benefit: the same query can get faster when you add an index.
    • See the plan: EXPLAIN shows how the database will run it.

📖 Revise: Lesson 1: What SQL is

Question ID module-55-q1 · Report a mistake in this question
2. How do you write the text Patna in SQL?

Why each option is right or wrong

  • “"Patna"” (Incorrect): Double quotes mean an identifier, like a column name, in standard SQL.
  • “Patna, without any quotes” (Incorrect): Without quotes, SQL treats it as a column or keyword.
  • “(Patna)” (Incorrect): Brackets group expressions; they don't mark text.
  • “'Patna'” is correct.

    SQL uses single quotes for text values. Double quotes are for identifiers, like column or table names, in PostgreSQL and standard SQL. Writing "Patna" makes the database look for a column called Patna.

    • Apostrophes inside text: double them, as in 'D''Souza'.
    • In app code: use parameters, not hand-built strings.

📖 Revise: Lesson 1: What SQL is

Question ID module-55-q2 · Report a mistake in this question
3. What does DROP TABLE do to the data inside?

Why each option is right or wrong

  • “Moves it into a recycle bin for thirty days” (Incorrect): Databases don't have a recycle bin for dropped tables.
  • “Keeps the data but hides the table from queries” (Incorrect): The data is deleted along with the table.
  • “Empties the table but keeps its columns for later” (Incorrect): That's TRUNCATE; DROP removes the table itself.
  • “Deletes it permanently, with the table” is correct.

    DROP TABLE removes the table's structure and every row in it. There's no undo unless you have a backup or are inside a transaction you can roll back.

    • Before running on real data: take a backup.
    • Only want to empty it: that's DELETE or TRUNCATE, still destructive.

📖 Revise: Lesson 2: Creating and changing tables

Question ID module-55-q3 · Report a mistake in this question
4. Why make schema changes through migration files?

Why each option is right or wrong

  • “Changes typed straight into production are faster and just as safe” (Incorrect): Hand changes aren't recorded, so other copies drift apart.
  • “Databases refuse changes that don't come from a file” (Incorrect): They accept any SQL; migrations are for consistency.
  • “Migrations make the database run queries faster” (Incorrect): They manage structure; speed depends on design and indexes.
  • “Every copy of the database changes the same recorded, repeatable way” is correct.

    A migration is a file with the SQL for one change, like adding a column. Committed to Git and run in order, migrations bring every database, on laptops, test and production, to the same structure, with a history of what changed.

    • Run by tools: like Prisma Migrate, Drizzle Kit or Knex.
    • Don't edit old migrations: add a new one instead.

📖 Revise: Lesson 2: Creating and changing tables

Question ID module-55-q4 · Report a mistake in this question
5. What does DELETE FROM customers; do?

Why each option is right or wrong

  • “Deletes only the most recently added customer” (Incorrect): Without WHERE, it deletes every row.
  • “Shows which rows would be deleted, without deleting them” (Incorrect): It really deletes them; there's no preview.
  • “Deletes the customers table itself, including its columns” (Incorrect): The table remains; only the rows go. DROP TABLE removes the table.
  • “Deletes every row in the table” is correct.

    Without a WHERE clause, DELETE applies to every row. The table remains, but it's empty. This is one of the most damaging mistakes in SQL.

    • Safety habit: write the WHERE clause first.
    • Some tools: warn or block DELETE without WHERE.

📖 Revise: Lesson 3: INSERT, UPDATE and DELETE

Question ID module-55-q5 · Report a mistake in this question
6. What's the safest way to write an UPDATE?

Why each option is right or wrong

  • “Write it without WHERE, then undo any rows that were wrong” (Incorrect): Without WHERE every row changes, and undoing it is often very hard or not possible without a backup.
  • “Run it twice to make sure it worked the first time” (Incorrect): Running it twice can apply the change twice.
  • “Ask the database to guess which rows you meant” (Incorrect): Databases do exactly what you write; check it first.
  • “Run a SELECT with the same WHERE first to check which rows change” is correct.

    Running SELECT * FROM ... WHERE ... with the exact condition shows which rows, and how many, will be affected. If the count is what you expect, change SELECT to UPDATE. A transaction lets you roll back if needed.

    • Transaction: BEGIN; UPDATE ...; check; COMMIT; or ROLLBACK;.
    • Check the row count: the database reports how many rows changed.

📖 Revise: Lesson 3: INSERT, UPDATE and DELETE

Question ID module-55-q6 · Report a mistake in this question
7. How do you find customers whose email is missing?

Why each option is right or wrong

  • “WHERE email = NULL” (Incorrect): Comparing with = NULL gives unknown, so no rows match.
  • “WHERE email = 'NULL'” (Incorrect): That finds the text NULL, not missing values.
  • “WHERE email == ''” (Incorrect): == isn't standard SQL, and an empty string isn't NULL.
  • “WHERE email IS NULL” is correct.

    Missing values are NULL, which isn't equal to anything, so = NULL matches nothing. IS NULL is the special test.

    • Also check: empty strings, if your data might contain ''.
    • Opposite: IS NOT NULL.

📖 Revise: Lesson 4: SELECT, filtering and sorting

Question ID module-55-q7 · Report a mistake in this question
8. Why use placed_at < '2026-11-01' rather than <= '2026-10-31'?

Why each option is right or wrong

  • “<= is slower than < in most databases” (Incorrect): Speed is the same; the result is what differs.
  • “SQL doesn't support the <= operator for dates” (Incorrect): It does; the issue is what the date means.
  • “< includes 1 November as well” (Incorrect): < excludes 1 November and includes all of 31 October.
  • “<= misses timestamps later on 31 October” is correct.

    '2026-10-31' means midnight at the start of that day. A timestamp like 31 October at 3 pm is later than that, so <= leaves it out. < '2026-11-01' includes all of 31 October.

    • Pattern: >= start AND < next day.
    • Works for: months and years too.

📖 Revise: Lesson 4: SELECT, filtering and sorting

Question ID module-55-q8 · Report a mistake in this question
9. What's the difference between WHERE and ORDER BY?

Why each option is right or wrong

  • “They do the same thing, written in different ways” (Incorrect): One filters, the other sorts.
  • “ORDER BY chooses rows; WHERE sorts them” (Incorrect): That's the wrong way round.
  • “WHERE is for text columns; ORDER BY is for numbers” (Incorrect): Both work with any column type.
  • “WHERE chooses rows; ORDER BY sorts them” is correct.

    WHERE filters, keeping only rows that match the condition. ORDER BY decides the order of whatever rows remain. They're often used together.

    • Example: WHERE city = 'Patna' ORDER BY name.
    • Order of writing: SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT.

📖 Revise: Lesson 4: SELECT, filtering and sorting

Question ID module-55-q9 · Report a mistake in this question
10. What does GROUP BY city with COUNT(*) give you?

Why each option is right or wrong

  • “Every row, sorted by city” (Incorrect): That's ORDER BY; GROUP BY combines rows into groups.
  • “The total number of cities in the table” (Incorrect): Counting distinct cities is COUNT(DISTINCT city).
  • “Only the city with the most rows” (Incorrect): It returns every city; add ORDER BY and LIMIT for the top one.
  • “One row per city with its number of rows” is correct.

    GROUP BY collects rows with the same city into one group, and COUNT(*) counts each group's rows. The result is a summary: each city once, with its count.

    • Example: SELECT city, COUNT(*) FROM customers GROUP BY city;.
    • Filter groups: with HAVING, like HAVING COUNT(*) > 10.

📖 Revise: Lesson 5: Counting and summarising

Question ID module-55-q10 · Report a mistake in this question

ProLearners Assist