SQL: data definition and data manipulation

Learning objectives

Write data definition statements that create a database with CREATE DATABASE and a table with CREATE TABLE and change one with ALTER TABLE, choosing suitable data types and declaring primary and foreign keys

Write SELECT queries using FROM, WHERE, ORDER BY and DISTINCT to extract the records that satisfy stated conditions, combining comparison operators with AND, OR and NOT

Use the aggregate functions COUNT, SUM, AVG, MIN and MAX together with GROUP BY to summarise data, and explain the difference between filtering rows before grouping and filtering groups afterwards

Write a query that joins two or more tables on matching key fields to answer a question that no single table could answer on its own

Write INSERT, UPDATE and DELETE statements to add, change and remove records, and explain the effect that a foreign key constraint has on an attempted deletion

Interpret an SQL statement written by somebody else, predict exactly which records it returns from given tables, and correct a statement that does not do what was intended