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. That is the job of the data definition language, and SQL is how you write it.
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).
SQL has two halves. DDL statements change the structure (the tables themselves); DML statements work with the data inside them. Every DDL statement in this lesson builds the library database used throughout this topic.
| Data definition language (DDL) | Data manipulation language (DML) | |
|---|---|---|
| Works on | the structure: databases, tables, fields, keys | the data: the records in the tables |
| Statements | CREATE DATABASE, CREATE TABLE, ALTER TABLE | SELECT, INSERT INTO, UPDATE, DELETE FROM |
| SQL data type | Holds | Example field |
|---|---|---|
| CHARACTER | a single character (fixed length) | Grade: 'A' |
| VARCHAR(n) | a string of up to n characters | Surname VARCHAR(20) |
| BOOLEAN | TRUE or FALSE | Returned |
| INTEGER | a whole number | Pages |
| REAL | a number with a fractional part | Price |
| DATE | a date | LoanDate |
| TIME | a time of day | StartTime |
CREATE DATABASE Library;
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.
In the lab, choose the example "Create a table" and run it: a new TEACHER table appears with its fields and data types. Then edit the statement and create a table of your own.
CREATE TABLE Member(
MemberID VARCHAR(4),
FirstName VARCHAR(20),
Surname VARCHAR(20),
FormGroup VARCHAR(3),
PRIMARY KEY (MemberID)
);
CREATE TABLE Loan(
LoanID VARCHAR(4),
BookID VARCHAR(4),
MemberID VARCHAR(4),
LoanDate DATE,
Returned BOOLEAN,
PRIMARY KEY (LoanID),
FOREIGN KEY (BookID) REFERENCES Book(BookID),
FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);
CREATE TABLE Book(
BookID VARCHAR(4),
Title VARCHAR(40),
Author VARCHAR(30),
Genre VARCHAR(10),
Price REAL,
PRIMARY KEY (BookID)
);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.
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Which statement belongs to the data definition language?
Which data type should store the price of a book, such as 8.50?
In the lab's "Create a table" example, which field is the primary key of TEACHER?
What is the industry standard language for both DDL and DML?
Designs change after the tables have been created. ALTER TABLE changes a table definition without losing the data already stored. Try the lab examples "Add a field" and "Add a foreign key".
ALTER TABLE Book ADD Pages INTEGER; ALTER TABLE Club ADD PRIMARY KEY (ClubID); ALTER TABLE Loan ADD FOREIGN KEY (MemberID) REFERENCES Member(MemberID);
ALTER TABLE Member ADD FOREIGN KEY (FormGroup) REFERENCES Form(FormGroup);
ALTER TABLE Member ADD Email VARCHAR(40);
ALTER TABLE changes the structure. To change the values stored in a field you need the DML statement UPDATE, covered in the next lesson.
Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.
Which statement adds a field DueDate to the LOAN table?
Which line correctly declares a foreign key in LOAN?
A field stores a student's grade as one letter, such as B. Which data type is most suitable?
Why must the MEMBER table be created before the LOAN table?
This skill is now recorded on your profile. Keep the chain going.