SQL Database
Testing Tutorial

-- Select ALL columns from a table (* = all)
SELECT * FROM users;

-- Tester use: After registering a user, verify all columns were saved

-- Select SPECIFIC columns (best practice — don't use * in production)
SELECT user_id, name, email, city
FROM users;

-- DISTINCT — get unique values only (no duplicates)
SELECT DISTINCT city
FROM users;
-- Tester use: Check how many unique cities have registered users

-- ORDER BY — sort results ASC (default) or DESC
SELECT order_id, total_amount, order_date
FROM orders
ORDER BY order_date DESC;  -- newest orders first

-- LIMIT — show only first N rows (MySQL syntax)
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5;  -- show only the 5 most recent orders

-- AS — give columns a readable alias in results
SELECT
    user_id   AS ID,
    name      AS "Customer Name",
    email     AS "Email Address"
FROM users;

-- SCENARIO: User filled registration form. UI shows "Account created!"
-- TESTER QUERY: Verify the row was actually inserted in DB
SELECT * FROM users
WHERE email = 'priya@gmail.com';

-- Expected result: 1 row with correct name, email, phone
-- user_id | name         | email            | phone      | city
-- --------|--------------|------------------|------------|-------
-- 101     | Priya Sharma | priya@gmail.com  | 9876543210 | Mumbai

-- SCENARIO: User placed an order. Verify order was created
SELECT order_id, user_id, total_amount, status
FROM orders
WHERE user_id = 101
ORDER BY order_date DESC
LIMIT 1;

-- Expected: status='pending', total_amount = 2999

-- Basic WHERE with = operator
SELECT * FROM users WHERE city = 'Mumbai';

-- Comparison operators: =  !=  <  >  <=  >=
SELECT * FROM orders WHERE total_amount >= 1000;
SELECT * FROM orders WHERE status != 'delivered';

-- AND — BOTH conditions must be true
SELECT * FROM users
WHERE city = 'Mumbai' AND created_at > '2024-01-01';
-- Tester: Users from Mumbai who registered after Jan 2024

-- OR — ANY ONE condition must be true
SELECT * FROM orders
WHERE status = 'failed' OR status = 'cancelled';
-- Tester: Find all failed or cancelled orders for validation

-- IN — match any value from a list (cleaner than multiple ORs)
SELECT * FROM orders
WHERE status IN ('failed', 'cancelled', 'refunded');
-- Same as above but cleaner. IN is equivalent to multiple ORs.

-- NOT IN — exclude specific values
SELECT * FROM orders
WHERE status NOT IN ('delivered', 'shipped');

-- BETWEEN — range check (inclusive of both ends)
SELECT * FROM orders
WHERE total_amount BETWEEN 500 AND 5000;
-- Tester: Orders with amount between ₹500 and ₹5000

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- Tester: All orders placed in year 2024

-- LIKE — pattern matching (% = any chars, _ = one char)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Tester: Find all users with Gmail addresses

SELECT * FROM users WHERE name LIKE 'Priya%';
-- Names starting with "Priya"

SELECT * FROM users WHERE name LIKE '%Kumar%';
-- Names containing "Kumar" anywhere

SELECT * FROM users WHERE phone LIKE '98_______';
-- 10-digit phone starting with "98" (_ = exactly 1 character)

-- ═══ INSERT ═══ Create new rows ═══════════════════════════════════

-- Insert one user (specify column names — best practice)
INSERT INTO users (name, email, phone, city)
VALUES ('Priya Sharma', 'priya@gmail.com', '9876543210', 'Mumbai');

-- Tester use: Create test data before running test cases

-- Insert multiple rows at once
INSERT INTO products (product_name, price, stock_qty, category)
VALUES
    ('iPhone 15',        79999, 50, 'Mobiles'),
    ('Samsung S24',      59999, 30, 'Mobiles'),
    ('Sony Headphones',  4999,  100, 'Electronics');

-- ═══ UPDATE ═══ Modify existing rows ════════════════════════════

-- Update a single user's city
UPDATE users
SET city = 'Delhi'
WHERE user_id = 101;
-- CRITICAL: Always use WHERE in UPDATE! Without it, ALL rows get updated!

-- Update multiple columns at once
UPDATE orders
SET status = 'delivered'
WHERE order_id = 5001;

-- Tester: After marking order as delivered in UI, verify in DB:
-- SELECT status FROM orders WHERE order_id = 5001;
-- Expected: 'delivered'

-- ═══ DELETE ═══ Remove specific rows ════════════════════════════

-- Delete a specific test user (cleanup)
DELETE FROM users
WHERE email = 'testuser@test.com';

-- CRITICAL: Always use WHERE in DELETE! Without it, ALL rows get deleted!

-- Delete all test orders older than a date (test data cleanup)
DELETE FROM orders
WHERE order_date < '2020-01-01'
  AND status = 'test';

-- ═══ INNER JOIN ═══ Only matched rows ═══════════════════════════
-- Find all orders with the customer name (orders + users)
SELECT
    o.order_id,
    u.name         AS customer_name,
    u.email,
    o.total_amount,
    o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

-- Result: Only orders where user exists in users table
-- order_id | customer_name | email           | total_amount | status
-- ---------|---------------|-----------------|--------------|--------
-- 5001     | Priya Sharma  | priya@gmail.com | 2999.00      | pending
-- 5002     | Rahul Mehta   | rahul@yahoo.com | 599.00       | delivered

-- ═══ LEFT JOIN ═══ All left rows + matched right ═════════════════
-- Find ALL users — including those who NEVER placed an order
SELECT
    u.user_id,
    u.name,
    o.order_id   -- will be NULL if user has no orders
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

-- user_id | name         | order_id
-- --------|--------------|----------
-- 101     | Priya Sharma | 5001
-- 102     | Rahul Mehta  | 5002
-- 103     | Ankit Singh  | NULL      ← user exists but has NO orders

-- Tester use case: Find users who registered but never ordered
SELECT u.user_id, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
-- Returns ONLY users with no orders (order_id = NULL)

-- ═══ 3-TABLE JOIN ═══ orders + users + products ═════════════════
-- Full order details: customer name, product name, qty, price
SELECT
    u.name            AS customer,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total,
    o.status
FROM orders o
INNER JOIN users u         ON o.user_id    = u.user_id
INNER JOIN order_items oi  ON o.order_id   = oi.order_id
INNER JOIN products p      ON oi.product_id = p.product_id
WHERE o.order_id = 5001;
-- Tester: Verify complete order details match what UI displays

-- COUNT: How many users have registered?
SELECT COUNT(*) AS total_users
FROM users;
-- Result: 1250

-- SUM: Total revenue from all delivered orders
SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'delivered';
-- Result: 4,56,78,900.00  ← verify this matches dashboard

-- AVG: Average order amount
SELECT AVG(total_amount) AS avg_order_value
FROM orders;
-- Result: 1850.50

-- MAX and MIN: Price range of products
SELECT
    MAX(price) AS highest_price,
    MIN(price) AS lowest_price
FROM products;
-- Result: highest_price=79999 | lowest_price=99

-- ═══ GROUP BY ═══ Aggregate per group ═══════════════════════════

-- Count orders per status (verify order distribution)
SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;
-- status     | order_count
-- -----------|------------
-- delivered  | 8920
-- pending    | 1230
-- cancelled  | 450
-- failed     | 180

-- Total orders and revenue per user (top customers)
SELECT
    u.name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY total_spent DESC
LIMIT 5;
-- Tester: Verify top customers shown in dashboard match DB

-- Count products per category
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- WHERE: Filter individual orders with amount > 1000 BEFORE grouping
SELECT status, COUNT(*) AS count
FROM orders
WHERE total_amount > 1000   -- filter rows FIRST (before grouping)
GROUP BY status;
-- This counts orders with amount>1000, grouped by status

-- HAVING: Filter groups (statuses) where order COUNT > 100
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status
HAVING COUNT(*) > 100;      -- filter GROUPS after grouping
-- This shows only status groups that have MORE THAN 100 orders

-- USING BOTH TOGETHER: Real tester scenario
-- Find cities that have MORE THAN 50 users who registered in 2024
SELECT
    city,
    COUNT(*) AS user_count
FROM users
WHERE created_at >= '2024-01-01'   -- WHERE filters 2024 users FIRST
GROUP BY city                          -- GROUP remaining rows by city
HAVING COUNT(*) > 50                  -- HAVING filters groups with >50
ORDER BY user_count DESC;

-- WRONG: Cannot use aggregate function in WHERE
SELECT city, COUNT(*)
FROM users
WHERE COUNT(*) > 50  -- ❌ ERROR! Cannot use COUNT in WHERE
GROUP BY city;

-- ═══ Subquery in WHERE ═══════════════════════════════════════════

-- Find all orders placed by users from 'Mumbai'
-- Without subquery: you'd need a JOIN
-- With subquery:
SELECT * FROM orders
WHERE user_id IN (
    SELECT user_id          -- inner query runs first
    FROM users
    WHERE city = 'Mumbai'  -- returns list: (101, 105, 118...)
);
-- Outer query: WHERE user_id IN (101, 105, 118...)

-- Find products that have NEVER been ordered
SELECT * FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
);
-- Tester: Check for stale/unused products in catalog

-- Find orders with amount ABOVE average order value
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders       -- inner query returns single value: 1850.50
);
-- Returns orders where amount > 1850.50

-- ═══ Subquery in SELECT (Scalar subquery) ════════════════════════
-- Show each order along with the user's total order count
SELECT
    order_id,
    total_amount,
    (SELECT COUNT(*)
     FROM orders o2
     WHERE o2.user_id = o1.user_id
    ) AS user_total_orders
FROM orders o1;
-- Each row shows the order + how many total orders that user has

-- ═══ EXISTS — check if subquery returns any row ════════════════
-- Find users who HAVE placed at least one order
SELECT name, email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
);
-- EXISTS returns true if the subquery returns at least one row

-- WRONG: Cannot use = or != to check for NULL
SELECT * FROM users WHERE phone = NULL;      -- ❌ Returns 0 rows (wrong!)
SELECT * FROM users WHERE phone != NULL;     -- ❌ Also returns 0 rows (wrong!)

-- CORRECT: Use IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;     -- ✅ Users with no phone number
SELECT * FROM users WHERE phone IS NOT NULL; -- ✅ Users who have a phone number

-- Tester use: Find incomplete user profiles (NULL in required fields)
SELECT user_id, name, email, phone
FROM users
WHERE phone IS NULL OR city IS NULL;
-- Bug: If phone/city are mandatory fields, these rows show a validation bug

-- COALESCE: Replace NULL with a default value
SELECT
    name,
    COALESCE(phone, 'Not provided') AS phone,
    COALESCE(city,  'Unknown')      AS city
FROM users;
-- COALESCE returns the first non-NULL value from its arguments

-- IFNULL (MySQL): Replace NULL with a value
SELECT IFNULL(phone, 'Not provided') FROM users;

-- NULL in aggregate functions: NULL values are IGNORED by COUNT(col)
SELECT
    COUNT(*)         AS total_rows,    -- counts ALL rows including nulls
    COUNT(phone)    AS rows_with_phone -- counts ONLY non-NULL phone rows
FROM users;
-- total_rows=1250 | rows_with_phone=1180 (70 users have NULL phone)

-- NULL in math: Any calculation with NULL = NULL
SELECT 100 + NULL;  -- Result: NULL (not 100!)
-- Tester: If price or quantity is NULL, order total = NULL — verify this bug!

-- Check for DUPLICATE email addresses (UNIQUE constraint violation)
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Bug: If rows returned → duplicate emails exist = constraint not enforced

-- Check for ORPHANED RECORDS (Foreign Key violation)
-- Orders with user_id that doesn't exist in users table
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
-- Bug: If rows returned → orphaned orders (user was deleted but orders remain)

-- Check for NEGATIVE PRICES (CHECK constraint violation)
SELECT * FROM products
WHERE price <= 0;
-- Bug: Products with 0 or negative price — business rule violation

-- Check for NULL in mandatory fields (NOT NULL violation)
SELECT user_id, name
FROM users
WHERE email IS NULL OR name IS NULL;
-- Bug: Email and name are required fields — should never be NULL

-- Verify order total = sum of order items (business rule)
SELECT
    o.order_id,
    o.total_amount           AS stored_total,
    SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);
-- Bug: Orders where stored total doesn't match calculated total
-- (pricing bug — possible rounding error or discount not applied)