Data Definition Language (DDL)

Data Definition Language (DDL) statements are used to define and manage the structure of database objects. These statements include commands for creating, altering, and dropping database schema elements.

Tables

Tables are the fundamental building blocks of relational databases, storing data in rows and columns. The CREATE TABLE statement defines the structure of a new table.

Creating a Table

The basic syntax for creating a table is:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ...
);

Example: Creating a 'Customers' Table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE
);

Altering a Table

The ALTER TABLE statement allows you to modify the structure of an existing table, such as adding, dropping, or modifying columns, or adding/dropping constraints.

Add Column: ALTER TABLE table_name ADD column_name datatype;
Drop Column: ALTER TABLE table_name DROP COLUMN column_name;
Modify Column: ALTER TABLE table_name ALTER COLUMN column_name datatype; (Syntax may vary by SQL dialect)

Example: Adding a 'Phone' Column to 'Customers'

ALTER TABLE Customers
ADD Phone VARCHAR(20);

Dropping a Table

The DROP TABLE statement removes a table and all its data from the database.

DROP TABLE table_name;

Caution: Dropping a table is a permanent operation and cannot be undone. All data within the table will be lost.

Columns

Columns represent attributes of the data stored in a table. Each column has a name and a data type. Constraints can be applied to columns to enforce data integrity.

Column Constraints

PRIMARY KEY: Uniquely identifies each record in a table.
NOT NULL: Ensures that a column cannot have a NULL value.
UNIQUE: Ensures that all values in a column are different.
FOREIGN KEY: Links data in one table to data in another table.
DEFAULT: Sets a default value for a column when no value is specified.
CHECK: Enforces a condition for the values in a column.

Constraints

Constraints are rules enforced on data columns to ensure the accuracy and reliability of the data in a database.

Types of Constraints

Table Constraints: Defined separately from column definitions, often for multi-column keys or more complex rules.
Column Constraints: Defined directly within the column definition.

Example: Adding a UNIQUE constraint to 'Email' and a CHECK constraint

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2) CHECK (Salary > 0),
    DepartmentID INT,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
    CONSTRAINT UQ_Email UNIQUE (Email)
);

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. They work like an index in a book, allowing the database to find rows more quickly without scanning the entire table.

Creating an Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example: Creating an index on 'LastName' for faster searching

CREATE INDEX idx_lastname
ON Customers (LastName);

Views

A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. Views are often used to simplify complex queries or to restrict access to certain data.

Creating a View

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Creating a view of customer contact information

CREATE VIEW CustomerContactInfo AS
SELECT CustomerID, FirstName, LastName, Email, Phone
FROM Customers
WHERE RegistrationDate > '2023-01-01';

You can query a view like a regular table:

SELECT * FROM CustomerContactInfo WHERE LastName = 'Smith';

MSDN Documentation