Use SQL to retrieve data with SELECT, FROM, WHERE and ORDER BY, and to insert, update and delete records.

What this dot point is asking

AQA wants you to write SQL to retrieve data using SELECT, FROM, WHERE and ORDER BY, and to change data using INSERT, UPDATE and DELETE.

Retrieving data with SELECT

SELECT name, age
FROM Students
WHERE age > 15
ORDER BY name ASC

This returns the name and age of every student over 15, sorted by name. The WHERE condition can combine tests with AND and OR, for example WHERE age > 15 AND year = 11, and can use comparison operators such as $=$, $<$, $>$ and $\ne$.

Selecting all fields

Using SELECT * returns every field in the table, rather than naming each one. This is convenient for quick checks, but naming the fields you actually need is clearer and avoids returning data you do not want.

Inserting, updating and deleting

INSERT INTO Students (name, age) VALUES ('Sam', 16)
UPDATE Students SET age = 17 WHERE name = 'Sam'
DELETE FROM Students WHERE age < 11

The single most important habit is to include WHERE in every UPDATE and DELETE: without it, the statement applies to the entire table, changing or deleting every record.

Filtering with conditions

The WHERE clause is where most of the thinking goes. A condition compares a field with a value using an operator such as $=$, $\ne$, $<$, $>$, $\le$ or $\ge$, and conditions can be combined. For example WHERE year > 2015 AND price < 10 returns only records that satisfy both tests, while WHERE author = 'Dahl' OR author = 'Blyton' returns records matching either. Text values are wrapped in quotation marks; numbers are not. Getting the operator and the logic right is the difference between returning the rows you want and returning none or all of them.

Sorting and combining clauses

ORDER BY sorts the results once they have been selected and filtered, ascending by default or descending with DESC, for example ORDER BY price DESC to list the most expensive first. The clauses must always appear in the fixed order SELECT, FROM, WHERE, ORDER BY: SELECT chooses the columns, FROM names the table, WHERE filters the rows, and ORDER BY arranges what is left. A query that retrieves the three cheapest books by one author therefore reads SELECT title, price FROM Books WHERE author = 'Dahl' ORDER BY price ASC, applying each clause in turn.

Try this

Q1. Write an SQL query to return all fields of every record in a table called Members. [2 marks]

• Cue. SELECT * FROM Members.

Q2. Write an SQL query to return the name of every member aged over 18, sorted by name. [3 marks]

• Cue. SELECT name FROM Members WHERE age > 18 ORDER BY name.