Database Practice Lab

A personal laboratory for practicing and mastering SQL Server database design, queries, and optimization.

This repository contains SQL scripts, Docker configurations, and solved exercises for learning relational databases. The main focus is on SQL Server, but the structure is designed to be extended to other DBMS in the future (Oracle, PostgreSQL, MySQL).

---

📁 Repository Structure

BDD/
├── SQL_Server/
│     └── Products/                       # Differents domains
│           ├── 0_create_db.sql           # Table creation (DDL) + constraints (PK, FK)
│           ├── 0_populate_db.sql         # Sample data insertion (DML)
│           ├── 1_init_queries.sql        # Basic queries (SELECT, WHERE, JOINs, ORDER BY)
│           ├── 2_tests_queries.sql       # Condition tests (comparison, range, IN, LIKE, NULL)
│           ├── 3_sum_queries.sql         # Aggregate functions (SUM, AVG, COUNT, MAX, MIN)
│           ├── 4_group_by.sql            # GROUP BY and HAVING queries
│           ├── 5_subqueries.sql          # Subqueries (IN, EXISTS, ANY, ALL, correlated)
│           └── docker-compose.yml        # SQL Server container configuration
├── .gitignore
└── README.md               

---

Getting Started

Prerequisites

• Docker Desktop (Windows/Mac) or Docker Engine (Linux)
• A SQL client (e.g., DBeaver, Azure Data Studio, or VS Code extensions)

1. Start SQL Server Container

Navigate to the BDD/SQL_Server/ directory and run:

cd BDD/SQL_Server/
docker-compose up -d

This will:

• Pull the official SQL Server 2022 Linux image
• Start a container named sqlserver-products
• Expose port 1433 for connections
• Persist data in a Docker volume (sqlserver-data)

2. Connect to the Database

Use any SQL client with the following credentials:

Setting	Value
Host	localhost or 127.0.0.1
Port	1433
Username	sa
Password	Sql_Password_123!
Database	MySQLDB

3. Run the SQL Scripts (Example)

Execute the scripts in order:

Order	Script	Description
1	0_create_db.sql	Creates the MiProyectoEstudio database and tables (Category, Product, Store, Product_x_Store)
2	0_populate_db.sql	Inserts sample data (8 categories, 40 products, 6 stores, 65+ stock entries)
3	1_init_queries.sql	Example queries to test the database (SELECT, JOINs, GROUP BY, aggregations)

---

Useful Commands

Stop and remove the container

docker-compose down

Remove container and data volume (hard reset)

docker-compose down -v

View container logs

docker logs sqlserver-products

---

Learning Goals

• Understand SQL DDL (CREATE, ALTER, DROP)
• Understand SQL DML (SELECT, INSERT, UPDATE, DELETE)
• Practice JOINs (INNER, LEFT, RIGHT, FULL, CROSS)
• Use aggregation functions (COUNT, SUM, AVG, MIN, MAX)
• Apply GROUP BY and HAVING clauses
• Work with NULL values and constraints (PK, FK, UNIQUE, DEFAULT)
• Learn containerized database management with Docker

---

Future Roadmap

• Add Oracle examples
• Add PostgreSQL examples
• Add MySQL examples
• Include stored procedures and triggers
• Add performance optimization exercises (indexes, execution plans)
• Add transaction management examples (COMMIT, ROLLBACK)

---

Notes

• Scripts are written for SQL Server (T-SQL). Some syntax may differ for other DBMS.
• The docker-compose.yml uses SQL Server 2022. For older versions, change the image tag to mcr.microsoft.com/mssql/server:2019-latest.