1.
SQL Basics & Architecture
SQL (Structured Query Language) is the standard language used to manage and manipulate
relational databases.
Relational Database Concepts
● Table: A collection of data organized into rows (records) and columns (fields).
● Primary Key: A unique identifier for each row in a table (cannot be NULL).
● Foreign Key: A field in one table that links to the Primary Key of another table, creating a
relationship.
SQL Sublanguages
● DDL (Data Definition Language): Defines database structure (CREATE, ALTER, DROP).
● DML (Data Manipulation Language): Manages data within objects (INSERT, UPDATE,
DELETE).
● DQL (Data Query Language): Retrieves data (SELECT).
● DCL (Data Control Language): Manages permissions (GRANT, REVOKE).
● TCL (Transaction Control Language): Manages transactions (COMMIT, ROLLBACK).
2. Core SQL Syntax & Queries
The Standard SELECT Template
This is the foundational structure of a SQL query. Execution order is different from writing order
(see notes below).
SQL
SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING AGGREGATE_FUNCTION(column3) > value
ORDER BY column1 ASC|DESC;
⚠️ Order of Execution: Computer reads SQL in this order:
FROM ➡️ WHERE ➡️ GROUP BY ➡️ HAVING ➡️ SELECT ➡️ ORDER BY ➡️ LIMIT
Basic Filtering Operators
● = , <> (or !=), <, >, <=, >=
● AND, OR, NOT
● IN (val1, val2): Checks if a value matches any value in a list.
● BETWEEN val1 AND val2: Filters within a range (inclusive).
● LIKE: Pattern matching (% matches any string, _ matches a single character).
○ Example: WHERE name LIKE 'A%' (Starts with A).
3. Joins (Combining Tables)
Joins are used to combine rows from two or more tables based on a related column between
them.
Join Type Description
INNER JOIN Returns records that have matching values
in both tables.
LEFT JOIN Returns all records from the left table, and
matched records from the right.
RIGHT JOIN Returns all records from the right table, and
matched records from the left.
FULL JOIN Returns all records when there is a match in
either left or right table.
Join Syntax Example
SQL
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
4. Aggregations & Grouping
Aggregation functions perform a calculation on a set of values and return a single value.
● COUNT(): Returns the number of rows.
● SUM(): Returns the total sum of a numeric column.
● AVG(): Returns the average value.
● MIN() / MAX(): Returns the lowest/highest value.
GROUP BY vs. HAVING
● Use GROUP BY to arrange identical data into groups (often used with aggregate
functions).
● Use HAVING to filter groups. WHERE cannot be used with aggregate functions;
HAVING can.
SQL
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
5. Intermediate & Advanced Concepts
Subqueries
A query nested inside another query.
SQL
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Window Functions
Unlike GROUP BY, window functions perform calculations across a set of table rows that are
still textually related to the current row (rows retain their separate identities).
SQL
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;
Common Table Expressions (CTEs)
A temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE
statement. Makes code much cleaner than nested subqueries.
SQL
WITH RegionalSales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
SELECT region
FROM RegionalSales
WHERE total_sales > 10000;
6. Constraints & DDL Cheat Sheet
Constraints are rules enforced on data columns to ensure data integrity.
● NOT NULL: Ensures a column cannot have a NULL value.
● UNIQUE: Ensures all values in a column are different.
● PRIMARY KEY: A combination of NOT NULL and UNIQUE.
● FOREIGN KEY: Prevents actions that would destroy links between tables.
● CHECK: Ensures the values in a column satisfy a specific condition (e.g., CHECK (Age >=
18)).
Quick DDL Commands
SQL
-- Create a new table
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
created_at DATE
);
-- Add a column
ALTER TABLE users ADD email VARCHAR(100);
-- Delete a table completely
DROP TABLE users;