Mini Challenge 1 — CREATE TABLE, INSERT, UPDATE, DELETE

---

title: “Mini Challenge 1 — Topics 1-4” part: 1 topic_number: 0 slug: “mini-challenge-01” difficulty: “Beginner” prerequisites: “create-table, insert, update, delete” —

Mini Challenge 1 — CREATE TABLE, INSERT, UPDATE, DELETE

Overview

You’ve learned the fundamental database operations: creating tables (CREATE TABLE), adding data (INSERT), modifying data (UPDATE), and removing data (DELETE). Now let’s combine them in realistic scenarios that test your understanding.

Rules:

• Work in the company_db database
• Test each query and verify your results
• If you make a mistake, you can always re-run the setup script from 00_setup_company_db.md

---

Challenge 1: Build a Customer Table

Create a table called customers with:

• customer_id (primary key, auto-increment)
• full_name (required, up to 100 characters)
• email (required, unique, up to 100 characters)
• phone (optional, up to 15 characters)
• signup_date (date, defaults to current date)
• is_active (boolean, defaults to TRUE)

Hint

Use appropriate data types: INT for ID, VARCHAR for text, DATE for dates, BOOLEAN for true/false. Remember NOT NULL, UNIQUE, DEFAULT, and AUTO_INCREMENT.

---

Challenge 2: New Hire Complete

Scenario: A new employee named Maya Patel just joined as a Data Scientist (department 10), starting today, with a salary of $96,000. Her manager is Olivia Martin (employee_id = 15). She’s been assigned to the “AI Chatbot” project (project_id = 6) as a “Machine Learning Engineer”.

Your Tasks:

1. Add Maya to the employees table
2. Add her project assignment to employee_projects
3. Verify she appears in both tables with the correct data

Hint 1

This requires two INSERT statements — one for employees, one for employee_projects. Remember: employee_id is AUTO_INCREMENT, so don't specify it. But you'll need to know what her new employee_id is for the second INSERT. Run a SELECT to find it after the first INSERT.

Hint 2

```sql -- After inserting Maya, find her employee_id: SELECT employee_id FROM employees WHERE first_name = 'Maya' AND last_name = 'Patel'; ```

---

Challenge 3: Department Reorganization

Scenario: The company is restructuring. All employees in Customer Support (department_id = 6) are being moved to Operations (department_id = 9). Additionally, everyone in Operations (including the transferred employees) gets a 3% raise.

Your Tasks:

1. Move all Customer Support employees to Operations
2. Give all Operations employees a 3% raise
3. Verify the changes with SELECT statements

Hint

Two UPDATE statements. First, change department_id for dept 6 employees. Second, increase salary by multiplying by 1.03 for dept 9 employees (which now includes the transferred folks).

---

Challenge 4: Project Cleanup

Scenario: All projects that ended before January 1, 2022 are being archived. This means:

1. Remove all employee assignments from those projects
2. Delete the projects themselves from the projects table

Your Tasks:

1. Identify which projects ended before 2022-01-01
2. Delete employee assignments for those projects
3. Delete the projects themselves
4. Verify the deletions

Hint 1

First, find the project_ids: ```sql SELECT project_id, project_name, end_date FROM projects WHERE end_date < '2022-01-01'; ```

Hint 2

You must delete from employee_projects FIRST (child table), then from projects (parent table). Why? Foreign key constraints.

---

Challenge 5: Correction Required

Scenario: You discover a data entry error. Employee “Sam Clark” (employee_id = 19) was entered with the wrong last name. His actual last name is “Clarke” (with an “e”). Also, his hire date was entered incorrectly — he was actually hired on September 26, 2022 (not the 25th).

Your Task:

1. Correct both errors with a single UPDATE statement
2. Verify the correction

---

Challenge 6: Volunteer Cleanup

Scenario: Employee Paul Garcia (employee_id = 16) was actually a volunteer, not a permanent employee. He’s no longer with the company. Remove all traces of him from the database (both his employee record and any project assignments).

Your Tasks:

1. Remove his project assignments first
2. Then remove his employee record
3. Verify he’s completely gone

Hint

Order matters! Delete from employee_projects first (child), then from employees (parent). If you try to delete from employees first, MySQL will block you due to foreign key constraints in employee_projects.

---

Answer Key

Challenge 1 Answer

```sql 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 ); ``` Verify: ```sql DESCRIBE customers; ```

Challenge 2 Answer

```sql -- Step 1: Add Maya to employees INSERT INTO employees (first_name, last_name, department_id, salary, hire_date, manager_id) VALUES ('Maya', 'Patel', 10, 96000.00, '2026-04-29', 15); -- Step 2: Find her employee_id (it will be 21 or the next available) SELECT employee_id, first_name, last_name FROM employees WHERE first_name = 'Maya' AND last_name = 'Patel'; -- Step 3: Add her project assignment (assuming her employee_id is 21) INSERT INTO employee_projects (employee_id, project_id, role) VALUES (21, 6, 'Machine Learning Engineer'); -- Step 4: Verify SELECT e.first_name, e.last_name, e.salary, e.department_id, p.project_name, ep.role FROM employees e JOIN employee_projects ep ON e.employee_id = ep.employee_id JOIN projects p ON ep.project_id = p.project_id WHERE e.first_name = 'Maya' AND e.last_name = 'Patel'; ```

Challenge 3 Answer

```sql -- Step 1: Move Customer Support employees to Operations UPDATE employees SET department_id = 9 WHERE department_id = 6; -- Step 2: Give all Operations employees a 3% raise UPDATE employees SET salary = salary * 1.03 WHERE department_id = 9; -- Step 3: Verify SELECT employee_id, first_name, last_name, department_id, salary FROM employees WHERE department_id = 9 ORDER BY salary DESC; ``` **Expected:** Ivy, Tina, and any others who were in dept 6 now show dept 9, and all have increased salaries.

Challenge 4 Answer

```sql -- Step 1: Identify old projects SELECT project_id, project_name, end_date FROM projects WHERE end_date < '2022-01-01'; -- Projects 10 (Cloud Infrastructure, ended 2021-06-01) matches -- Step 2: Delete employee assignments for those projects DELETE FROM employee_projects WHERE project_id IN ( SELECT project_id FROM projects WHERE end_date < '2022-01-01' ); -- Step 3: Delete the projects themselves DELETE FROM projects WHERE end_date < '2022-01-01'; -- Step 4: Verify SELECT * FROM projects WHERE project_id = 10; -- Should be empty ```

Challenge 5 Answer

```sql -- Correct both errors in one UPDATE UPDATE employees SET last_name = 'Clarke', hire_date = '2022-09-26' WHERE employee_id = 19; -- Verify SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE employee_id = 19; ``` **Expected:** Sam Clarke (with an "e") and hire_date of 2022-09-26.

Challenge 6 Answer

```sql -- Step 1: Delete project assignments first (child table) DELETE FROM employee_projects WHERE employee_id = 16; -- Step 2: Delete employee record (parent table) DELETE FROM employees WHERE employee_id = 16; -- Step 3: Verify he's gone SELECT * FROM employees WHERE employee_id = 16; -- Empty set SELECT * FROM employee_projects WHERE employee_id = 16; -- Empty set ```

---

How Did You Do?

• 6/6 correct: You’re crushing the basics!
• 4-5 correct: • Solid understanding, review the tricky parts
• 2-3 correct: Review CREATE TABLE, INSERT, UPDATE, DELETE
• 0-1 correct: Re-run the setup script and practice each topic again

---

Key Takeaways

• CREATE TABLE defines structure before adding data • Always consider foreign key relationships — delete children before parents
• UPDATE and DELETE without WHERE affect ALL rows — be careful!
• Use SELECT to verify your changes after every modification
• Multiple operations often need specific order (like deleting Paul’s assignments before deleting Paul)

---

Up Next

Continue to: ALTER TABLE → part1_05_alter_table.md

Ready to learn how to modify existing tables? Type ‘next’ to continue!