Why start with a hand-built mini project

A downloaded dataset comes with real-world messiness — which is valuable eventually, but it adds friction before a beginner has even practiced a JOIN. Writing the schema and sample data by hand first means every table, every relationship, and every row is fully understood before a single query is run against it. All three projects below run in any standard SQL dialect (PostgreSQL, MySQL, SQLite, SQL Server) with only minor syntax differences.

Project 1 — Library management system

Three tables: books, members, and loans — a classic one-to-many-to-many relationship structure.

library_schema.sql
CREATE TABLE books (
  book_id     INT PRIMARY KEY,
  title       VARCHAR(200),
  author      VARCHAR(100)
);

CREATE TABLE members (
  member_id   INT PRIMARY KEY,
  name        VARCHAR(100)
);

CREATE TABLE loans (
  loan_id     INT PRIMARY KEY,
  book_id     INT REFERENCES books(book_id),
  member_id   INT REFERENCES members(member_id),
  loan_date   DATE,
  return_date DATE
);

INSERT INTO books VALUES
  (1, 'The Pragmatic Programmer', 'Hunt & Thomas'),
  (2, 'Clean Code', 'Robert Martin'),
  (3, 'Designing Data-Intensive Applications', 'Martin Kleppmann');

INSERT INTO members VALUES
  (1, 'Ana'), (2, 'Ben'), (3, 'Cara');

INSERT INTO loans VALUES
  (1, 1, 1, '2026-06-01', '2026-06-10'),
  (2, 2, 1, '2026-06-15', NULL),
  (3, 1, 2, '2026-06-20', NULL);
library_queries.sql
-- Currently overdue / not-yet-returned books, with borrower name
SELECT b.title, m.name AS borrowed_by, l.loan_date
FROM loans l
JOIN books b   ON b.book_id = l.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.return_date IS NULL;

-- Most-borrowed book
SELECT b.title, COUNT(*) AS times_borrowed
FROM loans l
JOIN books b ON b.book_id = l.book_id
GROUP BY b.title
ORDER BY times_borrowed DESC
LIMIT 1;

Project 2 — Student grade tracker

grades_schema.sql
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(100)
);

CREATE TABLE courses (
  course_id  INT PRIMARY KEY,
  course_name VARCHAR(100)
);

CREATE TABLE grades (
  student_id INT REFERENCES students(student_id),
  course_id  INT REFERENCES courses(course_id),
  score      INT
);

INSERT INTO students VALUES (1,'Diego'), (2,'Elena'), (3,'Farid');
INSERT INTO courses VALUES (1,'Statistics'), (2,'Databases');
INSERT INTO grades VALUES
  (1,1,85), (1,2,92),
  (2,1,78), (2,2,88),
  (3,1,95), (3,2,81);
grades_queries.sql
-- Class average per course
SELECT c.course_name, ROUND(AVG(g.score), 1) AS class_average
FROM grades g
JOIN courses c ON c.course_id = g.course_id
GROUP BY c.course_name;

-- Top-scoring student in each course
SELECT c.course_name, s.name, g.score
FROM grades g
JOIN students s ON s.student_id = g.student_id
JOIN courses c  ON c.course_id = g.course_id
WHERE g.score = (
  SELECT MAX(g2.score) FROM grades g2 WHERE g2.course_id = g.course_id
);

Project 3 — Movie ratings mini-database

movies_schema.sql
CREATE TABLE movies (
  movie_id  INT PRIMARY KEY,
  title     VARCHAR(100),
  genre     VARCHAR(50)
);

CREATE TABLE ratings (
  movie_id  INT REFERENCES movies(movie_id),
  reviewer  VARCHAR(50),
  rating    INT
);

INSERT INTO movies VALUES
  (1, 'The SQL Job', 'Drama'),
  (2, 'Joins & Consequences', 'Thriller');

INSERT INTO ratings VALUES
  (1, 'user_a', 4), (1, 'user_b', 5),
  (2, 'user_a', 3), (2, 'user_c', 4);
movies_queries.sql
-- Average rating per movie, only movies with 2+ ratings
SELECT m.title, ROUND(AVG(r.rating), 1) AS avg_rating, COUNT(*) AS num_ratings
FROM movies m
JOIN ratings r ON r.movie_id = m.movie_id
GROUP BY m.title
HAVING COUNT(*) >= 2
ORDER BY avg_rating DESC;

How to level these up

  • Add a window function. Rank movies by rating within each genre using RANK() or DENSE_RANK().
  • Add a date dimension. Track loan or rating dates over time and compute a monthly trend.
  • Swap in a real dataset. Once the schema and queries feel natural, rebuild the same project against MovieLens (for the movie ratings idea) or a public library-systems dataset, at real scale.
  • Write it up. Push the schema, sample data, and queries to a GitHub repo with a short README — this is what turns a learning exercise into an actual portfolio piece.

Common mistakes

  • Skipping foreign keys in the schema. Even in a small hand-built project, declaring the relationships properly reinforces how relational data is meant to be modeled.
  • Too little sample data to see real patterns. A handful of rows is enough to test that a query runs, but not enough to spot an interesting trend — expanding to 20-30 rows per table makes results more meaningful.
  • Never leveling up. Hand-built projects are a starting point, not a finishing point — the real skill growth comes from eventually applying the same query patterns to a messier, larger, real dataset.

Key takeaways

  • A hand-built schema and sample dataset removes the friction of cleaning a downloaded file before practicing queries.
  • All three projects here — library, grades, movies — use the same core pattern: a few related tables, joined and aggregated.
  • Level up by adding window functions, a date dimension, or swapping in a larger real dataset.
  • Document and publish the finished version to turn a learning exercise into a portfolio piece.