Reading 1: Selecting and Filtering

From Design to Use

In Topic 5b, we focused on how a relational database is designed — organizing data into well-structured relations with tuples and attributes, controlling access through schemas and subschemas, and eliminating redundancy by giving each concept its own relation. That design work is the foundation.

Topic 5c is about what happens on top of that foundation: actually using a database to answer questions. In terms of the data investigation cycle we introduced in Topic 5a, we are now firmly in Stage 4 — analyze and explore. The database has been built and populated. Now we need to get information out of it.

The tool for doing that is SQL — Structured Query Language. SQL is the standard language for interacting with relational databases. It is used in virtually every relational database system in the world, from the small SQLite database embedded in your phone to the enormous systems behind Google, Amazon, and your bank. Learning to read SQL — to look at a query and understand what question it is asking — is the goal of this topic.

The Database We Will Use

Throughout this topic, we will work with the three-relation school database we developed in Topic 5b. Here it is as a reminder, with some sample data filled in:

Keep these tables in mind as you read through the examples below. When you see a SQL query, try to visualize it operating on these tables before reading the explanation.

The Basic SQL Query

Every SQL query we will look at in this topic follows the same basic pattern:

SELECT  <attributes you want to see>
FROM    <relation(s) to look in>
WHERE   <conditions the tuples must meet>

Reading a SQL query in plain English is straightforward once you know what each clause does:

• SELECT tells the database which attributes (columns) to include in the result. This is the "what do I want to see?" clause.
• FROM tells the database which relation (or relations) to look in. This is the "where is the data?" clause.
• WHERE tells the database which tuples (rows) to include, by specifying conditions that must be true. This is the "which records qualify?" clause.

Let's look at some examples, working from simple to more complex.

Example 1: The Simplest Query

The simplest possible SQL query retrieves everything from a single relation:

SELECT  LastName, FirstName, Grade
FROM    Student

What does this return? A list of every student's last name, first name, and grade level — one row per student, no filtering applied. Notice there is no WHERE clause here. When there is no WHERE clause, all tuples in the relation are returned.

Notice also that we listed specific attributes in the SELECT clause. We asked for LastName, FirstName, and Grade — but not StudentID. The result would include only those three columns, even though the STUDENT relation has four attributes. This is the "extracting columns" operation that the relational model calls PROJECT — in SQL, you project by simply listing the attributes you want in the SELECT clause.

Example 2: Adding a Filter

Now let's add a WHERE clause to filter which tuples are returned:

SELECT  LastName, FirstName
FROM    Student
WHERE   Grade = 10

What does this return? The last name and first name of every student in tenth grade. Only tuples where the Grade attribute equals 10 are included. From our sample data, this would return Priya Adeyemi and Marcus Hernandez.

Notice the structure: SELECT says what to show (LastName, FirstName), FROM says where to look (Student), and WHERE says which rows qualify (Grade = 10). Reading those three clauses in order gives you the plain-English question: "What are the names of all tenth-grade students?"

Example 3: Querying a Different Relation

SQL works exactly the same way on any relation. Here is a query on the COURSE relation:

SELECT  CourseName, Credits
FROM    Course
WHERE   Department = 'Mathematics'

What does this return? The name and credit value of every course in the Mathematics department. From our sample data, this would return one row: Algebra II, 1.0.

Plain-English question: "What are the names and credit values of all mathematics courses?"

Example 4: Multiple Conditions with AND

WHERE clauses can combine multiple conditions using AND and OR. Here is an example with AND:

SELECT  LastName, FirstName
FROM    Student
WHERE   Grade = 11
        AND LastName = 'Lindqvist'

What does this return? The name of any eleventh-grade student whose last name is Lindqvist. Both conditions must be true for a tuple to be included. From our sample data, this returns one row: Sofia Lindqvist.

Plain-English question: "Which eleventh-grade students have the last name Lindqvist?"

AND narrows results — a tuple must satisfy every condition. OR broadens results — a tuple needs to satisfy only one. Here is an OR example:

SELECT  CourseName, Department
FROM    Course
WHERE   Department = 'English'
        OR Department = 'Fine Arts'

What does this return? The name and department of every course in either the English or Fine Arts department. From our sample data: American Literature (English) and Studio Art (Fine Arts).

Plain-English question: "What courses are offered in the English or Fine Arts departments?"

A Strategy for Reading Any SQL Query

When you encounter a SQL query and want to understand what question it answers, try reading the clauses in this order:

1. FROM first — What relation (or relations) is being searched? This tells you the subject matter.
2. WHERE second — What conditions filter the results? This tells you the constraints or criteria.
3. SELECT last — What attributes appear in the result? This tells you what the answer actually looks like.

Assembling those three pieces into a natural-language question is exactly the skill the competency demo will ask you to demonstrate. Let's practice it in the Now You Try.

Now You Try

For each SQL query below, write a plain-English question that the query answers. Use the STUDENT, COURSE, and ENROLLMENT relations shown at the top of this reading. Suggested answers are in the answer key.

Query A:

SELECT  CourseName
FROM    Course
WHERE   Credits = 0.5

Query B:

SELECT  LastName, FirstName, Grade
FROM    Student
WHERE   Grade = 11

Query C:

SELECT  CourseID, Semester
FROM    Enrollment
WHERE   StudentID = 10042

Query D:

SELECT  StudentID
FROM    Enrollment
WHERE   CourseID = 'ENG401'
        AND Semester = 'Fall'