Study Guide Instructor view

Querying a Database with SQL

Teaching notes for Lesson 6.2, with controls for presenting it.

Keep this page on your own screen. Share only the lesson window. The lesson window holds no teaching notes, so sharing it cannot show them.

Presenting

  1. Open the lesson in its own window and share that window, not this one.
  2. Use Next and Previous here, or the lesson’s own, to move through the lesson. Next reveals one thing at a time: the next idea, or the answer to the question on screen, then the next step. Previous takes the last reveal back. The line below says what is on screen and what Next will show.
  3. Reveal hints and answers when the class is ready, and reset a demonstration before using it again.

The controls need JavaScript and a browser that supports BroadcastChannel. Without them, open the lesson in presentation mode and use its own controls.

The two windows talk to each other only inside this browser, on this device. Nothing is sent to a server, and no account is needed. A second device (a phone or another tablet) cannot follow the lesson window yet.

Lesson plan

Level
Intermediate
Time
About 70 minutes; the steps below add up to 76

Learners start from a librarian’s spoken question, see it written as a query on a small table, and predict outputs before each keyword is explained. They build up SELECT and FROM, WHERE, AND and OR, ORDER BY, and SUM and COUNT, filling in outputs row by row. The lesson ends with completing a query, spotting mistakes in queries and writing one of their own.

Objectives

  • Explain what a query is and what SELECT, FROM and WHERE do
  • Work out the output of a query on a given table, row by row
  • Combine conditions with AND and OR, and explain why one query returns nothing
  • Sort results with ORDER BY ASC and ORDER BY DESC
  • Use SUM and COUNT, and say what is different between them
  • Complete a partly written query and find the mistake in a faulty one

Before the lesson

  • Display the Book table where all learners can see it during the whole lesson.
  • Mini whiteboards for writing outputs and queries.
  • Remind learners that a statement ends with a semicolon and that the quote style is copied from the question.

Steps

  1. Introduction Opening

    On screen: the lesson’s title, module and number; Next adds its objectives and key terms, as the lesson page lists them; Next again begins the first step.

    Objectives on screen

    • Explain what a query is and what SELECT, FROM and WHERE do
    • Work out the output of a query on a given table, row by row
    • Combine conditions with AND and OR, and explain why one query returns nothing
    • Sort results with ORDER BY ASC and ORDER BY DESC
    • Use SUM and COUNT, and say what is different between them
    • Complete a partly written query and find the mistake in a faulty one

    Purpose: Learners start from a librarian’s spoken question, see it written as a query on a small table, and predict outputs before each keyword is explained. They build up SELECT and FROM, WHERE, AND and OR, ORDER BY, and SUM and COUNT, filling in outputs row by row. The lesson ends with completing a query, spotting mistakes in queries and writing one of their own.

  2. 1. A question for the librarian Think 5 min

    Purpose: Learners describe in their own words how they would find fantasy books over 300 pages, which is exactly what a query does.

    Ask

    • How would you answer this from the catalogue?
    • Which details of each book do you look at?

    Responses to expect

    • Check each book’s genre and number of pages
    • Keep it only if both are right

    Misconceptions to challenge

    • A learner may believe: “The librarian just knows the answer.” Respond: Ask how a computer would know. It needs the question in a strict form.

    Checking understanding: Each learner writes the steps as a numbered list.

    Depending on the answers

    • If learners skip the ‘both’ idea: ask what to do with a fantasy book with 200 pages

    In the lesson window

    • Think it through: With the catalogue open at the full list of books, how would you answer that question? Describe each step you take, in order.
  3. 2. The table, and a first question Discover 8 min

    Purpose: Introduce the table, SELECT and FROM, by predicting the output of the simplest query.

    Ask

    • What does SELECT Title FROM Book; output?
    • Which word names the table?

    Responses to expect

    • All nine titles
    • FROM

    Misconceptions to challenge

    • A learner may believe: “SELECT names the table.” Respond: SELECT lists fields; FROM names the table.

    Checking understanding: The question about which name is the table.

    Depending on the answers

    • If learners hesitate over the semicolon: say that it ends the statement, as a full stop does

    In the lesson window

    • Think it through: Read the query line by line. What do you think SELECT Title FROM Book; will output?
    • Check your understanding: In SELECT BookID, Pages FROM Book; which of these is the name of the table?
  4. 3. Only the rows that fit: WHERE Experiment 10 min

    Purpose: Learners predict which records WHERE keeps, then complete the output table.

    Ask

    • Which records have the genre Fantasy?
    • Why is there no fourth row?

    Responses to expect

    • Records 1, 3 and 8
    • There is no fourth Fantasy book

    Misconceptions to challenge

    • A learner may believe: “WHERE changes the table.” Respond: WHERE only chooses which records appear in the output.
    • A learner may believe: “The sign < means more than.” Respond: < means less than; > means more than.

    On the board or with the pen

    • Tick the matching rows in the Book table with the pen before writing the output.

    Checking understanding: The output table and the sign question.

    Depending on the answers

    • If learners output Title instead of BookID: reread the SELECT line together

    In the lesson window

    • Think it through: Before you fill in the output below, which records do you expect this query to keep? Check the Genre column of each one.
    • Check your understanding: Which query outputs the titles of all books with more than 300 pages?
  5. 4. Two conditions: AND and OR Predict 10 min

    Purpose: Learners predict AND and OR from the library question, then trace both on the table.

    Ask

    • Which Fantasy books also have more than 300 pages?
    • Why does Genre = Science AND Genre = Mystery output nothing?

    Responses to expect

    • Records 1 and 3
    • A book has only one genre

    Misconceptions to challenge

    • A learner may believe: “AND means both kinds of book.” Respond: AND needs both conditions true for the same record.
    • A learner may believe: “A bare value can follow OR.” Respond: Each side needs a whole condition with a field name.

    Checking understanding: The tracing table and the science-or-historical check.

    Depending on the answers

    • If learners write AND for ‘or’: ask whether one record could pass both conditions

    In the lesson window

    • Think it through: Look at the Fantasy records (1, 3 and 8). Which of them also have more than 300 pages? Now imagine the pupil had asked for books that are Fantasy or have more than 300 pages. Would record 7 (Adventure, 352 pages) be included?
    • Check your understanding: The librarian wants every book that is Science or Historical. Which WHERE line does that?
  6. 5. Putting results in order: ORDER BY Discover 8 min

    Purpose: Sorting by a field in both directions, using a nine-row output.

    Ask

    • Which book comes first with the fewest pages first?
    • What does DESC mean?

    Responses to expect

    • Record 9
    • Largest first

    Misconceptions to challenge

    • A learner may believe: “ORDER BY filters records.” Respond: It only changes the order of those that remain.

    Checking understanding: The nine-row output table and the check combining WHERE and ORDER BY.

    Depending on the answers

    • If learners reverse ASC and DESC: ask which end the sort begins with

    In the lesson window

    • Think it through: Which book would come first, and which last, if the books were listed with the fewest pages first? Look at the Pages column.
    • Check your understanding: What does the query above output?
  7. 6. Adding up and counting: SUM and COUNT Explain 9 min

    Purpose: Separate adding values (SUM) from counting records (COUNT(*)).

    Ask

    • How many Fantasy copies are there in total?
    • How many Fantasy titles are there?

    Responses to expect

    • 6 copies
    • 3 titles

    Misconceptions to challenge

    • A learner may believe: “COUNT adds a field.” Respond: COUNT(*) counts records; SUM adds a number field.
    • A learner may believe: “SUM can add text.” Respond: SUM needs a numeric field.

    Checking understanding: The copies-owned check.

    Depending on the answers

    • If learners use COUNT for copies: ask whether they want the number of records or a total of a field

    In the lesson window

    • Think it through: The librarian asks: “How many Fantasy copies do we own in total?” Which field would you add up, and what is the total? And how many different Fantasy titles are there?
    • Check your understanding: Which query outputs the number of copies the library owns of books with fewer than 200 pages?
  8. 7. Reading a whole query Explain 8 min

    Purpose: A worked example showing the order for working out the output of a longer query, then one for the learner.

    Ask

    • Which records does WHERE keep?
    • In what order do the rows appear?

    Responses to expect

    • Records 1 and 3
    • 3 then 1, by Pages DESC

    Misconceptions to challenge

    • A learner may believe: “Work out the output in the order the query is written.” Respond: Start from the table, then WHERE, then ORDER BY, then SELECT.

    Checking understanding: The prediction question that follows the worked example.

    Depending on the answers

    • If learners list fields before filtering: redo the method with the steps in order on the board

    In the lesson window

    • Apply it: What does the query above output?
  9. 8. Completing and correcting queries Apply 10 min

    Purpose: Completing a query, spotting a missing AND, spotting SUM on text, then writing a query from a request.

    Ask

    • What is missing from this query?
    • What should replace SUM(Title)?

    Responses to expect

    • ORDER BY; AND between the conditions
    • COUNT(*)

    Misconceptions to challenge

    • A learner may believe: “Every query needs ORDER BY.” Respond: It is optional.

    Checking understanding: The written query for Science books, compared with the model answer.

    Depending on the answers

    • If learners write keywords in a different order: use SELECT, FROM, WHERE, ORDER BY as a fixed order

    Stretch

    • Write a query for the total pages of the Mystery books.

    In the lesson window

    • Apply it: Which words complete the query above?
    • Apply it: What is wrong with the pupil’s query?
    • Apply it: Another pupil writes SELECT SUM(Title) FROM Book; hoping to find out how many books there are. What is wrong, and what should it be?
    • Think it through: Write a query that outputs the titles of the Science books, shortest book first.
  10. 9. Show what you know Consolidate 8 min

    Purpose: Explain SELECT against WHERE, and predict a final COUNT with OR.

    Ask

    • What does SELECT choose and what does WHERE choose?
    • Which records pass Genre = Fantasy OR Pages > 300?

    Responses to expect

    • Fields; records
    • 1, 3, 7 and 8

    Misconceptions to challenge

    • A learner may believe: “Records passing both conditions are counted twice with OR.” Respond: Each record is counted once.

    Checking understanding: The final count question and the ‘least sure’ question.

    Depending on the answers

    • If learners double count record 1: list the passing records first, then count them

    In the lesson window

    • Think it through: A friend says: “SELECT and WHERE both choose things.” Explain what SELECT chooses and what WHERE chooses, with an example from the Book table.
    • Apply it: What does the query above output?
    • Think it through: Which part of SQL are you least sure about? Write one question you would ask your teacher, and try to answer it using the Book table.
  11. Lesson recap Closing

    On screen: the lesson’s objectives again; Next adds the closing question, which asks the class to explain each one in their own words without looking back. It says nothing about what anyone has learnt: use the answers to decide what to revisit.

    Points to listen for

    • SELECT lists fields, FROM names the table, WHERE keeps the records that pass.
    • AND needs both conditions true for the same record; OR needs at least one.
    • ORDER BY field ASC or DESC sorts the output; SUM adds a number field; COUNT(*) counts records.
    • To find an output: table, then WHERE, then ORDER BY, then SELECT.

Consolidation

  • SELECT lists fields, FROM names the table, WHERE keeps the records that pass.
  • AND needs both conditions true for the same record; OR needs at least one.
  • ORDER BY field ASC or DESC sorts the output; SUM adds a number field; COUNT(*) counts records.
  • To find an output: table, then WHERE, then ORDER BY, then SELECT.