-- Select all columns
SELECT * FROM users;

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

-- With alias
SELECT name AS full_name, email AS contact FROM users;

-- Distinct values
SELECT DISTINCT country FROM users;

-- Limit results
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- pagination

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE name IN ('Alice', 'Bob', 'Carol');
SELECT * FROM users WHERE name LIKE 'A%';    -- starts with A
SELECT * FROM users WHERE name LIKE '%son';  -- ends with son
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE age > 18 AND country = 'US';
SELECT * FROM users WHERE age < 18 OR country = 'UK';
SELECT * FROM users WHERE NOT country = 'US';

-- ORDER BY
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price DESC;
SELECT * FROM products ORDER BY category ASC, price DESC;

-- GROUP BY
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;  -- filter groups (not WHERE!)

SELECT COUNT(*) FROM users;               -- total rows
SELECT COUNT(email) FROM users;           -- non-null emails
SELECT SUM(amount) FROM orders;           -- total
SELECT AVG(age) FROM users;               -- average
SELECT MIN(price) FROM products;          -- minimum
SELECT MAX(price) FROM products;          -- maximum

-- INNER JOIN — only matching rows in both tables
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN — all rows from left, NULL if no right match
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN — all rows from right, NULL if no left match
SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- FULL OUTER JOIN — all rows from both tables
SELECT users.name, orders.amount
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

-- Self join
SELECT a.name, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

-- INSERT
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);

INSERT INTO users (name, email)
VALUES
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

-- UPDATE
UPDATE users
SET age = 26, email = 'new@example.com'
WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 13;

CREATE TABLE users (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  age        INT CHECK(age >= 0),
  country    VARCHAR(50) DEFAULT 'US',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Foreign key
CREATE TABLE orders (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  amount  DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE amount > 100
);

-- Subquery in FROM
SELECT avg_order.*
FROM (
  SELECT user_id, AVG(amount) AS avg_amount
  FROM orders
  GROUP BY user_id
) AS avg_order
WHERE avg_amount > 50;

WITH high_value_users AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 1000
)
SELECT users.name, hvu.total
FROM users
JOIN high_value_users hvu ON users.id = hvu.user_id;

-- Row number
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

-- Partition by department
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);
CREATE INDEX idx_composite ON orders(user_id, created_at);
DROP INDEX idx_email ON users;
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';