SQL and Python Training Program
Program Learning Objectives
By the end of the program participants will be able to:
• Understand relational database concepts and data structures
• Write efficient SQL queries for analytics and reporting
• Work with joins, window functions, and analytical queries
• Learn Python programming fundamentals
• Process data using Python libraries (NumPy, Pandas)
• Build simple data pipelines using SQL and Python
• Perform data analysis for business insights
Tools and Technologies
• MySQL / PostgreSQL
• Python 3.x
• Jupyter Notebook
• Pandas
• NumPy
• Matplotlib
• SQLAlchemy
• VS Code
Lab Setup Requirements
Software
• MySQL / PostgreSQL Server
• Python 3.x
• Anaconda / pip environment
• Jupyter Notebook
• VS Code
Python Libraries
• pandas
• numpy
• matplotlib
• seaborn
• sqlalchemy
WEEK 1 – SQL Foundations (40 Hours)
Day 1 – Data and Database Fundamentals
Topics
• Data ecosystem overview
• Role of SQL in Data Engineering and Data Science
• Relational Database Management System (RDBMS)
• Tables, rows, columns
• Data types
• Keys (Primary Key, Foreign Key)
• Database schemas
Hands-On Lab
Lab 1 – Database Setup
Create database for Retail Business
Tables
Customers
Orders
Products
Order_Items
Insert sample data.
Practice
• Explore table structure
• Insert and update records
Assignment
Design database schema for Online Shopping System
Day 2 – SQL Query Fundamentals
Topics
• SELECT statement
• Filtering using WHERE
• Comparison operators
• Logical operators
• ORDER BY
• LIMIT
Hands-On Lab
Lab 2
Write queries to retrieve:
• Customers from specific city
• Products above certain price
• Latest orders
Assignment
Create queries for Sales dataset:
1. Retrieve top 10 products by price
2. Retrieve customers with highest purchases
Day 3 – Aggregate Functions
Topics
• COUNT
• SUM
• AVG
• MIN
• MAX
• GROUP BY
• HAVING
Hands-On Lab
Lab 3
Generate reports:
• Total sales revenue
• Sales by category
• Average order value
Assignment
Create daily sales analytics queries.
Day 4 – SQL Joins
Topics
• Relationships between tables
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• SELF JOIN
Hands-On Lab
Lab 4
Queries:
• Customer order history
• Products purchased by each customer
• Orders without customer records
Assignment
Create queries to find:
• Customers with highest spending
• Products never sold
Day 5 – Subqueries and Derived Tables
Topics
• Subqueries
• Nested queries
• EXISTS
• IN clause
• Derived tables
Hands-On Lab
Lab 5
Queries:
• Customers spending above average
• Most expensive product per category
Mini Project
Retail Data Analysis using SQL
Tasks
• Monthly revenue
• Top customers
• Top products
WEEK 2 – Advanced SQL and Analytics (40 Hours)
Day 6 – Advanced Joins
Topics
• Multi-table joins
• Join performance considerations
• Query readability
Hands-On Lab
Lab 6
Build queries combining:
Customers
Orders
Products
Payments
Assignment
Create customer lifetime value query.
Day 7 – Window Functions
Topics
• Window function concepts
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• PARTITION BY
Hands-On Lab
Lab 7
Queries
• Rank customers by revenue
• Rank products by category
Assignment
Top 3 products in each category.
Day 8 – Analytical SQL Functions
Topics
• LEAD
• LAG
• Running totals
• Moving averages
Hands-On Lab
Lab 8
Queries
• Sales growth analysis
• Daily revenue trend
Assignment
Create monthly growth analysis report.
Day 9 – Common Table Expressions (CTE)
Topics
• CTE syntax
• Recursive CTE
• Query modularization
Hands-On Lab
Lab 9
Rewrite complex queries using CTE.
Assignment
Calculate top regions by revenue using CTE.
Day 10 – SQL Optimization
Topics
• Query optimization
• Indexing
• Query execution plans
• Best practices
Hands-On Lab
Lab 10
• Create indexes
• Analyze query performance
SQL Capstone Project
Dataset: E-commerce dataset
Tasks
• Customer behavior analysis
• Product performance
• Revenue trends
Deliverables
SQL queries + insights.
WEEK 3 – Python for Data Engineering and Analytics (40 Hours)
Day 11 – Python Programming Basics
Topics
• Python installation
• Variables and data types
• Operators
• Input/output
Hands-On Lab
Lab 11
Programs
• Simple calculator
• Sales tax calculator
Assignment
Write program to calculate average sales revenue.
Day 12 – Control Flow
Topics
• If statements
• Loops (for, while)
• Nested loops
Hands-On Lab
Lab 12
Programs
• Discount calculator
• Customer classification system
Assignment
Build program to calculate order totals for multiple customers.
Day 13 – Python Functions and Modules
Topics
• Functions
• Lambda functions
• Modules
• Code reusability
Hands-On Lab
Lab 13
Create reusable Python functions for:
• Revenue calculation
• Data transformation
Assignment
Create Python utility library for data calculations.
Day 14 – Python Data Structures
Topics
• Lists
• Tuples
• Dictionaries
• Sets
Hands-On Lab
Lab 14
Programs
• Store customer records
• Process order lists
Assignment
Write program to find top selling product.
Day 15 – NumPy Fundamentals
Topics
• Arrays
• Vectorized operations
• Numerical computing
Hands-On Lab
Lab 15
• Perform statistical analysis
• Compute averages and metrics
Day 16 – Pandas Introduction
Topics
• DataFrames
• Reading CSV/Excel
• Data inspection
Hands-On Lab
Lab 16
Load dataset and perform
• Filtering
• Sorting
• Grouping
Day 17 – Data Cleaning and Transformation
Topics
• Handling missing values
• Data transformation
• Feature engineering basics
Hands-On Lab
Lab 17
Clean and transform retail dataset.
Day 18 – Data Visualization
Topics
• Matplotlib basics
• Seaborn basics
• Business dashboards
Hands-On Lab
Lab 18
Create charts:
• Sales trends
• Product performance
• Category revenue
Day 19 – Python + SQL Integration
Topics
• Connecting Python to databases
• Executing SQL queries from Python
• Data extraction pipelines
Hands-On Lab
Lab 19
• Connect Python to MySQL
• Extract data
• Process with Pandas
Day 20 – Final Capstone Project
Project
Build End-to-End Data Analysis Pipeline
Steps
1. Extract data using SQL
2. Load into Python
3. Clean data using Pandas
4. Perform analytics
5. Generate visualization
Deliverables
• SQL scripts
• Python notebook
• Analytical report
Evaluation Criteria
• Daily Lab Exercises – 30%
• Assignments – 30%
• Capstone Project – 40%