By the end of this lesson, you’ll be able to:

  • Understand what Data Definition Language (DDL) is.

  • Create new tables with the CREATE TABLE statement.

  • Modify existing tables safely using ALTER TABLE.

  • Delete tables properly with DROP TABLE.

  • Apply data types, constraints, and defaults effectively.

CREATE TABLE — Defining Your Schema

CREATE TABLE table_name (
    column_name data_type [constraint],
    column_name data_type [constraint],
    ...
);

Every table has:

  • A name

  • One or more columns

  • Each column has a data type

  • Optionally: constraints, defaults, or keys

Example: Creating a Simple Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50),
    email       VARCHAR(100) UNIQUE,
    country     VARCHAR(30) DEFAULT 'Unknown',
    signup_date DATE DEFAULT CURRENT_DATE
);

Explanation

ElementPurpose
INTStores integers
VARCHAR(50)Variable-length string (up to 50 chars)
NOT NULLPrevents missing values
UNIQUEEnsures no duplicates
DEFAULTSets a fallback value if none is provided
PRIMARY KEYUniquely identifies each record
CURRENT_DATEAuto-fills with today’s date

Example: Creating a Table With a Foreign Key

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    amount       DECIMAL(10,2),
    status       VARCHAR(20) DEFAULT 'pending',
    order_date   DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Purpose:
Ensures that each customer_id in orders exists in customers.

Best Practices for Creating Tables

  • ✅ Use consistent naming (snake_case: order_date, not OrderDate)

  • ✅ Define constraints early to enforce data quality.

  • ✅ Always include a primary key.

  • ✅ Avoid hardcoding types — choose appropriate ones for expected data volume.

  • ⚠️ Never create tables without a plan for relationships.

ALTER TABLE — Modifying an Existing Table

You can add, rename, or delete columns, and even add constraints.

🔹 Add a New Column

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(20);

🔹 Modify a Column’s Type or Default

ALTER TABLE customers
ALTER COLUMN phone_number TYPE TEXT;

ALTER TABLE customers
ALTER COLUMN country SET DEFAULT 'France';

🔹 Rename a Column or Table

ALTER TABLE customers
RENAME COLUMN phone_number TO contact_number;

ALTER TABLE customers
RENAME TO clients;

🔹 Drop (Remove) a Column

ALTER TABLE customers
DROP COLUMN contact_number;

🔹 Add or Drop Constraints

ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);


ALTER TABLE customers
DROP CONSTRAINT unique_email;

Best Practices for Creating Tables

    • 🧩 Changes lock the table temporarily — avoid doing it on high-traffic systems.

    • 🧩 Always back up your data before altering structures.

    • 🧩 You can combine several ALTER statements in one migration script.

DROP TABLE — Removing a Table

Basic syntax

DROP TABLE table_name;

    • This permanently deletes:

      • The table definition

      • All its data

      • All relationships (foreign keys)

Example

DROP TABLE orders;

Safe Drop (Optional)

DROP TABLE IF EXISTS orders;

WARNING

DROP TABLE is irreversible — once executed, the data and schema are gone. In production, prefer soft deletes (marking rows as inactive) or archiving before dropping.

Putting It All Together

-- 1. Create table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30),
    hire_date DATE DEFAULT CURRENT_DATE
);

-- 2. Add new column
ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2);

-- 3. Change default value
ALTER TABLE employees
ALTER COLUMN department SET DEFAULT 'Engineering';

-- 4. Drop a column
ALTER TABLE employees
DROP COLUMN hire_date;

-- 5. Remove the table completely
DROP TABLE employees;

Quick Reference

ActionSQL CommandExample
Create tableCREATE TABLECREATE TABLE users (...);
Add columnALTER TABLE ... ADD COLUMNALTER TABLE users ADD COLUMN email TEXT;
Rename columnALTER TABLE ... RENAME COLUMNALTER TABLE users RENAME COLUMN email TO user_email;
Drop columnALTER TABLE ... DROP COLUMNALTER TABLE users DROP COLUMN email;
Drop tableDROP TABLEDROP TABLE users;

Summary

  • CREATE TABLE defines the structure of your data.

  • ALTER TABLE lets you adjust that structure safely over time.

  • DROP TABLE permanently removes it — use with caution.

  • Proper data types and constraints ensure performance and integrity.

  • Always test structural changes before running in production.

PreviousNext