SQL Learning Project

A comprehensive SQL practice environment with sample databases and progressive exercises.

What's Inside

• Sample Databases: Realistic datasets to practice with
• Progressive Exercises: From basic SELECT to advanced JOINs and subqueries
• Solutions: Step-by-step explanations for each exercise
• Quick Reference: Essential SQL syntax and commands

Getting Started

1. Install SQLite (if not already installed):

   # Ubuntu/Debian
   sudo apt install sqlite3
   
   # macOS
   brew install sqlite
   
   # Windows - download from https://sqlite.org/download.html

2. Create the sample database (choose one method):

   Method A - Using Python (recommended):

   python3 setup/create_database.py

   Method B - Using SQL script:

   # Remove existing database first (if any)
   rm -f company.db
   # Create fresh database
   sqlite3 company.db < setup/create_database.sql

3. Start practicing:

   sqlite3 company.db

Database Schema

Company Database

• employees: Employee information (id, name, department, salary, hire_date)
• departments: Department details (id, name, manager_id, budget)
• projects: Project information (id, name, department_id, budget, start_date, end_date)
• employee_projects: Many-to-many relationship between employees and projects

Exercise Structure

Beginner (exercises/01_beginner/)

• Basic SELECT statements
• WHERE clauses and filtering
• Sorting with ORDER BY
• Basic aggregate functions (COUNT, SUM, AVG)

Intermediate (exercises/02_intermediate/)

• JOINs (INNER, LEFT, RIGHT)
• GROUP BY and HAVING
• Subqueries
• Date/time functions

Advanced (exercises/03_advanced/)

• Complex JOINs with multiple tables
• Window functions
• Common Table Expressions (CTEs)
• Performance optimization

How to Use

1. Read the exercise description in each .md file
2. Try writing your SQL query
3. Test it in SQLite: sqlite3 company.db
4. Check your answer against the solution
5. Read the explanation to understand the concepts

Quick Commands

# Enter SQLite CLI
sqlite3 company.db

# Show all tables
.tables

# Show table structure
.schema employees

# Run a SQL file
.read exercises/01_beginner/exercise_01.sql

# Exit SQLite
.quit

Tips for Learning SQL

1. Start small: Begin with simple SELECT statements
2. Practice regularly: SQL is best learned through hands-on practice
3. Understand the data: Always explore your tables first with SELECT * FROM table LIMIT 5
4. Read error messages: SQLite gives helpful error messages
5. Experiment: Try variations of queries to see what happens

Resources

• SQLite Documentation
• SQL Tutorial
• SQLite Browser - GUI tool for exploring databases

Happy learning! 🚀