# SQL Cheatsheet {% embed url="" fullWidth="true" %} ## 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`