MySQL CRUD Operations

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email, age FROM users;

-- Column aliases
SELECT
    name AS full_name,
    CONCAT(first_name, ' ', last_name) AS display_name,
    price * quantity AS total
FROM orders;

-- Distinct values
SELECT DISTINCT city FROM users;
SELECT DISTINCT COUNT(DISTINCT user_id) AS unique_users FROM orders;

-- Limit and offset
SELECT * FROM users LIMIT 10;              -- First 10 rows
SELECT * FROM users LIMIT 10 OFFSET 20;   -- Skip 20, get next 10
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- Safer with ORDER BY

-- Single column
SELECT * FROM users ORDER BY name ASC;      -- A-Z
SELECT * FROM users ORDER BY created_at DESC; -- Newest first

-- Multiple columns
SELECT * FROM users ORDER BY city ASC, age DESC;
-- Sorts by city, then within each city by age

-- Custom sort order
SELECT * FROM products ORDER BY FIELD(status, 'active', 'pending', 'archived');

-- Sort by expression
SELECT * FROM products ORDER BY (price * stock) DESC;

-- Numeric conditions
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE stock IN (0, 1, 5);

-- Text conditions
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name REGEXP '^[A-Z]';  -- Starts with uppercase

-- Date conditions
SELECT * FROM orders WHERE created_at >= '2024-01-01';
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- NULL conditions
SELECT * FROM users WHERE phone IS NOT NULL;
SELECT * FROM users WHERE deleted_at IS NULL;

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;

-- Sum, Average, Min, Max
SELECT
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders;

-- Group by
SELECT
    city,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age
FROM users
GROUP BY city
ORDER BY user_count DESC;

-- Having (filters groups, not rows)
SELECT
    city,
    COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 10
ORDER BY user_count DESC;

-- Complete query order
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 75000
ORDER BY avg_salary DESC
LIMIT 5;

-- INNER JOIN
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (all users, even without orders)
SELECT u.name, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- RIGHT JOIN
SELECT o.product, u.name
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- Multiple JOINs
SELECT
    u.name,
    o.product,
    c.category_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id;

-- Self JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- WHERE subquery
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 100
);

-- FROM subquery (derived table)
SELECT avg_spending, COUNT(*) AS user_count
FROM (
    SELECT user_id, AVG(amount) AS avg_spending
    FROM orders
    GROUP BY user_id
) AS user_averages
GROUP BY ROUND(avg_spending, -1);

-- EXISTS
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Correlated subquery
SELECT name, age,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;