SQL Basics Cheat Sheet
Essential SQL commands and syntax for querying, modifying, and managing relational databases. Bookmark this page for quick reference.
| Syntax | Description |
|---|
SELECT col1, col2 FROM table | Retrieve specific columns from a table |
SELECT * FROM table | Retrieve all columns from a table |
SELECT DISTINCT col FROM table | Return only unique values in the result set |
SELECT col FROM table WHERE condition | Filter rows based on a condition |
WHERE col = 'value' | Match rows where column equals a specific value |
WHERE col LIKE 'A%' | Match rows where column starts with "A" (% = any characters) |
WHERE col LIKE '%word%' | Match rows containing "word" anywhere in the column |
WHERE col IN ('a', 'b', 'c') | Match rows where column value is in the given list |
WHERE col BETWEEN 10 AND 50 | Match rows where column value is within a range (inclusive) |
WHERE col IS NULL | Match rows where the column has no value |
WHERE col IS NOT NULL | Match rows where the column has a value |
ORDER BY col ASC | Sort results in ascending order (default) |
ORDER BY col DESC | Sort results in descending order |
LIMIT 10 | Return only the first 10 rows |
LIMIT 10 OFFSET 20 | Skip 20 rows, then return the next 10 |
| Syntax | Description |
|---|
SELECT * FROM a INNER JOIN b ON a.id = b.a_id | Return rows that have matching values in both tables |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id | Return all rows from the left table, with matches from the right (or NULL) |
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id | Return all rows from the right table, with matches from the left (or NULL) |
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id | Return all rows from both tables, with NULLs where there is no match |
SELECT * FROM a CROSS JOIN b | Return the Cartesian product of both tables (every combination) |
SELECT * FROM emp e1 JOIN emp e2 ON e1.mgr_id = e2.id | Self join — join a table to itself using aliases |
| Syntax | Description |
|---|
SELECT COUNT(*) FROM table | Count the total number of rows |
SELECT COUNT(col) FROM table | Count non-NULL values in a column |
SELECT COUNT(DISTINCT col) FROM table | Count the number of unique non-NULL values |
SELECT SUM(col) FROM table | Calculate the sum of a numeric column |
SELECT AVG(col) FROM table | Calculate the average of a numeric column |
SELECT MIN(col) FROM table | Return the smallest value in a column |
SELECT MAX(col) FROM table | Return the largest value in a column |
GROUP BY col | Group rows sharing a value so aggregate functions apply per group |
GROUP BY col HAVING COUNT(*) > 5 | Filter groups after aggregation (like WHERE but for groups) |
| Syntax | Description |
|---|
INSERT INTO table (col1, col2) VALUES ('a', 'b') | Insert a single row with specified values |
INSERT INTO table (col1, col2) VALUES ('a', 'b'), ('c', 'd') | Insert multiple rows in a single statement |
INSERT INTO table SELECT ... FROM other_table | Insert rows from the result of a SELECT query |
UPDATE table SET col = 'value' WHERE id = 1 | Update specific rows matching the WHERE condition |
UPDATE table SET col1 = 'a', col2 = 'b' WHERE id = 1 | Update multiple columns in a single statement |
DELETE FROM table WHERE id = 1 | Delete rows matching the WHERE condition |
MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... | Upsert — insert or update depending on whether the row exists |
| Syntax | Description |
|---|
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(100)) | Create a new table with columns and types |
CREATE TABLE new_t AS SELECT * FROM old_t | Create a new table from the results of a query |
ALTER TABLE t ADD col VARCHAR(50) | Add a new column to an existing table |
ALTER TABLE t DROP COLUMN col | Remove a column from an existing table |
ALTER TABLE t RENAME COLUMN old TO new | Rename a column |
DROP TABLE t | Permanently delete a table and all its data |
TRUNCATE TABLE t | Delete all rows from a table quickly (cannot be rolled back in some DBs) |
INT, BIGINT, SMALLINT | Integer data types (whole numbers of various sizes) |
DECIMAL(p, s), NUMERIC(p, s) | Exact numeric types with precision and scale |
VARCHAR(n), CHAR(n), TEXT | String data types (variable-length, fixed-length, unlimited) |
DATE, TIME, TIMESTAMP | Date and time data types |
BOOLEAN | True/false data type |
| Syntax | Description |
|---|
col INT PRIMARY KEY | Uniquely identifies each row; cannot be NULL |
FOREIGN KEY (col) REFERENCES other(id) | Enforce a link between data in two tables |
col VARCHAR(50) UNIQUE | Ensure all values in a column are different |
col INT NOT NULL | Prevent NULL values in a column |
col INT DEFAULT 0 | Set a default value when none is provided |
CHECK (col > 0) | Ensure column values satisfy a given condition |
CREATE INDEX idx_name ON table(col) | Create an index to speed up queries on a column |
CREATE UNIQUE INDEX idx ON table(col) | Create a unique index (also enforces uniqueness) |
DROP INDEX idx_name | Remove an index from the database |
| Syntax | Description |
|---|
WHERE col IN (SELECT col FROM other) | Filter using the result of another query |
WHERE col = (SELECT MAX(col) FROM table) | Scalar subquery — returns a single value for comparison |
SELECT * FROM (SELECT ... ) AS sub | Use a subquery as a derived table in the FROM clause |
WHERE EXISTS (SELECT 1 FROM other WHERE ...) | Return rows only if the subquery finds at least one match |
WHERE NOT EXISTS (SELECT 1 FROM other WHERE ...) | Return rows only if the subquery finds no matches |
WITH cte AS (SELECT ... ) SELECT * FROM cte | Common Table Expression — define a named temporary result set |
WITH RECURSIVE cte AS (base UNION ALL recursive) SELECT * FROM cte | Recursive CTE — useful for hierarchical or tree-structured data |
| Syntax | Description |
|---|
CONCAT(str1, str2) | Concatenate two or more strings together |
SUBSTRING(str, start, length) | Extract a portion of a string from a given position |
UPPER(str) | Convert a string to uppercase |
LOWER(str) | Convert a string to lowercase |
TRIM(str) | Remove leading and trailing whitespace |
LENGTH(str) | Return the number of characters in a string |
REPLACE(str, 'old', 'new') | Replace all occurrences of a substring within a string |
COALESCE(col, 'default') | Return the first non-NULL value from a list of arguments |
CAST(col AS VARCHAR) | Convert a value from one data type to another |
| Syntax | Description |
|---|
CURRENT_TIMESTAMP | Return the current date and time |
CURRENT_DATE | Return the current date (no time component) |
CURRENT_TIME | Return the current time (no date component) |
EXTRACT(YEAR FROM date_col) | Extract a part (year, month, day, hour, etc.) from a date |
date_col + INTERVAL '7' DAY | Add a duration to a date or timestamp |
date_col - INTERVAL '1' MONTH | Subtract a duration from a date or timestamp |
CAST(col AS DATE) | Convert a string or timestamp to a date type |