SQL Commands Cheatsheet
SQL Commands Cheatsheet
A quick-reference guide for standard Structured Query Language (SQL) statements, covering data querying (DQL), manipulation (DML), definition (DDL), relational joins, aggregations, transactions, and database indexing.
Data Query Language (DQL)
Retrieve and read data records from tables.
SELECT column1, column2 -- Specify columns to retrieve
FROM users -- Source table
WHERE age >= 18 -- Filter rows on criteria
ORDER BY last_name ASC, age DESC -- Sort rows (ASC is default)
LIMIT 10 OFFSET 20; -- Limit returned rows (pagination: skip first 20, show next 10)
SELECT DISTINCT country -- Retrieve unique, non-duplicate values only
FROM users;
Data Manipulation Language (DML)
Insert, modify, and delete data records.
-- 1. INSERT (Create)
INSERT INTO users (first_name, last_name, email, age)
VALUES ('Alice', 'Smith', 'alice@smith.com', 30);
-- Insert multiple rows in a single query
INSERT INTO users (first_name, last_name)
VALUES ('Bob', 'Jones'), ('Charlie', 'Brown');
-- 2. UPDATE (Modify)
UPDATE users
SET email = 'new_alice@smith.com', age = 31
WHERE id = 1; -- IMPORTANT: Always specify WHERE or all rows will be modified!
-- 3. DELETE (Remove)
DELETE FROM users
WHERE id = 5; -- IMPORTANT: Always specify WHERE or all rows will be deleted!
Data Definition Language (DDL)
Define, alter, and manage database schema structures.
-- 1. Create Table with standard constraints
CREATE TABLE employees (
id SERIAL PRIMARY KEY, -- Auto-incrementing unique identifier
first_name VARCHAR(50) NOT NULL, -- Field cannot be NULL
email VARCHAR(100) UNIQUE, -- Field must have unique values across table
salary DECIMAL(10, 2) CHECK (salary > 0), -- Validator check constraint
role VARCHAR(50) DEFAULT 'Staff', -- Fallback default value
department_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL -- Relational binding
);
-- 2. Alter Table (Modify columns)
ALTER TABLE employees ADD COLUMN phone_number VARCHAR(15);
ALTER TABLE employees DROP COLUMN phone_number;
ALTER TABLE employees ALTER COLUMN role SET NOT NULL;
-- 3. Drop Table (Delete completely)
DROP TABLE employees;
Filtering Operators
Used in WHERE clauses to refine criteria.
WHERE age BETWEEN 18 AND 30 -- Inclusive range (18 and 30 are included)
WHERE country IN ('US', 'CA') -- Match any value in a list
WHERE email LIKE '%@gmail.com' -- Pattern match: % is wildcard (starts with anything, ends with @gmail.com)
WHERE first_name ILIKE 'a%' -- Case-insensitive pattern match (PostgreSQL specific)
WHERE department_id IS NULL -- Match null values
WHERE phone IS NOT NULL -- Match non-null values
WHERE active = true AND (role = 'Admin' OR points > 100); -- Logical operators
Relational JOINs
Query and combine columns from multiple tables based on related foreign keys.
-- 1. INNER JOIN: Returns rows with matching keys in BOTH tables
SELECT employees.first_name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
-- 2. LEFT JOIN: Returns ALL rows from the left table, and matched rows from the right table
SELECT employees.first_name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id; -- Right columns are NULL if unmatched
-- 3. RIGHT JOIN: Returns ALL rows from the right table, and matched rows from the left table
SELECT employees.first_name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
-- 4. FULL OUTER JOIN: Returns all rows when there is a match in EITHER left or right table
SELECT employees.first_name, departments.name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;
Aggregations & Grouping
SELECT COUNT(*) -- Count all rows
SELECT SUM(salary) -- Sum of all values in column
SELECT AVG(salary) -- Mathematical average
SELECT MIN(age), MAX(age) -- Lowest and highest values
-- GROUP BY: Aggregate metrics categorized by groups
SELECT department_id, AVG(salary) as average_salary
FROM employees
GROUP BY department_id; -- Categorize aggregations by unique department_ids
-- HAVING: Filter groups on aggregated criteria (cannot use WHERE for aggregated calculations!)
SELECT department_id, SUM(salary) as total_budget
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000; -- Filters grouped rows after aggregation
Transactions
Ensure database integrity by locking multiple statements into atomic all-or-nothing blocks.
BEGIN TRANSACTION; -- Start transaction (or START TRANSACTION / BEGIN)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If both succeed, save changes permanently
COMMIT;
-- If an error occurs midway, discard all changes since BEGIN
-- ROLLBACK;
Database Indexes
Optimize search and fetch performance on large datasets.
CREATE INDEX idx_user_email -- Create index
ON users (email);
CREATE UNIQUE INDEX idx_uniq_uuid -- Create unique constraint index
ON users (uuid);
DROP INDEX idx_user_email; -- Delete index