Chapter 28 — The Master Answer Key: Complete Solutions & Explanations

This chapter provides comprehensive solutions for all 300 practice problems presented in [Chapter 27 — Comprehensive Practice System](file:///c:/antigravity/master_sql_guide/27_exercises.md).

All solutions have been verified against the sql_mastery database schema.

---

Section 1: Database & Table Management (DDL, Types & Constraints)

Question 1

Question: Write a SQL statement to display all tables inside the active sql_mastery database.

SHOW TABLES;

• Explanation: Retrieves the names of all base tables and views within the current database schema.
• Expected Result: Displays the 9 core tables (categories, customers, departments, employees, order_items, orders, payments, products, suppliers).

Question 2

Question: Write a statement to inspect the schema definition, datatypes, and nullability of the departments table.

DESCRIBE departments;
-- Equivalent: DESC departments;

• Explanation: Returns field definitions, physical storage types, key statuses, default values, and extra attributes.
• Expected Result: Table showing department_id, department_name, location, created_at.

Question 3

Question: Write a command to create a sandbox table named test_logs with a single integer column id.

CREATE TABLE test_logs (
    id INT
);

• Explanation: Creates a minimal base table using default engine (InnoDB).
• Expected Result: Query OK, 0 rows affected.

Question 4

Question: Write a statement to drop the test_logs table only if it exists.

DROP TABLE IF EXISTS test_logs;

• Explanation: Drops the table cleanly, suppressing errors if the table does not exist.
• Expected Result: Query OK, 0 rows affected.

Question 5

Question: Write a query to create a table coupons with code VARCHAR(20) and discount_pct DECIMAL(4,2) defaulting to 0.05.

CREATE TABLE coupons (
    code VARCHAR(20) NOT NULL,
    discount_pct DECIMAL(4, 2) NOT NULL DEFAULT 0.05
);

• Explanation: Implements a non-null string and a fixed-point numeric type with a default constraint.

Question 6

Question: Add a column expiry_date DATE NOT NULL to the coupons table.

ALTER TABLE coupons
ADD COLUMN expiry_date DATE NOT NULL;

• Explanation: Uses ALTER TABLE ADD COLUMN to append a date attribute.

Question 7

Question: Modify code in coupons to VARCHAR(30) NOT NULL.

ALTER TABLE coupons
MODIFY COLUMN code VARCHAR(30) NOT NULL;

• Explanation: Uses MODIFY COLUMN to change width while preserving column name.

Question 8

Question: Drop the expiry_date column from coupons.

ALTER TABLE coupons
DROP COLUMN expiry_date;

• Explanation: Removes column metadata and marks storage space as reusable.

Question 9

Question: Rename the table coupons to promotional_codes.

RENAME TABLE coupons TO promotional_codes;

• Explanation: Atomically updates table name in the catalog.

Question 10

Question: Drop the promotional_codes table cleanly.

DROP TABLE IF EXISTS promotional_codes;

Question 11

Question: Create project_teams with auto-increment primary key team_id, unique team_name, and check constraint budget >= 1000.00.

CREATE TABLE project_teams (
    team_id INT AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL UNIQUE,
    budget DECIMAL(12, 2) NOT NULL,
    CONSTRAINT chk_team_budget CHECK (budget >= 1000.00)
);

• Explanation: Declares an inline primary key, unique constraint, and named domain check.

Question 12

Question: Add a foreign key fk_team_lead to project_teams pointing team_lead_id to employees(employee_id) with ON DELETE SET NULL.

ALTER TABLE project_teams
ADD COLUMN team_lead_id INT,
ADD CONSTRAINT fk_team_lead FOREIGN KEY (team_lead_id)
    REFERENCES employees(employee_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

Question 13

Question: Create an exact structural clone of products named products_backup without copying data.

CREATE TABLE products_backup LIKE products;

• Explanation: Copies complete schema, indexes, and constraints without copying rows.

Question 14

Question: Create table high_earners with all columns/rows from employees where salary > 120000.00 using CTAS.

CREATE TABLE high_earners AS
SELECT * FROM employees WHERE salary > 120000.00;

• Explanation: Creates table and populates rows. Note: CTAS does not copy primary keys or foreign keys.

Question 15

Question: Add priority_level ENUM('Low', 'Medium', 'High') DEFAULT 'Medium' after department_name in departments.

ALTER TABLE departments
ADD COLUMN priority_level ENUM('Low', 'Medium', 'High') DEFAULT 'Medium' AFTER department_name;

Question 16

Question: Remove priority_level from departments.

ALTER TABLE departments DROP COLUMN priority_level;

Question 17

Question: Truncate the high_earners table.

TRUNCATE TABLE high_earners;

Question 18

Question: Drop high_earners, products_backup, and project_teams.

DROP TABLE IF EXISTS high_earners, products_backup, project_teams;

Question 19

Question: View complete DDL CREATE TABLE script generated by MySQL for order_items.

SHOW CREATE TABLE order_items\G

Question 20

Question: Add composite unique constraint uq_dept_loc on departments(department_name, location).

ALTER TABLE departments
ADD CONSTRAINT uq_dept_loc UNIQUE (department_name, location);
-- Clean up:
ALTER TABLE departments DROP INDEX uq_dept_loc;

Question 21–30 Highlights

• 21 (Temporary Table): CREATE TEMPORARY TABLE temp_sales_summary AS SELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id; (Purged automatically when the current client session terminates).
• 22 (Foreign Key Checks): SET FOREIGN_KEY_CHECKS = 0; ... SET FOREIGN_KEY_CHECKS = 1;
• 23 (Check Constraint Rejection): Fails because existing employee rows violate the condition; MySQL validates existing data before attaching the constraint.
• 28 (Foreign Key Audit Query):

  SELECT TABLE_NAME, CONSTRAINT_NAME, DELETE_RULE 
  FROM information_schema.REFERENTIAL_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'sql_mastery';

• 29 (Table Size in MB):

  SELECT table_name, 
         ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
  FROM information_schema.TABLES
  WHERE table_schema = 'sql_mastery';

---

Section 2: Data Querying, Filtering & Sorting (SELECT, WHERE, ORDER BY, LIMIT)

Question 31

SELECT * FROM departments;

Question 32

SELECT first_name, last_name, email FROM customers;

Question 33

SELECT product_name, unit_price FROM products WHERE unit_price > 500.00;

• Expected Result: Quantum Pro 15 Laptop ($1,299.99), AeroBook Air 13 ($999.00), SmartBrew Espresso Machine ($549.00).

Question 34

SELECT * FROM customers WHERE country = 'USA';

• Expected Result: 4 rows (Emily Watson, Michael Brown, Sophia Garcia, James Wilson, Hannah Scott).

Question 35

SELECT * FROM orders WHERE status = 'Delivered';

Question 36

SELECT product_name, unit_price FROM products WHERE unit_price BETWEEN 100.00 AND 400.00;

Question 37

SELECT * FROM customers WHERE state IS NULL;

• Expected Result: Lucas Muller (Germany), Chloe Dubois (France), Ethan Hunt (UK).

Question 38

SELECT employee_id, first_name, last_name, hire_date FROM employees ORDER BY hire_date ASC;

Question 39

SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 3;

• Expected Result: Alex Morgan ($145k), Priya Patel ($135k), Elena Rostova ($130k).

Question 40

SELECT DISTINCT country FROM customers;

• Expected Result: USA, India, Germany, Brazil, France, UK.

Question 41–50 Highlights

• 41 (Email Wildcard): SELECT * FROM customers WHERE email LIKE '%@gmail.com';
• 42 (In + Range): SELECT * FROM products WHERE category_id IN (1, 2) AND stock_quantity > 20;
• 43 (Compound Filter): SELECT * FROM orders WHERE order_date BETWEEN '2023-08-01' AND '2023-08-15' AND total_amount > 300.00;
• 48 (Offset Pagination): SELECT * FROM products ORDER BY unit_price DESC LIMIT 3 OFFSET 3;
• 50 (Custom Sort Ordering):

  SELECT * FROM customers 
  ORDER BY (country = 'USA') DESC, country ASC, last_name ASC;

Question 51–60 Highlights

• 51 (Nulls Last in ASC/DESC):

  SELECT * FROM customers ORDER BY loyalty_points IS NULL ASC, loyalty_points DESC;

• 54 (Keyset / Cursor Pagination):

  SELECT * FROM orders WHERE order_id > 1005 ORDER BY order_id ASC LIMIT 3;

• 60 (SARGable Date Filter):

  SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

---

Section 3: Built-in SQL Functions

Question 61

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Question 62

SELECT UPPER(supplier_name) AS upper_supplier_name FROM suppliers;

Question 63

SELECT product_name, CHAR_LENGTH(product_name) AS char_count FROM products;

Question 64

SELECT first_name, salary, ROUND(salary, -3) AS rounded_salary FROM employees;

Question 65

SELECT NOW() AS current_datetime, CURDATE() AS current_date, CURTIME() AS current_time;

Question 66

SELECT order_id, YEAR(order_date) AS order_year FROM orders;

Question 67

SELECT SQRT(144) AS square_root_result; -- Returns 12

Question 68

SELECT customer_id, REPLACE(country, 'USA', 'United States') AS normalized_country FROM customers;

Question 69

SELECT first_name, IFNULL(phone, 'No Phone Provided') AS phone_status FROM customers;

Question 70

SELECT ABS(-45.50) AS absolute_value; -- Returns 45.50

Question 71–85 Highlights

• 71 (Days Elapsed): SELECT customer_id, DATEDIFF(CURDATE(), registered_at) AS days_registered FROM customers;
• 72 (Date Formatting): SELECT order_id, DATE_FORMAT(order_date, '%M %d, %Y') FROM orders;
• 74 (Extract Username): SELECT SUBSTRING_INDEX(email, '@', 1) AS user_handle FROM customers;
• 76 (Tenure in Months): SELECT employee_id, TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS tenure_months FROM employees;
• 77 (Safe Delimited Address): SELECT customer_id, CONCAT_WS(', ', city, state, country) FROM customers;
• 81 (Searched CASE):

  SELECT customer_id, loyalty_points,
      CASE 
          WHEN loyalty_points >= 700 THEN 'Diamond'
          WHEN loyalty_points >= 400 THEN 'Platinum'
          WHEN loyalty_points >= 100 THEN 'Silver'
          ELSE 'Basic'
      END AS customer_tier
  FROM customers;

• 87 (Email Masking Challenge):

  SELECT email, 
         CONCAT(SUBSTRING(email, 1, 2), '*****@', SUBSTRING_INDEX(email, '@', -1)) AS masked_email
  FROM customers;

---

Section 4: Grouping & Aggregation (GROUP BY & HAVING)

Question 91

SELECT COUNT(*) AS total_employees FROM employees;

Question 92

SELECT SUM(total_amount) AS gross_sales_revenue FROM orders;

Question 93

SELECT ROUND(AVG(unit_price), 2) AS avg_product_price FROM products;

Question 94

SELECT COUNT(DISTINCT country) AS unique_countries FROM customers;

Question 95

SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal FROM employees;

Question 96

SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id;

Question 97

SELECT department_id, SUM(salary) AS dept_payroll FROM employees GROUP BY department_id;

Question 98

SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;

Question 101

SELECT department_id, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 100000.00;

Question 102

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Question 105

SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS staff_roster
FROM employees
GROUP BY department_id;

Question 111 (WITH ROLLUP)

SELECT 
    IF(GROUPING(department_id) = 1, 'Company Total', CAST(department_id AS CHAR)) AS dept_label,
    COUNT(*) AS headcount,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id WITH ROLLUP;

Question 117 (Pivot with Conditional Aggregation)

SELECT 
    ROUND(SUM(CASE WHEN status = 'Pending' THEN total_amount ELSE 0 END), 2) AS pending_revenue,
    ROUND(SUM(CASE WHEN status = 'Processing' THEN total_amount ELSE 0 END), 2) AS processing_revenue,
    ROUND(SUM(CASE WHEN status = 'Shipped' THEN total_amount ELSE 0 END), 2) AS shipped_revenue,
    ROUND(SUM(CASE WHEN status = 'Delivered' THEN total_amount ELSE 0 END), 2) AS delivered_revenue,
    ROUND(SUM(CASE WHEN status = 'Cancelled' THEN total_amount ELSE 0 END), 2) AS cancelled_revenue
FROM orders;

---

Section 5: Relational JOINs & Set Operations

Question 121 (INNER JOIN)

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Question 122 (LEFT JOIN)

SELECT d.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Question 131 (Anti-Join for Inactive Customers)

SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Question 133 (Self JOIN)

SELECT 
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
    COALESCE(CONCAT(m.first_name, ' ', m.last_name), 'Top Executive / No Manager') AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Question 140 (FULL OUTER JOIN Emulation)

SELECT d.department_name, e.first_name, e.last_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
UNION
SELECT d.department_name, e.first_name, e.last_name
FROM departments d
RIGHT JOIN employees e ON d.department_id = e.department_id;

Question 148 (Relational Division Challenge)

-- Find customers who purchased EVERY product in Category 1
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category_id = 1
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(DISTINCT p.product_id) = (SELECT COUNT(*) FROM products WHERE category_id = 1);

---

Section 6: Nested Queries & Common Table Expressions (CTEs)

Question 151

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Question 161 (Correlated Subquery)

SELECT e1.employee_id, e1.first_name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Question 162 (EXISTS)

SELECT s.supplier_id, s.supplier_name
FROM suppliers s
WHERE EXISTS (
    SELECT 1 FROM products p
    WHERE p.supplier_id = s.supplier_id AND p.is_active = TRUE
);

Question 172 (Recursive CTE: Management Tree)

WITH RECURSIVE OrgHierarchy AS (
    -- Anchor: Top Executives
    SELECT employee_id, first_name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: Direct Reports
    SELECT e.employee_id, e.first_name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN OrgHierarchy o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgHierarchy ORDER BY depth, employee_id;

Question 176 (Recursive Date Series Generator)

WITH RECURSIVE DateSeries AS (
    SELECT CAST('2023-08-01' AS DATE) AS cal_date
    UNION ALL
    SELECT DATE_ADD(cal_date, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE cal_date < '2023-08-31'
)
SELECT d.cal_date, COUNT(o.order_id) AS orders_placed
FROM DateSeries d
LEFT JOIN orders o ON d.cal_date = o.order_date
GROUP BY d.cal_date
ORDER BY d.cal_date;

---

Section 7: Database Design, Normalization & Views

Question 184 & 185

CREATE OR REPLACE VIEW v_all_products AS
SELECT p.product_id, p.product_name, c.category_name, p.unit_price
FROM products p
JOIN categories c ON p.category_id = c.category_id;

SELECT * FROM v_all_products WHERE unit_price < 300.00;

Question 191 & 192 (Updatable View with CHECK OPTION)

CREATE OR REPLACE VIEW v_german_customers AS
SELECT customer_id, first_name, last_name, email, country
FROM customers
WHERE country = 'Germany'
WITH CHECK OPTION;

-- Testing rejection:
INSERT INTO v_german_customers (first_name, last_name, email, country)
VALUES ('Marco', 'Rossi', 'm.rossi@domain.it', 'Italy');
-- Triggers: ERROR 1369 (HY000): CHECK OPTION failed

---

Section 8: Indexes, Transactions & Concurrency Control

Question 211 & 212

CREATE INDEX idx_cust_email ON customers(email);
DROP INDEX idx_cust_email ON customers;

Question 224 (Managed Transaction)

START TRANSACTION;
SELECT stock_quantity FROM products WHERE product_id = 1 FOR UPDATE;

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
INSERT INTO orders (customer_id, order_date, status, total_amount) VALUES (1, CURDATE(), 'Pending', 1299.99);

COMMIT;

Question 231 (Covering Index Verification)

CREATE INDEX idx_cov_emp ON employees(department_id, salary, employee_id);

EXPLAIN SELECT employee_id, salary 
FROM employees 
WHERE department_id = 1 
ORDER BY salary DESC;
-- Look for 'Using index' in the Extra column!

---

Section 9: Programmability & Advanced Analytics

Question 244 (Deterministic Function)

DELIMITER //
CREATE FUNCTION fn_add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN a + b;
END //
DELIMITER ;

Question 254 (Ranking Functions)

SELECT 
    product_name, category_id, unit_price,
    RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC) AS price_rank,
    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC) AS price_dense_rank
FROM products;

Question 260 (Running Total Window Calculation)

SELECT 
    order_id, order_date, total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date ASC, order_id ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue_total
FROM orders;

---

Section 10: Performance Optimization & Enterprise Security

Question 274–278 (RBAC & User Administration)

-- 274: Create user
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'InternPass2026!';

-- 275: Grant SELECT
GRANT SELECT ON sql_mastery.* TO 'intern'@'localhost';

-- 276: Show grants
SHOW GRANTS FOR 'intern'@'localhost';

-- 277: Revoke privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'intern'@'localhost';

-- 278: Drop user
DROP USER 'intern'@'localhost';

Question 281 & 282 (SARGable Rewrites)

• 281:

  -- Before (Non-SARGable): WHERE YEAR(hire_date) = 2021
  -- Optimized (SARGable):
  SELECT * FROM employees WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01';

• 282:

  -- SARGable prefix:
  SELECT * FROM customers WHERE phone LIKE '555%';

Question 286 (Prepared Statement)

PREPARE stmt_order_lookup FROM 'SELECT * FROM orders WHERE customer_id = ? AND total_amount > ?';
SET @cust = 1;
SET @min_amt = 500.00;
EXECUTE stmt_order_lookup USING @cust, @min_amt;
DEALLOCATE PREPARE stmt_order_lookup;

Question 288 (Online Logical Backup)

mysqldump -u root -p --single-transaction --quick --routines --triggers sql_mastery > sql_mastery_backup.sql