Play Store Application link – SQL in 18 steps – App on Google Play

Introduction to DDL

DDL (Data Definition Language) is used to define and manage database structures. Think of it as designing a house before moving in—you decide on the number of rooms (tables), their size (columns), and the rules (constraints) they must follow.

Database Operations

1. CREATE DATABASE – Making a New Home for Data

To create a new database, use:

CREATE DATABASE SchoolDB;

• MySQL and PostgreSQL support this syntax.
• In Oracle, you need additional setup using CREATE DATABASE with administrative privileges.

2. ALTER DATABASE – Renovating the Database

To modify database settings (e.g., changing character set):

ALTER DATABASE SchoolDB CHARACTER SET utf8mb4;

3. DROP DATABASE – Demolishing a Database

To delete an entire database (Caution: This removes all tables and data!):

DROP DATABASE SchoolDB;

Table Operations

1. CREATE TABLE – Constructing a Table

Example: A Students table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 5),
    email VARCHAR(100) UNIQUE,
    admission_date DATE DEFAULT CURRENT_DATE
);

MySQL vs Oracle Differences:

2. ALTER TABLE – Remodeling a Table

• Adding a new column:

ALTER TABLE Students ADD COLUMN phone VARCHAR(15);

• Modifying a column type:

ALTER TABLE Students MODIFY COLUMN name VARCHAR(150);

• Removing a column:

ALTER TABLE Students DROP COLUMN phone;

3. DROP TABLE – Removing a Table

DROP TABLE Students;

SQL Constraints: Setting Rules for Data Integrity

Constraints prevent invalid data from entering the database.

1. PRIMARY KEY – Ensures uniqueness of each row

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

2. FOREIGN KEY – Links tables together

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

3. UNIQUE – Prevents duplicate values in a column

ALTER TABLE Students ADD CONSTRAINT unique_email UNIQUE (email);

4. CHECK – Ensures values meet a condition

ALTER TABLE Students ADD CONSTRAINT check_age CHECK (age >= 5);

5. DEFAULT – Sets a default value

ALTER TABLE Students ALTER COLUMN admission_date SET DEFAULT CURRENT_DATE;

TRUNCATE TABLE – Clearing All Data Without Removing Structure

Unlike DROP TABLE, TRUNCATE TABLE keeps the table but deletes all rows efficiently.

TRUNCATE TABLE Students;

Conclusion

DDL commands define the structure of a database. Understanding them helps in building solid, well-structured databases.