-- DDL
CREATE TABLE users (id INT, name VARCHAR(50));
-- DML
INSERT INTO users VALUES (1, 'Sara');

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

SELECT * FROM users WHERE phone IS NULL;

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

SELECT dept, AVG(salary) FROM employees GROUP BY dept;

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

SELECT COUNT(*), COUNT(phone) FROM users;

COMMIT;
-- or
ROLLBACK;

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price > 0)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending'
);

SELECT * FROM products ORDER BY price DESC;

SELECT * FROM products ORDER BY price DESC LIMIT 10;

SELECT * FROM products WHERE name LIKE 'App%';

SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;

SELECT DATEDIFF(NOW(), created_at) AS days_since_signup FROM users;

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

SELECT dept, COUNT(*) FROM employees
GROUP BY dept
HAVING COUNT(*) > 5;

DELETE FROM users WHERE id = 1;
TRUNCATE TABLE users;
DROP TABLE users;

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

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

SELECT a.color, b.size
FROM colors a CROSS JOIN sizes b;

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;

CREATE INDEX idx_users_email ON users(email);

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

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

CREATE PROCEDURE GetUserById(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail\.com$';

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT COALESCE(phone, 'N/A') FROM users;

SELECT name,
  CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_band
FROM employees;

SELECT DISTINCT city FROM customers;

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

-- Vulnerable:
"SELECT * FROM users WHERE username = '" + input + "'"
-- Safe (parameterized):
"SELECT * FROM users WHERE username = ?"

CREATE TEMPORARY TABLE temp_results AS
SELECT * FROM orders WHERE status = 'pending';

SELECT dept, GROUP_CONCAT(name SEPARATOR ', ') FROM employees GROUP BY dept;

CREATE TABLE logs (
  id INT,
  message VARCHAR(255)
) ENGINE=MyISAM;

-- Using LIMIT/OFFSET (nth = 3rd highest)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 2;

-- Using DENSE_RANK
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 3;

SELECT e.name FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees e2
  WHERE e2.dept_id = e.dept_id
);

WITH RECURSIVE org_chart AS (
  SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

SELECT name, salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

SELECT name, dept, salary,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM sales;

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

SAVEPOINT before_update;
UPDATE accounts SET balance = 0 WHERE id = 1;
ROLLBACK TO before_update;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM a
LEFT JOIN b ON a.id = b.a_id AND b.active = 1
WHERE a.status = 'open';

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

INSERT INTO inventory (product_id, quantity)
VALUES (1, 10)
ON DUPLICATE KEY UPDATE quantity = quantity + 10;