Understand and write basic SQL queries including SELECT, WHERE, AND, OR, ORDER BY

Databases – IGCSE 0478 (Topic 9)

Objective

Understand and write basic SQL queries using SELECT, WHERE, AND, OR, ORDER BY, COUNT and SUM. Identify a suitable primary key, choose appropriate data types, and recognise why NULL values are not allowed in a primary key when defining a single‑table database.

1. What is a Database?

• A database is an organised collection of data that can be stored, accessed and updated efficiently.
• In the IGCSE you will only be asked to work with single‑table databases – no joins between tables are examined.

2. Primary Key

• A primary key uniquely identifies each record in a table.
• Requirements (check‑list):
  • Unique – no two rows share the same value.
  • Non‑NULL – a primary key column cannot contain NULL.
  • Stable – the value should not change over time.
• A natural key (e.g., StudentID) is acceptable only if it meets the three criteria above.

3. Choosing Data Types (Cambridge‑approved types)

4. Sample Table – Students

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    Name      STRING,
    Age       INTEGER,
    Gender    CHAR(1),          -- ‘M’ or ‘F’
    Grade     CHAR(1),          -- A, B, C …
    Score     INTEGER,
    BirthDate DATE
);

5. The SELECT Statement

Retrieves data from a table. Keywords are conventionally written in upper‑case (examiners ignore case, but the style helps readability).

SELECT column1, column2, …
FROM table_name;

• Use * to select all columns.

6. Filtering Rows – WHERE

SELECT column1, column2
FROM table_name
WHERE condition;

• Comparison operators: =, <>, <, >, <=, >=.
• String and date literals must be enclosed in single quotes.

7. Combining Conditions – AND & OR

• AND – both conditions must be true.
• OR – at least one condition must be true.
• Use parentheses ( ) to control evaluation order.

Combined‑criteria example (WHERE + AND/OR + ORDER BY)

SELECT Name, Score
FROM Students
WHERE Gender = 'F' AND (Grade = 'A' OR Score > 90)
ORDER BY Score DESC;

8. Sorting Results – ORDER BY

• Default order is ascending (ASC).
• Use DESC for descending order.
• Multiple columns can be listed, separated by commas.

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC, column2 DESC;

9. Aggregation Functions (COUNT & SUM)

• COUNT(column) – number of rows (or non‑NULL values) in the column.
• SUM(column) – total of all numeric values in the column.
• Alias syntax (AS alias_name) is optional but useful; the alias appears in the answer sheet.

-- How many students scored above 80?
SELECT COUNT(*) AS NumHighScorers
FROM Students
WHERE Score > 80;

-- Total of all scores (for a later average calculation)
SELECT SUM(Score) AS TotalScore
FROM Students;

10. Example Queries Covering All Required Constructs

1. Simple SELECT + WHERE – students scoring > 80

   SELECT Name, Score
   FROM Students
   WHERE Score > 80;
   

2. AND / OR with parentheses – female students who are either grade A or have a score > 90

   SELECT *
   FROM Students
   WHERE Gender = 'F' AND (Grade = 'A' OR Score > 90);
   

3. ORDER BY (multiple columns) – descending score, then alphabetical name

   SELECT Name, Score
   FROM Students
   ORDER BY Score DESC, Name ASC;
   

4. Numeric comparison – IDs of students aged ≤ 15

   SELECT StudentID
   FROM Students
   WHERE Age <= 15;
   

5. COUNT with WHERE – number of students with grade ‘A’

   SELECT COUNT(*) AS NumAStudents
   FROM Students
   WHERE Grade = 'A';
   

6. SUM – total of all scores

   SELECT SUM(Score) AS TotalScore
   FROM Students;
   

7. DATE condition – students born after 1 January 2007

   SELECT Name, BirthDate
   FROM Students
   WHERE BirthDate > '2007-01-01';
   

11. Practice Questions

1. Write a query to display the Name and Grade of all male students.
2. List the records of students who have a Score between 80 and 90 inclusive.
3. Show the Name, Age and Score of students whose Grade is not ‘C’.
4. Retrieve all columns for students whose Gender is ‘F’ and Age is greater than 15, sorted by Score from highest to lowest.
5. How would you modify a query to return results in alphabetical order by Name?
6. Primary‑key question: Which field would you choose as the primary key for the Students table and why?
   Marking hint:* 1 mark for naming a unique field, 1 mark for a justification that mentions uniqueness, non‑NULL and stability.
7. Data‑type question: Suggest an appropriate data type for each column in the Students table.
   Marking hint:* 1 mark per correct data‑type (6 marks total).
8. Aggregation question: Write a query that returns the number of students who scored at least 85.

12. Summary (Key Take‑aways)

• SELECT chooses which columns to display.
• WHERE limits rows based on one or more conditions.
• AND and OR combine conditions; parentheses control precedence.
• ORDER BY sorts the final result set (ASC or DESC).
• COUNT and SUM are the only aggregation functions required for the IGCSE; use AS to give the result a clear alias.
• Primary key must be unique, non‑NULL and stable; a natural key is acceptable only if it meets these criteria.
• Choose data types from the five Cambridge‑approved options; specify length for CHAR (e.g., CHAR(1)).
• Remember that joins are **not** examined – all queries are on a single table.
• Practise writing queries on the sample Students table to build confidence for the exam.