SQL Cheat Sheet: Your Essential

Guide
Welcome to your comprehensive SQL Cheat Sheet, designed to provide quick syntax
references and real-time examples for common database operations. Whether you're a
beginner looking to grasp the fundamentals or an experienced developer needing a
quick refresher, this guide covers essential SQL commands from basic queries to
advanced joins and data manipulation. Mastering SQL is crucial for anyone working
with relational databases, enabling efficient data retrieval, analysis, and management.
Basic SQL Queries & Data Filtering
Basic Queries Filtering Data (WHERE Clause)
SQL's foundation lies in its ability to retrieve data. The SELECT The WHERE clause is indispensable for narrowing down your results
statement is your primary tool for this, allowing you to specify which based on specific conditions. It allows you to filter rows before any
columns you want to see. grouping or aggregation occurs.

-- Select all columns -- Get employees with salary > 50000

SELECT * FROM employees; SELECT * FROM employees WHERE salary > 50000;

-- Select specific columns -- Use AND / OR

SELECT name, salary FROM employees; SELECT * FROM employees WHERE department = 'HR' AND salary >
30000;
-- Rename column using alias
SELECT name AS employee_name FROM employees; -- Use IN / NOT IN
SELECT * FROM employees WHERE department IN ('HR', 'Sales');
Aliases, created with AS, are useful for making your output more
readable, especially when dealing with complex queries or multiple -- Use BETWEEN
tables. SELECT * FROM employees WHERE salary BETWEEN 40000 AND
70000;

-- Use LIKE (for pattern matching)

SELECT * FROM employees WHERE name LIKE 'A%'; -- Names starting
with A

The LIKE operator is particularly powerful for pattern matching, using

wildcards like '%' (any sequence of characters) and '_' (any single
character).
Sorting, Limiting & Aggregating Data

Sorting (ORDER BY) Limiting Rows Aggregations

The ORDER BY clause is used to sort the The LIMIT clause is used to restrict the Aggregate functions perform calculations
result-set of a query in ascending (ASC) or number of rows returned by a query. This is on a set of rows and return a single
descending (DESC) order. By default, particularly useful for pagination or when summary value. Common functions include
results are sorted in ascending order. you only need to see a subset of the data. COUNT, SUM, AVG, MAX, and MIN.

-- Sort by salary ascending -- Get top 5 salaries SELECT COUNT(*) FROM employees; --
SELECT * FROM employees ORDER BY SELECT * FROM employees ORDER BY Count rows
salary ASC; salary DESC LIMIT 5; SELECT AVG(salary) FROM employees; --
Average salary
-- Sort by name descending Note that LIMIT syntax can vary across SELECT MAX(salary), MIN(salary) FROM
SELECT * FROM employees ORDER BY different SQL databases (e.g., TOP in SQL employees; -- Highest & lowest salary
name DESC; Server, ROWNUM in Oracle).
These functions are often used in
conjunction with the GROUP BY clause to
perform calculations on groups of rows.
Grouping Data & Filtering Groups
Grouping (GROUP BY) Filtering Groups (HAVING)

The GROUP BY clause groups rows that have the same values in While WHERE filters individual rows, HAVING filters groups created by
specified columns into summary rows, like "find the number of the GROUP BY clause. This is essential when you need to apply
employees in each department". It is often used with aggregate conditions to the results of aggregate functions.
functions.
-- Departments with total salary > 100000
-- Average salary per department SELECT department, SUM(salary) FROM employees GROUP BY
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;
department;
The HAVING clause is always placed after the GROUP BY clause and
-- Count employees per role before the ORDER BY clause.
SELECT role, COUNT(*) FROM employees GROUP BY role;

When using GROUP BY, any column in the SELECT list that is not an
aggregate function must also be in the GROUP BY clause.
Understanding SQL Joins

INNER JOIN LEFT JOIN

Returns rows when there is a match in both tables. It's the most Returns all rows from the left table, and the matching rows from the
common type of join and is used to combine rows from two or more right table. If there is no match, the columns from the right table will
tables based on a related column between them. have NULL values.

SELECT e.name, d.department_name SELECT e.name, d.department_name

FROM employees e FROM employees e
JOIN departments d ON e.dept_id = d.id; LEFT JOIN departments d ON e.dept_id = d.id;

RIGHT JOIN FULL OUTER JOIN

Returns all rows from the right table, and the matching rows from the Returns all rows when there is a match in one of the tables. It
left table. If there is no match, the columns from the left table will have combines the results of both LEFT and RIGHT joins. (Not shown in
NULL values. (Not shown in input, but a common counterpart). input, but a common counterpart).

Joins are fundamental for working with relational databases, allowing you to combine data from multiple tables to get a complete picture.
Advanced Queries: Subqueries
A subquery (or inner query) is a query nested inside another SQL query. It can be used in various clauses like SELECT, FROM, WHERE, and
HAVING. Subqueries are executed first, and their results are then used by the outer query.

Scalar Subquery Row Subquery Table Subquery

Returns a single value (one row, one Returns a single row with multiple columns. Returns a table (multiple rows, multiple
column). Often used in the WHERE or Can be used in the WHERE clause with columns). Can be used in the FROM clause,
HAVING clause to compare against a single operators like IN or = for multiple column effectively creating a temporary table that
value. comparisons. the outer query can then query.

-- Employees with above-average salary

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);

Subqueries are powerful for solving complex problems that require multiple steps of data retrieval or aggregation.
Data Manipulation: CRUD Operations
SQL is not just for querying data; it's also used to manage the database structure and manipulate data within tables. These are often referred to as
CRUD operations: Create, Read, Update, Delete.

1 2

Create Table Insert Record

The CREATE TABLE statement is used to define a new table in the The INSERT INTO statement is used to add new rows of data into a
database, specifying its name, columns, and their data types. table.

CREATE TABLE employees ( INSERT INTO employees (id, name, salary, department)
id INT PRIMARY KEY, VALUES (1, 'Alice', 60000, 'HR');
name VARCHAR(100),
salary INT,
department VARCHAR(50)
);

3 4

Update Record Delete Record

The UPDATE statement is used to modify existing records in a table. The DELETE FROM statement is used to remove existing records
The WHERE clause is crucial here to specify which records to from a table. Again, the WHERE clause is vital to prevent deleting all
update. records.

UPDATE employees SET salary = 70000 WHERE name = 'Alice'; DELETE FROM employees WHERE id = 1;

These commands are the backbone of database management, allowing you to maintain the integrity and relevance of your data.
Real-Time Use Case: Salary Dashboard & Next Steps
Salary Dashboard Insights
SQL queries are the foundation for building dynamic dashboards and reporting tools.
Here are examples of how SQL can provide valuable business insights:

-- Get top-paid employee in each department

SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department;

-- Count of employees earning more than average

SELECT COUNT(*) FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

These queries demonstrate how to extract specific, actionable data, which can then be
visualised in a dashboard to monitor key performance indicators (KPIs) and make
informed decisions.

Further Exploration

This cheat sheet provides a solid foundation. For those interested in practical application, consider building a working SQL dashboard using tools like
Streamlit and SQLite. This would allow you to interact with your data in real-time and visualise the results of your queries.

Would you like a working SQL dashboard using Streamlit and SQLite? I can generate that project next, or export this cheat sheet into PDF or
markdown. Let me know what format or extension you want!