Practice Questions — CREATE, ALTER, DROP Tables

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0)
);

INSERT INTO products (name, price) VALUES
    ('Wireless Mouse',    799.00),
    ('Mechanical Keyboard', 4999.00),
    ('USB-C Charger',      1299.50);

SELECT COUNT(*) AS total FROM products;
-- Output:
-- +-------+
-- | total |
-- +-------+
-- |     3 |
-- +-------+

ALTER TABLE employees
    ADD COLUMN bonus DECIMAL(8,2) DEFAULT 0,
    RENAME COLUMN department TO dept,
    MODIFY COLUMN name VARCHAR(80) NOT NULL;

DESCRIBE employees;
-- Expected structure:
-- emp_id (PK), name VARCHAR(80) NOT NULL, dept VARCHAR(30),
-- salary DECIMAL(10,2), bonus DECIMAL(8,2) DEFAULT 0.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2)
);

INSERT INTO products VALUES
    (1, 'Pen',    10.00),
    (2, 'Notebook', 55.00),
    (3, 'Bag',    499.00),
    (4, 'Bottle', 199.00),
    (5, 'Mug',     149.00);

SELECT COUNT(*) AS step1 FROM products;   -- 5

DELETE FROM products WHERE price < 100;    -- removes Pen and Notebook
SELECT COUNT(*) AS step2 FROM products;   -- 3

TRUNCATE TABLE products;
SELECT COUNT(*) AS step3 FROM products;   -- 0

DROP TABLE products;
-- SELECT COUNT(*) FROM products;  -- would ERROR: Table doesn't exist

-- 1) Full backup (schema + data)
DROP TABLE IF EXISTS customers_backup_20260411;
CREATE TABLE customers_backup_20260411 LIKE customers;
INSERT INTO customers_backup_20260411 SELECT * FROM customers;

-- 2) Proceed with risky change on the live table
ALTER TABLE customers DROP COLUMN legacy_code;

-- Verify
SELECT COUNT(*) AS backup_rows FROM customers_backup_20260411;
DESCRIBE customers;
-- If anything goes wrong, we can restore from customers_backup_20260411.

ALTER TABLE orders
    ADD CONSTRAINT pk_orders PRIMARY KEY (order_id),
    ADD CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    ADD CONSTRAINT chk_amount CHECK (amount > 0),
    ADD CONSTRAINT uq_emp_date UNIQUE (emp_id, order_date);

DESCRIBE orders;
-- Expected keys: order_id (PRI), emp_id (MUL), (emp_id, order_date) UNIQUE composite

-- If something fails (e.g., existing rows violate the CHECK),
-- you can drop a specific constraint by name:
-- ALTER TABLE orders DROP CHECK chk_amount;
-- ALTER TABLE orders DROP FOREIGN KEY fk_emp;

-- Step 1: rename the physical table
RENAME TABLE employees TO staff;

-- Step 2: create a view with the old name for backward compatibility
CREATE OR REPLACE VIEW employees AS
SELECT * FROM staff;

-- Step 3: verify both names still work
SELECT COUNT(*) AS via_new_name   FROM staff;
SELECT COUNT(*) AS via_old_name   FROM employees;

-- Both counts should match.
--
-- This is the pattern big companies use during rename migrations:
-- rename the table, keep a view with the old name so existing app code keeps working,
-- then gradually update call sites over several releases, and finally drop the view.
--
-- Once all call sites are updated:
-- DROP VIEW employees;