Reading 3: Reading Queries in Practice

Bringing It All Together

In Readings 1 and 2, you learned to read two types of SQL queries:

• Single-relation queries using SELECT, FROM, and WHERE to filter rows and choose columns from one table.
• Multi-relation queries that add JOIN conditions to combine information from two or more tables.

In both cases, the goal was the same: look at a query and state in plain English what question it is answering. This reading gives you extended practice with that skill, using a slightly different database scenario so you can confirm that the approach generalizes beyond the specific examples you have already seen.

We also close the loop on the data investigation cycle — connecting what SQL does back to the framework we introduced in Topic 5a.

A New Database: Professional Development

Recall from Topic 5b's Checking for Understanding that we identified a problematic single-relation design for tracking staff professional development. Here is a properly redesigned version with three relations:

Take a moment to orient yourself to this database. STAFF stores information about each staff member. TRAINING stores information about each professional development offering. COMPLETION records which staff member completed which training and when — it is the linking relation between the other two, exactly like ENROLLMENT was in the student database.

Worked Examples

Here are four queries on this database, each followed by a full reading walkthrough.

Worked Example 1

SELECT  Title, Hours
FROM    Training
WHERE   Provider = 'AEA'

Reading it: FROM Training — looking at professional development offerings. WHERE Provider = 'AEA' — only those offered by the AEA. SELECT Title, Hours — we want the name and duration of each.

Plain-English question: What are the titles and durations of professional development offerings provided by the AEA?

From our sample data: Differentiated Instruction (6 hours) and Reading Across the Curriculum (6 hours).

Worked Example 2

SELECT  Staff.LastName, Staff.FirstName
FROM    Staff, Completion
WHERE   Staff.StaffID = Completion.StaffID
        AND Completion.TrainingID = 'PD-02'

Reading it: FROM Staff, Completion — combining staff information with completion records. The JOIN condition links them via StaffID. The filter restricts to training PD-02 (Data Literacy for Educators). SELECT gets the staff names.

Plain-English question: Which staff members have completed the Data Literacy for Educators training?

From our sample data: Okonkwo, Adaeze and Rivera, Carmen.

Worked Example 3

SELECT  Training.Title, Completion.CompletionDate
FROM    Staff, Completion, Training
WHERE   Staff.StaffID = Completion.StaffID
        AND Completion.TrainingID = Training.TrainingID
        AND Staff.LastName = 'Rivera'
        AND Staff.FirstName = 'Carmen'

Reading it: All three relations are involved. The two JOIN conditions chain Staff → Completion → Training. The two filter conditions identify a specific staff member by name. SELECT retrieves the training title and completion date.

Plain-English question: What professional development has Carmen Rivera completed, and when did she complete each training?

From our sample data: Data Literacy for Educators (2024-10-03) and Reading Across the Curriculum (2024-11-01).

Worked Example 4

SELECT  Staff.LastName, Staff.FirstName, Staff.Department
FROM    Staff, Completion, Training
WHERE   Staff.StaffID = Completion.StaffID
        AND Completion.TrainingID = Training.TrainingID
        AND Training.Provider = 'State'

Reading it: All three relations. JOIN conditions chain the same way as Example 3. The filter restricts to state-provided training. SELECT gets staff name and department.

Plain-English question: Which staff members (and in what departments) have completed state-provided professional development?

From our sample data: Bergmann, Lars — English (he completed Culturally Responsive Teaching, which is state-provided).

Closing the Loop: SQL and the Investigation Cycle

Step back for a moment and consider what SQL actually does in the context of the data investigation cycle from Topic 5a.

In Stage 1 of the cycle, an investigator poses a precise question. In Stage 4, they analyze the data to find an answer. SQL is the bridge between those two stages when the data lives in a relational database. A well-formed SQL query is a precise question expressed in a language the database can execute.

Notice how closely the process of writing — or reading — a SQL query mirrors the requirements of a good investigation question. Both require you to specify: what subject are you asking about (FROM), what criteria define the records you care about (WHERE), and what information you want to retrieve (SELECT). Vague questions produce vague queries and vague results. Precise questions produce precise queries and useful answers.

This is also why the Iowa middle school standard MS-DAA-23 — "Use a digital tool to sort, filter, group, and summarize structured data" — is really teaching a version of what SQL does, years before students encounter SQL formally. Filtering rows is WHERE. Selecting columns is SELECT. Grouping and summarizing are more advanced SQL features beyond this course, but they follow the same logic. The conceptual foundation is the same at every grade level; only the tool and the sophistication change.

Now You Try

For each SQL query below, write a plain-English question that the query answers. Use the STAFF, TRAINING, and COMPLETION relations shown in this reading. Suggested answers are in the answer key.

Query A:

SELECT  LastName, FirstName
FROM    Staff
WHERE   Department = 'Mathematics'

Query B:

SELECT  Training.Title, Training.Hours
FROM    Staff, Completion, Training
WHERE   Staff.StaffID = Completion.StaffID
        AND Completion.TrainingID = Training.TrainingID
        AND Staff.Department = 'Mathematics'

Query C:

SELECT  Staff.LastName, Staff.FirstName
FROM    Staff, Completion, Training
WHERE   Staff.StaffID = Completion.StaffID
        AND Completion.TrainingID = Training.TrainingID
        AND Training.Hours >= 6
        AND Staff.Department = 'Science'