SQL Cheat Sheet

SELECT col1, col2           -- choose columns
FROM table_name;

SELECT *                    -- every column
FROM table_name;

SELECT DISTINCT city        -- unique values only
FROM customers;

SELECT price * qty AS total -- calculated column with an alias
FROM order_items;

SELECT * FROM employees
WHERE salary > 50000
  AND (department = 'Sales' OR city IN ('Mumbai', 'Pune'));
-- AND runs before OR: use parentheses when mixing them

SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC
LIMIT 10;                 -- MySQL, PostgreSQL, SQLite
-- SQL Server: SELECT TOP 10 ...
-- Standard / Oracle: FETCH FIRST 10 ROWS ONLY

SELECT department,
       COUNT(*)    AS people,
       AVG(salary) AS avg_salary
FROM employees
WHERE active = 1          -- filter rows (before grouping)
GROUP BY department
HAVING COUNT(*) > 5       -- filter groups (after grouping)
ORDER BY avg_salary DESC;

-- only matching rows
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

-- every customer, orders if any (NULL if none)
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

-- customers with no orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

INSERT INTO customers (name, city)
VALUES ('Asha', 'Mumbai');

UPDATE customers
SET city = 'Pune'
WHERE customer_id = 3;    -- without WHERE, every row changes!

DELETE FROM customers
WHERE customer_id = 3;    -- without WHERE, every row is deleted!

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE,
  city        VARCHAR(50) DEFAULT 'Unknown'
);

ALTER TABLE customers ADD phone VARCHAR(20);
DROP TABLE customers;     -- removes the table and its data