Ultimate SQL Guide
MySQL + PostgreSQL | Beginner to Expert | Practical and concise
This guide teaches the core SQL skills you need for analytics, backend work, and data engineering. It stays focused on
the concepts that matter most, with examples in standard SQL and notes where MySQL and PostgreSQL differ.
What you will master SELECT, joins, aggregation, window functions, CTEs, indexing, transactions, optimization, schema d
How to use it Read a section, run the examples, then do the practice task before moving on
Database focus MySQL and PostgreSQL side by side, with comments on what is common and what is different
Tip: write the queries yourself instead of only reading them. SQL improves fastest when you practice on a real sample database.
Page 1
1. SQL and databases
SQL is the language used to ask questions of relational databases. A table stores data in rows and columns. A
primary key identifies each row. A foreign key links one table to another.
A database usually contains many related tables. Good design avoids duplicate data and keeps records consistent.
Key points
- Important terms: DBMS, RDBMS, table, row, column, schema, primary key, foreign key, constraint.
- Common engines: MySQL and PostgreSQL.
Example
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(120) UNIQUE
);
Practice: Practice: describe a school database with students, classes, and enrollments.
2. Your first queries
SELECT reads data. FROM chooses the table. DISTINCT removes duplicates. AS creates an alias.
Start with simple selects and make sure you know how to read the result set.
Key points
- Use SELECT * only when exploring. In real work, list the columns you need.
- Aliases make queries easier to read, especially when joining tables.
Example
SELECT customer_id, full_name AS name
FROM customers;
Practice: Practice: select the top 10 products from a products table with readable column names.
3. Filtering rows
WHERE filters rows before grouping happens. Use AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL, and IS NOT
NULL to build conditions.
Remember that NULL means unknown, not zero and not empty.
Key points
- LIKE uses % for many characters and _ for a single character.
- Use parentheses when mixing AND and OR to avoid mistakes.
Example
SELECT *
FROM employees
WHERE department = 'Sales'
AND salary BETWEEN 3000 AND 6000;
Practice: Practice: find all customers from Zambia or Eritrea whose email is missing.
4. Sorting and limiting
ORDER BY sorts the result set. LIMIT returns only a fixed number of rows. OFFSET skips rows before returning
results.
Page 2
Sorting happens after filtering. That matters when you want the top records.
Key points
- MySQL and PostgreSQL both support LIMIT and OFFSET.
- For large pagination, OFFSET can become slow; later you will learn better approaches.
Example
SELECT product_name, price
FROM products
ORDER BY price DESC, product_name ASC
LIMIT 5;
Practice: Practice: show the cheapest 20 items and sort ties alphabetically.
5. Data types
Choose data types carefully. Use integers for counts, decimal for exact money, date/time types for timestamps, and
text types for names and descriptions.
MySQL and PostgreSQL use slightly different type names, but the ideas are the same.
Key points
- For money, DECIMAL is safer than FLOAT.
- PostgreSQL has strong support for JSONB, arrays, UUID, and advanced types.
Example
price DECIMAL(10,2)
created_at TIMESTAMP
is_active BOOLEAN
Practice: Practice: pick data types for a payments table and explain each choice.
6. INSERT, UPDATE, DELETE
INSERT adds rows. UPDATE changes existing rows. DELETE removes rows. TRUNCATE removes all rows quickly.
Use WHERE with UPDATE and DELETE unless you truly want every row changed.
Key points
- INSERT INTO ... VALUES (...) adds one row at a time.
- INSERT INTO ... SELECT ... copies data from another query.
Example
INSERT INTO customers (customer_id, full_name)
VALUES (1, 'Amina Tesfaye');
UPDATE customers
SET full_name = 'Amina T.'
WHERE customer_id = 1;
Practice: Practice: insert three sample rows, update one, and delete one safely.
7. Aggregation
Aggregate functions summarize many rows into one result: COUNT, SUM, AVG, MIN, and MAX.
Aggregation is one of the most important SQL skills for analytics and reporting.
Key points
Page 3
- COUNT(*) counts rows, even if some columns are NULL.
- COUNT(column_name) ignores NULL values in that column.
Example
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Practice: Practice: calculate total sales and average order value by month.
8. GROUP BY and HAVING
GROUP BY forms groups before aggregates are calculated. HAVING filters groups after aggregation.
Use WHERE for row filtering and HAVING for group filtering.
Key points
- A common mistake is trying to use WHERE with SUM or AVG directly.
- Grouping is the base of many business reports.
Example
SELECT department, COUNT(*) AS staff_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;
Practice: Practice: list products with more than 100 sales.
9. Joins
Joins combine rows from multiple tables. Learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS
JOIN, and SELF JOIN.
Most real SQL work depends on joins.
Key points
- INNER JOIN keeps matching rows only.
- LEFT JOIN keeps all rows from the left table and matches where possible.
- PostgreSQL supports FULL OUTER JOIN directly. MySQL 8 does not support FULL OUTER JOIN natively, so you usually
simulate it with UNION.
Example
SELECT o.order_id, c.full_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;
Practice: Practice: join orders, customers, and order_items to show each order with customer name and total quantity.
10. Subqueries
A subquery is a query inside another query. Use it for comparisons, filtering, and when a result must be computed
first.
Learn correlated and non-correlated subqueries.
Key points
- IN is good for multiple possible matches.
Page 4
- EXISTS is often better when you only care whether a related row exists.
Example
SELECT full_name
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Practice: Practice: find customers who have never placed an order.
11. Set operations
Set operations combine result sets with UNION, UNION ALL, INTERSECT, and EXCEPT.
Use UNION when you want to remove duplicates. Use UNION ALL when you want speed and are okay keeping
duplicates.
Key points
- PostgreSQL supports INTERSECT and EXCEPT directly.
- MySQL supports UNION and UNION ALL; INTERSECT and EXCEPT are not available in older MySQL versions.
Example
SELECT email FROM customers
UNION
SELECT email FROM suppliers;
Practice: Practice: combine email lists from two tables and remove duplicates.
12. Constraints
Constraints protect data quality. They tell the database what is allowed.
The core constraints are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.
Key points
- Constraints are more than decoration; they prevent bad data from entering the table.
- Use them whenever the rule should always be true.
Example
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Practice: Practice: add a check constraint for positive quantities.
13. Keys and relationships
A primary key uniquely identifies a row. A foreign key points to the primary key in another table.
You should know candidate keys, composite keys, natural keys, and surrogate keys.
Key points
- Composite keys are built from multiple columns.
- Surrogate keys are artificial IDs, often used for simplicity.
Page 5
Example
PRIMARY KEY (student_id, class_id)
Practice: Practice: decide which tables in a bank database should use surrogate IDs.
14. Database design
Good design starts from the business rules. Draw entities, attributes, and relationships before writing SQL.
A clean schema is easier to query, maintain, and scale.
Key points
- Ask what should be stored once and referenced many times.
- Separate repeating data into related tables.
Example
customers -> orders -> order_items -> products
Practice: Practice: design a library schema with books, authors, borrowers, and loans.
15. Normalization
Normalization reduces duplication and update problems. Learn 1NF, 2NF, 3NF, BCNF, and when to stop normalizing.
Do not over-normalize blindly; sometimes analytics and performance need controlled denormalization.
Key points
- 1NF: values should be atomic.
- 2NF: no partial dependency on part of a composite key.
- 3NF: no transitive dependency on a non-key column.
Example
Good normalization removes repeating groups and keeps one fact in one place.
Practice: Practice: normalize a poorly designed customer table with repeated addresses and product info.
16. Views and CTEs
A view is a saved query. A CTE starts with WITH and improves readability for complex logic.
Recursive CTEs are useful for hierarchies such as org charts and trees.
Key points
- Views simplify repeated reporting logic.
- CTEs help you break a big query into readable steps.
Example
WITH sales_by_day AS (
SELECT order_date, SUM(total_amount) AS revenue
FROM orders
GROUP BY order_date
)
SELECT * FROM sales_by_day;
Practice: Practice: create a CTE that finds monthly revenue.
17. String, numeric, and date functions
Page 6
Functions transform data. Common string functions include CONCAT, SUBSTRING, TRIM, LOWER, UPPER, and
LENGTH.
Numeric functions include ROUND, CEIL, FLOOR, ABS, and POWER. Date functions help you add, subtract, and
extract time parts.
Key points
- MySQL and PostgreSQL function names overlap a lot, but some details differ.
- Learn the date arithmetic syntax for both systems.
Example
SELECT UPPER(TRIM(full_name)), ROUND(price, 2)
FROM products;
Practice: Practice: clean a list of customer names and calculate invoice totals rounded to 2 decimals.
18. CASE, COALESCE, and NULL handling
CASE gives you if-then logic inside SQL. COALESCE returns the first non-NULL value. NULLIF returns NULL when
two values match.
These are essential for cleaning data and building business rules in queries.
Key points
- Use CASE for categories and labels.
- Use COALESCE when you need a fallback value.
Example
SELECT full_name,
CASE
WHEN salary >= 8000 THEN 'high'
WHEN salary >= 4000 THEN 'medium'
ELSE 'low'
END AS salary_band
FROM employees;
Practice: Practice: label orders as small, medium, or large by amount.
19. Window functions
Window functions calculate results across a set of related rows without collapsing them into one row.
Learn ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and moving sums.
Key points
- Window functions are one of the biggest steps from intermediate to advanced SQL.
- PARTITION BY splits the data into groups. ORDER BY defines the sequence inside each group.
Example
SELECT employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
Practice: Practice: find the top 3 salaries in each department.
20. Window frames and running totals
Page 7
A window frame controls which rows are included in each calculation. This matters for running totals and moving
averages.
Common use cases include cumulative revenue, rolling averages, and retention analysis.
Key points
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a common running-total frame.
- Learn the difference between ROWS and RANGE.
Example
SELECT order_date,
SUM(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders;
Practice: Practice: build a 7-day moving average from a sales table.
21. Indexes
Indexes speed up searches, joins, ordering, and filtering. The most common index structure is a B-tree.
Use indexes carefully: they can make reads faster but writes slower.
Key points
- Index columns that appear often in WHERE, JOIN, and ORDER BY clauses.
- Composite indexes should match your most common query pattern.
- PostgreSQL supports partial indexes and expression indexes very well.
Example
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
Practice: Practice: choose useful indexes for orders, payments, and customers.
22. Execution plans and optimization
EXPLAIN shows how the database will run a query. EXPLAIN ANALYZE shows what actually happened during
execution.
Learn to spot full table scans, bad join order, missing indexes, and expensive sorts.
Key points
- Optimization usually starts by reducing rows early and using good indexes.
- Sometimes the best fix is query redesign, not another index.
Example
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;
Practice: Practice: compare the plan before and after adding an index.
23. Transactions and ACID
Transactions keep related changes together. Either all changes succeed, or none do.
Page 8
ACID means Atomicity, Consistency, Isolation, and Durability.
Key points
- Use transactions for money movement, inventory changes, and multi-step updates.
- Always commit when the work is valid and rollback when something goes wrong.
Example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
Practice: Practice: write a transaction for transferring money between two accounts.
24. Locks, isolation, and concurrency
When many users work at once, the database must prevent conflicting changes. That is concurrency control.
Learn locks, deadlocks, and isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and
Serializable.
Key points
- PostgreSQL uses MVCC heavily, which gives strong concurrency behavior.
- MySQL InnoDB also supports transactions and row-level locking.
Example
Isolation level choices affect consistency, speed, and the chance of reading uncommitted data.
Practice: Practice: explain why two users updating the same row at the same time can cause problems.
25. Stored procedures and triggers
Stored procedures package SQL logic on the server. Triggers run automatically when events happen, such as
INSERT, UPDATE, or DELETE.
Use them for repeated business rules, validation, logging, and auditing.
Key points
- Keep logic simple and readable. Do not hide too much important business logic in triggers.
- Stored procedures differ more between MySQL and PostgreSQL than basic SQL does.
Example
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
SET NEW.updated_at = NOW();
Practice: Practice: design a trigger that logs every change to an audit table.
26. Security
Security means controlling who can read and change data. Learn users, roles, privileges, GRANT, REVOKE, and
password management.
Also learn how SQL injection happens and how parameterized queries stop it.
Key points
- Least privilege is the safest default.
Page 9
- Never build SQL by concatenating raw user input.
Example
GRANT SELECT, INSERT ON customers TO analyst_role;
Practice: Practice: list the minimum permissions a reporting user should have.
27. MySQL and PostgreSQL differences
Much of SQL is shared, but the two systems differ in syntax, features, and advanced behavior.
You should know the differences well enough to adapt queries quickly.
Key points
- MySQL: common in web apps, simple deployment, strong InnoDB engine, LIMIT and OFFSET syntax, procedural extensions
are practical.
- PostgreSQL: very feature-rich, strong standards support, excellent window functions, CTEs, JSONB, arrays, partial indexes,
and MVCC behavior.
Example
Examples of differences:
- FULL OUTER JOIN: supported in PostgreSQL, not native in older MySQL versions
- BOOLEAN and JSON handling differ slightly
- UPSERT syntax differs: INSERT ... ON CONFLICT in PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL
Practice: Practice: rewrite one query so it works in both systems.
28. Data warehousing and analytics
OLTP systems handle daily operations. OLAP systems handle analysis and reporting. Warehouses store data for fast
analytics.
Learn star schema, snowflake schema, fact tables, and dimension tables.
Key points
- Facts store measurable events such as sales or clicks.
- Dimensions store descriptive context such as customer, product, or date.
Example
fact_sales(order_date_key, product_key, customer_key, quantity, revenue)
Practice: Practice: design a star schema for an online store.
29. Advanced SQL patterns
Advanced patterns include gaps and islands, top-N per group, deduplication, cohort analysis, retention queries, and
recursive hierarchies.
These appear often in interviews and real analytical work.
Key points
- Window functions and CTEs solve many of these problems cleanly.
- Practice pattern recognition, not just syntax memorization.
Example
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
Page 10
SELECT * FROM ranked WHERE rn = 1;
Practice: Practice: find each customer's most recent order.
30. Projects to build
Use projects to lock in your skills. Build real schemas, write queries, and test edge cases.
Good projects include a library system, e-commerce store, school system, banking app, and sales dashboard.
Key points
- Each project should include tables, constraints, joins, reports, and at least one advanced feature such as window functions or
transactions.
- Document your design decisions as you go.
Example
Project checklist:
1. Schema design
2. Sample data
3. Core queries
4. Reporting queries
5. Indexes
6. Transactions
7. Security
Practice: Practice: build one complete project and write 20 useful queries for it.
31. Fast revision cheat sheet
Core order of a query: SELECT -> FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT.
Row filtering happens before grouping. Group filtering happens in HAVING. Window functions keep rows visible.
Key points
- Always check data types, NULL behavior, and join keys.
- Use EXPLAIN when a query feels slow.
Example
SELECT columns
FROM table
JOIN other_table ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;
Practice: Practice: say the order from memory until it feels automatic.
Page 11
How to study this guide
- Read one section.
- Type the example yourself.
- Change the table and column names to fit a new topic.
- Write at least three queries without looking.
- Use real data when possible.
- Move to the next section only after the current one feels easy.
Suggested order of mastery
1) SELECT and filtering 2) joins and aggregation 3) grouping and subqueries 4) constraints and design 5) CTEs and
window functions 6) indexes and plans 7) transactions and concurrency 8) security and advanced patterns
Final rule
SQL mastery comes from repetition. Read less, query more.
Page 12