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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

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';

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

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.

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.

dvdIDtitlepublishedinStockprice
1River Song1995-04-04211.99
2The Glass Tower2010-08-08513.50
3Space Dogs2012-12-1269.00
4Paper Moons2020-02-20112.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.

  1. CREATE TABLE tbl_room (roomCode VARCHAR(4) PRIMARY KEY, capacity INTEGER, projector BOOLEAN);
  2. 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 ...

Key idea: CREATE TABLE name (field TYPE, ...); INSERT INTO table (fields) VALUES (...); UPDATE table SET field = value WHERE ...; DELETE FROM table WHERE ...; UNION stacks results without duplicates; JOIN matches rows on related keys.

Check yourself

  1. 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.

  2. How many rows does the same query return with UNION ALL instead of UNION?
    Show answer

    12. 8 + 4 = 12; duplicates are kept.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.