All subjects · Information Technology · WIT11 2 hour written exam on IT systems, networks and data
SQL: creating, populating and linking tables (CREATE, INSERT, UPDATE, DELETE, UNION, JOIN)
Edexcel IAL Information Technology · WIT11 2 hour written exam on IT systems, networks and data
Spec point 5.3.2 (b), (c) and (d): creating tables with appropriate data types, populating them (insert, amend, delete) and linking tables with UNION and JOIN. The June 2025 report listed the common errors: missing TABLE, missing data types, missing INTO and missing VALUES.
Creating a table
CREATE TABLE tbl_member (
memberID VARCHAR(6) PRIMARY KEY,
firstName VARCHAR(30),
lastName VARCHAR(30),
joined DATE
);Every field needs a data type: VARCHAR(n)/TEXT for text (and codes with leading zeros), INTEGER, DECIMAL(p,s) or REAL for numbers with decimals, DATE, BOOLEAN. Mark the primary key; a foreign key can be declared with FOREIGN KEY (field) REFERENCES other_table(field).
If memberID were INTEGER, '062833' would be stored as 62833, losing the leading zero.
Populating: INSERT, UPDATE, DELETE
INSERT INTO tbl_member (memberID, firstName, lastName, joined)
VALUES ('062833', 'Amira', 'Haddad', '2025-09-01');
UPDATE tbl_member SET lastName = 'Haddad-Ali' WHERE memberID = '062833';
DELETE FROM tbl_member WHERE joined < '2026-01-01';- INSERT needs INTO and VALUES, values in the same order as the fields.
- UPDATE and DELETE need a WHERE clause, or every record is changed or deleted.
After inserting three members (joined 2025-09-01, 2026-01-15, 2026-03-03) and running the DELETE above, 2 records remain.
Linking tables: UNION and JOIN
- UNION combines the results of two SELECTs with the same number and type of columns into one list, removing duplicates; UNION ALL keeps duplicates.
SELECT title FROM tbl_book UNION SELECT title FROM tbl_dvd gives 9 unique titles (UNION ALL gives 12 rows). This was the June 2024 question: unique titles available in one or both formats.
- JOIN combines columns from related tables where a condition matches, usually foreign key = primary key:
SELECT tbl_shift.date, tbl_employee.address
FROM tbl_shift
INNER JOIN tbl_employee ON tbl_shift.employee = tbl_employee.empNum;An INNER JOIN returns only rows with a match in both tables. Joining tbl_book and tbl_dvd on title returns the 3 titles available in both formats.
| dvdID | title | published | inStock | price |
|---|---|---|---|---|
| 1 | River Song | 1995-04-04 | 2 | 11.99 |
| 2 | The Glass Tower | 2010-08-08 | 5 | 13.50 |
| 3 | Space Dogs | 2012-12-12 | 6 | 9.00 |
| 4 | Paper Moons | 2020-02-20 | 1 | 12.99 |
tbl_dvd
Worked example
Write SQL to create a table tbl_room with a room code (for example 'B07'), a capacity and whether it has a projector, then add room B07 with capacity 30 and a projector.
- CREATE TABLE tbl_room (roomCode VARCHAR(4) PRIMARY KEY, capacity INTEGER, projector BOOLEAN);
- INSERT INTO tbl_room (roomCode, capacity, projector) VALUES ('B07', 30, TRUE);
Answer: CREATE TABLE with a data type for every field, then INSERT INTO ... VALUES ...
Check yourself
- Using tbl_book and tbl_dvd, how many rows does SELECT title FROM tbl_book UNION SELECT title FROM tbl_dvd; return?
Show answer
9. 8 book titles plus 4 DVD titles, with the 3 titles found in both counted once: 9.
- How many rows does the same query return with UNION ALL instead of UNION?
Show answer
12. 8 + 4 = 12; duplicates are kept.
- How many rows does SELECT tbl_book.title FROM tbl_book INNER JOIN tbl_dvd ON tbl_book.title = tbl_dvd.title; return?
Show answer
3. Only titles in both tables match: River Song, The Glass Tower, Paper Moons.
- Which INSERT statement is correctly formed?
- INSERT tbl_member (memberID, firstName) ('104512', 'Tomas');
- INSERT INTO tbl_member VALUE '104512', 'Tomas';
- ADD INTO tbl_member ('104512', 'Tomas');
- INSERT INTO tbl_member (memberID, firstName) VALUES ('104512', 'Tomas');
Show answer
INSERT INTO tbl_member (memberID, firstName) VALUES ('104512', 'Tomas');. INTO and VALUES are both needed; missing them were common errors in June 2025.
- Which CREATE statement is correct?
- CREATE tbl_item (itemID, name);
- CREATE TABLE tbl_item (itemID INTEGER PRIMARY KEY, name VARCHAR(40));
- CREATE TABLE tbl_item (itemID, name);
- MAKE TABLE tbl_item itemID name;
Show answer
CREATE TABLE tbl_item (itemID INTEGER PRIMARY KEY, name VARCHAR(40));. Needs the word TABLE and a data type for each field.
- What happens if DELETE FROM tbl_member; is run with no WHERE clause?
- Only the first record is deleted
- Nothing happens
- The table structure is deleted but records stay
- Every record in the table is deleted
Show answer
Every record in the table is deleted. WHERE limits which rows are affected.
- Three members joined on 2025-09-01, 2026-01-15 and 2026-03-03. After DELETE FROM tbl_member WHERE joined < '2026-01-01'; how many records remain?
Show answer
2. Only the 2025 member is deleted, leaving 2.
- Which statement changes the capacity of room B07 to 32?
- UPDATE tbl_room SET capacity = 32 WHERE roomCode = 'B07';
- UPDATE tbl_room capacity 32;
- INSERT INTO tbl_room SET capacity = 32;
- CHANGE tbl_room WHERE roomCode = 'B07' TO 32;
Show answer
UPDATE tbl_room SET capacity = 32 WHERE roomCode = 'B07';. UPDATE ... SET ... WHERE.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (2018), Unit 1
- Pearson Edexcel IAL Information Technology WIT11/01 question paper, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 mark scheme, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 examiner report, June 2025
Last updated 8 November 2026. Spotted a mistake? Tell us.