Crafting SQL Databases(DDL)

Blueprinting Database Structures

Learning Outcome

5

Write basic SQL DDL statements confidently

4

Relate DDL commands to real-life Instagram examples

3

Use DDL commands to create, modify, and delete database structures

2

Identify different DDL commands and their purpose

1

Understand what DDL commands are in SQL

Before learning DDL, let’s recall:

Tables contain rows and columns

We use SQL to define, manipulate, and control data

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is Data?

Raw facts (likes, comments, usernames)

What is a Database?

Organized storage of data

What is DBMS/RDBMS?

Software to manage databases

What is SQL?

Language to interact with database

Instagram engineers must first:

  • Design User table
  • Design Post table
  • Decide columns like username, email, post_id, caption

This design and structure creation is done using DDL commands.

 In SQL, the commands used to define and manage structure are called DDL commands.

DDL (Data Definition Language)

DDL commands are used to:

  • Create database objects

  • Modify table structure

  • Delete database objects

DDL works on structure, not on actual data.

Examples:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

CREATE Command

Used to create database or table

Instagram Example

Creating a table to store Instagram users.

CREATE TABLE users (
  user_id INT,
  username VARCHAR(50),
  email VARCHAR(100)
);

Analogy
Creating a new profile form layout on Instagram.

ALTER Command

Used to modify existing table structure

Instagram Example

Instagram adds a new feature: phone number

ALTER TABLE users
ADD phone VARCHAR(15);

Analogy

Adding a new field in Instagram profile settings.

DROP Command

Used to delete a table permanently

Instagram Example

Instagram removes an unused table.

DROP TABLE users;

Important

  • Structure + data both are deleted
  • Cannot be recovered easily

Analogy

 Deleting the entire Instagram feature forever.

TRUNCATE Command

Used to remove all records, structure remains

Instagram Example

Clearing all user data for testing.

TRUNCATE TABLE users;

Analogy

Deleting all Instagram accounts but keeping the signup form ready.

RENAME Command

Used to rename table

Instagram Example

Renaming users table to insta_users.

RENAME TABLE users 
TO insta_users;

Analogy

Changing feature name from Users to Instagram Users.​

Summary

3

Instagram uses DDL before storing any real data

2

Used to create and manage tables and schema

1

DDL defines how data is stored

Quiz

What does DDL stand for in SQL?

A. Data Delete Language

B. Data Definition Language

C. Data Description Language

D. Data Display Language

 

Quiz-Answer

What does DDL stand for in SQL?

A. Data Delete Language

B. Data Definition Language

C. Data Description Language

D. Data Display Language