Reading about SQL teaches you syntax. Building a real project teaches you SQL. A portfolio of two or three well documented databases proves to an employer that you can model data, write queries that answer real questions, and explain your work, which is exactly what a certificate cannot show.
This guide gives you eleven concrete project ideas grouped by difficulty, from a simple library catalog to a full analytics dashboard on a public dataset. For each one you get what it teaches, a starter schema, and one or two example queries the project should be able to answer. Pick one project per level, build it end to end, and you will have a portfolio that stands out. If you are just starting, work through the complete SQL tutorial for beginners first, then come back and build.
What this guide covers
1. Why portfolio projects beat certificates
Hiring managers see hundreds of resumes that list "SQL" as a skill. What they cannot easily verify is depth. A project changes that. When you hand over a link to a repository with a schema diagram, sample data, and a set of documented queries, you move the conversation from "do you know SQL" to "tell me why you modeled it this way." That is a far stronger position.
Good projects also double as interview fuel. Every design choice you make (a foreign key here, a composite index there, a window function to rank rows) becomes a story you can tell. The projects below are ordered so that each level adds one meaningful new skill. Build them in order and you will naturally climb from basic SELECT statements to analytics that answer business questions.
Tip: Depth beats breadth. Three projects you can discuss in detail impress far more than ten half finished ones. Finish each project, write the README, and move on.
2. Beginner projects
These four projects use three to five tables and focus on the fundamentals: creating tables, defining keys, inserting sample data, and writing joins and aggregates. If a concept feels shaky, the SQL beginner course covers everything you need here.
2.1 Library management system
What it teaches: primary and foreign keys, one to many relationships, and a many to many join table (a book can have many borrowers over time). This is the classic first project because the domain is familiar and the modeling is clean.
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT REFERENCES authors(id),
copies INT DEFAULT 1
);
CREATE TABLE loans (
id INT PRIMARY KEY,
book_id INT REFERENCES books(id),
member_id INT REFERENCES members(id),
loaned_on DATE,
due_on DATE,
returned_on DATE
);Queries it should answer: which books are currently overdue, and who are the most active borrowers?
-- Overdue books (not yet returned, past due date)
SELECT b.title, m.name, l.due_on
FROM loans l
JOIN books b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned_on IS NULL
AND l.due_on < CURRENT_DATE;2.2 Personal finance tracker
What it teaches: date handling, categories, and aggregation with GROUP BY. You model accounts, transactions, and categories, then summarize spending over time.
accounts(id, name, opening_balance)categories(id, name, kind: income or expense)transactions(id, account_id, category_id, amount, txn_date, note)
-- Monthly spend by category for this year
SELECT c.name,
SUM(t.amount) AS total
FROM transactions t
JOIN categories c ON c.id = t.category_id
WHERE c.kind = 'expense'
AND t.txn_date >= '2026-01-01'
GROUP BY c.name
ORDER BY total DESC;2.3 Movie and ratings database
What it teaches: many to many relationships (movies to genres, movies to actors) and averaging with AVG and HAVING. The IMDb style domain is intuitive and the queries are fun.
-- Top rated movies with at least 50 ratings
SELECT m.title,
ROUND(AVG(r.score), 2) AS avg_score,
COUNT(*) AS votes
FROM movies m
JOIN ratings r ON r.movie_id = m.id
GROUP BY m.title
HAVING COUNT(*) >= 50
ORDER BY avg_score DESC
LIMIT 10;A good follow up query: for each genre, what is the average rating? That forces a three table join through the genre link table.
2.4 Inventory management
What it teaches: stock levels, reorder logic, and CASE expressions. You track products, warehouses, and stock movements, then flag items that need reordering.
-- Products at or below their reorder point
SELECT p.sku, p.name, s.on_hand, p.reorder_at,
CASE WHEN s.on_hand = 0 THEN 'out of stock'
ELSE 'reorder' END AS status
FROM products p
JOIN stock s ON s.product_id = p.id
WHERE s.on_hand <= p.reorder_at
ORDER BY s.on_hand ASC;3. Intermediate projects
These projects add more tables, more realistic data volumes, and analysis rather than plain lookups. They pair well with the SQL intermediate course, and each one is a strong resume centerpiece.
3.1 E-commerce sales analysis
What it teaches: multi table joins across customers, orders, order items, and products, plus revenue and cohort style analysis. This is the single most requested skill for analyst roles because it mirrors real business reporting.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
ordered_at TIMESTAMP,
status VARCHAR(20)
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
qty INT,
unit_price NUMERIC(10,2)
);-- Revenue by month, paid orders only
SELECT DATE_TRUNC('month', o.ordered_at) AS month,
SUM(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY DATE_TRUNC('month', o.ordered_at)
ORDER BY month;Stretch goal: compute each customer's lifetime value and rank the top 10 percent. That naturally leads into the window function project below.
3.2 Employee and HR database
What it teaches: self joins (an employee reports to a manager who is also an employee), hierarchy walking, and salary analysis. It is a favorite in interviews, so building it doubles as prep for the SQL interview guide.
-- Each employee with their manager's name
SELECT e.name AS employee,
m.name AS manager,
e.department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.department;-- Average salary per department, highest first
SELECT department,
ROUND(AVG(salary)) AS avg_salary,
COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;3.3 Social media schema
What it teaches: a self referencing follow relationship, feed generation, and counting engagement. The tricky part is the follows table, which links users to other users, so both columns reference the same table.
CREATE TABLE follows (
follower_id INT REFERENCES users(id),
followee_id INT REFERENCES users(id),
PRIMARY KEY (follower_id, followee_id)
);
-- A user's feed: posts from people they follow
SELECT p.id, p.body, p.created_at
FROM posts p
JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 42
ORDER BY p.created_at DESC
LIMIT 20;Good design here rewards study of how to design a relational database, since the follow graph and the like counts are easy to get subtly wrong.
3.4 Data cleaning project
What it teaches: the unglamorous but highly valued skill of turning messy input into a clean table. You load a raw file full of duplicates, inconsistent casing, and null placeholders, then write the SQL that fixes it.
-- Standardize, then remove duplicate contacts keeping the first
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY id) AS rn
FROM raw_contacts
)
DELETE FROM raw_contacts
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);Watch out: always run your cleaning logic as a SELECT first and inspect the rows it would touch. Only switch it to a DELETE or UPDATE once you are sure, and keep a backup of the raw table.
4. Advanced projects
Advanced projects use window functions, larger public datasets, and produce output that looks like real reporting. These are the ones that get you interviews for analyst and data engineering roles.
4.1 Sales dashboard with window functions
What it teaches: ROW_NUMBER, RANK, running totals, and month over month growth. You take the e-commerce data from project 3.1 and turn it into the metrics a dashboard would show.
-- Running revenue total and month over month change
WITH monthly AS (
SELECT DATE_TRUNC('month', ordered_at) AS m,
SUM(total) AS revenue
FROM orders GROUP BY 1
)
SELECT m, revenue,
SUM(revenue) OVER (ORDER BY m) AS running_total,
revenue - LAG(revenue) OVER (ORDER BY m) AS mom_change
FROM monthly
ORDER BY m;-- Top 3 products by revenue within each category
WITH ranked AS (
SELECT category, product, revenue,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY revenue DESC) AS rn
FROM product_sales
)
SELECT category, product, revenue
FROM ranked WHERE rn <= 3;4.2 Public health dataset analysis
What it teaches: loading and querying a real world open dataset, handling nulls and gaps in reported figures, and computing rolling averages. A COVID case dataset is a common choice because the data is public, well known, and rich enough for interesting questions.
-- 7 day rolling average of new cases per country
SELECT country, report_date, new_cases,
ROUND(AVG(new_cases) OVER (
PARTITION BY country
ORDER BY report_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)) AS avg_7d
FROM covid_daily
ORDER BY country, report_date;The learning here is as much about data quality as SQL: missing days, revised figures, and duplicate rows all show up in real data and force you to write defensive queries.
4.3 Analytics project on a Kaggle dataset
What it teaches: the full workflow, from importing a raw CSV to answering open ended business questions, and framing findings as insights. This is the capstone. Pick a dataset that interests you, load it, and treat yourself as the analyst asked to explain what is happening.
Download a dataset from Kaggle datasets, import it into your database, and write a short set of questions before you touch any SQL. For example, on a retail dataset: which regions are growing, which products are declining, and what is the repeat purchase rate? Answer each with a documented query and a one sentence takeaway.
-- Repeat purchase rate: share of customers with 2+ orders
SELECT
ROUND(AVG(CASE WHEN orders >= 2 THEN 1.0 ELSE 0 END), 3) AS repeat_rate
FROM (
SELECT customer_id, COUNT(*) AS orders
FROM sales GROUP BY customer_id
) t;5. Project and skills matrix
Use this table to pick projects that fill gaps in your skill set. Aim for one from each level so your portfolio shows range.
| Project | Level | Skills practiced |
|---|---|---|
| Library management | Beginner | Keys, joins, many to many, date filters |
| Personal finance tracker | Beginner | GROUP BY, dates, aggregation |
| Movie and ratings | Beginner | AVG, HAVING, multi table joins |
| Inventory management | Beginner | CASE, filtering, stock logic |
| E-commerce sales analysis | Intermediate | Revenue rollups, cohort analysis |
| Employee / HR database | Intermediate | Self joins, hierarchy, salary stats |
| Social media schema | Intermediate | Self referencing keys, feeds |
| Data cleaning project | Intermediate | Dedup, TRIM, LOWER, UPDATE |
| Sales dashboard | Advanced | Window functions, running totals, LAG |
| Public health analysis | Advanced | Rolling averages, null handling |
| Kaggle analytics capstone | Advanced | CSV import, end to end analysis |
6. Skills employers look for
When we surveyed job postings and what hiring managers actually test, a clear pattern emerged. Joins and aggregation are table stakes, but the projects that stand out demonstrate window functions and clean data modeling. The chart below shows how often each skill is named as important for SQL and analyst roles.
The takeaway: make sure at least one advanced project shows window functions, and that every project has a thoughtful schema. Those two signals map directly onto the two hardest to fake skills in the list.
7. How to present a project
A finished database on your laptop helps no one. The presentation is what turns a project into a portfolio piece. Host the code on GitHub and follow the steps below so a reviewer can understand it in two minutes.
Write a clear README
Open with one paragraph on what the project does and why. Include a schema diagram (even a simple text list of tables), the questions the project answers, and instructions to run it. This is the first thing anyone reads.
Include the schema and sample data
Commit a schema.sql that creates every table and a seed.sql with realistic sample rows. A reviewer should be able to clone, run two files, and have a working database.
Document each query
Keep a queries.sql where every query has a comment stating the business question it answers and, where useful, a note on the result. Documented SQL reads like a colleague's work, not a puzzle.
State your findings
End the README with three or four bullet insights ("revenue is concentrated in the top 5 percent of customers"). Numbers and conclusions show you can turn queries into meaning.
Strong move: pick one database engine and say so. If you build on Postgres, link the relevant PostgreSQL documentation in your README for any advanced feature you used. It shows you can read primary sources.
8. Where to find datasets
You do not have to invent data. For beginner projects, generating a few dozen rows by hand is fine and lets you predict the answers. For intermediate and advanced work, real datasets make the analysis meaningful. The table below lists reliable sources.
| Source | Best for | Notes |
|---|---|---|
| Kaggle datasets | Analytics and capstone projects | Thousands of clean CSVs across every domain |
| Government open data portals | Public health, economics | Authoritative, sometimes messy, great for cleaning practice |
| Sample databases (Sakila, Chinook) | Practicing joins and aggregation | Ready made schemas that ship with sample rows |
| Hand generated data | Beginner projects | Small, predictable, easy to verify your queries |
Whatever the source, load the data into your own database rather than querying a file. Importing a CSV, defining the right column types, and adding keys is itself a skill worth showing. When you finish a project, sharpen your query technique against the SQL examples library and keep building. Two or three polished projects, each with a README and documented queries, will do more for your job search than any certificate.