Skip to content
MarkSchemeMarkScheme
Sign up — no card

9618 · 8.3

Data Definition Language (DDL) and Data Manipulation Language (DML) — practice questions

Practice and worked examples for 9618 Data Definition Language (DDL) and Data Manipulation Language (DML). Short previews only — attempt the full question in MarkScheme against the official scheme.

Worked example 1

Write a DDL statement to create a table named Tutor to store information about school tutors. The table should include a unique ID, the tutor's first name, last name, and subject. The ID should be the primary key, and the names cannot be empty. Define appropriate data types.

Show solution outline
CREATE TABLE Tutor (
    TutorID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Subject VARCHAR(100)
);

Explanation:

  • CREATE TABLE Tutor (...): This is the DDL command to create a new table named Tutor.
  • TutorID INT PRIMARY KEY: Defines a column TutorID of type Integer and sets it as the PRIMARY KEY. This ensures every tutor has a unique, non-null ID.
  • FirstName VARCHAR(50) NOT NULL: Defines a column for the first name, allowing up to 50 characters. The NOT NULL constraint ensures this field must have a value.
  • LastName VARCHAR(50) NOT NULL: Same as above for the last name.
  • Subject VARCHAR(100): Defines a column for the subject, allowing up to 100 characters. This can be left NULL if a tutor's subject is not yet assigned.

Worked example 2

Using the Tutor table created previously, perform the following DML operations:

  1. Add a new tutor: Dr. Alan Turing, who teaches Computer Science and has TutorID 101.
  2. Change the subject for TutorID 101 to 'Advanced Computer Science'.
  3. Retrieve the first and last names of all tutors who teach a subject containing the word 'Computer'.
Show solution outline

1. Insert a new record:

INSERT INTO Tutor (TutorID, FirstName, LastName, Subject)
VALUES (101, 'Alan', 'Turing', 'Computer Science');

Explanation: The INSERT INTO command specifies the table and the columns to be populated. The VALUES clause provides the data for the new row, in the corresponding order.

2. Update an existing record:

UPDATE Tutor
SET Subject = 'Advanced Computer Science'
WHERE TutorID = 101;

Explanation: The UPDATE command targets the Tutor table. SET specifies the column and its new value. The WHERE clause is crucial; it isolates the update to only the row where TutorID is 101.

3. Select specific data:

SELECT FirstName, LastName
FROM Tutor
WHERE Subject LIKE '%Computer%';

Explanation: SELECT specifies the columns we want to see. FROM indicates the table to query. WHERE Subject LIKE '%Computer%' filters the results to include only tutors whose Subject field contains the substring 'Computer'. The % is a wildcard character.

Mark a 9618 question on this topicFull lessonOpen in course studio