Understand SQL for retrieving data with SELECT, FROM, WHERE and ORDER BY, joining tables, and modifying data with INSERT, UPDATE and DELETE.

What this dot point is asking

AQA wants you to write SQL to retrieve data (SELECT, FROM, WHERE, ORDER BY), join tables, and modify data with INSERT, UPDATE and DELETE.

Retrieving data

SELECT Name, Mark
FROM Student
WHERE Mark >= 50
ORDER BY Mark DESC;

The WHERE clause can combine conditions with AND, OR and NOT, and use LIKE for pattern matching (with wildcards) and BETWEEN for ranges. Because SQL is declarative, you describe the result set you want and the database management system decides how to find it efficiently, which is the practical example of the declarative paradigm covered in the languages topic.

Joining tables

SELECT Student.Name, Course.Title
FROM Student, Course
WHERE Student.CourseID = Course.CourseID;

The join is the direct payoff of normalisation: because data was split into separate tables to remove redundancy, a join is needed to recombine it for a report. The join condition follows the same foreign-key-to-primary-key link that the relationship was built on, which is why you must know the keys to write a correct join. Forgetting the condition produces a Cartesian product, every combination of rows, which is almost never what is wanted.

Modifying data

The single most dangerous mistake in this topic is omitting the WHERE clause from an UPDATE or DELETE, because the command then silently affects every row in the table. This connects to transaction processing: such bulk changes are exactly why databases wrap modifications in transactions that can be rolled back, and why ACID properties matter when many users change data at once.