The Ultimate SQL Cheat Sheet Collection

When it comes to SQL, having a handy reference can save you time and help you recall syntax quickly. Below is an ultimate SQL cheat sheet that covers key syntax and commands – from basic queries to advanced functions. Bookmark this section and refer to it whenever you need a quick refresher on SQL syntax during practice or interviews. (No need to memorize every detail – that’s what cheat sheets are for!)

SQL Command Categories

SQL commands can be divided into a few primary categories​geekflare.com​datacamp.com:

• DDL (Data Definition Language) – Commands that define or alter the structure of the database. Examples: CREATE, ALTER, DROP. These affect schemas (tables, indexes, etc.).
• DML (Data Manipulation Language) – Commands that manipulate data within tables. Examples: SELECT, INSERT, UPDATE, DELETE. (Some put SELECT in its own category DQL, but it’s fine to include it here.)
• TCL (Transaction Control Language) – Commands that control transactions. Examples: COMMIT (save changes), ROLLBACK (undo changes), SAVEPOINT (set a savepoint in a transaction).
• DCL (Data Control Language) – Commands related to permissions. Examples: GRANT, REVOKE privileges to users/roles.

Knowing these categories helps you understand the intent of a command. For instance, if an interviewer asks about DDL vs DML, you’d say DDL changes schema (like altering tables) whereas DML changes data (like updating rows).

Basic SQL Commands (CRUD)

• SELECT – Retrieve data from one or more tables. It’s the most used SQL command. Basic form: SELECT column1, column2 FROM table WHERE condition;. You can select literal values or expressions, use * for all columns (though in production, usually specify needed columns).
• INSERT – Add new rows to a table. Example: INSERT INTO Employees (name, age) VALUES ('Alice', 30); (inserts one row). You can insert multiple rows by providing multiple sets of values, or insert results of a SELECT from another table.
• UPDATE – Modify existing rows in a table. Example: UPDATE Employees SET age = 31 WHERE name = 'Alice'; (this increments Alice’s age). Without a WHERE clause, an UPDATE will affect all rows, so be careful!
• DELETE – Remove rows from a table. Example: DELETE FROM Employees WHERE age < 18;. Like UPDATE, use WHERE to target specific rows (otherwise, DELETE without a condition will delete all rows in the table).

(CRUD = Create, Read, Update, Delete — corresponding to INSERT, SELECT, UPDATE, DELETE.)

These commands are straightforward, but remember: SELECT is for querying, and it doesn’t modify data, whereas INSERT/UPDATE/DELETE do modify data.

Table Creation and Alteration (DDL)

• CREATE TABLE – Define a new table and its columns. For example:

CREATE TABLE Customers (
   customer_id INT PRIMARY KEY,
   name VARCHAR(100),
   signup_date DATE,
   status VARCHAR(20) DEFAULT 'active'
);


• This creates a Customers table with an integer primary key, a name, a date, and a status with a default value. You can also create other objects: CREATE DATABASE, CREATE INDEX, CREATE VIEW, etc., following similar syntax patterns (specify the object and properties).​almabetter.com​almabetter.com
• ALTER TABLE – Modify an existing table’s structure. Common uses: add a column, drop a column, change a column’s data type, or add constraints. E.g., ALTER TABLE Customers ADD email VARCHAR(255); (adds a new column)​almabetter.com. Or ALTER TABLE Customers ALTER COLUMN status VARCHAR(50); to change data type (syntax varies by SQL dialect). You can also use this to add/drop indexes in some databases (CREATE INDEX is more common for adding index).​codesignal.com​codesignal.com
• DROP TABLE – Remove a table and all its data/structure completely. E.g., DROP TABLE Customers;. Use with caution! There’s also DROP DATABASE to delete a database, DROP INDEX to remove an index, etc. Once dropped, data is gone (unless you have backups).​simplilearn.com
• TRUNCATE TABLE – Quickly delete all rows from a table, freeing space, but keeping the table structure intact. E.g., TRUNCATE TABLE Customers;. It’s DDL (or DML depending on the database, but usually non-transactional) and cannot have a WHERE clause. It’s faster than DELETE for large tables because it bypasses row-by-row deletion and logging.

Cheat sheet tip: Creating a table from a SELECT – Some SQL dialects allow CREATE TABLE new_table AS SELECT ... to make a new table with the result of a query. This is handy for making backup copies or sample tables.

Filtering and Sorting Data

• WHERE – Filter rows in SELECT, UPDATE, DELETE. Example: SELECT * FROM Orders WHERE amount > 100 AND status = 'OPEN';. The condition can use operators (=, >, <, <>, >=, <=), range (BETWEEN), list membership (IN), pattern matching (LIKE), and logical connectors (AND, OR, NOT). Remember operator precedence or use parentheses for clarity when mixing AND/OR.
• AND / OR – Combine conditions. AND requires both conditions to be true, OR requires at least one. E.g., WHERE status = 'OPEN' OR status = 'PENDING'. Use parentheses to group logic as needed.
• LIKE – Pattern match on strings. % matches any sequence of characters, _ matches a single character. E.g., WHERE name LIKE 'J%n' finds names starting with J and ending with n (J...n)​hellopm.co​hellopm.co. WHERE email LIKE '%@gmail.com' finds emails ending in @gmail.com. Case sensitivity depends on the collation or the SQL dialect (SQL Server is case-insensitive by default for text, MySQL might be case-sensitive depending on collation, etc., but ILIKE in Postgres does case-insensitive like).
• ORDER BY – Sort the result set. Syntax: ORDER BY column1 [ASC|DESC], column2 .... Default is ascending (ASC). E.g., SELECT * FROM Employees ORDER BY department, salary DESC; (sort by department ascending, and within each department, salary descending). You can order by expressions or positions (e.g., ORDER BY 2 meaning second selected column) but using column names is clearer​almabetter.com​simplilearn.com.
• LIMIT (MySQL, Postgres, SQLite) / TOP (SQL Server) / FETCH FIRST N ROWS (standard SQL) – Restrict the number of rows returned. E.g., MySQL: SELECT * FROM Customers ORDER BY signup_date DESC LIMIT 10; (latest 10 customers). In SQL Server: SELECT TOP 10 * FROM Customers ORDER BY signup_date DESC;. This is useful for pagination or just previewing data. (PostgreSQL also supports OFFSET m LIMIT n for skipping m rows).

Grouping and Aggregation

• GROUP BY – Group rows that have the same values in specified columns into summary rows. Typically used with aggregate functions (SUM, COUNT, AVG, MIN, MAX). Example: SELECT department, AVG(salary) FROM Employees GROUP BY department; – this produces one row per department with the average salary​simplilearn.com​simplilearn.com. Important: Every non-aggregated column in the SELECT must appear in the GROUP BY. In the example, department is grouped and we aggregate salary; we couldn’t also select an individual employee name without aggregating or grouping by it.
• HAVING – A filter for groups (applied after grouping). Similar to WHERE but for aggregated data. E.g., SELECT department, SUM(salary) AS total_pay FROM Employees GROUP BY department HAVING total_pay > 500000; – this will only include departments with total salary > 500k​simplilearn.com. You can also use aggregate expressions in HAVING directly: HAVING SUM(salary) > 500000. Note that HAVING is applied after the aggregation step, whereas WHERE is applied before grouping (on individual rows).
• Aggregate Functions – As mentioned earlier: COUNT, SUM, AVG, MIN, MAX are the core aggregates​simplilearn.com​simplilearn.com. A few notes:
  • COUNT(*) counts rows, COUNT(column) counts non-NULL values in that column.
  • COUNT(DISTINCT column) gives count of unique non-null values.
  • Aggregates ignore NULL values (except COUNT(*)), so AVG, SUM, etc., consider only non-nulls.
  • You can use aggregates without GROUP BY to get a single summary of the whole table (e.g., SELECT COUNT(*) FROM Orders; gives one number).

Example combining these: “Find the average and maximum order amount for each customer who has placed more than 5 orders.”

SELECT customer_id, AVG(amount) AS avg_amt, MAX(amount) AS max_amt, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) > 5;


This groups orders by customer, computes avg and max, and then uses HAVING to restrict to groups with more than 5 orders.

Joining Tables

• INNER JOIN – Return only matching rows between tables (the default type of JOIN). Syntax: FROM tableA [INNER] JOIN tableB ON tableA.col = tableB.col. Only rows where the ON condition is true for both tables are included​codesignal.com​codesignal.com. If no match, the row is omitted.
• LEFT JOIN (LEFT OUTER JOIN) – Return all rows from the left table, and matched rows from the right table. Unmatched right side appears as NULLs​forums.sqlteam.com. Use when you want to keep all records of the primary table regardless of whether there’s related data. (Right Join is analogous, keeping all from right table – you can usually swap table order and use left join instead of needing right join.)
• FULL JOIN (FULL OUTER JOIN) – Return all rows when there is a match in one of the tables. So it keeps non-matching rows from both, with NULLs on the side that’s missing. Not supported in some systems like MySQL directly (MySQL would need a UNION of left and right results to simulate full join).
• CROSS JOIN – Cartesian product (every row of A paired with every row of B). Rarely used unless specifically needed. (Equivalent to not using a JOIN…ON and just listing two tables in FROM without a WHERE to link them, in old SQL style).

Note on join syntax: Prefer the explicit JOIN ... ON ... syntax over the old style FROM A, B WHERE A.id = B.id because it’s clearer and it separates join conditions from filtering conditions.

Also, there’s NATURAL JOIN (joins on all same-named columns, not recommended because it’s implicit and can be ambiguous) and USING (col) syntax (a shorthand if joining on a column with the same name in both tables).

JOIN example: Suppose you have Customers(id, name) and Orders(id, customer_id, amount). An inner join to combine customers with their orders:

SELECT C.name, O.amount
FROM Customers C
JOIN Orders O
 ON C.id = O.customer_id;


This will list each order with the customer’s name. If a customer has no orders, they won’t appear (inner join drops them). If you did a LEFT JOIN instead, all customers would appear – those with no orders would have NULL for O.amount.

• Aliasing tables: As seen above with Customers C, it’s good practice to alias table names (especially when joining) to make query shorter and more readable. Then use the alias prefix on each column reference (C.name).

Subqueries and CTEs

• Subquery (Subselect) – A query nested inside another query. Often used in WHERE or HAVING clauses, or in the FROM clause as a derived table. Example: SELECT name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees); – finds employees with salary above the overall average​codesignal.com​codesignal.com. Subqueries can also appear in FROM: e.g., SELECT ... FROM (SELECT ... FROM ...) AS sub. When in FROM, it must be given an alias. Use cases: filtering based on aggregated values (as above), checking existence (WHERE EXISTS (subquery)), membership (IN (subquery)), or shaping data for joins (derived tables).
• EXISTS / NOT EXISTS – Checks for existence of rows in a subquery (typically correlated subquery). EXISTS(subquery) returns true if the subquery returns at least one row. For example: WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.customer_id = Customers.id) could filter customers that have at least one order. NOT EXISTS does the opposite (customers with no orders).
• Common Table Expression (CTE) – A CTE (introduced with WITH) is like a temporary named result set you can reference in the main query​codesignal.com​codesignal.com. It’s especially useful for readability with complex queries and for recursive queries. Syntax:

WITH SalesByDept AS (
   SELECT department_id, SUM(amount) AS total_sales
   FROM Sales
   GROUP BY department_id
)
SELECT d.name, s.total_sales
FROM SalesByDept s
JOIN Departments d ON d.id = s.department_id;


• Here, SalesByDept is a CTE that we can use in the subsequent SELECT. CTEs make queries more modular. Think of it as defining a “virtual table” for that query. Unlike a subquery in the FROM clause, a CTE can be referenced multiple times (and recursive CTEs can reference themselves). CTEs improve readability and are easier to debug step by step.

Useful SQL Functions and Constructs

• String functions: e.g., UPPER()/LOWER() to change case, SUBSTRING(str, start, length) to extract part of a string, CONCAT() (or || in Oracle/Postgres) to concatenate strings, TRIM() to remove whitespace, LENGTH() to get string length. Example: SELECT CONCAT(first_name, ' ', last_name) FROM Employees;.
• Date/Time functions: These vary by SQL dialect. Common ones: NOW() or CURRENT_TIMESTAMP for current date-time, CURDATE() (current date), DATEADD() or ADDDATE() to add days, DATEDIFF() to find difference between dates, EXTRACT() to get part of a date (like year or month). E.g., SELECT EXTRACT(YEAR FROM order_date) FROM Orders; gives the year of each order. Or in MySQL, YEAR(order_date). Know the basics of how to manipulate dates (this often comes up in analysis queries, like grouping by month or computing age from birthdate).
• CASE expression: SQL’s conditional logic in SELECT or other clauses. It’s like an if-then. Example:

SELECT
 order_id, amount,
 CASE
   WHEN amount >= 1000 THEN 'High Value'
   WHEN amount >= 500 THEN 'Medium Value'
   ELSE 'Low Value'
 END AS order_size
FROM Orders;


• This categorizes orders by amount. CASE is very useful for creating categorized or derived values on the fly. It can also be used in aggregations (like sum only certain condition: SUM(CASE WHEN status='OPEN' THEN 1 ELSE 0 END) to count open status).
• DISTINCT: Use with SELECT to get unique values. E.g., SELECT DISTINCT department_id FROM Employees; lists each department once. You can also do COUNT(DISTINCT col) in aggregations.
• COALESCE(expr1, expr2, ..., exprN): Returns the first non-NULL expression from the list. Commonly used to substitute NULL with a default. E.g., COALESCE(phone, 'N/A') would output 'N/A' if phone is NULL. Similar: IFNULL(expr, value) in MySQL, or the NVL(expr, value) in Oracle.
• IS NULL / IS NOT NULL: Check for NULL specifically (since = NULL doesn’t work as expected). E.g., WHERE email IS NULL finds missing emails.
• UNION / UNION ALL: (We discussed above) combine result sets. UNION eliminates duplicates, UNION ALL keeps all rows​simplilearn.com.
• JOIN using USING: If both tables have a column with the same name you’re joining on, some SQL flavors let you do JOIN tableB USING (columnName) which is a shorthand that automatically equates those columns. E.g., FROM Orders JOIN Customers USING (customer_id). This is equivalent to ... ON Orders.customer_id = Customers.customer_id. It’s handy, but only works when column names match exactly.

Transactions

• BEGIN (or START TRANSACTION): Begins a transaction block (in which your DML statements can be rolled back or committed together). In some SQL interfaces, every command is auto-committed by default unless you start a transaction.
• COMMIT: Finalize the transaction – make all changes permanent​codesignal.com​codesignal.com.
• ROLLBACK: Undo all changes made in the current transaction, reverting to the state at BEGIN.
• SAVEPOINT name: (Optional) within a transaction, set a savepoint that you can roll back to without rolling back the entire transaction.

Cheat tip: In most interview scenarios, you won’t write out transaction control unless specifically asked (like a question on transfers or multi-step operations). But it’s good to remember: if they ask “how do you ensure these two updates happen together or not at all?” the answer is “wrap them in a transaction with COMMIT at the end, or roll back on error.”

Indexes and Performance

• CREATE INDEX – Add an index to a table to improve lookup speed on a column (or set of columns). Example: CREATE INDEX idx_employee_name ON Employees(last_name);. This would help queries filtering or sorting by last_name.
• DROP INDEX – Remove an index when it’s not needed.

Indexes make reads faster but writes slightly slower (because the index must be updated). They also take storage space. Typically, you index primary keys (often automatic as PRIMARY KEY implies an index) and foreign keys, and any column that you frequently search on (e.g., a user email or login).

For cheat sheet brevity, remember: Use EXPLAIN to see how a query is executed (which indexes used, etc.) – not needed in the interview unless it’s a DBA role, but it’s a tip for performance tuning.

• USE INDEX hint: Some databases allow query hints to force or prefer a specific index​codesignal.com. For instance, MySQL SELECT * FROM Employees FORCE INDEX(idx_employee_name) WHERE last_name = 'Smith';. Use hints only if necessary; normally the optimizer picks the best index.

Miscellaneous Tips

• NULL handling: Understand that NULL in SQL means “unknown” or missing. It can trip up logic (e.g., in a WHERE, NULL = NULL is false because you don’t know if they’re equal; you must use IS NULL). Aggregates ignore NULLs. Functions like COALESCE help to replace NULLs with defaults.
• Set operations: We covered UNION. There’s also INTERSECT and EXCEPT in standard SQL (not in MySQL though) to get intersection or difference of two query results.
• Stored Procedures & Functions: In some cases, you might see syntax for creating stored routines. It’s beyond a basic cheat sheet, but note: a stored procedure performs actions (and is invoked with CALL), and a stored function returns a value and can be used in SQL (like SELECT myFunction(...)). They allow procedural logic (if-else, loops) on the server side.

This cheat sheet covers a lot of ground. During an interview, you’re not expected to recite syntax perfectly from memory – but being familiar with this material means you can quickly recall or recognize what you need. If you get stuck on syntax during an interview, you can describe what you want to do (using terminology like “I would left join the tables on this key, then group by X to get the count”) – this often is acceptable. However, the more fluent you are with the syntax, the more confident you’ll be. So use this cheat sheet to review and practice writing queries that use these elements.