Part of the SQL Data Definition Language (DDL), the CREATE statement is used to create databases and tables.

It is common to use the DROP command to remove any existing database/tables, otherwise the CREATE command could generate an error is created and halt the SQL script.

CREATE Database

A database server can have many databases, for different applications or users.

CREATE DATABASE databaseName

Note: Access and SQLite store databases as separate files, so the CREATE database command does not get used.

CREATE Table

This is used to specify filed names, datatypes, keys and constraints.

CREATE TABLE tableName (
    fieldName1 dataType,
    fieldName2 dataType,
    ......
);

Data Types

In National 5 and Higher you were expected to use datatypes: text, number, date, time, Boolean.
In Advanced Higher you must use standard SQL types as described on the Data Types page (varchar, int, float, date, time)

Primary and Keys

fieldName dataType PRIMARY KEY,
-- or
PRIMARY KEY (fieldName1)
-- or
PRIMARY KEY (fieldName1, fieldName2, ...)

fieldName dataType FOREIGN KEY REFERENCES tableName (fieldName)
-- or
FOREIGN KEY(fieldName) REFERENCES tableName (fieldName)

Some dialects of SQL allow the PRIMARY or FOREIGN KEY constraint to be applied in the clause used to identify the data type for the field; other dialects require the PRIMARY or FOREIGN KEY constraint to be applied in a separate clause.

If the primary or foreign key consists of multiple columns, users must specify them in a separate clause at the end of the CREATE TABLE statement.

Auto Increment

AUTO_INCREMENT automatically generates a unique number when a new record is inserted. This is often used for surrogate keys.

fieldName dataType AUTO_INCREMENT

-- example
userID int AUTO_INCREMENT

Required Fields

Required fields use NOT NULL to ensure a field always contains a value and is not left empty (NULL)

fieldName dataType NOT NULL

-- example
membershipType varchar(10) NOT NULL

Check Constraints

Check constraints ensure that a field value meets specific conditions.

fieldName dataType CHECK(fieldName condition)

-- examples
level VARCHAR(2) CHECK(status IN ('N4', 'N5', 'H', 'AH' ) )
month INT CHECK(month BETWEEN 1 AND 12)

Standard SQL provides the CHECK constraint, as described and exemplified here. However, the CHECK constraint is not provided in all dialects of SQL (for example, MS Access and MySQL do not support the use of CHECK).

In the case of MySQL, the CHECK constraint is ignored and the intended data validation is not carried out. To implement the CHECK constraint in MySQL, triggers or views must be used.

Note: candidates should implement triggers or views within their project solution, as required; however, these constraints are not assessed in the Advanced Higher Computing Science course.

Multiple Constraints

It is possible to apply several constraints to one field, for example:

 fieldName dataType NOT NULL PRIMARY KEY

Example: Travel Agency

DROP DATABASE IF EXISTS TravelAgency;
CREATE DATABASE TravelAgency;

CREATE TABLE Resort (
resortID int NOT NULL PRIMARY KEY,
resortName varchar(20) NOT NULL,
resortType varchar(20) NOT NULL CHECK (resortType IN('coastal', 'city', 'island', 'country'))
);

CREATE TABLE Hotel (
hotelRef varchar(4) NOT NULL PRIMARY KEY,
hotelName varchar(20) NOT NULL,
resortID int NOT NULL,
starRating int NOT NULL CHECK(starRating >=1 AND starRating <= 5),
seasonStartDate date,
mealPlan varchar(17) NOT NULL CHECK(mealPlan IN('Room Only', 'Bed and Breakfast', 'Half Board', 'Full Board')),
checkInTime time NOT NULL,
pricePersonNight float(6,2) NOT NULL CHECK(pricePersonNight >=50 AND pricePersonNight <= 250),
FOREIGN KEY (resortID) REFERENCES Resort(resortID)
);

CREATE TABLE Customer (
customerNo int AUTO_INCREMENT PRIMARY KEY,
firstname varchar(20) NOT NULL,
surname varchar(20) NOT NULL,
address varchar(40) NOT NULL,
town varchar(20) NOT NULL,
postcode varchar(8) NOT NULL
);

CREATE TABLE Booking (
hotelRef varchar(4) NOT NULL,
customerNo int NOT NULL,
startDate date NOT NULL,
numberNights int NOT NULL CHECK(numberNights >=1),
numberInParty int NOT NULL CHECK(numberInParty >=1),
PRIMARY KEY (customerNo, hotelRef, startDate),
FOREIGN KEY (customerNo) REFERENCES Customer(customerNo),
FOREIGN KEY (hotelRef) REFERENCES Hotel(hotelRef)
);