🗃️ sql_files_practice

Practical MySQL exercises for daily SQL learning

A structured collection of MySQL exercises, example schemas, and query solutions created for practice, revision, and reference. Each SQL file represents an independent problem, experiment, or solution designed to strengthen core database concepts.

---

🚀 Overview

sql_files is a personal SQL practice repository focused on building a strong foundation in MySQL through hands-on problem solving. It covers query writing, schema design, data manipulation, and optimization techniques using plain .sql files that can be executed in any compatible MySQL environment.

This repository works well as both a learning archive and a practical reference set for revising SQL concepts before interviews, exams, or project work.

---

✨ Features

SQL Practice and Revision

• 🧠 Hands-On Exercises - Solve independent SQL problems for practice and revision.
• 🗂️ Plain SQL Files - Keep every exercise portable and easy to run.
• 🧱 Schema and Seed Examples - Use sample tables and inserts for reproducible testing.
• 🔍 Concept Coverage - Practice SELECT, JOIN, GROUP BY, window functions, DDL, DML, and indexing.

Learning Workflow

• 🔁 Repeatable Execution - Re-run files easily in Docker or a local MySQL setup.
• 🧪 Query Iteration - Test and refine queries against small datasets.
• 📚 Reference-Friendly Structure - Organize problems, solutions, fixtures, and scripts clearly.
• ⚙️ Expandable Setup - Add metadata, scripts, and CI as the repository grows.

---

🛠️ Tech Stack

• Database: MySQL 8+
• Query Files: SQL (.sql)
• Container Support: Docker, Docker Compose
• Optional Tools: mysql client, Adminer, phpMyAdmin
• Automation: Bash scripts, GitHub Actions

---

📁 Repository Structure

sql_files/
├── README.md
├── problems/        # SQL problem statements and starter files
├── solutions/       # Completed SQL solutions
├── fixtures/        # Schema creation and seed data
├── migrations/      # Schema-only migration files
└── scripts/         # Helper scripts such as run-all or run-one

If your files are currently mixed together, organizing them into these folders makes the repository easier to navigate and maintain.

---

⚙️ Getting Started

Prerequisites

• Docker (recommended), or a local MySQL 8+ installation
• Optional: mysql client for direct command-line execution
• Optional: Docker Compose for a smoother local setup

---

🐳 Quick Start with Docker

Start a MySQL container:

docker run --name sql-dev \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=exercises \
  -p 3306:3306 \
  -d mysql:8.0

Once MySQL finishes initializing, connect using the MySQL client:

docker run -it --rm --network host mysql:8.0 mysql -h127.0.0.1 -P3306 -uroot -prootpass exercises

If --network host is not supported on your system, use:

docker exec -it sql-dev mysql -uroot -prootpass exercises

---

📦 Docker Compose Setup

A simple docker-compose.yml can make the environment easier to reuse:

version: "3.8"
services:
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: exercises
    ports:
      - "3306:3306"
    volumes:
      - ./fixtures:/docker-entrypoint-initdb.d:ro

Place your seed files inside fixtures/. MySQL will run them automatically on the first container startup.

---

▶️ Run SQL Files

Run a Single SQL File

docker exec -i sql-dev mysql -uroot -prootpass exercises < path/to/file.sql

Or:

cat path/to/file.sql | docker exec -i sql-dev mysql -uroot -prootpass exercises

If using a local MySQL installation:

mysql -h 127.0.0.1 -P 3306 -u root -p exercises < path/to/file.sql

Run All SQL Files in a Directory

for f in ./problems/*.sql; do
  echo "Running $f"
  docker exec -i sql-dev mysql -uroot -prootpass exercises < "$f"
done

For bulk execution with careful ordering:

cat ./fixtures/*.sql ./migrations/*.sql | docker exec -i sql-dev mysql -uroot -prootpass exercises

---

🧪 Testing and Query Iteration

• Keep seed data small so query results stay easy to inspect.
• Use SELECT * FROM table LIMIT 20; to quickly preview data.
• Run EXPLAIN before complex queries to understand execution plans.
• Wrap destructive experiments in transactions so they can be rolled back safely.

Example

START TRANSACTION;
-- DML statements here
ROLLBACK;

---

🧾 Conventions and Tips

• Use filename patterns like 01-create-schema.sql or 02-joins-basic.sql.
• Separate fixtures, problems, and solutions for cleaner organization.
• Add a short comment header at the top of each file describing the goal.
• Prefer idempotent DDL so files can be re-run without manual cleanup.

Example File Header

-- 03-top-customers.sql
-- Goal: Return top 5 customers by total spent in 2024.

Example Idempotent DDL

DROP TABLE IF EXISTS users;
CREATE TABLE users (...);

For larger collections, consider maintaining a small index.json or index.yml with metadata like difficulty, topic, or expected output.

---

💡 Improvements to Consider

• Add an index.json or index.yml for exercise metadata.
• Create helper scripts such as scripts/run-all.sh and scripts/run-problem.sh.
• Add a lightweight test harness that compares query results with expected outputs.
• Integrate Adminer or phpMyAdmin for easier visual inspection.
• Set up GitHub Actions to run fixtures and validate solutions automatically.

---

🌐 Optional Adminer Setup

To add a simple browser-based SQL interface, extend Docker Compose with Adminer:

services:
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Then open http://localhost:8080 and connect it to your MySQL service.

---

🤝 Contributing

If you want to expand the repository:

1. Follow the existing filename conventions.
2. Keep fixtures small, readable, and self-contained.
3. Include problem descriptions and expected outputs where possible.
4. Open a pull request explaining what the SQL file demonstrates.

---

📝 License

This repository is intended for personal learning and SQL practice. If you plan to share or reuse it publicly, adding a license such as MIT is recommended.

---

📧 Contact

For questions, collaboration, or repository updates:

• GitHub: DishiGpt

---

Built as a personal SQL practice space for learning, experimentation, and database problem solving.