The Ultimate SQL Cheatsheet

Whether you're building a simple app or managing a massive data warehouse, SQL (Structured Query Language) is an indispensable skill. This cheatsheet covers everything from basic queries to database normalization and performance optimization.

CRUD Queries

The foundational operations of any database: Create, Read, Update, and Delete.

-- CREATE (Insert Data)
INSERT INTO users (first_name, last_name, email)
VALUES ('Jane', 'Doe', 'jane@example.com');
 
-- READ (Select Data)
SELECT * FROM users;
SELECT first_name, email FROM users WHERE id = 1;
 
-- UPDATE (Modify Data)
UPDATE users
SET email = 'jane.doe@newdomain.com'
WHERE id = 1;
 
-- DELETE (Remove Data)
DELETE FROM users WHERE id = 1;

SQL Joins Cheat Sheet

Joins are used to combine rows from two or more tables based on a related column between them.

-- INNER JOIN: Returns records that have matching values in both tables
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
 
-- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
 
-- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
SELECT orders.amount, employees.name
FROM orders
RIGHT JOIN employees ON orders.employee_id = employees.id;
 
-- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
SELECT customers.name, orders.amount
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

Aggregate Functions

Functions that perform a calculation on a set of values and return a single value.

-- COUNT: Returns the number of rows
SELECT COUNT(*) FROM users;
 
-- SUM: Returns the total sum of a numeric column
SELECT SUM(amount) FROM orders WHERE status = 'completed';
 
-- AVG: Returns the average value of a numeric column
SELECT AVG(price) FROM products;
 
-- MIN & MAX: Returns the smallest and largest value
SELECT MIN(price), MAX(price) FROM products;
 
-- GROUP BY: Groups rows that have the same values into summary rows
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;
 
-- HAVING: Used instead of WHERE with aggregate functions
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status 
HAVING COUNT(*) > 10;

PostgreSQL Commands

PostgreSQL (psql) specific CLI commands for database administration.

# Connect to a database
psql -U username -d dbname
 
# List all databases
\l
 
# Connect to a specific database
\c dbname
 
# List all tables in the current database
\dt
 
# Describe a specific table
\d tablename
 
# Quit psql
\q

MySQL vs PostgreSQL

A quick comparison of the two most popular open-source relational databases.

| Feature | MySQL | PostgreSQL | | :--- | :--- | :--- | | Primary Use Case | Web applications, simple read-heavy apps | Complex analytical apps, data integrity | | JSON Support | Basic JSON data type | Advanced JSONB type with indexing | | Concurrency | Good (Table/Row locking) | Excellent (MVCC) | | Extensibility | Limited | Highly extensible (Custom types, PostGIS) | | Syntax Strictness | Forgiving | Very Strict (Standard SQL compliant) |

SQL Query Optimization

Tips for writing faster, more efficient queries.

1. Use EXPLAIN: Prepend EXPLAIN or EXPLAIN ANALYZE to your query to see the execution plan.

   EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

2. Select only what you need: Avoid SELECT *. Explicitly name the columns you need.
3. Use Indexes: Add indexes to columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.

   CREATE INDEX idx_users_email ON users(email);

4. Avoid leading wildcards in LIKE: LIKE '%name' prevents the database from using an index. Use LIKE 'name%' if possible.
5. Limit results: Use LIMIT when you only need a subset of the data.

Database Normalization

The process of structuring a relational database to reduce data redundancy and improve data integrity.

• 1st Normal Form (1NF):
  • Each table cell should contain a single value.
  • Each record needs to be unique.
• 2nd Normal Form (2NF):
  • Must be in 1NF.
  • Single Column Primary Key that does not functionally depend on any subset of candidate key relation.
• 3rd Normal Form (3NF):
  • Must be in 2NF.
  • Has no transitive functional dependencies (Every non-key attribute must depend directly on the primary key).