SQL Database Management Training Program: GreenCycle Rental & Flights Booking

A comprehensive training program to master SQL for relational database management through real-world scenarios across two databases:

• GreenCycle Film Rental Shop
• Flights Booking System

---

Key Components

Tools & Technologies

• Primary Database: PostgreSQL 13+
• GUI: pgAdmin 4
• ER Diagrams:
  • GreenCycle Film Rental
  • Flights Booking

Core SQL Topics Covered

Category	Key Concepts
Data Retrieval	SELECT, WHERE, JOIN, GROUP BY, window functions
Data Manipulation	INSERT, UPDATE, DELETE, transactions, stored procedures
Advanced Analytics	CTEs, ROLLUP, CUBE, correlated subqueries, GROUPING SETS
Optimization	Indexing, partitioning, query tuning, privilege management
Administration	Table creation/alteration, views, user-defined functions, role-based access

---

Learning Progression

15 modules structured to build end-to-end expertise:

Modules	Focus Area	Example Challenges
01-03	Foundational Queries	List customers, filter payments, count films
04-07	Functions & Joins	Anonymize emails, revenue analysis by city
08-10	Advanced Analytics	Replacement cost tiers, NULL value management
11-12	Window Functions & Grouping	Revenue running totals, seasonal sales analysis
13-15	Optimization & Admin	Table constraints, stored procedures, query tuning

---

Repository Structure

Root
├── 01 - Getting started with SQL!
│   ├── 01 - SELECT.sql
│   ├── 02 - ORDER BY.sql
│   └── ...
├── 02 - Filtering
│   ├── 01 - WHERE.sql
│   ├── 02 - BETWEEN.sql
│   └── ...
├── ERD for Database
│   ├── Flights_Booking.png
│   └── GreenCycle_film_rental_shop.png
└── ... (15 topic-specific folders)

---

Challenges & Projects

100+ Real-World Challenges

• Filtering: Identify unreturned rentals, analyze payment trends.
• Joins: Optimize seat bookings, map customer districts.
• Window Functions: Calculate revenue running totals, rank salaries.
• Admin: Design stored procedures, enforce constraints.

Highlighted Projects

1. Course Project (Modules 08 & 13)
   • Analyze film replacement costs and revenue by category.
   • Design employee salary tracking with window functions.
2. Final Tasks
   • Optimize queries for flight revenue.
   • Implement role-based security and transactions.

---

Certification-Ready Outcomes

• Top Films: DOGMA FAMILY (Animation, $178.70 revenue).
• Key Metrics:
  • Avg. Software Engineer salary: $6,028.
  • Most common film category: Sports (74 titles).
• District Analysis: Saint-Denis (highest avg. customer lifetime value: $216.54).

---

##️ Tools in Action

Optimization Techniques

• Indexing: Accelerate payment date searches.
• Partitioning: Split large tables by rental dates.

---

Outcomes & Deliverables

• Practical Skills: Solve 100+ challenges across filtering, joins, and analytics.
• Business Insights: Build dashboards for revenue trends and inventory analysis.
• Certification Prep: Master SQL for roles in data analysis, BI, and DB administration.

---

How to Use This Repository

1. Clone the repo:

   git clone https://github.com/your-username/sql-database-training.git

2. Set up PostgreSQL: Import provided ERDs and sample data.
3. Explore modules: Navigate folders sequentially or focus on specific topics.