Lesson 3 of 4

Databases: Lesson 3

SQL: Querying Data

Write SELECT queries to retrieve data from databases. Use WHERE to filter, ORDER BY to sort, and LIKE with wildcards to match patterns. Includes worked examples for all boards.

55 minutes All exam boards

Why this matters

A database with millions of records is useless if you cannot ask it questions. SQL - Structured Query Language - is the language you use to retrieve exactly the data you need, filter out what you do not, and sort the results. Every major website you use runs SQL queries behind the scenes thousands of times per second.

Think about it: When you search for a product on a website, a SQL query runs: find all products WHERE name contains the search term AND stock > 0, ORDER BY price. The results you see are that query's output.

Key terms

Terms you need to know

SQL

Structured Query Language. The standard language for managing and querying relational databases.

SELECT

Specifies which fields to return. SELECT * returns all fields.

FROM

Specifies which table to query.

WHERE

Filters records. Only rows matching the condition are returned.

ORDER BY

Sorts results by a field. ASC = ascending (A-Z, 1-9). DESC = descending (Z-A, 9-1).

Used in WHERE to match a pattern. % matches any sequence of characters; _ matches one character.

Wildcard

A special character that stands in for unknown characters. % and _ are the two SQL wildcards.

Dataset for this lesson

We will query this Students table throughout

All worked examples below use this table. Get familiar with the structure before we write queries against it.

StudentID	FirstName	LastName	Age	Year	IsEnrolled	Score
1001	Alice	Smith	15	10	TRUE	82
1002	Bob	Brown	16	11	TRUE	67
1003	Clara	Davis	15	10	FALSE	91
1004	Dan	Evans	16	11	TRUE	54
1005	Eve	Foster	15	10	TRUE	78
1006	Frank	Green	17	12	TRUE	43
1007	Grace	Hill	16	11	FALSE	88

SELECT and FROM

Choosing what to retrieve

SELECT field1, field2 FROM TableName

SELECT specifies which fields to include in the results. List field names separated by commas, or use * to select all fields. FROM specifies the table.

Query

SELECT *
FROM Students

Returns all fields and all rows from the Students table.

Query

SELECT FirstName, LastName, Score
FROM Students

Returns only three fields for all rows. Other fields are excluded from the result.

WHERE clause

Filtering records with conditions

WHERE condition

Only rows where the condition is TRUE are returned. You can use =, <, >, <=, >=, <> (not equal). String values are enclosed in single quotes.

Query - filter by number

SELECT FirstName, LastName, Score
FROM Students
WHERE Score > 75

Result (3 rows)

FirstName	LastName	Score
Alice	Smith	82
Clara	Davis	91
Eve	Foster	78
Grace	Hill	88

Query - filter by string

SELECT FirstName, LastName
FROM Students
WHERE Year = 11
AND IsEnrolled = TRUE

Result (2 rows)

FirstName	LastName
Bob	Brown
Dan	Evans

AND / OR

AND requires both conditions to be true. OR requires at least one to be true. When combining AND and OR in one query, use brackets to make the order of evaluation clear - just like BODMAS in maths.

ORDER BY

Sorting results

ORDER BY field ASC | DESC

ASC sorts ascending (A to Z, lowest to highest). DESC sorts descending (Z to A, highest to lowest). ASC is the default if you omit the direction.

Query

SELECT FirstName, LastName, Score
FROM Students
WHERE IsEnrolled = TRUE
ORDER BY Score DESC

Result - enrolled students, highest score first

FirstName	LastName	Score
Alice	Smith	82
Eve	Foster	78
Bob	Brown	67
Dan	Evans	54
Frank	Green	43

LIKE and wildcards

Pattern matching in WHERE

WHERE field LIKE 'pattern'

LIKE performs pattern matching. Use % to match any sequence of characters, and _ to match exactly one character.

Starts with S

WHERE LastName LIKE 'S%'

Matches: Smith. Does not match: Brown, Davis.

Ends with n

WHERE LastName LIKE '%n'

Matches: Brown, Green. Does not match: Smith.

Contains 'av'

WHERE LastName LIKE '%av%'

Matches: Davis, Evans. The % before and after means any characters on either side.

4-letter names

WHERE FirstName LIKE '____'

Four underscores: matches names of exactly 4 characters. Matches: Alice (5) - no. Bob (3) - no. Actually matches: none in this table with exactly 4 chars.

Quick quiz

Test yourself - using the Students table above

1. Which SQL clause do you use to filter rows based on a condition?

Correct. WHERE is the filtering clause. SELECT chooses which fields to show; FROM specifies the table; ORDER BY sorts the results.

2. What does WHERE LastName LIKE 'H%' return from the Students table?

Correct. 'H%' matches last names that start with H. From the table, only Grace Hill has a last name starting with H.

3. How would you sort results so that students with the highest scores appear first?

Correct. ORDER BY Score DESC sorts from highest to lowest (descending). ASC would sort lowest to highest. Only ORDER BY performs sorting - WHERE filters, SELECT picks fields.

Think deeper

Challenge question

Write a SQL query to return the first name, last name and score of all enrolled Year 10 students, ordered by score from highest to lowest. Use the Students table from this lesson.

SELECT FirstName, LastName, Score
FROM Students
WHERE Year = 10 AND IsEnrolled = TRUE
ORDER BY Score DESC

This returns Alice Smith (82), Eve Foster (78). Clara Davis is Year 10 but IsEnrolled = FALSE so she is excluded.

SQL Sandbox

Write and run SQL queries against a live database. Practise SELECT, WHERE, ORDER BY and LIKE with immediate results.

Lesson 2 Lesson 4: SQL Manipulation

Printable Worksheets

Practice what you have learned

Three levels of worksheet for this lesson. Download, print and complete offline.

Recall

SQL SELECT Recall

Fill-in-the-blank SQL queries, keyword identification, and WHERE clause syntax matching exercises.

Download

Apply

Query Construction

Write SELECT queries from plain-English descriptions. Predict output from given tables and queries.

Download

Exam Style

Exam-Style Questions

Multi-part exam questions on SQL syntax, query writing and output tracing. Mark scheme included.

Download

Lesson 3 - Databases

SQL: Querying Data

Starter activity

Show students a list of names and ask them to find everyone over 15 and sort alphabetically - by hand. Then show them the SQL query that does the same thing. The point: SQL is just asking a question in a very precise way the computer understands.

Lesson objectives

Write a SELECT query using FROM and WHERE for a given scenario.

Use AND and OR to combine conditions in a WHERE clause.

Sort results using ORDER BY ASC and DESC.

Use LIKE with the % wildcard to match patterns.

Trace a given SQL query and predict its output from a table.

Key vocabulary

SQL

Structured Query Language. Standard language for querying and manipulating relational databases.

WHERE

Filters rows. Only returns records where the condition evaluates to TRUE.

LIKE / %

Pattern matching. % matches any sequence of characters in that position.

Exit tickets

Write a SQL query to return the names of all students in Year 11. [2 marks]

What does WHERE LastName LIKE '%s' return? [1 mark]

Write a query returning FirstName, Score for enrolled students with score above 70, sorted by score descending. [4 marks]

Homework suggestion

Using the SQL Sandbox, complete five tasks: (1) retrieve all students, (2) retrieve only enrolled students, (3) retrieve students with score between 60 and 80, (4) retrieve students whose last name starts with a vowel, (5) write a query a partner wrote and verify the output.

Resources

SQL Sandbox Worksheet 1 - Recall Worksheet 2 - Apply Worksheet 3 - Exam Style Unit Exam Paper (all 4 lessons)