SQL Tutorial for QA Database Testing and Verification

DELIMITER //

CREATE PROCEDURE SetupTestData(
  IN  p_email    VARCHAR(100),
  IN  p_role     VARCHAR(20),
  OUT p_user_id  INT
)
BEGIN
  -- Clean any existing record first
  DELETE FROM users WHERE email = p_email;

  -- Insert fresh test user
  INSERT INTO users (email, role, created_at)
  VALUES (p_email, p_role, NOW());

  -- Return the generated ID
  SET p_user_id = LAST_INSERT_ID();
END //

DELIMITER ;

-- Call the procedure
CALL SetupTestData('qa@test.com', 'user', @uid);
SELECT @uid;

DELIMITER //
CREATE PROCEDURE CleanupTestData()
BEGIN
  DELETE FROM orders WHERE reference LIKE 'TEST-%';
  DELETE FROM users  WHERE email     LIKE '%@test.com';
END //
DELIMITER ;

-- Call in test teardown
CALL CleanupTestData();

Connection conn = DriverManager.getConnection(url, user, pass);
CallableStatement cs = conn.prepareCall(
    "{CALL SetupTestData(?, ?, ?)}");

cs.setString(1, "qa@test.com");
cs.setString(2, "user");
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();

int userId = cs.getInt(3);
System.out.println("Created user ID: " + userId);

CREATE TABLE users (
  id          INT           AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100)  NOT NULL,
  email       VARCHAR(150)  NOT NULL UNIQUE,
  role        VARCHAR(20)   NOT NULL DEFAULT 'user',
  age         INT           CHECK (age >= 18),
  is_active   BOOLEAN       DEFAULT TRUE,
  created_at  DATETIME      DEFAULT NOW()
);

CREATE TABLE orders (
  id          INT           AUTO_INCREMENT PRIMARY KEY,
  user_id     INT           NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  status      VARCHAR(20)   DEFAULT 'pending',
  created_at  DATETIME      DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify a column type
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Rename a table
ALTER TABLE users RENAME TO app_users;

-- Drop table (permanent — cannot be rolled back)
DROP TABLE IF EXISTS temp_test_data;

-- Truncate (remove all rows, reset auto-increment)
TRUNCATE TABLE test_logs;

-- Find rows where phone is missing
SELECT * FROM users WHERE phone IS NULL;

-- Find rows where phone is provided
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE: use first non-null value
SELECT name, COALESCE(phone, mobile, 'No contact') AS contact
FROM users;

-- IFNULL: replace NULL with a default (MySQL)
SELECT name, IFNULL(discount, 0) AS discount
FROM orders;

-- NULLIF: avoid division by zero
SELECT total / NULLIF(quantity, 0) AS unit_price
FROM order_items;

-- SUM ignores NULLs — use COALESCE to treat NULL as 0
SELECT SUM(COALESCE(discount, 0)) FROM orders;

-- Count non-null values only
SELECT COUNT(phone) FROM users; -- ignores NULLs
SELECT COUNT(*) FROM users;    -- counts ALL rows including NULLs

-- Classify order amounts
SELECT
  id, amount,
  CASE
    WHEN amount < 100               THEN 'Low'
    WHEN amount BETWEEN 100 AND 999 THEN 'Medium'
    ELSE 'High'
  END AS amount_tier
FROM orders;

-- Simple CASE: translate status code
SELECT id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'A' THEN 'Approved'
    WHEN 'R' THEN 'Rejected'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

-- Pivot: count test results per type in one row
SELECT
  SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) AS passed,
  SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failed,
  SUM(CASE WHEN status = 'SKIP' THEN 1 ELSE 0 END) AS skipped
FROM test_runs;

-- Custom sort: FAIL first, then PENDING, then PASS
SELECT * FROM test_runs
ORDER BY
  CASE status
    WHEN 'FAIL'    THEN 1
    WHEN 'PENDING' THEN 2
    ELSE 3
  END;

-- Case-insensitive email search
SELECT * FROM users WHERE LOWER(email) = 'qa@test.com';

-- Build full name from parts
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Check email format (must contain @)
SELECT * FROM users WHERE INSTR(email, '@') = 0;

-- Trim whitespace from imported data
UPDATE users SET name = TRIM(name) WHERE name != TRIM(name);

-- Extract domain from email
SELECT email,
  SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM users;

-- Orders created in the last 7 days
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Format date for reports
SELECT DATE_FORMAT(created_at, '%d %M %Y') AS order_date
FROM orders;

-- Days since registration
SELECT name, DATEDIFF(NOW(), created_at) AS days_registered
FROM users ORDER BY days_registered DESC;

-- Users registered in April 2026
SELECT * FROM users
WHERE YEAR(created_at) = 2026
  AND MONTH(created_at) = 4;

-- Top 10 most recent orders
SELECT id, user_id, amount, status
FROM orders
ORDER BY created_at DESC
LIMIT 10;

-- Sort by multiple columns: status ASC, then amount DESC
SELECT * FROM orders
ORDER BY status ASC, amount DESC;

-- Page 1 (10 per page)
SELECT * FROM products
ORDER BY name ASC
LIMIT 10 OFFSET 0;

-- Page 2
SELECT * FROM products
ORDER BY name ASC
LIMIT 10 OFFSET 10;

-- Page N formula
-- LIMIT topicsize OFFSET (page-1)*topicsize

-- Get a random sample of 5 test records
SELECT * FROM users
ORDER BY RAND()
LIMIT 5;

-- QA: verify first page = most recent (newest first)
SELECT id, created_at FROM orders
ORDER BY created_at DESC
LIMIT 1; -- should match what UI shows as latest

-- UNION: all admin and manager emails (no duplicates)
SELECT email FROM users WHERE role = 'admin'
UNION
SELECT email FROM users WHERE role = 'manager';

-- UNION ALL: all orders from two regions (keep dupes)
SELECT id, amount, 'EU' AS region FROM eu_orders
UNION ALL
SELECT id, amount, 'US' AS region FROM us_orders
ORDER BY amount DESC;

-- INTERSECT: users who exist in BOTH tables
-- (PostgreSQL/SQL Server — MySQL use JOIN instead)
SELECT email FROM users
INTERSECT
SELECT email FROM newsletter_subscribers;

-- MySQL equivalent of INTERSECT using JOIN
SELECT DISTINCT u.email
FROM users u
JOIN newsletter_subscribers ns ON u.email = ns.email;

-- EXCEPT: rows in source but NOT in target (ETL QA)
SELECT id, email FROM source_users
EXCEPT
SELECT id, email FROM target_users;

-- MySQL EXCEPT equivalent using LEFT JOIN
SELECT s.id, s.email
FROM source_users s
LEFT JOIN target_users t ON s.id = t.id
WHERE t.id IS NULL;

-- EXISTS: users who have at least one order
SELECT u.id, u.name, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id   -- correlated: references outer row
);

-- NOT EXISTS: users who have NEVER placed an order
SELECT u.id, u.name, u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

-- QA: assert a user was created after registration test
SELECT CASE WHEN EXISTS (
  SELECT 1 FROM users WHERE email = 'new@test.com'
) THEN 'PASS' ELSE 'FAIL' END AS test_result;

-- NOT EXISTS: safer than NOT IN when NULLs may exist
-- BAD (fails if any user_id in orders is NULL):
SELECT * FROM users WHERE id NOT IN (
  SELECT user_id FROM orders
);
-- GOOD:
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- employees(id, name, manager_id)
-- SELF JOIN: show employee alongside their manager
SELECT
  e.name    AS employee,
  m.name    AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name;

-- Find duplicate emails in the users table
SELECT
  a.id   AS id1,
  b.id   AS id2,
  a.email
FROM users a
JOIN users b
  ON  a.email = b.email
  AND a.id < b.id;  -- avoid pairing (1,2) AND (2,1)

-- Find orders from the same user placed on the same day
SELECT
  a.id  AS order1,
  b.id  AS order2,
  a.user_id,
  DATE(a.created_at) AS order_date
FROM orders a
JOIN orders b
  ON  a.user_id = b.user_id
  AND DATE(a.created_at) = DATE(b.created_at)
  AND a.id < b.id;

-- Count orders per user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Users with MORE than 5 orders (HAVING filters groups)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- WHERE + GROUP BY + HAVING together
-- Only 2026 orders, grouped by month, months with > 100 orders
SELECT
  MONTH(created_at) AS month,
  COUNT(*) AS total,
  SUM(amount)       AS revenue
FROM orders
WHERE YEAR(created_at) = 2026      -- filter rows first
GROUP BY MONTH(created_at)           -- then group
HAVING COUNT(*) > 100               -- then filter groups
ORDER BY month;

-- Detect duplicate emails (QA use case)
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Create a QA verification view (orders with user info)
CREATE VIEW vw_order_details AS
  SELECT
    o.id          AS order_id,
    u.name        AS customer_name,
    u.email,
    o.amount,
    o.status,
    o.created_at
  FROM orders o
  INNER JOIN users u ON o.user_id = u.id;

-- Query the view exactly like a table
SELECT * FROM vw_order_details WHERE status = 'failed';

-- Update view definition
CREATE OR REPLACE VIEW vw_order_details AS
  SELECT o.id, u.email, o.amount, o.status, o.created_at
  FROM orders o JOIN users u ON o.user_id = u.id;

-- QA view: all test users (exclude production)
CREATE VIEW vw_test_users AS
  SELECT * FROM users WHERE email LIKE '%@test.com';

-- Drop when no longer needed
DROP VIEW IF EXISTS vw_test_users;

-- Step 1: find high-value orders
-- Step 2: get those customers
WITH high_value_orders AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 5000
)
SELECT u.name, u.email, h.total
FROM users u
JOIN high_value_orders h ON u.id = h.user_id;

-- Multiple CTEs: chained steps
WITH
active_users AS (
  SELECT id, name FROM users WHERE is_active = TRUE
),
recent_orders AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY user_id
)
SELECT a.name, r.cnt
FROM active_users a
JOIN recent_orders r ON a.id = r.user_id;

-- employees(id, name, manager_id)
WITH RECURSIVE org_tree AS (
  -- Anchor: start from the CEO (no manager)
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: join each employee to their manager
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree ORDER BY level;

-- Rank users by total spend
SELECT
  name, total_spent,
  RANK()       OVER (ORDER BY total_spent DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rnk,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS row_num
FROM user_totals;

-- Deduplicate: keep latest record per user
WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT * FROM deduped WHERE rn = 1;

-- LAG: compare each order to the previous one
SELECT
  id, amount,
  LAG(amount, 1) OVER (ORDER BY created_at) AS prev_amount,
  amount - LAG(amount, 1) OVER (ORDER BY created_at) AS change
FROM orders;

-- Running total per user
SELECT
  user_id, amount, created_at,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS running_total
FROM orders;

-- Single-column index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);

-- Composite index (column order matters!)
-- Useful for: WHERE status = ? AND created_at > ?
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Drop an index
DROP INDEX idx_users_email ON users;

-- Show all indexes on a table
SHOW INDEXES FROM orders;

-- EXPLAIN shows the query execution plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Key columns to check in EXPLAIN output:
-- type: ALL = full scan (bad), ref/eq_ref = index (good)
-- key: which index is being used (NULL = no index)
-- rows: estimated rows scanned (lower = faster)
-- Extra: "Using where", "Using index" (index-only = very fast)

-- EXPLAIN ANALYZE (MySQL 8+): shows actual execution stats
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01';

-- Transfer $100 from account A to account B atomically
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both succeed: commit permanently
COMMIT;

-- If something went wrong: undo everything
-- ROLLBACK;

START TRANSACTION;

INSERT INTO orders (user_id, amount) VALUES (1, 500);
SAVEPOINT after_order;

INSERT INTO payments (order_id, method) VALUES (LAST_INSERT_ID(), 'card');

-- Payment failed — roll back only to savepoint (keep order)
ROLLBACK TO SAVEPOINT after_order;

-- Retry with different payment method
INSERT INTO payments (order_id, method) VALUES (LAST_INSERT_ID(), 'bank');
COMMIT;

-- Verify order total is rounded to 2 decimal places
SELECT id, amount,
  ROUND(amount, 2)    AS rounded,
  TRUNCATE(amount, 2) AS truncated
FROM orders
WHERE amount != ROUND(amount, 2); -- find rounding issues

-- Calculate percentage of passed tests
SELECT
  ROUND(
    SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) * 100.0
    / COUNT(*), 1
  ) AS pass_rate_pct
FROM test_runs;

-- Find odd-numbered records (MOD)
SELECT * FROM users WHERE MOD(id, 2) = 1;

-- Absolute difference between expected and actual amounts
SELECT order_id,
  expected_amount, actual_amount,
  ABS(expected_amount - actual_amount) AS discrepancy
FROM order_audit
WHERE ABS(expected_amount - actual_amount) > 0.01;

-- Generate random test price between 10 and 100
SELECT ROUND(10 + RAND() * 90, 2) AS random_price;

-- Audit log table
CREATE TABLE user_audit (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT,
  action     VARCHAR(10),
  old_role   VARCHAR(20),
  new_role   VARCHAR(20),
  changed_at DATETIME DEFAULT NOW()
);

-- Trigger: log every role change
DELIMITER //
CREATE TRIGGER trg_user_role_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.role != NEW.role THEN
    INSERT INTO user_audit (user_id, action, old_role, new_role)
    VALUES (OLD.id, 'UPDATE', OLD.role, NEW.role);
  END IF;
END //
DELIMITER ;

-- Trigger: set updated_at automatically
DELIMITER //
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- Trigger: prevent deletion of admin users
DELIMITER //
CREATE TRIGGER trg_no_admin_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  IF OLD.role = 'admin' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot delete admin users';
  END IF;
END //
DELIMITER ;

-- View existing triggers
SHOW TRIGGERS FROM your_database;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_user_role_audit;

-- Table with JSON column
-- orders(id, payload JSON)
-- payload: {"user":"alice","items":[{"sku":"A1","qty":2}],"total":49.99}

-- Extract a value from JSON
SELECT id,
  JSON_EXTRACT(payload, '$.user')  AS user_name,
  JSON_EXTRACT(payload, '$.total') AS total
FROM orders;

-- Shorthand operator (returns unquoted string)
SELECT payload->>'$.user' AS user_name FROM orders;

-- Filter by JSON field value
SELECT * FROM orders
WHERE payload->>'$.user' = 'alice';

-- Count items in JSON array
SELECT id,
  JSON_LENGTH(payload, '$.items') AS item_count
FROM orders
WHERE JSON_LENGTH(payload, '$.items') > 3; -- orders with >3 items

-- Validate all JSON in a TEXT column is valid
SELECT id, payload
FROM api_logs
WHERE JSON_VALID(payload) = 0; -- find broken JSON

-- Build JSON from relational data
SELECT
  JSON_OBJECT(
    'id',    id,
    'name',  name,
    'email', email
  ) AS user_json
FROM users LIMIT 5;

-- Aggregate: all order IDs per user as a JSON array
SELECT
  user_id,
  JSON_ARRAYAGG(id) AS order_ids
FROM orders
GROUP BY user_id;