CREATE TABLE comments (
  id INT PRIMARY KEY,
  post_id INT,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);
-- deleting a post automatically deletes all its comments

SELECT * FROM users WHERE phone = NULL;      -- WRONG: matches nothing, ever
SELECT * FROM users WHERE phone IS NULL;      -- correct way to check for NULL
SELECT * FROM users WHERE phone IS NOT NULL;  -- correct way to check for NOT NULL

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  total DECIMAL(10,2)
);
INSERT INTO orders (total) VALUES (49.99); -- id is generated automatically

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price >= 0),
  status VARCHAR(20) CHECK (status IN ('active','discontinued'))
);

SELECT * FROM products
WHERE price BETWEEN 10 AND 50
  AND category IN ('Electronics','Books')
  AND name LIKE 'Wireless%'    -- starts with 'Wireless'
  AND status != 'discontinued';

SELECT * FROM employees
ORDER BY department ASC, salary DESC;
-- push NULLs to the end regardless of direction:
SELECT * FROM employees ORDER BY commission IS NULL, commission DESC;

-- page 3, 20 items per page (offset-based, simple but slow for deep pages)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;
-- cursor-based (fast even for 'deep' pages)
SELECT * FROM products WHERE id > 1043 ORDER BY id LIMIT 20;

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       SUBSTRING(email, 1, LOCATE('@', email)-1) AS username,
       TRIM(bio) AS clean_bio,
       REPLACE(phone, '-', '') AS digits_only,
       LOWER(email) AS normalized_email
FROM users;

SELECT
  NOW() AS right_now,
  DATEDIFF(CURDATE(), order_date) AS days_since_order,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS warranty_expires,
  DATE_FORMAT(order_date, '%M %d, %Y') AS pretty_date
FROM orders;

-- these two are logically equivalent:
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT * FROM b LEFT JOIN a ON a.id = b.a_id;

-- simulating FULL OUTER JOIN in MySQL
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.id = b.a_id;

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

-- deliberately generate every size/color combination
SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors;

-- IN: compares against a list of values
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
-- EXISTS: just checks if any matching row exists (correlated)
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);

-- correlated: 'd.id' references the OUTER query's current row
SELECT d.name,
  (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS emp_count
FROM departments d;

SELECT dept_avgs.department, dept_avgs.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avgs   -- alias is required
WHERE dept_avgs.avg_salary > 60000;

WITH dept_avgs AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avgs d ON d.department = e.department
WHERE e.salary > d.avg_salary;

SELECT status, COUNT(*) AS how_many
FROM orders
GROUP BY status
ORDER BY how_many DESC;

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = 'active';
-- now query it like a regular table:
SELECT * FROM active_customers WHERE name LIKE 'A%';

CREATE VIEW simple_view AS SELECT id, name, email FROM customers;
UPDATE simple_view SET email = 'new@email.com' WHERE id = 1; -- works: updates the real customers table
-- but a view with a JOIN or GROUP BY generally can't be updated this way

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
-- check: type (want ref/range, not ALL), key (is an index being used?), rows (how many examined?)