🫐SQL Cheatsheet

DDL Commands

DML Commands

Data Type

Constraints

Constraint	Explanation	Rules	SQL Example
Primary Key	Uniquely identifies each record in a table. Only one primary key allowed.	- Column(s) must be unique - Cannot contain null values - Limited to one per table	CREATE TABLE table_name ( id INT PRIMARY KEY );
Foreign Key	Links to primary key in another table. Enforces referential integrity.	- Column(s) must match type of referenced column(s) - Values must exist in referenced table	CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) );
Unique	Ensures values in column are unique. Only distinct values allowed.	- Column(s) cannot contain duplicate values	CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) UNIQUE );
Not Null	Mandates column cannot contain NULL values.	- Column cannot contain null values	CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );
Check	Validates values against logical expression.	- Inserted/updated values must evaluate to true	CREATE TABLE users ( age INT CHECK (age > 0) );
Default	Sets default value if none provided.	- Default value is inserted if no other value specified	CREATE TABLE users ( country VARCHAR(50) DEFAULT 'United States' );
Index	Improves lookup performance on columns.	- Can be unique or non-unique - Single column or multiple columns	CREATE INDEX idx_lastname ON users(lastname);

Joins

Join Type	Explanation	Rules	Example
INNER JOIN	Returns records that have matching values in both tables	Matches rows from the first table with rows from the second table where the join condition is met	SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
LEFT JOIN	Returns all records from the left table, and the matched records from the right table	Returns all rows from the left table, along with any matching rows from the right table	SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
RIGHT JOIN	Returns all records from the right table, and the matched records from the left table	Returns all rows from the right table, along with any matching rows from the left table	SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
FULL OUTER JOIN	Returns all records when there is a match in either left or right table	Returns rows when there is a match in one of the tables	SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id
CROSS JOIN	Returns rows combined by cartesian product between two tables	No join condition needed, combines every row from the first table with every row from the second table	SELECT * FROM table1 CROSS JOIN table2

GROUP

Term	Explanation	Rules	Example
GROUP BY	Used to group rows that have the same values into summary rows	Used after the FROM and WHERE clauses. Must be used with aggregate functions like COUNT, MAX etc	SELECT department, COUNT(*) FROM employees GROUP BY department
GROUP BY Column	Groups rows based on values in the specified column	Column used in GROUP BY must be present in the SELECT statement	SELECT department, COUNT(*) FROM employees GROUP BY department
GROUP BY Multiple Columns	Groups rows based on multiple column values	List multiple columns in GROUP BY to group on combinations of values	SELECT department, city, COUNT(*) FROM employees GROUP BY department, city
HAVING	Filters grouped rows that meet a specified condition	Goes after GROUP BY. Can reference aggregated columns	SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10
ROLLUP	Provides subtotals at different grouping levels	Used after GROUP BY, provides extra super-aggregate rows	SELECT year, quarter, SUM(profit) FROM sales GROUP BY year, quarter WITH ROLLUP