Practice Questions — INSERT, UPDATE, DELETE - Manipulating Data

DROP TABLE IF EXISTS students;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(30),
    marks INT
);

INSERT INTO students (id, name, city, marks) VALUES
    (1, 'Aarav Sharma', 'Mumbai',    87),
    (2, 'Priya Iyer',   'Bengaluru', 92),
    (3, 'Rohan Verma',  'Delhi',     78),
    (4, 'Ananya Reddy', 'Hyderabad', 95),
    (5, 'Vikram Singh', 'Pune',      65);

SELECT * FROM students;

UPDATE students
SET marks = 80, city = 'Mumbai'
WHERE id = 5;

-- Verify
SELECT * FROM students WHERE id = 5;
-- Expected:
-- | 5 | Vikram Singh | Mumbai | 80 |

UPDATE students
SET marks = marks + 5
WHERE city IN ('Mumbai', 'Bengaluru');

SELECT id, name, city, marks FROM students ORDER BY id;
-- Aarav: 92 (was 87), Priya: 97 (was 92), others unchanged.

DROP TABLE IF EXISTS click_counts;

CREATE TABLE click_counts (
    page VARCHAR(100) PRIMARY KEY,
    clicks INT NOT NULL DEFAULT 0
);

-- 3 clicks on /home
INSERT INTO click_counts (page, clicks) VALUES ('/home', 1)
  ON DUPLICATE KEY UPDATE clicks = clicks + 1;
INSERT INTO click_counts (page, clicks) VALUES ('/home', 1)
  ON DUPLICATE KEY UPDATE clicks = clicks + 1;
INSERT INTO click_counts (page, clicks) VALUES ('/home', 1)
  ON DUPLICATE KEY UPDATE clicks = clicks + 1;

-- 2 clicks on /about
INSERT INTO click_counts (page, clicks) VALUES ('/about', 1)
  ON DUPLICATE KEY UPDATE clicks = clicks + 1;
INSERT INTO click_counts (page, clicks) VALUES ('/about', 1)
  ON DUPLICATE KEY UPDATE clicks = clicks + 1;

SELECT * FROM click_counts;
-- Expected:
-- +--------+--------+
-- | page   | clicks |
-- +--------+--------+
-- | /home  |      3 |
-- | /about |      2 |
-- +--------+--------+

-- Setup
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS employees_archive;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    active TINYINT DEFAULT 1
);

CREATE TABLE employees_archive LIKE employees;

INSERT INTO employees VALUES
    (101, 'Kavya Nair',    60000, 1),
    (102, 'Arjun Mehta',   85000, 1),
    (103, 'Sneha Patel',   72000, 0),   -- inactive
    (104, 'Rahul Joshi',   95000, 1),
    (105, 'Meera Gupta',   65000, 0);   -- inactive

-- Atomic archive
START TRANSACTION;

INSERT INTO employees_archive
SELECT * FROM employees WHERE active = 0;

DELETE FROM employees WHERE active = 0;

COMMIT;

-- Verify
SELECT COUNT(*) AS live FROM employees;           -- 3
SELECT COUNT(*) AS archived FROM employees_archive; -- 2
SELECT * FROM employees_archive;

-- STEP 1: Preview — what rows will be affected?
SELECT id, name, city, marks
FROM students
WHERE city = 'Delhi';
-- (confirm the list looks right before proceeding)

-- STEP 2: Begin transaction so we can ROLLBACK if something looks wrong.
START TRANSACTION;

-- STEP 3: Apply the change
UPDATE students
SET city = 'New Delhi',
    marks = marks + 10
WHERE city = 'Delhi';

-- STEP 4: Post-check — do the updated rows look right?
SELECT id, name, city, marks
FROM students
WHERE city = 'New Delhi';
-- If the rows look correct, proceed. If not, ROLLBACK.

-- STEP 5: Commit or rollback based on verification
COMMIT;
-- OR:
-- ROLLBACK;

-- This workflow — preview, transaction, apply, verify, commit — is how
-- senior engineers handle production updates. Never skip the preview.
-- Never skip the verify. If ANYTHING looks wrong, ROLLBACK.