SQL Cheat Sheet: A Complete Guide for Developers

Introduction

SQL (Structured Query Language) is the standard language for relational database management systems. This cheat sheet provides a comprehensive guide to SQL commands, functions, and concepts.

Related: Check out our Git Commands Cheat Sheet for version control and Docker Cheat Sheet for containerization.

Basic Queries

The foundation of SQL is querying data.

Command	Description
SELECT col1, col2 FROM table	Selects specific columns from a table.
SELECT * FROM table	Selects all columns from a table.
SELECT DISTINCT col FROM table	Selects unique values from a column.
SELECT col AS alias FROM table	Renames a column in the result set.
SELECT ... LIMIT n	Limits the number of rows returned to n.

Filtering Data

Use WHERE to filter records.

Command	Description
SELECT ... WHERE col = 'value'	Filters rows where the column equals a value.
SELECT ... WHERE col <> 'value'	Filters rows where the column does not equal a value.
SELECT ... WHERE col > n	Filters for values greater than n.
SELECT ... WHERE col BETWEEN x AND y	Filters for values between x and y.
SELECT ... WHERE col LIKE 'pattern'	Filters for values matching a pattern (% for wildcard).
SELECT ... WHERE col IN ('a', 'b')	Filters for values in a list.
SELECT ... WHERE col IS NULL	Filters for NULL values.
SELECT ... WHERE cond1 AND cond2	Combines conditions with a logical AND.
SELECT ... WHERE cond1 OR cond2	Combines conditions with a logical OR.

Joining Tables

Combine rows from two or more tables.

Command	Description
... INNER JOIN table2 ON t1.col = t2.col	Returns records with matching values in both tables.
... LEFT JOIN table2 ON t1.col = t2.col	Returns all records from the left table, and matched from the right.
... RIGHT JOIN table2 ON t1.col = t2.col	Returns all records from the right table, and matched from the left.
... FULL OUTER JOIN table2 ON ...	Returns all records when there is a match in either left or right table.
... CROSS JOIN table2	Returns the Cartesian product of the two tables.

Aggregating Data

Summarize data using aggregate functions.

Command	Description
SELECT COUNT(col) FROM table	Counts the number of rows.
SELECT SUM(col) FROM table	Calculates the sum of values.
SELECT AVG(col) FROM table	Calculates the average of values.
SELECT MIN(col) FROM table	Finds the minimum value.
SELECT MAX(col) FROM table	Finds the maximum value.
SELECT ... GROUP BY col	Groups rows that have the same values into summary rows.
SELECT ... HAVING condition	Filters groups based on a condition (used with GROUP BY).

Data Manipulation

Modify data in the database.

Command	Description
INSERT INTO table (c1, c2) VALUES (v1, v2)	Inserts a new row into a table.
UPDATE table SET col = val WHERE cond	Updates existing rows in a table.
DELETE FROM table WHERE cond	Deletes rows from a table.

Data Definition

Define and manage the database schema.

Command	Description
CREATE DATABASE name	Creates a new database.
CREATE TABLE name (...)	Creates a new table.
ALTER TABLE name ADD col type	Adds a column to a table.
ALTER TABLE name DROP COLUMN col	Deletes a column from a table.
DROP TABLE name	Deletes a table.
CREATE INDEX name ON table (col)	Creates an index on a column.
DROP INDEX name	Deletes an index.

Advanced Commands

More complex SQL operations.

Command	Description
SELECT CASE WHEN ... THEN ... END	Implements conditional logic in a SELECT statement.
SELECT ... UNION ...	Combines the result sets of two or more SELECT statements.
WITH alias AS (SELECT ...) SELECT ...	Creates a Common Table Expression (CTE).
SELECT ... FROM (subquery) AS alias	Uses a subquery in a FROM clause.
GRANT ... ON ... TO ...	Gives a user permissions to a database object.
REVOKE ... ON ... FROM ...	Takes away user permissions.

Common Functions

Built-in SQL functions.

String Functions

Function	Description
CONCAT(str1, str2)	Concatenates two or more strings.
SUBSTRING(str, start, len)	Extracts a substring from a string.
UPPER(str) / LOWER(str)	Converts a string to uppercase or lowercase.
LENGTH(str)	Returns the length of a string.
TRIM(str)	Removes leading and trailing spaces.

Numeric Functions

Function	Description
ROUND(num, dec)	Rounds a number to a specified number of decimal places.
CEIL(num)	Rounds a number up to the nearest integer.
FLOOR(num)	Rounds a number down to the nearest integer.
ABS(num)	Returns the absolute value of a number.
MOD(num1, num2)	Returns the remainder of a division.

Date Functions

Function	Description
NOW()	Returns the current date and time.
DATE(expr)	Extracts the date part of a date or datetime expression.
DATEDIFF(date1, date2)	Returns the number of days between two dates.
DATE_ADD(date, INTERVAL val unit)	Adds a time/date interval to a date.

Conclusion

This SQL cheat sheet provides a solid foundation for database operations. For more advanced topics and database-specific syntax, refer to the official documentation for your database system.