SQL DDL (Data Definition Language)

COMP207 Lectures 2021-09-28

This covers how to:

Create
Alter
Delete

databases, tables and their attributes.

Create a Database

Databases are similar to a folder and are initially empty. They can be filled with tables.

CREATE DATABASE databasename;

The commands in SQL are not case-sensitive. Conventionally, they are in upper-case.

Creating Tables

CREATE TABLE Table_name (
	column1 datatype,
	column2 datatype,
	column3 datatype
);

You can use whatever type of white-space you want to separate key-words.

The schema of this table would be:

Table_name(column1, column2, column3)

Dataypes

Datatype	Description
`INT`	Integers
`FLOAT`	Floating-point numbers
`CHAR(x)`	Fixed length string of length `x`. Spaces are required for padding.
`VARCHAR(x)`	Variable length string of max length `x`.
`DATE`	Date of format `YYY-MM-DD`.
`DATETIME`	Format of `YYY-MM-DD HH:MI:SS`.
`XML`	For XML files.
`BLOB`	For binary files.

There are also other data-types for other precisions.

Example

For the following table:

birthday	first_name	family_name
1990-11-10	Anne	Smith
2000-02-05	David	Jones

You would use the following command to create an appropriate table:

CREATE TABLE Employees (
	birthday DATE,
	first_name VARCHAR(100),
	family_name VARCHAR(100)
);

The schema of this table would be:

Employees(birthday, first_name, family_name)

Unique

You can specify that you want some attributes to be unique per entry:

CREATE TABLE Employees (
	birthday DATE,
	first_name VARCHAR(100),
	family_name VARCHAR(100)
	CONSTRAINT UC_Employees UNIQUE(birthday, first_name)
);

UC_Employess is just a reference so that we can refer to the constraint.

Primary Key

You can specify how the data should be sorted physically in storage by defining a primary key.

CREATE TABLE Employees (
	birthday DATE,
	first_name VARCHAR(100),
	family_name VARCHAR(100)
	CONSTRAINT PK_Employees PRIMARY KEY(birthday, first_name)
);

There can only be one primary key constraint per table.
Rows in a primary key must be unique.

Foreign Key

A foreign key is used to link tow tables together explicitly:

It points from some attributes in one table (the child table) to the primary key of another (the parent table).
It ensures that the values, in the attributes of the foreign key in the child table, must also be in the parent table.
Can be used to create custom data-types, by having a table with all the values you want to allow.

Create the parent table:

 CREATE TABLE Employees (
     e_id INT,
     first_name VARCHAR(100),
     family_name VARCHAR(100)
     CONSTRAINT PK_Employees PRIMARY KEY(e_id)
 );

Create a child table referencing the parent table:

 CREATE TABLE Transaction (
     emp_id INT,
     CONSTRAINT FK_Transactions FOREIGN KEY (emp_id)
         REFERENCES Employees(e_id)
 );

Deleting

Deleting is called DROP in SQL. To delete a database you would use:

DROP DATABASE CS_Store;

To delete a table:

DROP TABLE Employees;

You can also remove unique, primary key or foreign keys. You should look this up if required.

Modifying Tables

To alter a table adding an attribute you could:

ALTER TABLE Employees ADD email VARCHAR(100);

To alter and existing attribute:

ALTER TABLE Employees MODIFY email VARCHAR(200);

This allows longer emails under the email attribute.

The MODIFY keyword is variable depending on the implementation.

To remove a column you must modify the table:

ALTER TABLE Employees DROP COLUMN email;