DDL is a sub language of SQL which describes the syntax of the handling of schemas.

Creating Tables

Tables are created using the CREATE TABLE directive, and then specifying the name of the table as well as the schema of it's fields.

CREATE TABLE person (
	person_id SMALLINT UNSIGNED PRIMARY KEY,

	first_name VARCHAR(20) NOT NULL,
	last_name VARCHAR(20) NOT NULL
);

Create a table person with a primary key and first and last name fields.

Modifying Tables

Tables can be modified using the ALTER TABLE directive with the table name and then a set of operations:

• ADD <column_name> <datatype> - Add a new column with the name column_name and of type datatype.
• DROP <column_name> - Removes the column of column_name from the table.

ALTER TABLE person
ADD middle_name VARCHAR(20) NOT NULL;

Deleting Tables

Tables can be removed using the DROP TABLE or DROP TABLE IF EXISTS directives the IF EXISTS variant prevents an error if the table doesn't exist.

DROP TABLE IF EXISTS person;

Remove the table person if it was created already.

Constraints

Table constraints are used to specify rules for certain columns in a table they are placed after the column datatype when creating or modifying a table.

CREATE TABLE person (
	person_id SMALLINT UNSIGNED PRIMARY KEY,
	--                          ^–------––– Constraint

	first_name VARCHAR(20) NOT NULL,
	--                     ^–––––––– Constraint
	last_name VARCHAR(20) NOT NULL
	--                    ^–––––––– Constraint
);

List of Constraints

• NOT NULL - Ensures the column is not empty (null value).
• UNIQUE - All values in the column are different.
• PRIMARY KEY - Specifies that it's the unique identifier for each row in a table and is a combination of NOT NULL and UNIQUE.
• FOREIGN KEY - Prevents actions that would destroy the links between tables (must reference a primary key of another row - typically from another table)
• CHECK - Ensures the column satisfies a given boolean expression (for example CHECK (age >= 18))
• DEFAULT - Provides a default value if no value is specified.

Some constraints like PRIMARY KEY, FOREIGN KEY can be declared out of line and can accept a combination of multiple columns.

CREATE TABLE enrollment (
    student_id INT UNSIGNED NOT NULL,
    course_id  INT UNSIGNED NOT NULL,
    enrolled_on DATE NOT NULL,

    PRIMARY KEY (student_id, course_id),
    
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id)  REFERENCES course(course_id)
);

Views

Views are virtual tables created from queries on one or more base tables. A view does not store data; instead, it displays data stored in other tables dynamically. Views are useful for simplifying complex queries and restricting access to specific data.

Creating Views

Views are created using the CREATE VIEW directive, followed by the view name and the AS keyword with a SELECT query.

CREATE VIEW person_details AS
SELECT 
	person_id,
	first_name,
	last_name
FROM person
WHERE person_id > 0;

Create a view person_details that displays only active person records.

Dropping Views

Views can be removed using the DROP VIEW or DROP VIEW IF EXISTS directives. The IF EXISTS variant prevents an error if the view doesn't exist.

DROP VIEW IF EXISTS person_details;

Remove the view person_details if it exists.

Modifying Views

To change a view's definition, use the CREATE OR REPLACE VIEW directive, which will either create the view if it doesn't exist or replace its definition.

CREATE OR REPLACE VIEW person_details AS
SELECT 
	person_id,
	first_name,
	last_name,
	middle_name
FROM person
WHERE person_id > 0;

Replace the person_details view with an updated definition that includes the middle name field.