DDL (CREATE, ALTER, DROP) in DBMS Theory - Mini Project: Build & Apply

DDL (CREATE, ALTER, DROP) Basics

📖 Scenario: You are managing a small library database. You need to create a table to store book information, then update the table structure, and finally remove the table when it is no longer needed.

🎯 Goal: Build SQL commands step-by-step to create a table, alter it by adding a new column, and then drop the table.

📋 What You'll Learn

Create a table named Books with columns BookID (integer, primary key) and Title (varchar(100))

Add a new column Author (varchar(100)) to the Books table

Drop the Books table

💡 Why This Matters

🌍 Real World

Database administrators and developers use DDL commands to set up and modify database structures for applications like libraries, stores, or websites.

💼 Career

Knowing how to create, alter, and drop tables is essential for roles involving database management, backend development, and data engineering.

1

Create the Books table

Write a SQL CREATE TABLE statement to create a table called Books with two columns: BookID as an integer primary key, and Title as a varchar of length 100.

DBMS Theory

-- Write the CREATE TABLE statement for Books
-- Your code here

Need a hint?

Use CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(100)); to create the table.

2

Add Author column

Write a SQL ALTER TABLE statement to add a new column called Author with type varchar(100) to the existing Books table.

DBMS Theory

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100)
);
-- Write the ALTER TABLE statement to add Author column
-- Your code here

Need a hint?

Use ALTER TABLE Books ADD Author VARCHAR(100); to add the new column.

3

Drop the Books table

Write a SQL DROP TABLE statement to remove the Books table from the database.

DBMS Theory

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100)
);
ALTER TABLE Books ADD Author VARCHAR(100);
-- Write the DROP TABLE statement for Books
-- Your code here

Need a hint?

Use DROP TABLE Books; to delete the table.

4

Review and finalize DDL commands

Ensure your SQL script includes the CREATE TABLE statement for Books, the ALTER TABLE statement adding the Author column, and the DROP TABLE statement to remove the table.

DBMS Theory

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100)
);
ALTER TABLE Books ADD Author VARCHAR(100);
DROP TABLE Books;
-- Review your full DDL script
-- Your code here

Need a hint?

Make sure all three statements are present and correctly written.