Essential SQL Queries Reference Guide
Basic Query Structure
sql
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC];
Data Retrieval
Basic SELECT
sql
-- Select all columns
SELECT * FROM employees;
-- Select specific columns
SELECT first_name, last_name, salary FROM employees;
-- Select with column alias
SELECT first_name AS name, salary*12 AS annual_salary FROM employees;
Filtering with WHERE
sql
-- Basic conditions
SELECT * FROM employees WHERE department_id = 50;
SELECT * FROM products WHERE price > 100;
SELECT * FROM orders WHERE order_date = '2023-01-15';
-- Multiple conditions
SELECT * FROM employees WHERE salary > 5000 AND department_id = 20;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Computers';
SELECT * FROM orders WHERE NOT status = 'Shipped';
-- Pattern matching
SELECT * FROM employees WHERE last_name LIKE 'S%'; -- Names starting with S
SELECT * FROM products WHERE description LIKE '%wireless%'; -- Contains "wireless"
Sorting with ORDER BY
sql
-- Sort by single column
SELECT * FROM employees ORDER BY last_name; -- Ascending (default)
SELECT * FROM products ORDER BY price DESC; -- Descending
-- Sort by multiple columns
SELECT * FROM employees ORDER BY department_id, salary DESC;
DISTINCT for unique values
sql
SELECT DISTINCT department_id FROM employees;
Data Aggregation
Aggregate Functions
sql
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT SUM(quantity) FROM order_items;
SELECT MAX(price) FROM products;
SELECT MIN(hire_date) FROM employees;
GROUP BY
sql
-- Group by one column
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
-- Group by multiple columns
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, job_id;
HAVING (filtering for groups)
sql
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
Joins
INNER JOIN
sql
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
LEFT JOIN (LEFT OUTER JOIN)
sql
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
RIGHT JOIN (RIGHT OUTER JOIN)
sql
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
FULL JOIN (FULL OUTER JOIN)
sql
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
Self Join
sql
SELECT e1.employee_id, e1.last_name AS employee, e2.last_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
Subqueries
Subquery in WHERE
sql
-- Find employees with salary above average
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Find employees in the Sales department
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
Subquery in FROM
sql
SELECT department_id, avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) dept_avg
WHERE avg_salary > 6000;
Data Modification
INSERT
sql
-- Insert single row with all columns
INSERT INTO employees VALUES (1001, 'John', 'Smith', 'jsmith@example.com', '2023-01-15', 5000,
-- Insert with specific columns
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (1002, 'Jane', 'Doe', 50);
-- Insert multiple rows
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (1003, 'Robert', 'Johnson', 60),
(1004, 'Maria', 'Garcia', 60);
UPDATE
sql
-- Update all rows in a table
UPDATE products SET price = price * 1.1; -- 10% price increase
-- Update with condition
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50;
-- Update multiple columns
UPDATE employees
SET salary = salary * 1.05,
commission_pct = commission_pct + 0.05
WHERE department_id = 80;
DELETE
sql
-- Delete specific rows
DELETE FROM orders WHERE order_date < '2020-01-01';
-- Delete all rows (keep table structure)
DELETE FROM temp_employees;
Table Operations
CREATE TABLE
sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE,
salary DECIMAL(10,2),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
ALTER TABLE
sql
-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
-- Modify a column
ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 5000;
ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(100); -- MySQL syntax
-- Drop a column
ALTER TABLE employees DROP COLUMN temp_column;
DROP TABLE
sql
DROP TABLE IF EXISTS temp_employees;
Advanced Queries
UNION and UNION ALL
sql
-- UNION (removes duplicates)
SELECT employee_id, first_name FROM current_employees
UNION
SELECT employee_id, first_name FROM former_employees;
-- UNION ALL (keeps duplicates)
SELECT product_id, product_name FROM products_2022
UNION ALL
SELECT product_id, product_name FROM products_2023;
CASE Expression
sql
SELECT employee_id, first_name, last_name,
CASE
WHEN salary < 5000 THEN 'Low'
WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
ELSE 'High'
END AS salary_category
FROM employees;
Common Table Expressions (CTE)
sql
WITH dept_salary AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.employee_id, e.last_name, e.salary, ds.avg_salary
FROM employees e
JOIN dept_salary ds ON e.department_id = ds.department_id
WHERE e.salary > ds.avg_salary;
Window Functions
sql
-- Ranking
SELECT employee_id, last_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
-- Running totals
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Indexes
sql
-- Create index
CREATE INDEX idx_last_name ON employees(last_name);
-- Create unique index
CREATE UNIQUE INDEX idx_email ON employees(email);
-- Drop index
DROP INDEX idx_last_name;
Transaction Control
sql
-- Begin transaction
BEGIN TRANSACTION; -- or START TRANSACTION; or BEGIN;
-- Commit changes
COMMIT;
-- Rollback changes
ROLLBACK;
-- Savepoint
SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;
