SQL Cheatsheet

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

INSERT INTO users (name, email)
VALUES ('Luis', 'luis@example.com');

SELECT * FROM users;

SELECT name, email FROM users;

SELECT * FROM users
ORDER BY name ASC
LIMIT 10;

SELECT * FROM users WHERE id = 1;

SELECT * FROM users
WHERE age > 18 AND status = 'active';

SELECT * FROM users WHERE name LIKE 'L%';
SELECT * FROM users WHERE id IN (1, 2, 3);

SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;

SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT COUNT(*), SUM(total), AVG(total)
FROM orders;

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

UPDATE users
SET email = 'new@example.com'
WHERE id = 1;

DELETE FROM users WHERE id = 1;

ALTER TABLE users ADD COLUMN age INT;

DROP TABLE users;

CREATE INDEX idx_email ON users(email);

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

SELECT avg_total.user_id, avg_total.avg
FROM (
  SELECT user_id, AVG(total) AS avg
  FROM orders
  GROUP BY user_id
) AS avg_total;