Skip to content

SQL Cheatsheet — Query Quick Reference

DodaTech 2 min read

In this tutorial, you'll learn about SQL Cheatsheet. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

SQL queries for data retrieval, filtering, joining, aggregation, modification, and schema management — a compact reference for MySQL, PostgreSQL, and other relational databases.

SELECT & Filtering

SELECT col1, col2 FROM table WHERE condition ORDER BY col1 DESC LIMIT 10;
SELECT DISTINCT city FROM customers;
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM orders WHERE created_at > '2024-01-01';
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM items WHERE id IN (1, 3, 5);

JOINs

Join Type Returns
INNER JOIN Matching rows in both tables
LEFT JOIN All left + matching right (NULL if no match)
RIGHT JOIN All right + matching left
FULL JOIN All rows from both sides
CROSS JOIN Cartesian product 
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Aggregation & GROUP BY

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1
GROUP BY dept_id
HAVING COUNT(*) > 5
ORDER BY emp_count DESC;

Aggregate Functions

Function What it does
COUNT(*) Count rows
SUM(col) Sum values
AVG(col) Average value
MIN(col) Minimum value
MAX(col) Maximum value

Data Modification

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO logs (msg) VALUES ('start'), ('stop');  -- multi-row

UPDATE users SET email = 'new@example.com' WHERE id = 1;

DELETE FROM users WHERE id = 1;
TRUNCATE TABLE logs;          -- faster, no rollback

CREATE & ALTER Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
DROP TABLE users;

Indexes

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_unq_email ON users(email);
CREATE INDEX idx_name_email ON users(name, email);  -- composite
DROP INDEX idx_email;

String & Date Functions

-- Strings
UPPER(name); LOWER(name); LENGTH(name);
TRIM(name); SUBSTRING(name, 1, 3);
REPLACE(name, 'old', 'new'); CONCAT(first, ' ', last);

-- Dates
NOW(); CURDATE(); DATE(created_at);
YEAR(created_at); DATE_ADD(created_at, INTERVAL 7 DAY);
DATEDIFF(NOW(), created_at);
What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation (GROUP BY). HAVING filters groups after aggregation. Use WHERE for individual row conditions (e.g., WHERE status = 'active'), and HAVING for aggregate conditions (e.g., HAVING COUNT(*) > 5).

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right — non-matching right-side columns become NULL. Use LEFT JOIN when you need all records from the primary table regardless of matches.

What does an index do in SQL?

An index is a data structure (typically B-tree) that speeds up data retrieval on the indexed columns. It acts like a book's index — instead of scanning every row, the database jumps to the indexed location. Indexes speed up SELECT/WHERE/JOIN but slow down INSERT/UPDATE/DELETE

See the MySQL and PostgreSQL tutorials for database-specific details.

← Previous Docker Cheatsheet — Commands & Quick Reference Next → Linux Commands Cheatsheet — Essential Reference

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Cheatsheets