SQL: Querying & Changing a Database — Explainer

Step 1 of 13

TRY IT · SQL LAB

Run your own SQL

The three Sweet Escape tables are loaded in your browser. Type a statement and press Run (or Ctrl+Enter). You can SELECT, INSERT, UPDATE and DELETE. Referential integrity is switched on. Changes stay on this page only, and Reset data puts everything back.

TABLES · click to view

underlined = primary key · * = foreign key

Ctrl+Enter runs · separate statements with ;

Press Run to see the result.

Challenges

List the name and price of every product that is in stock and costs less than $10.
Show one answer
```
SELECT Name, Price
FROM PRODUCT
WHERE InStock = 'Yes' AND Price < 10;
```
How many customers live in San Fernando?
Show one answer
```
SELECT COUNT(*)
FROM CUSTOMER
WHERE Town = 'San Fernando';
```
Find the total quantity of Sorrel (ProductID 9) sold.
Show one answer
```
SELECT SUM(Qty)
FROM SALE
WHERE ProductID = 9;
```

List each customer’s name beside the name of every product they bought, newest sale first.

Show one answer

SELECT CUSTOMER.FullName, PRODUCT.Name, SALE.SaleDate
FROM SALE
  JOIN CUSTOMER ON SALE.CustomerID = CUSTOMER.CustomerID
  JOIN PRODUCT ON SALE.ProductID = PRODUCT.ProductID
ORDER BY SALE.SaleDate DESC;

Add yourself as customer 8, then record a sale (SaleID 13) of 2 Pine Tarts (ProductID 7) for you, dated today (write dates as 'YYYY-MM-DD').
Show one answer
```
INSERT INTO CUSTOMER VALUES (8, 'Your Name', 'Your Town', NULL);
INSERT INTO SALE VALUES (13, 8, 7, 2, '2026-10-15');
SELECT * FROM SALE WHERE CustomerID = 8;
```
Try to delete Priya Singh (customer 3). Why is it refused? Change the SQL so that she can be removed.
Show one answer
```
DELETE FROM SALE WHERE CustomerID = 3;
DELETE FROM CUSTOMER WHERE CustomerID = 3;
SELECT * FROM CUSTOMER;
```

REVISION · PAST-PAPER PRACTICE

Test yourself

Multiple-choice items in Paper 1 style and structured questions in Paper 2 style, adapted from CAPE Information Technology Unit 2 past papers. Your score and answers are saved on this device. When you have finished, submit them to your teacher at the end of this section.

MCQ best –Structured –

Paper 1 practice · 11 multiple-choice items

Choose one answer for each item, then check. Aim for 80% or better.

EduTech practice item · Module 1 Obj. 13 (query languages tested in CAPE U2 P1 2019 Item 12; 2020 Item 14)

1. Which of the following SQL statements are part of the Data Manipulation Language (DML)?
I. INSERT
II. UPDATE
III. CREATE TABLE

(A) I and II only (B) I and III only (C) II and III only (D) I, II and III

Answer: A. INSERT and UPDATE change the data, so they are DML. CREATE TABLE defines structure, so it is DDL.

Adapted from CAPE U2 P1 2019 Item 9; 2022 Item 11; 2024 Item 11

2. STAFF table:

Name	Dept	Salary
Bisram	Sales	4 500
Cumberbatch	IT	6 200
Daley	Sales	5 100
Edwards	Finance	3 900
Fung	IT	4 800

SELECT COUNT(*) FROM STAFF WHERE Dept = 'Sales' OR Salary > 5000;
What value is returned?

(A) 1 (B) 2 (C) 3 (D) 4

Answer: C. OR needs only ONE condition to be true: Bisram and Daley are in Sales, and Cumberbatch earns more than 5 000, so 3 rows are counted. With AND, only Daley would count.

Adapted from the CAPE IT Unit 2 specimen Paper 1, Item 10

3. LIST table, FirstName column: Kerry, John, Dwight, Shelly, Marcus.
SELECT FirstName FROM LIST WHERE FirstName LIKE '%e%';
How many records are returned?

(A) 1 (B) 2 (C) 3 (D) 4

Answer: B. '%e%' matches any name with an e anywhere in it: Kerry and Shelly. Standard SQL has no CONTAINS; LIKE with % on both sides does that job.

Adapted from the CAPE IT Unit 2 specimen Paper 1, Item 12

4. The CONTACT table (ID, FirstName, LastName) includes the row 2 | Nadia | Persad. Which statement removes ONLY Nadia Persad?

(A) DROP FROM CONTACT WHERE ID = 2 (B) DELETE FROM FirstName WHERE ID = 2 (C) DELETE WHERE ID = 2 (D) DELETE FROM CONTACT WHERE ID = 2

Answer: D. DELETE FROM names the TABLE (CONTACT) and WHERE picks the row. DROP removes a whole table, FirstName is a column, and DELETE needs FROM and a table name.

Adapted from the CAPE IT Unit 2 specimen Paper 1, Item 13

5. CONTACT also stores each person’s DOB (date of birth). Which query lists the names of everyone born BEFORE 1 January 2008?

(A) SELECT FirstName, LastName FROM CONTACT WHERE DOB < '2008-01-01'; (B) SELECT FirstName, LastName FROM CONTACT WHERE DOB > '2008-01-01'; (C) SELECT FirstName, LastName FROM CONTACT WHERE DOB = '2008-01-01'; (D) SELECT * FROM CONTACT WHERE DOB >= '2008-01-01';

Answer: A. Earlier dates are smaller, so ‘before’ means <. The question asks for names, so list the name columns rather than *.

EduTech practice item · Module 1 Obj. 13

6. Which clause lists products from the MOST expensive to the cheapest?

(A) ORDER BY Price ASC (B) GROUP BY Price (C) ORDER BY Price DESC (D) SORT BY Price DESC

Answer: C. ORDER BY sorts the result and DESC means descending (largest first). ASC, the default, puts the cheapest first. SQL has no SORT BY.

EduTech practice item · Module 1 Obj. 13

7. A report must show only the towns with more than 5 customers. In SELECT Town, COUNT(*) FROM CUSTOMER GROUP BY Town … the condition COUNT(*) > 5 belongs in the

(A) WHERE clause (B) HAVING clause (C) ORDER BY clause (D) FROM clause

Answer: B. HAVING filters the groups made by GROUP BY. WHERE filters single rows before grouping, so it cannot use COUNT().

EduTech practice item · Module 1 Obj. 12–13

8. SALE (SaleID, CustomerID*, Qty) and CUSTOMER (CustomerID, FullName) are joined to show each sale with the customer’s name. Which ON condition is correct?

(A) ON SALE.SaleID = CUSTOMER.CustomerID (B) ON SALE.Qty = CUSTOMER.FullName (C) ON CUSTOMER.CustomerID = 1 (D) ON SALE.CustomerID = CUSTOMER.CustomerID

Answer: D. A join matches the foreign key (SALE.CustomerID) with the primary key it refers to (CUSTOMER.CustomerID).

EduTech practice item · Module 1 Obj. 13

9. A clerk runs UPDATE PRODUCT SET Price = 15; on a table of 40 products. What happens?

(A) Every product’s price becomes 15 (B) Only the first product changes (C) The DBMS asks which row to change (D) Nothing changes until a WHERE clause is added

Answer: A. Without WHERE, UPDATE changes EVERY row. Check the WHERE clause before running any UPDATE or DELETE.

EduTech practice item · COUNT(DISTINCT …) as written in CAPE U2 P2 2021–2025

10. The 6 rows of ORDERS have these CustomerID values: C3, C1, C3, C2, C1, C3.
What does SELECT COUNT(DISTINCT CustomerID) FROM ORDERS; return?

(A) 1 (B) 2 (C) 3 (D) 6

Answer: C. DISTINCT counts each different value once: C1, C2 and C3 make 3. COUNT(*) would give 6.

Adapted from CAPE U2 P1 2019 Item 14 (referential integrity)

11. SALE.CustomerID is a foreign key to CUSTOMER and referential integrity is enforced. No customer 99 exists. INSERT INTO SALE VALUES (20, 99, 4, 1, '2026-10-07'); will

(A) add the sale and create customer 99 (B) add the sale with an empty CustomerID (C) delete every sale for customer 99 (D) be rejected by the DBMS

Answer: D. Referential integrity rejects a foreign key value that matches no primary key in the parent table. The customer must be added first.

Paper 2 practice · structured questions (28 marks)

Write a full answer to each part, then show the mark scheme and tick every point you earned. Marks for a part are capped at its maximum. Mark schemes are EduTech’s own; CXC does not publish them.

EduTech practice question · Module 1 Obj. 13 (styles from the CAPE IT Unit 2 specimen Paper 1, Items 10–13)

Question 2 [13 marks]

Coral Reef Swim Club keeps this SWIMMER table:

SwimmerID	Name	AgeGroup	BestTime
S1	Ava Ramlal	U15	38.4
S2	Jabari Hinds	U17	36.9
S3	Kiara Lewis	U15	41.2
S4	Omari Clarke	U15	37.1
S5	Zara Ali	U13	39.8

(a) State the output of SELECT Name FROM SWIMMER WHERE AgeGroup = 'U15' AND BestTime < 40 ORDER BY BestTime; [3]

MARK SCHEME (a) · max 3

Omari Clarke [1]Ava Ramlal [1]In that order (fastest time first), with Kiara Lewis left out [1]

(b) A coach runs DELETE FROM SWIMMER; intending to remove only Zara Ali. Explain what happens and write the correct statement. [3]

MARK SCHEME (b) · max 3

Every swimmer is deleted, because there is no WHERE clause [1]DELETE FROM SWIMMER [1]WHERE SwimmerID = 'S5' (or Name = 'Zara Ali') [1]

(c) Race times are stored in RESULT (SwimmerID*, EventID, Time). Explain why the DBMS refuses DELETE FROM SWIMMER WHERE SwimmerID = 'S2'; while Jabari Hinds has results, and how he could be removed. [3]

MARK SCHEME (c) · max 3

Referential integrity is enforced [1]RESULT rows have a foreign key referring to S2, which would be left pointing at nothing [1]Delete (or reassign) his RESULT rows first, then delete the swimmer [1]

(d) Distinguish between the Data Definition Language (DDL) and the Data Manipulation Language (DML), giving ONE SQL command for each. [4]

MARK SCHEME (d) · max 4

DDL defines or changes the structure of the database (tables, fields, keys) [1]DDL example: CREATE TABLE, ALTER TABLE or DROP TABLE [1]DML adds, changes, deletes or retrieves the data in the tables [1]DML example: INSERT, UPDATE, DELETE or SELECT [1]

Submit to your teacher

Send your multiple-choice choices and written answers to your teacher for marking. Your teacher will also see whether you opened the mark scheme or checked your answers before submitting. You can submit again if you improve your answers.

Full name Class / group

Items are adapted from CAPE® Information Technology Unit 2 Papers 01 and 02 (2019–2026) for practice. © Caribbean Examinations Council for the original papers.