SQL data definition language lesson pack

At a glance

The shape of the lesson

Topic

Databases

Syllabus reference

Cambridge International AS and A Level Computer Science 9618, 8.3

Level

AS Level

Duration

45 minutes, lab-led; stretches to a 60 minute block (see the contingency notes)

Position in topic

Lesson 5 of 6 in Databases; follows Database management systems; leads into SQL data manipulation language

Central model

Fig. 1: CREATE TABLE names the table, defines each field and declares the primary key

Cooperative structure

Frayer Model

Assessment

Stage 1 check and the exam-style plenary from the student lesson, plus the worksheet as the exit task

Learning objectives

By the end of the lesson, learners can

Section by section

DDL, DML and data types: SQL has two halves.
CREATE TABLE: In the lab, choose the example "Create a table" and run it: a new TEACHER table appears with its fields and data types.
ALTER TABLE: Designs change after the tables have been created.

Key points to leave on the board

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.

The core visual model

One picture every idea returns to

The DBMS carries out all creation and modification of the database structure using its data definition language (DDL), and all queries and maintenance of the data using its data manipulation language (DML). The industry standard for both is SQL. The DDL statements you need are CREATE DATABASE, CREATE TABLE (each field with a data type: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME), ALTER TABLE, PRIMARY KEY (field) and FOREIGN KEY (field) REFERENCES Table(Field).

Fig. 1: CREATE TABLE names the table, defines each field and declares the primary key

Lesson sequence

Forty-five minutes, phase by phase

Time	Phase	What happens in the room	Grouping
0 to 5 min	Hook: predict	Open with the question in the title, Build the structure.. Before a single record can be stored, the database and its tables have to exist, with every field given a name and a data type, and the keys that link the tables declared. Put the figure on the board: it is the one picture every idea in the lesson returns to.	Whole class, then pairs
5 to 13 min	Teach and check: DDL, DML and data types	SQL has two halves. Model the worked example Choosing data types on the board, one line at a time.	Whole class, then pairs
13 to 21 min	Teach and check: CREATE TABLE	In the lab, choose the example "Create a table" and run it: a new TEACHER table appears with its fields and data types. Project tab 6 of the lab (DDL and DML: create a table) and let a student drive it while the class predicts. Model the worked example Write the DDL on the board, one line at a time.	Pairs
21 to 30 min	Teach and check: ALTER TABLE	Designs change after the tables have been created. Project tab 6 of the lab (DDL and DML: change a table) and let a student drive it while the class predicts. Model the worked example Link a new table on the board, one line at a time. Your turn: Write the SQL to add a field Email, up to 40 characters, to the MEMBER table.	Frayer Model
30 to 36 min	Check	The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong.	Individual, then whole class
36 to 43 min	Plenary	The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording.	Individual, random call
43 to 45 min	Exit and homework	Set the worksheet below (6 questions, 17 marks). Learners self-assess against the objectives before they leave.	Individual

Timing and contingency

Protect the parts that carry the learning

The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.

Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.

Cut first if the build overruns: the last check in ALTER TABLE, never the check or the plenary.

In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.

Running the cooperative task

Frayer Model

Pairs fill a four-box grid for the key term: definition, characteristics, examples and non-examples. The non-examples box is where the misconceptions surface, so give it the most time.

The full structure, with its variations and the other twelve, is on the cooperative-learning page.

Examiner traps to pre-empt

What to head off, and how

Trap learners fall into	Teaching move that pre-empts it
An ID such as M01 or a phone number such as 07700 900123 is text, not INTEGER: it can have letters or a leading zero, and you never do arithmetic with it.	Return to DDL, DML and data types: ask a pair to state the correct version and show it on the lab before moving on.
A foreign key field must have the same data type as the primary key it references.	MemberID VARCHAR(4) in MEMBER needs MemberID VARCHAR(4) in LOAN, not INTEGER.
ALTER TABLE changes the structure.	To change the values stored in a field you need the DML statement UPDATE, covered in the next lesson.

Worksheet

SQL data definition language: worksheet

6 questions, 17 marks. Written in the style of the papers; no past paper question is reproduced.

NameClassDate

Q1 · explain[2 marks]

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

Q2 · 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).

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

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

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

Q6 · write[1 mark]

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

Answer key and marking notes

Q1 · 2 marks

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

Q2 · 5 marks

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

Q3 · 3 marks

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

Q4 · 3 marks

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

Q5 · 3 marks

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

Q6 · 1 mark

CREATE DATABASE Cinema;

Quiz

6 multiple-choice questions on this lesson

From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.

Assign in the app Run as a class game Topic question bank

Q11 mark

Which pair of SQL statements both belong to the data definition language (DDL)?

ACREATE TABLE and ALTER TABLE
BCREATE TABLE and INSERT INTO
CALTER TABLE and UPDATE
DSELECT and DELETE FROM

Q21 mark

A field stores a customer's telephone number, such as 07700 900461. Which SQL data type is most suitable?

AVARCHAR(15)
BINTEGER
CREAL
DCHARACTER

Q31 mark

This statement is run and the DBMS reports a syntax error.

CREATE TABLE Club(
    ClubID VARCHAR(3),
    ClubName VARCHAR(25)
    MeetDay VARCHAR(9),
    PRIMARY KEY (ClubID)
);

What is the error?

AA comma is missing after ClubName VARCHAR(25)
BPRIMARY KEY (ClubID) must come before the field definitions
CVARCHAR must be written without a size in brackets
DThe statement must end with a comma instead of a semicolon

Q41 mark

The tables BOOK and LOAN have been created, but the link between them was not declared. Which statement makes BookID in LOAN a foreign key?

AALTER TABLE Loan ADD FOREIGN KEY (BookID) REFERENCES Book(BookID);
BALTER TABLE Book ADD FOREIGN KEY (BookID) REFERENCES Loan(BookID);
CALTER TABLE Loan ADD PRIMARY KEY (BookID) REFERENCES Book(BookID);
DUPDATE Loan SET FOREIGN KEY (BookID) REFERENCES Book(BookID);

Q51 mark

MEMBER has already been created with MemberID VARCHAR(4) as its primary key, holding values such as M01. A developer then runs:

CREATE TABLE Fine(
    FineID VARCHAR(4),
    MemberID INTEGER,
    Amount REAL,
    Paid BOOLEAN,
    PRIMARY KEY (FineID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

What is wrong with this design?

AMemberID in FINE is INTEGER but the primary key it references is VARCHAR(4), so values such as M01 cannot be stored
BFINE cannot have a foreign key because MEMBER was created before it
CAmount should be INTEGER because money is always a whole number of pence in SQL
DThe foreign key must be declared before the primary key in a CREATE TABLE statement

Q61 mark

The BOOK table already holds five records. The statement ALTER TABLE Book ADD Pages INTEGER; is run. What does the table hold afterwards?

AThe same five records, each with a new Pages field that is empty
BNo records, because changing the structure deletes the existing data
CThe same five records, each with Pages set to 0 automatically
DFive records unchanged, plus one new record holding the field name Pages

Quiz key

Q1: A

CREATE TABLE and ALTER TABLE both create or change the structure, so both are DDL. INSERT INTO, UPDATE, SELECT and DELETE FROM all work with the records stored in the tables, so they are DML; each wrong option includes at least one of them.

Q2: A

A phone number is text: it can start with 0 and contain a space, and it is never used in calculations, so VARCHAR with a suitable maximum length is right. INTEGER and REAL would lose the leading zero and cannot hold the space. CHARACTER holds a single character only.

Q3: A

Each field definition must be followed by a comma, and the line ClubName VARCHAR(25) has none, so the DBMS meets MeetDay where it expects a comma. The keys are correctly declared after the fields, VARCHAR(n) needs its size, and a statement ends with a semicolon.

Q4: A

The foreign key is in LOAN, so ALTER TABLE Loan ADD FOREIGN KEY (BookID), and it refers to the primary key BookID of BOOK. The second option points the link the wrong way, making BOOK depend on LOAN. A PRIMARY KEY does not take a REFERENCES clause, and UPDATE changes data values, not the table structure.

Q5: A

A foreign key must have the same data type as the primary key it references; MemberID values like M01 are text, so an INTEGER field cannot hold them. Creating MEMBER first is exactly what is required. REAL is a sensible type for an amount such as 2.50, and PRIMARY KEY then FOREIGN KEY after the fields is the normal order.

Q6: A

ALTER TABLE adds the field to the definition without losing the stored data, and the new field is empty (null) in every existing record until an UPDATE fills it. Existing data is kept, no default of 0 is set unless one is defined, and no new record is inserted.

Lab and demo

What to project

The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.

Tab 6DDL and DML: create a table

Slides

Slides for this lesson

A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.

Also for this topic

Everything else you can use

Student lessonSQL data definition language Practice pageWorked solutions, reveal one at a time Topic question bankEvery MCQ for Databases Revision notesOne page per lesson, with the traps Notes PDFPrint the topic notes Topic planDatabases: every lesson in order

Original work by the TheLucidSTEM team. The worksheet items are written in the style of the papers; no past paper question is reproduced.