Course Overview:
SQL Intermediate
This course builds upon the foundational SQL skillsadvanced queries, automation, and data transformation. Participants will master CTEs, window functions, transactions, and JSON handling while applying SQL to real-world analytics and business problems.
SQL Intermediate
Course Objectives
By the end of this course, participants will be able to:
- Write advanced SQL queries for Data Analysis.
- Perform Advanced Data Aggregation & Transformation.
- Manage Transactions, Concurrency, and Data Integrity.
- Automate SQL Operations & Work with Semi-Structured Data.
SQL Intermediate Modules
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
- Writing Correlated & Non-Correlated Subqueries
- Using Common Table Expressions (CTEs) for Readability
- Introduction to Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
- Applying LAG & LEAD for Trend Analysis
MODULE 2: Advanced Data Aggregation & Transformation
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
- Grouping Sets (ROLLUP, CUBE, GROUPING SETS)
- Using CASE Statements for Conditional Logic
- Pivoting & Unpivoting Data for Analysis
- Handling String Manipulation & Regular Expressions
MODULE 3: Transactions, Concurrency & Data Integrity
MODULE 1: Subqueries, Common Table Expressions (CTEs) & Window Functions
MODULE 3: Transactions, Concurrency & Data Integrity
- Understanding ACID Properties in SQL Transactions
- Using COMMIT, ROLLBACK, and SAVEPOINTS
- Managing Deadlocks & Locking Mechanisms (Row-Level vs. Table-Level Locks)
- Handling Concurrency Issues & Isolation Levels
MODULE 4: Stored Procedures & SQL Automation
MODULE 5: Working with JSON & Semi-Structured Data in SQL
MODULE 3: Transactions, Concurrency & Data Integrity
- Writing Stored Procedures for Automating Queries
- Using Input & Output Parameters in Procedures
- Managing Triggers for Automated Database Actions
- Implementing Error Handling & Exception Management in SQL
MODULE 5: Working with JSON & Semi-Structured Data in SQL
MODULE 5: Working with JSON & Semi-Structured Data in SQL
MODULE 5: Working with JSON & Semi-Structured Data in SQL
- Introduction to JSON & XML Data Handling in SQL
- Parsing & Querying JSON Data (JSON_VALUE, JSON_QUERY, JSON_TABLE)
- Using SQL for ETL Processes and Data Wrangling
MODULE 6: Time-Series & Trend Analysis with SQL
MODULE 5: Working with JSON & Semi-Structured Data in SQL
MODULE 5: Working with JSON & Semi-Structured Data in SQL
- Performing Time-Based Aggregations (Monthly, Quarterly, Yearly)
- Using Window Functions for Running Totals & Moving Averages
- Analyzing Seasonal Trends & Forecasting Data
MODULE 7: Data Cleaning & Preparation for Analysis
MODULE 8: Practical Case Studies & Hands-On Projects
MODULE 8: Practical Case Studies & Hands-On Projects
- Identifying & Removing Duplicate Records
- Handling Missing Values & Outliers
- Standardizing Data Formats for Consistency
- Data Normalization & Denormalization Techniques
MODULE 8: Practical Case Studies & Hands-On Projects
MODULE 8: Practical Case Studies & Hands-On Projects
MODULE 8: Practical Case Studies & Hands-On Projects
- Retail Analysis: Understanding Customer Buying Behavior at Walmart
- Financial Data Analysis: Analyzing Sales Revenue & Profit Margins
- Marketing Analytics: Customer Segmentation Using SQL
Course Expectations
Course Duration
16 hours (Flexible delivery: 2 hours per session over 4 weeks )
expected outcome
Students will be able to optimize queries, analyze trends, and transform raw data into meaningful business insights.
assessment
- Hands-On Practice: Structured exercises after each module.
- Case Study Project: Solve a real-world business problem using advanced SQL techniques.
Prerequisites
Completion of Introduction to SQL or equivalent knowledge, including basic SELECT queries, joins, and CRUD operations.
Materials provided
- Access to a larger, more complex sample database.
- Advanced SQL reference guide.
- Query optimization cheat sheet.
Copyright © 2025 SQL Friends - All Rights Reserved.
Powered by BookWorm IT
This website uses cookies.
We use cookies to analyze website traffic and optimize your website experience. By accepting our use of cookies, your data will be aggregated with all other user data.