Tech News, Tech & Coding · Free Online Tools

IT Question Answer
Tools
Back to Cheat Sheets
SQL Commands Cheat Sheet with Examples

SQL Commands Cheat Sheet with Examples

Cheat SheetsBy Muhammad Fareed
sqldatabasequeriescheat sheet

Short answer: SQL is the language used to read and change data in relational databases. This cheat sheet covers the statements you will use daily: SELECT to read, INSERT, UPDATE and DELETE to change, JOIN to combine tables, and GROUP BY to summarize.

Reading data with SELECT

SELECT name, email FROM users WHERE active = 1 ORDER BY name LIMIT 10;
  • SELECT * returns every column; list the columns you need instead when possible.
  • WHERE filters rows, ORDER BY sorts them and LIMIT restricts how many come back.
  • DISTINCT removes duplicate rows.

Filtering operators

OperatorUse
=, <>, >, <Compare values
AND, OR, NOTCombine conditions
IN (1, 2, 3)Match any listed value
BETWEEN 10 AND 20Match a range
LIKE 'A%'Match a pattern
IS NULLCheck for missing values

Use IS NULL, not = NULL, because NULL is never equal to anything.

Changing data

INSERT INTO users (name, email) VALUES ('Asha', 'asha@example.com');
UPDATE users SET active = 0 WHERE id = 5;
DELETE FROM users WHERE id = 5;

Always include a WHERE clause in UPDATE and DELETE. Without it, the statement affects every row.

Joining tables

  • INNER JOIN returns rows that match in both tables.
  • LEFT JOIN returns every row from the left table, with NULLs where there is no match.
  • RIGHT JOIN is the mirror image of LEFT JOIN.
  • FULL JOIN returns all rows from both sides, where the database supports it.
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

Grouping and summaries

SELECT country, COUNT(*) AS total
FROM customers
GROUP BY country
HAVING COUNT(*) > 10;

Common aggregate functions are COUNT, SUM, AVG, MIN and MAX. WHERE filters rows before grouping, and HAVING filters the groups after.

Tables and indexes

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);
CREATE INDEX idx_users_email ON users (email);

An index speeds up lookups on a column but slows writes slightly and uses space, so add indexes for columns you search or join on often.

Handy extras

  • AS renames a column or table in the output, for example COUNT(*) AS total.
  • CASE WHEN adds if-else logic inside a query.
  • UNION stacks the results of two queries, removing duplicates, while UNION ALL keeps them.
  • A subquery is a query inside another query, such as WHERE id IN (SELECT user_id FROM orders).

Common mistakes

  1. Forgetting WHERE on UPDATE or DELETE.
  2. Building queries by joining user input into strings, which allows SQL injection. Use parameterized queries.
  3. Using SELECT * in production code.
  4. Comparing with NULL using an equals sign.

Subqueries and common table expressions

-- Customers who spent more than the average order value
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders
  WHERE total > (SELECT AVG(total) FROM orders)
);

-- The same idea, easier to read with a CTE
WITH big_orders AS (
  SELECT customer_id
  FROM orders
  WHERE total > (SELECT AVG(total) FROM orders)
)
SELECT DISTINCT c.name
FROM customers c
JOIN big_orders b ON b.customer_id = c.id;

Window functions

Window functions calculate values across related rows without collapsing them into one row like GROUP BY does:

SELECT
  customer_id,
  order_date,
  total,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  RANK() OVER (ORDER BY total DESC) AS size_rank
FROM orders;

They are supported in current versions of PostgreSQL, MySQL, SQL Server, SQLite and Oracle, and they appear often in data analysis work and interviews.

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both

Group related changes in a transaction so they succeed or fail together. The exact keyword (BEGIN or START TRANSACTION) varies slightly by database. See transactions and ACID.

Order of execution

StepClause
1FROM and JOIN
2WHERE
3GROUP BY
4HAVING
5SELECT
6ORDER BY, then LIMIT

This logical order explains why you cannot use a column alias from SELECT inside WHERE in most databases. To keep queries safe in applications, always use parameters; see how to prevent SQL injection.

Working with NULL

SELECT name FROM customers WHERE phone IS NULL;       -- not "= NULL"
SELECT name, COALESCE(phone, 'not provided') AS phone FROM customers;
SELECT COUNT(*), COUNT(phone) FROM customers;         -- COUNT(column) skips NULLs

NULL means "unknown", not zero or an empty string. Any comparison with NULL using = or <> returns unknown, so the row is filtered out. Use IS NULL and IS NOT NULL, and COALESCE to supply a default value. Aggregates such as SUM and AVG ignore NULL values, which can surprise you when calculating averages.

Frequently asked questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, while HAVING filters groups after aggregation.

Which join should I use?

Use INNER JOIN when you only want matching rows, and LEFT JOIN when you want all rows from the main table even without a match.

Does SQL syntax differ between databases?

The basics are shared, but details such as LIMIT, date functions and string handling differ between MySQL, PostgreSQL, SQL Server and others.

How do I practise SQL?

Install SQLite or PostgreSQL locally and load a sample dataset, then answer real questions about it. Review SQL interview questions once you are comfortable.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows that match a condition and can be rolled back in a transaction. TRUNCATE quickly removes all rows but keeps the table. DROP removes the table itself, including its structure.