Your Ultimate SQL Cheat Sheet: From Beginner Basics to Advanced Queries

Summary: The SQL Cheat Sheet provides a handy reference for mastering SQL commands. It covers database creation, querying data using SELECT and WHERE, joins, data manipulation with INSERT and UPDATE, and advanced operations like transactions and constraints. Perfect for developers and analysts to streamline database tasks efficiently.

Introduction

In today’s data-driven world, the ability to interact with databases is no longer a niche skill—it’s a fundamental requirement for developers, analysts, data scientists, and even marketers. At the heart of database interaction lies SQL (Structured Query Language), the standard language for managing and manipulating data stored in relational database management systems (RDBMS).

Whether you’re just starting your journey with databases or you’re a seasoned professional looking for a quick refresher, having a handy reference guide is invaluable. 

This SQL cheat sheet aims to be that guide, covering the essential commands and concepts you need to effectively work with databases. We’ll walk through everything from creating your first database to executing complex queries and managing data integrity.

Let’s dive in!

SQL, pronounced “sequel” or simply “S-Q-L,” is the lingua franca of relational databases. Developed in the early 1970s, it allows users to perform various operations like creating, reading, updating, and deleting (CRUD) data. 

Popular database systems like MySQL, PostgreSQL, SQL Server, Oracle, and SQLite all use SQL, although minor syntax variations (often called “flavours”) exist between them.

Key Takeaways

This cheat sheet is structured logically, following the typical lifecycle of data interaction:

• Setting up the database environment.
• Populating it with data.
• Querying and retrieving information.
• Modifying existing data.
• Removing data.
• Advanced techniques for complex tasks.

Keep this guide bookmarked – it’s designed to be your go-to reference for everyday SQL tasks.

Tabular Representation of SQL Cheat Sheet

Create a Database in SQL

Before you can store data, you need a container for it – a database. The command is straightforward:

• CREATE DATABASE: The SQL statement to create a new database.
• database_name: Replace this with the desired name for your database (e.g., company_db, blog_platform).

Note: Depending on your SQL client or environment, you might need specific permissions to create databases. Some systems may automatically switch you to use the newly created database, while others require an explicit command like USE database_name; (common in MySQL and SQL Server).

Creating Data in SQL

Once you have a database, you need structure (tables) and content (rows/records).

Creating Tables (CREATE TABLE)

Tables organize data into rows and columns. Each column has a name and a data type (e.g., INT for integers, VARCHAR(n) for variable-length strings, DATE for dates).

• CREATE TABLE: The statement to define a new table.
• table_name: The name of your table (e.g., employees, products).
• column1, column2…: Names for the columns in your table.
• datatype: Specifies the type of data the column holds (e.g., INT, VARCHAR(255), DECIMAL(10, 2), DATE, TIMESTAMP, BOOLEAN). Data types can vary slightly between RDBMS.
• constraint: Optional rules applied to columns (e.g., NOT NULL, UNIQUE). We’ll cover constraints in more detail later.
• PRIMARY KEY: A constraint that uniquely identifies each record in a table.

Example:

Inserting Data (INSERT INTO)

After creating a table, you can populate it with data using the INSERT INTO statement.

Syntax (inserting a single row)

• The column list is optional if you provide values for all columns in the table’s defined order.
• Values must match the data types of the corresponding columns.

Example:

Syntax (inserting multiple rows – varies slightly by RDBMS)

Example

Reading/Querying Data in SQL

The cornerstone of SQL is retrieving data. This is done using the SELECT statement.

Syntax:

• SELECT: Specifies the columns you want to retrieve.
• FROM: Specifies the table(s) from which to retrieve the data.

Retrieve all columns

Use the asterisk (*) as a wildcard.

Retrieve specific columns

Alt Text: Image showing the code to retrieve specific columns

Using Aliases (AS)

You can rename columns or tables in the output for clarity.

Updating/Manipulating Data in SQL

Often, you need to modify existing data in your tables. The UPDATE statement is used for this.

Syntax:

• UPDATE: The statement to modify existing records.
• SET: Specifies the columns to update and their new values.
• WHERE: Crucial! This clause filters which rows are updated. If you omit WHERE, all rows in the table will be updated. Always be cautious.

Example: Update Alice Smith’s salary.

Example: Give everyone hired before 2023 a 5% raise.

Deleting Data in SQL

Removing data can mean deleting specific rows, entire tables, or even whole databases.

Deleting Rows (DELETE FROM)

Removes specific records from a table based on a condition.

Syntax:

• DELETE FROM: The statement to remove rows.
• WHERE: Extremely Important! Specifies which rows to delete. If omitted, all rows in the table will be deleted.

Example: Delete the employee with employee_id 103

Deleting All Rows (TRUNCATE TABLE)

A faster way to remove all rows from a table, but use caution. It often resets auto-increment counters and might not trigger certain database rules (like triggers) compared to DELETE.

Deleting Tables (DROP TABLE)

Removes the entire table structure and all its data permanently.

Example:

Deleting Databases (DROP DATABASE)

Removes the entire database and all its contents (tables, views, etc.) permanently. Use with extreme caution!

Filtering Data in SQL (WHERE)

The WHERE clause is used with SELECT, UPDATE, and DELETE statements to filter records based on specified conditions.

Common Comparison Operators:

• = : Equal to
• != or <> : Not equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to

Example: Find employees with a salary greater than $70,000.

Logical Operators (AND, OR, NOT)

Combine multiple conditions.

• AND: Both conditions must be true.
• OR: At least one condition must be true.
• NOT: Reverses the truth value of a condition.

Example: Find employees named ‘Bob’ OR earning more than $60,000.

Example: Find employees NOT named ‘Alice’.

Other Useful Filtering Keywords

BETWEEN: Checks if a value is within a range (inclusive).

IN: Checks if a value matches any value in a list.

LIKE: Performs pattern matching using wildcards:

• %: Matches any sequence of zero or more characters.
• _: Matches any single character.

IS NULL / IS NOT NULL: Checks for null (missing) values.

SQL Operators

Beyond filtering, SQL uses operators for calculations and comparisons.

Arithmetic Operators: +, -, *, /, % (Modulo – remainder of division, syntax may vary)

• Comparison Operators: Covered in the WHERE clause section (=, !=, >, <, >=, <=).
• Logical Operators: Covered in the WHERE clause section (AND, OR, NOT).
• Set Operators: Combine results from two or more SELECT statements.
  • UNION: Combines results, removing duplicates. Columns must match in number and compatible data types.
  • UNION ALL: Combines results, including duplicates. Faster than UNION.
  • INTERSECT: Returns only rows that exist in both result sets (not supported by all RDBMS, e.g., MySQL).
  • EXCEPT (or MINUS in Oracle): Returns rows from the first result set that are not in the second (not supported by all RDBMS, e.g., MySQL).

Aggregating Data in SQL

Aggregate functions perform calculations on a set of rows and return a single summary value. They are often used with the GROUP BY clause.

Common Aggregate Functions:

• COUNT(): Counts the number of rows. COUNT(*) counts all rows; COUNT(column_name) counts non-NULL values in that column.
• SUM(): Calculates the sum of values in a numeric column.
• AVG(): Calculates the average of values in a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

Example: Count the total number of employees.

Example: Calculate the total and average salary.

Grouping Results (GROUP BY)

Groups rows with the same values in one or more columns, allowing aggregate functions to operate on each group.

Syntax:

Example: Count employees hired each year.

(Note: YEAR() function extracts the year from a date; exact function name might vary by RDBMS).

Filtering Groups (HAVING)

Filters groups after aggregation, based on the aggregate result. WHERE filters rows before aggregation.

Syntax:

Example: Find years where more than 1 employee was hired.

Constraints in SQL

Constraints enforce rules on data in tables, ensuring data accuracy and integrity. They are typically defined during table creation (CREATE TABLE) or added later (ALTER TABLE).

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column (or a set of columns) are distinct. NULLs are often treated specially (e.g., multiple NULLs might be allowed).
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table. A table can have only one primary key (which might consist of multiple columns).
• FOREIGN KEY: Establishes a link between two tables. It enforces referential integrity – values in the foreign key column(s) must match values in the referenced table’s primary key column(s), or be NULL.
• CHECK: Ensures that values in a column satisfy a specific condition (e.g., salary > 0).
• DEFAULT: Sets a default value for a column if no value is specified during INSERT.

Joins in SQL

Joins combine rows from two or more tables based on a related column between them (usually a foreign key relationship).

INNER JOIN

Returns only the rows where the join condition is met in both tables. This is the most common type.

(e and d are table aliases for brevity)

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table (the first table mentioned) and matching rows from the right table. If there’s no match in the right table, NULL values are returned for its columns.

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table (the second table mentioned) and matching rows from the left table. If there’s no match in the left table, NULL values are returned for its columns.

FULL OUTER JOIN

Returns all rows when there is a match in either the left or the right table. If there’s no match for a row from one side, NULLs are returned for columns from the other side. (Not supported by all RDBMS, e.g., MySQL).

CROSS JOIN

Returns the Cartesian product of the two tables – every row from the first table combined with every row from the second table. Use with caution, as it can produce very large result sets. Often used without an ON clause.

SQL Functions

SQL provides built-in functions to perform operations on data. They can be broadly categorized:

• String Functions: Manipulate text data. Examples:
  • CONCAT() / ||: Concatenate strings (syntax varies).
  • LENGTH() / LEN(): Get string length.
  • SUBSTRING() / SUBSTR(): Extract a portion of a string.
  • UPPER() / LOWER(): Convert case.
  • REPLACE(): Replace occurrences of a substring.
  • TRIM(): Remove leading/trailing whitespace.
• Numeric Functions: Perform mathematical operations. Examples:
  • ROUND(): Round a number.
  • CEILING() / FLOOR(): Round up/down to the nearest integer.
  • ABS(): Get the absolute value.
  • MOD() / %: Modulo operator.
• Date and Time Functions: Manipulate date/time values. Examples:
  • NOW() / CURRENT_TIMESTAMP: Get the current date and time.
  • CURRENT_DATE: Get the current date.
  • DATE() / YEAR() / MONTH() / DAY(): Extract parts of a date.
  • DATE_ADD() / DATE_SUB() (syntax varies): Add or subtract time intervals.
• Aggregate Functions: Already covered (COUNT, SUM, AVG, MIN, MAX).
• Conversion Functions: Convert data types (e.g., CAST(), CONVERT()).

Example:

Subqueries in SQL

A subquery (or inner query) is an SQL query nested inside another SQL query (the outer query). They can appear in SELECT, FROM, WHERE, or HAVING clauses.

For Example (in WHERE clause): Find employees whose salary is above the average salary.

Example (in SELECT clause – Scalar Subquery): Show each employee’s salary and the company average.

Example (in FROM clause – Derived Table)

Subqueries can be correlated (inner query depends on the outer query) or non-correlated (inner query runs independently). Non-correlated subqueries are generally more efficient.

Views in SQL

A view is a virtual table based on the result set of a stored SQL query. It doesn’t store data itself but provides a way to look at data from one or more underlying tables.

Benefits:

• Simplicity: Hide complex joins and calculations.
• Security: Restrict access to specific columns or rows.
• Consistency: Ensure users access data through a predefined structure.

Creating a View:

Example: Create a view showing employee names and their department names.

Querying a View: Treat it like a regular table.

Dropping a View:

Indexes in SQL

An index is a special lookup table that the database search engine can use to speed up data retrieval operations (SELECT queries). It’s like an index in the back of a book.

How it works: Indexes create pointers to data in tables based on the values in one or more columns. This allows the database to find rows matching WHERE clauses or join conditions more quickly, without scanning the entire table.

Creating an Index:

Example: Create an index on the last_name column of the employees table.

Trade-offs

• Faster Reads: Significantly improves SELECT query performance, especially on large tables.
• Slower Writes: INSERT, UPDATE, and DELETE operations become slightly slower because the index also needs to be updated.
• Storage Space: Indexes consume additional disk space.

Indexes are automatically created for PRIMARY KEY and UNIQUE constraints. You should create additional indexes strategically on columns frequently used in WHERE clauses or JOIN conditions.

Dropping an Index

Alt text: Image showing code for dropping an index

Transactions in SQL

A transaction is a sequence of SQL operations performed as a single logical unit of work. Transactions ensure data integrity using the ACID properties:

• Atomicity: All operations within a transaction succeed, or none of them do. If any part fails, the entire transaction is rolled back.
• Consistency: A transaction brings the database from one valid state to another. Constraints are enforced.
• Isolation: Concurrent transactions do not interfere with each other. Each transaction behaves as if it were the only one running.
• Durability: Once a transaction is successfully committed, its changes are permanent and survive system failures.

Key Commands:

• START TRANSACTION or BEGIN TRANSACTION: Marks the beginning of a transaction.
• COMMIT: Saves all changes made during the transaction permanently.
• ROLLBACK: Undoes all changes made since the START TRANSACTION.
• SAVEPOINT savepoint_name: Creates a point within a transaction to which you can later roll back using ROLLBACK TO SAVEPOINT savepoint_name.

Example: Transferring funds (requires both debit and credit to succeed)

(Error handling logic depends on the specific SQL environment/application code)

Advanced Mixed Data in SQL (CASE Statement)

The CASE statement provides conditional logic within SQL queries, similar to if-then-else statements in programming languages.

Syntax:

Example: Categorize employees based on salary.

Common Table Expressions (CTEs)

Another advanced feature, CTEs (WITH … AS), allow you to define temporary, named result sets within a single SQL statement. They improve readability and organization for complex queries, often replacing subqueries or views for specific tasks.

Conclusion

SQL is a powerful and essential language for anyone working with data. This cheat sheet provides a solid foundation and a quick reference for the most common commands and concepts. From creating databases and tables to performing complex queries with joins, aggregations, and conditional logic, you now have a map to navigate the SQL landscape.

Remember, proficiency comes with practice. Experiment with these commands, explore your specific database system’s documentation for flavour variations and additional features, and tackle real-world data problems. Happy querying!

Frequently Asked Questions

What Is an SQL Cheat Sheet?

An SQL Cheat Sheet is a concise reference guide listing common SQL commands, syntax, functions, and concepts. It helps users quickly look up how to perform specific database operations like querying, updating, or structuring data, serving as a handy reminder for both beginners and experienced professionals.

What are the Essential SQL Commands Covered in A Cheat Sheet?

Essential commands typically include SELECT (for querying), INSERT INTO (for adding data), UPDATE (for modifying data), and DELETE (for removing data). Core concepts like CREATE DATABASE/TABLE, WHERE clauses (filtering), JOINs (combining tables), aggregate functions (COUNT, SUM, AVG), and GROUP BY are also fundamental.

How Can an SQL Cheat Sheet Help Beginners?

For beginners, a cheat sheet demystifies SQL by providing clear syntax examples for basic operations. It acts as a quick lookup tool, reducing the need to memorize everything upfront. Seeing commands grouped logically (like CRUD operations or filtering) helps build a foundational understanding of SQL’s structure and capabilities.

Can I Create My Own SQL Cheat Sheet?

Absolutely! Creating your own cheat sheet is a great learning exercise. Tailor it to the specific SQL flavour you use most (e.g., MySQL, PostgreSQL) and include the commands, functions, and complex query patterns you encounter frequently in your work. Personalizing it makes it even more effective.