AS · Practice questions · SQL data definition language

Define it, then fill it.

Six original Cambridge-style questions on SQL DDL, with model statements and mark points.

Original questions All questions on this page are original work, written in the Cambridge AS & A Level style. They are not from past papers. They test the same concepts and skills the syllabus rewards.

Keep these straight

Name, type, comma.

DDL changes structure (CREATE, ALTER); DML works with data (SELECT, INSERT, UPDATE, DELETE).
Types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME. IDs and phone numbers are text.
Keys: PRIMARY KEY (field); FOREIGN KEY (field) REFERENCES Table(Field).
Check: brackets matched, commas between lines, a semicolon at the end.

Explain

[2 marks]

Explain the difference between a data definition language (DDL) and a data manipulation language (DML).

DDL is used by the DBMS to create and modify the structure of the database (tables, fields, keys). ✓
DML is used to query and maintain the data stored in the tables (select, insert, update, delete). ✓

Write

[5 marks]

A cinema stores films in a table FILM. Write an SQL statement to create the table with these fields: FilmID (a code such as F0123, primary key), Title (up to 50 characters), Rating (one character, such as U), Duration (whole number of minutes), Released (date) and InColour (true or false).

CREATE TABLE Film( ✓
FilmID VARCHAR(5), Title VARCHAR(50), ✓
Rating CHARACTER, Duration INTEGER, ✓
Released DATE, InColour BOOLEAN, ✓
PRIMARY KEY (FilmID) ); ✓

Write

[3 marks]

A table SCREENING(ScreeningID, FilmID, ScreenNo, StartTime) has already been created with ScreeningID as its primary key. Write SQL statements to (a) make FilmID a foreign key linked to FILM and (b) add a field Price that stores a real number.

(a) ALTER TABLE Screening ADD FOREIGN KEY (FilmID) ✓
REFERENCES Film(FilmID); ✓
(b) ALTER TABLE Screening ADD Price REAL; ✓

Recall

[3 marks]

State a suitable SQL data type for (a) a telephone number, (b) the time a lesson starts, (c) whether a fee has been paid.

(a) VARCHAR(n), e.g. VARCHAR(15) (it may have a leading zero or spaces). ✓
(b) TIME. ✓
(c) BOOLEAN. ✓

Analysis

[3 marks]

A student writes:

CREATE TABLE Pupil(
    PupilID INTEGER
    Name VARCHAR(30),
    FormID VARCHAR(3),
    PRIMARY KEY (Name)
);

Identify three problems with this statement.

A comma is missing after PupilID INTEGER. ✓
Name is not a suitable primary key: two pupils can have the same name; PupilID should be the primary key. ✓
FormID is probably meant to be a foreign key, but no FOREIGN KEY (FormID) REFERENCES ... is declared (accept: it must match the type of the referenced key). ✓

Write

[1 marks]

Write the SQL statement to create a new database called Cinema.

CREATE DATABASE Cinema; ✓

Mark this once you have attempted all six and checked your working. It records a Practiced badge on the topic and adds a one-time bonus. Revealing the solutions alone does not count.