Databases & SQL Project Ideas

Design and query a SQLite database to record income, expenses, and categories for monthly budget analysis.

• Create tables for users, categories, and transactions with proper foreign keys
• Insert at least 50 seed rows of realistic data
• Write SELECT queries with WHERE, ORDER BY, and LIMIT clauses
• Aggregate spending per category using GROUP BY and SUM
• Filter results by date range using BETWEEN or comparison operators

Model a library system with books, authors, and borrowers, then write queries to manage checkouts and returns.

• Design a normalized schema with books, authors, members, and loans tables
• Enforce NOT NULL and UNIQUE constraints on key columns
• Write JOIN queries to list books with their author names
• Track due dates and flag overdue loans with a query
• Generate a report of most-borrowed books using GROUP BY and ORDER BY

Build a product catalog database with categories and tags, then write flexible search and filter queries.

• Create tables for products, categories, and a many-to-many tags relationship
• Populate with 100+ products across at least 8 categories
• Implement full-text search using LIKE or FTS extensions
• Filter products by price range, category, and tag simultaneously
• Sort results by relevance score or price with pagination using LIMIT/OFFSET

Model a company org chart with departments, roles, salaries, and performance reviews, then write analytical queries.

• Build a self-referencing employees table to represent manager hierarchies
• Write recursive CTEs to traverse the org chart up to 5 levels deep
• Calculate average salary per department and flag employees below the median
• Track salary history in a separate table and compute year-over-year raises
• Create views for common HR reports and restrict column access with column-level SELECT

Design a warehouse inventory database with stock movements, suppliers, and reorder logic using triggers and transactions.

• Create tables for products, warehouses, stock_movements, and suppliers
• Write ACID transactions to atomically transfer stock between warehouses
• Implement a trigger that automatically creates a reorder_request when stock drops below threshold
• Build a stored procedure to process incoming shipments and update inventory
• Query low-stock alerts and supplier lead times with correlated subqueries

Load a dataset of posts, likes, and follows into PostgreSQL and write window-function queries to compute engagement metrics.

• Import a CSV dataset of 10,000+ posts and user interactions using COPY or bulk insert
• Compute rolling 7-day post engagement using window functions (SUM OVER, ROW_NUMBER)
• Rank top 10 users by follower growth per month with RANK() and PARTITION BY
• Identify influencer chains using recursive CTEs on the follows graph
• Create materialized views for dashboard queries and benchmark with EXPLAIN ANALYZE

Architect a PostgreSQL database for a multi-tenant SaaS app with strict data isolation, RLS policies, and schema migrations.

• Design a tenant-scoped schema where every table includes a tenant_id foreign key
• Enable Row-Level Security policies so each tenant sees only their own data
• Create roles with least-privilege GRANT statements for app, read-only, and admin users
• Write a Flyway or Liquibase migration sequence for 5 iterative schema changes
• Benchmark query performance before and after adding partial indexes on tenant_id

Build a data warehouse in PostgreSQL with partitioned tables and star schema to store and analyze years of stock price data.

• Model a star schema with a fact_prices table and dimension tables for stocks, dates, and sectors
• Partition fact_prices by year using declarative table partitioning
• Load 5+ years of OHLCV stock data via a scripted ETL process
• Write window queries to compute 50-day and 200-day moving averages and detect golden crosses
• Optimize slow queries using EXPLAIN ANALYZE, composite indexes, and covering indexes

Set up a PostgreSQL primary-replica cluster, simulate failures, and measure replication lag and recovery time objectives.

• Configure streaming replication between one primary and two standby PostgreSQL instances using Docker
• Verify synchronous vs asynchronous replication lag under a write-heavy load test
• Promote a standby to primary and update connection strings to simulate failover
• Implement logical replication to sync a subset of tables to an analytics replica
• Document RTO and RPO measurements and tune checkpoint and WAL settings