Mastering SQL: The Ultimate Guide to Querying and Managing Relational Databases

📘 Chapter 2: Data Definition Language (DDL)

🧠 What is DDL?

Data Definition Language (DDL) is a subset of SQL used to define and manage the structure of database objects, such as:

• Databases
• Tables
• Columns
• Indexes
• Constraints

Unlike DML (Data Manipulation Language), which deals with data, DDL deals with the structure of the database itself.

🔧 Key DDL Commands

📦 1. CREATE – Creating Database Objects

✅ Creating a Database

sql

CREATE DATABASE SchoolDB;

✅ Creating a Table

sql

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(100),

    Age INT,

    EnrollmentDate DATE

);

🔹 Data Types

✅ Table with Constraints

sql

CREATE TABLE Courses (

    CourseID INT PRIMARY KEY,

    Title VARCHAR(100) NOT NULL,

    CreditHours INT CHECK (CreditHours > 0),

    DepartmentID INT,

    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);

🧱 2. ALTER – Modifying Table Structure

✅ Add a Column

sql

ALTER TABLE Students

ADD Email VARCHAR(150);

✅ Modify a Column

sql

ALTER TABLE Students

MODIFY Age TINYINT;

Note: Syntax may vary (e.g., MODIFY for MySQL, ALTER COLUMN in SQL Server/PostgreSQL).

✅ Drop a Column

sql

ALTER TABLE Students

DROP COLUMN Email;

✅ Rename a Table

sql

ALTER TABLE Students

RENAME TO StudentInfo;

🗑️ 3. DROP – Removing Objects

✅ Drop a Table

sql

DROP TABLE Students;

• Completely deletes structure and all data.
• Cannot be rolled back in most RDBMS.

✅ Drop a Database

sql

DROP DATABASE SchoolDB;

⚠️ Use caution—this removes all tables and data in the database.

🔄 4. TRUNCATE – Clearing Table Data

✅ Clear All Rows (Preserve Structure)

sql

TRUNCATE TABLE Students;

🧩 5. Constraints in DDL

Constraints enforce rules at the table level.

🔹 Types of Constraints

✅ Example with All Constraints

sql

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    Salary DECIMAL(10, 2) CHECK (Salary > 0),

    JoinDate DATE DEFAULT CURRENT_DATE,

    DepartmentID INT,

    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);

🔍 6. Viewing Table Schema

✅ Describe Table (MySQL / Oracle)

sql

DESCRIBE Students;

✅ Using INFORMATION_SCHEMA (Standard SQL)

sql

SELECT column_name, data_type, is_nullable

FROM information_schema.columns

WHERE table_name = 'Students';

🧪 Practice Exercise

sql

-- Create a department table

CREATE TABLE Departments (

    DepartmentID INT PRIMARY KEY,

    DepartmentName VARCHAR(50) UNIQUE NOT NULL

);

-- Alter table: Add phone number

ALTER TABLE Departments

ADD Phone VARCHAR(20);

-- Create table referencing foreign key

CREATE TABLE Professors (

    ProfessorID INT PRIMARY KEY,

    Name VARCHAR(100),

    DepartmentID INT,

    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);

-- Truncate data from table

TRUNCATE TABLE Professors;

-- Drop a table

DROP TABLE Professors;

📚 Real-World Scenarios for DDL

✅ Summary Table