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.
```sql
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.
```sql
-- 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.
```sql
-- 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.
```sql
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.
```sql
-- 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
```sql
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.
```sql
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.
```sql
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
```
---