SQL language cheat sheet: Quick Reference

Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you’re retrieving, inserting, updating, or deleting data, SQL allows you to perform operations efficiently and accurately. As one of the foundational skills for anyone working with data, mastering SQL is essential for database administrators, data analysts, and software developers alike. This cheat sheet serves as a quick reference to core SQL commands and syntax.

TL;DR – SQL Language Quick Reference

This guide provides a concise overview of essential SQL commands, including SELECT, INSERT, UPDATE, DELETE, and key clauses like WHERE, GROUP BY, and ORDER BY. If you’re already familiar with SQL, use this sheet as a refresher or rapid lookup. It includes commands for table creation, joins, aggregate functions, indexing, and more. Keep this reference nearby when writing or debugging SQL code.

1. Basic Syntax and Data Retrieval

To begin working with data in SQL, the SELECT statement is commonly used. This command allows users to query data from one or more tables.

SELECT column1, column2 FROM table_name;

Add WHERE to filter rows based on specified conditions:

SELECT * FROM employees WHERE salary > 50000;

To eliminate duplicate values in a result set, use DISTINCT:

SELECT DISTINCT department FROM employees;

2. Filtering and Sorting Data

To fine-tune your queries, SQL offers clauses that define how results are sorted and filtered.

• ORDER BY – Sorts the result-set in ascending or descending order.
• LIMIT – Restricts the number of rows returned (syntax may vary between databases).
• BETWEEN, IN, and LIKE – Provide advanced filtering options.

SELECT name, hire_date FROM employees 
WHERE hire_date BETWEEN '2022-01-01' AND '2023-01-01' 
ORDER BY hire_date DESC;

3. Inserting and Updating Data

Adding data to a table is done using the INSERT INTO statement:

INSERT INTO employees (name, department, salary)
VALUES ('Jane Doe', 'Engineering', 75000);

To update existing data:

UPDATE employees 
SET salary = 80000 
WHERE name = 'Jane Doe';

And to delete data:

DELETE FROM employees 
WHERE name = 'Jane Doe';

4. Aggregate Functions

Aggregate functions allow you to perform calculations on a set of values.

• COUNT() – Counts the number of rows
• AVG() – Calculates average value
• SUM() – Sums values
• MIN()/MAX() – Finds minimum or maximum

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

5. Table Creation and Schema Management

To build a new table, use the CREATE TABLE command:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  hire_date DATE,
  salary DECIMAL(10, 2)
);

You can modify an existing table with ALTER TABLE:

ALTER TABLE employees 
ADD COLUMN email VARCHAR(100);

Remove a column:

ALTER TABLE employees 
DROP COLUMN email;

And delete the table entirely:

DROP TABLE employees;

6. Relationships and Joins

Relational databases use foreign keys and relationships to connect data across multiple tables. SQL supports several types of joins:

• INNER JOIN – Returns matching rows from both tables
• LEFT JOIN – All rows from the left, and matching from the right table
• RIGHT JOIN – All rows from the right, and matching from the left table
• FULL OUTER JOIN – All rows from both tables

SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department = d.id;

7. Subqueries and Aliases

Subqueries are nested queries used within another SQL statement. They are useful for filtering, calculating, or comparing data.

SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Aliases provide temporary names to tables or columns, improving readability:

SELECT name AS employee_name, department AS dept
FROM employees;

8. Indexing and Performance

Indexes help speed up query performance by allowing the database to find data faster.

CREATE INDEX idx_salary 
ON employees(salary);

Delete an index:

DROP INDEX idx_salary;

Be cautious with indexes—while they speed up reads, they may slow down writes.

9. Transactions and Consistency

Transactions ensure that a group of operations is completed atomically. They are crucial for maintaining data integrity.

• BEGIN TRANSACTION
• COMMIT confirms the changes
• ROLLBACK undoes the transaction in case of error

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

10. Common SQL Data Types

Data types define the kind of data a column can hold. Usage can vary slightly between systems, but these are common types:

11. Useful SQL Functions

SQL includes a wide array of built-in functions beyond aggregates:

• NOW() – Current timestamp
• UPPER(), LOWER() – Convert text case
• LENGTH() – Checks string length
• ROUND() – Rounds numeric values

SELECT name, UPPER(department), ROUND(salary, 0) 
FROM employees;

12. Tips and Best Practices

To write effective and manageable SQL, follow these recommendations:

• Use aliases to improve query readability
• Comment your SQL using -- for single-line or /* */ for multi-line
• Normalize