Mini Challenge 2 — ALTER TABLE, DROP TABLE

---

title: “Mini Challenge 2 — Topics 5-6” part: 1 topic_number: 0 slug: “mini-challenge-02” difficulty: “Beginner” prerequisites: “alter-table, drop-table” —

Mini Challenge 2 — ALTER TABLE, DROP TABLE

Overview

You’ve learned how to modify existing tables (ALTER TABLE) and remove tables (DROP TABLE). Now let’s practice those skills in realistic scenarios.

Prerequisite: Make sure you’ve completed Mini Challenge 1 and created the customers table. If you dropped it, recreate it first:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(15),
    signup_date DATE DEFAULT (CURRENT_DATE),
    is_active BOOLEAN DEFAULT TRUE
);

---

Challenge 1: Modify the Employees Table

The company wants to track more employee information:

1. Add a column called email (VARCHAR(100))
2. Add a column called date_of_birth (DATE)
3. Add a column called is_active (BOOLEAN, default TRUE)

Do all three in separate ALTER TABLE statements.

---

Challenge 2: Fix a Design Mistake

You created the customers table but forgot to make email NOT NULL. Fix it by:

1. Altering the email column to add the NOT NULL constraint
2. Verify the change with DESCRIBE customers

Hint

Use ALTER TABLE ... MODIFY COLUMN to redefine the column with NOT NULL.

---

Challenge 3: Table Cleanup

Drop the following tables if they exist (safely):

• office_supplies
• meeting_rooms
• customers
• Any test tables you created

Use IF EXISTS to avoid errors.

---

Challenge 4: The Complete Workflow

Create a table called orders:

• order_id (primary key, auto-increment)
• customer_id (integer, foreign key to customers table… but wait, we dropped customers!)
• order_date (datetime, defaults to current timestamp)
• total_amount (decimal 10,2, required)
• status (VARCHAR(20), defaults to ‘pending’)

Problem: You can’t create this because customers table doesn’t exist anymore!

Your task:

1. Recreate the customers table from Challenge 1
2. Create the orders table with the foreign key
3. Insert a test customer
4. Insert a test order for that customer
5. Verify both tables with SELECT

---

Answer Key

Challenge 1 Answer

```sql ALTER TABLE employees ADD COLUMN email VARCHAR(100); ALTER TABLE employees ADD COLUMN date_of_birth DATE; ALTER TABLE employees ADD COLUMN is_active BOOLEAN DEFAULT TRUE; ``` Verify: ```sql DESCRIBE employees; SELECT first_name, email, date_of_birth, is_active FROM employees LIMIT 5; ``` All new columns will have NULL values (except is_active which defaults to TRUE/1).

Challenge 2 Answer

```sql -- First, check if any emails are NULL SELECT * FROM customers WHERE email IS NULL; -- If there are NULLs, either delete those rows or set a default value -- UPDATE customers SET email = '[email protected]' WHERE email IS NULL; -- Now modify the column ALTER TABLE customers MODIFY COLUMN email VARCHAR(100) NOT NULL UNIQUE; ``` Verify: ```sql DESCRIBE customers; ``` The email column should now show "NO" in the Null column.

Challenge 3 Answer

```sql DROP TABLE IF EXISTS office_supplies; DROP TABLE IF EXISTS meeting_rooms; DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS temp_test; DROP TABLE IF EXISTS tasks; ``` These statements won't error even if the tables don't exist. Verify: ```sql SHOW TABLES; ``` You should only see the original company_db tables: departments, employees, employee_projects, projects.

Challenge 4 Answer

```sql -- Step 1: Recreate customers table CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, full_name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, phone VARCHAR(15), signup_date DATE DEFAULT (CURRENT_DATE), is_active BOOLEAN DEFAULT TRUE ); -- Step 2: Create orders table CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) DEFAULT 'pending', FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Step 3: Insert test customer INSERT INTO customers (full_name, email, phone) VALUES ('John Doe', '[email protected]', '555-1234'); -- Step 4: Insert test order (customer_id will be 1) INSERT INTO orders (customer_id, total_amount) VALUES (1, 299.99); -- Step 5: Verify SELECT * FROM customers; SELECT * FROM orders; -- Better: Join them SELECT c.full_name, o.order_id, o.order_date, o.total_amount, o.status FROM customers c JOIN orders o ON c.customer_id = o.customer_id; ``` **Expected output:** One customer and one order, properly linked by foreign key.

---

Key Takeaways

• ALTER TABLE modifies existing table structure without losing data • Use appropriate constraints (NOT NULL, UNIQUE, DEFAULT) • Create parent tables before child tables (foreign key dependencies) • Use IF EXISTS when dropping tables to avoid errors • Check for existing data before adding NOT NULL constraints • DESCRIBE shows table structure — use it to verify changes

---

Up Next

Next topic: SELECT with FROM and WHERE → part1_07_select_from_where.md

Now the real fun begins — querying data!