Tags:conceptdatabaseSQLDDLdatatypes Status:🟩

---

SQL DDL

Summary

SQL Data Definition Language (DDL) is used to define and manage the structure of the database schema. It deals with the creation, modification, and deletion of database objects (not any data / instance related).

Details

DLL is primarily used by the database administrator (DBA) to define the database’s data model. There are four common commands: CREATE TABLE, ALTER TABLE, DROP TABLE & TRUNCATE.

Data types

VARCHAR is the go-to data type for strings.

Create command

This creates a new table with specified columns.

CREATE TABLE table_name ( 
	att_name1 DATATYPE PRIMARY KEY, /* PRIMARY KEY is optional */
	att_name2 DATATYPE,
	att_name3 DATATYPE
);

Alter command

This creates a new column to the table. DEFAULT and the value afterwards is the default value.

ALTER TABLE table_name ADD column
MYDATATYPE DEFAULT value;

Drop command

This remove database objects. Remember that order matters when dropping tables. Always first drop foreign tables before key tables.

DROP TABLE table_name;
DROP VIEW view_name;
DROP INDEX index_name;
DROP SCHEMA schema_name CASCADE; /* CASCADE is used to delete all its objects */

Truncate command

This removes all records in a table. This cannot be combined be with the delete command.

TRUNCATE TABLE table_name;

Null

By default, attributes can be NULL, which means no value. Except: PRIMARY KEY attributes Except: NOT NULL attributes Allowing NULL values is a design decision!

Examples

Creating tables with Primary Keys and Foreign keys

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);
 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Multiple-Attribute Primary Keys

CREATE TABLE CourseEnrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

Not Null Constraints

CREATE TABLE Transcripts (
    TranscriptID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    Grade CHAR(1) NOT NULL
);