DROP TABLE and ALTER TABLE in SQL - Mini Project: Build & Apply

Managing Tables with DROP TABLE and ALTER TABLE

📖 Scenario: You are working as a database assistant for a small bookstore. The bookstore wants to keep their database tidy and up-to-date by removing old tables and modifying existing tables to add new information.

🎯 Goal: Build SQL commands to remove an unnecessary table and modify an existing table by adding a new column.

📋 What You'll Learn

Create a table named OldBooks with specific columns

Create a variable to hold the name of the table to drop

Write a SQL command to drop the OldBooks table using the variable

Write a SQL command to add a new column Publisher to the Books table

💡 Why This Matters

🌍 Real World

Database administrators often need to remove old tables and update existing tables to keep data organized and relevant.

💼 Career

Knowing how to safely drop tables and alter table structures is essential for database maintenance and evolution in many IT and data roles.

1

Create the OldBooks table

Write a SQL statement to create a table called OldBooks with columns BookID as integer primary key, Title as text, and YearPublished as integer.

SQL

-- Your code here to create the OldBooks table

Need a hint?

Use CREATE TABLE OldBooks followed by column definitions inside parentheses.

2

Set the table name to drop

Create a SQL variable called @tableToDrop and set it to the string 'OldBooks'.

SQL

CREATE TABLE OldBooks (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    YearPublished INTEGER
);
-- Your code here to declare @tableToDrop

Need a hint?

Use DECLARE @tableToDrop VARCHAR(50) = 'OldBooks'; to create the variable.

3

Drop the OldBooks table using the variable

Write a SQL statement to drop the table named in the variable @tableToDrop. Use dynamic SQL with EXEC or equivalent to drop the table.

SQL

CREATE TABLE OldBooks (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    YearPublished INTEGER
);

DECLARE @tableToDrop VARCHAR(50) = 'OldBooks';
-- Your code here to drop the table using @tableToDrop

Need a hint?

Use EXEC('DROP TABLE ' + @tableToDrop); to drop the table dynamically.

4

Add a new column Publisher to the Books table

Write a SQL statement to add a new column called Publisher of type TEXT to the existing table Books using ALTER TABLE.

SQL

CREATE TABLE OldBooks (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    YearPublished INTEGER
);

DECLARE @tableToDrop VARCHAR(50) = 'OldBooks';

EXEC('DROP TABLE ' + @tableToDrop);
-- Your code here to add Publisher column to Books table

Need a hint?

Use ALTER TABLE Books ADD Publisher TEXT; to add the new column.