Databases & SQL

A database stores tables

• A database keeps data in tables, like a spreadsheet.
• Each row is a record; each column is a field.
• We ask questions in a language called SQL.

SELECT: choose columns

• SELECT picks which columns to show.
• FROM names the table to read.
• * means "all columns".

SELECT name, grade FROM student;

WHERE: filter rows

• WHERE keeps only the rows whose condition is true.
• Put text in single quotes: 'A'.

SELECT name FROM student WHERE grade = 'A';

ORDER BY: sort the results

• ORDER BY sorts the rows by a column.
• Add DESC for largest first, or ASC (the default) for smallest first.

SELECT name, age FROM student ORDER BY age DESC;

AND and OR: two conditions

• AND keeps a row only when both conditions are true.
• OR keeps a row when at least one is true.

SELECT name, age FROM student WHERE grade = 'A' AND age >= 17;

COUNT and SUM

• COUNT(*) counts the rows that match; SUM(field) adds up a number field.
• Name the answer with AS, so its column has a clear heading.

SELECT COUNT(*) AS n FROM student WHERE grade = 'A';

In the exam: SQL

• IGCSE uses SELECT, FROM, WHERE, ORDER BY, SUM and COUNT, with AND, OR and the comparisons =, <>, <, >, <=, >=.
• The primary key is a field whose value is different in every record, like id here.
• To work out what a query shows, read it as FROM → WHERE → SELECT → ORDER BY.

SELECT Name, Grade
FROM Student
WHERE Grade = 'A';

Common mistakes

• Text values go in single quotes: WHERE name = 'Ann'.
• SELECT chooses the columns; WHERE filters the rows.
• Show only the fields the question asks for: filtering on grade does not mean grade belongs in SELECT.
• "How many" is COUNT; "total" is SUM.

Now you try

• Write one SQL query for each task.
• Press Check answer to run it against the table.