Read, understand and complete SQL scripts to query data

Databases – Reading, Understanding and Completing SQL Scripts (Cambridge IGCSE 0478)

1. What is examined?

• Identifying the primary key for a table.
• Choosing appropriate field (column) types – INTEGER, CHAR, VARCHAR, DATE, BOOLEAN, etc.
• Applying simple validation constraints (e.g. CHECK (value BETWEEN …)).
• Writing and completing single‑table SELECT statements using the clauses: SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING (only with COUNT or SUM).
• Understanding the order in which the clauses are processed.

What you will NOT be examined on (to avoid over‑learning):

• Multi‑table JOIN operations.
• UPDATE, DELETE, or INSERT statements (except a single example of INSERT for table design).
• Advanced functions such as STRING_AGG, window functions, or sub‑queries beyond the simple EXISTS check.

2. Core Database Concepts

2.1 Example – Designing a table (only for reference)

CREATE TABLE Students (
    StudentID   INTEGER PRIMARY KEY,
    FirstName   VARCHAR(20) NOT NULL,
    LastName    VARCHAR(20) NOT NULL,
    Year        INTEGER CHECK (Year BETWEEN 9 AND 13)
);

Only the concepts above are required for the exam; you will not be asked to write the full CREATE TABLE statement.

3. Single‑Table SELECT Syntax

The basic structure (always written in upper‑case in exam answers):

SELECT column1, column2, …
FROM   table_name
WHERE  condition                -- optional, filters rows before any aggregation
GROUP BY column1, column2, …    -- optional, needed when using COUNT or SUM
HAVING aggregate_condition      -- optional, filters groups after aggregation
ORDER BY column [ASC|DESC];    -- optional, final sorting

3.1 Clause order in processing

1. FROM – determine the source table.
2. WHERE – keep only rows that satisfy the condition.
3. GROUP BY – form groups (if an aggregate function is used).
4. HAVING – keep only groups meeting the aggregate condition.
5. SELECT – choose columns or calculate aggregates.
6. ORDER BY – sort the final result set.

3.2 Allowed aggregate functions (Cambridge)

• COUNT(column) – number of non‑NULL values.
• SUM(column) – total of numeric values.

When an aggregate is used, every column in the SELECT list must either be aggregated or appear in the GROUP BY list.

4. Reading and Completing SQL Scripts

Each script below contains blanks (indicated by _____). Fill them in with the correct clause or expression.

4.1 Example script

SELECT FirstName, LastName, Year
FROM Students
WHERE _____
ORDER BY Year ASC;

Complete the WHERE clause to show only Year 10 pupils.

4.2 Practice scripts

1. List the full names of all Year 10 students.

   SELECT CONCAT(FirstName, ' ', LastName) AS FullName
   FROM Students
   WHERE _____;
           

2. Find the total number of students in each year group.

   SELECT Year, COUNT(*) AS NumStudents
   FROM Students
   GROUP BY _____
   ORDER BY Year;
           

3. Show the average mark for the Enrollments table (single‑table example).

   SELECT AVG(Mark) AS AvgMark
   FROM Enrollments
   WHERE _____;          -- e.g. restrict to a particular course if required
           

4. Retrieve the IDs of students who have not yet taken any exam (assume a table Results with StudentID).

   SELECT s.StudentID
   FROM Students AS s
   WHERE NOT EXISTS (
       SELECT 1 FROM Results r
       WHERE r.StudentID = _____
   );
           

5. Insert a new course called “Geography” worth 3 credits (only the values are required for the exam).

   INSERT INTO Courses (CourseID, CourseName, Credits)
   VALUES (_____, 'Geography', _____);
           

6. Increase every mark that is below 50 by 5 points.

   UPDATE Enrollments
   SET Mark = Mark + 5
   WHERE _____;
           

5. Sample Data (single‑table focus)

StudentID | FirstName | LastName | Year
---------------------------------------
1         | Alice     | Brown    | 10
2         | Ben       | Carter   | 11
3         | Clara     | Davis    | 10
4         | David     | Evans    | 12

EnrollID | StudentID | Mark
-----------------------------
1        | 1         | 78
2        | 1         | 85
3        | 2         | 62
4        | 3         | 90
5        | 4         | 47

6. Common Pitfalls (IGCSE focus)

• Using a column not in GROUP BY without aggregating it. Example: SELECT Year, FirstName FROM Students GROUP BY Year; is illegal.
• Confusing WHERE and HAVING. Use WHERE for row filters; HAVING only when a GROUP BY is present.
• Leaving out the terminating semicolon. The exam board expects a ; at the end of every statement.
• Writing column names incorrectly (case‑sensitivity). Stick to the exact names used in the schema.
• Assuming JOIN is required. All IGCSE questions can be answered with a single table.

7. Quick Reference – Exam‑Friendly SQL Cheat Sheet

8. Self‑Check Questions

1. Define “primary key”. Give an example of a suitable primary key for the Students table.
2. Choose the most appropriate data type for each of these fields: StudentID, FirstName, Year, Mark.
3. Write a query that lists all students in Year 12, sorted alphabetically by last name.
4. Write a query that returns the number of students in each year group, showing only years with at least three students.
5. Explain why the following statement is illegal and correct it:
   SELECT Year, FirstName FROM Students GROUP BY Year;

9. Teacher Reference Answers

1. Primary key – a column that uniquely identifies each row and cannot be NULL. Example: StudentID in the Students table.
2. • StudentID – INTEGER (or INT)
   • FirstName – VARCHAR(20) (or CHAR(20) if fixed length)
   • Year – INTEGER with a CHECK (Year BETWEEN 9 AND 13)
   • Mark – INTEGER with CHECK (Mark BETWEEN 0 AND 100)

3. SELECT FirstName, LastName, Year
   FROM Students
   WHERE Year = 12
   ORDER BY LastName ASC;
           

4. SELECT Year, COUNT(*) AS NumStudents
   FROM Students
   GROUP BY Year
   HAVING COUNT(*) >= 3
   ORDER BY Year;
           

5. The statement is illegal because FirstName is neither aggregated nor listed in the GROUP BY. Correct version:

   SELECT Year, COUNT(*) AS NumStudents
   FROM Students
   GROUP BY Year;
           

   (If you need FirstName, you must remove GROUP BY or aggregate it appropriately.)