Duration 5 days – 35 hrs
Overview
The Intermediate SQL Training Course builds upon fundamental SQL knowledge to equip participants with more advanced querying techniques, database optimization strategies, and practical applications in real-world scenarios. Participants will learn to handle complex data extraction, aggregation, subqueries, window functions, and improve query performance, preparing them for professional-level SQL use in business, analytics, and development environments.
Objectives
- Write complex SQL queries involving multiple tables and conditions.
- Perform advanced data aggregation and grouping operations.
- Use subqueries, Common Table Expressions (CTEs), and window functions.
- Optimize queries for better performance and scalability.
- Implement intermediate database management operations such as views, indexes, and transactions.
- Apply best practices in SQL coding for maintainability and efficiency.
Audience
- Experienced Database Administrators (DBAs)\ with basic SQL knowledge aiming to deepen their database querying skills.
- Business analysts, data analysts, software developers, and IT professionals.
- Individuals preparing for advanced database certifications or roles requiring strong SQL expertise.
Pre- requisites
- Completion of a Basic SQL Training Course or equivalent hands-on experience.
- Familiarity with basic SQL concepts such as:
- SELECT, INSERT, UPDATE, DELETE commands
Simple joins
Basic WHERE clauses and conditions
Course Content
Module 1: Review of Basic SQL Concepts
- Quick recap of SELECT, JOINs, and WHERE conditions
- Recap exercises to ensure readiness for intermediate topics
Module 2: Advanced Data Retrieval Techniques
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN deep dive
- Using UNION and UNION ALL
- Working with complex WHERE and HAVING clauses
Module 3: Subqueries and Common Table Expressions (CTEs)
- Writing scalar, correlated, and nested subqueries
- Introduction to CTEs
- Recursive CTEs for hierarchical data
Module 4: Aggregations, Grouping, and Set Operations
- GROUP BY with complex aggregations
- Using ROLLUP and CUBE for advanced grouping
- Set operators: INTERSECT, EXCEPT
Module 5: Window Functions
- Introduction to OVER() and PARTITION BY
- Ranking functions (ROW_NUMBER, RANK, DENSE_RANK)
- Aggregate window functions (SUM, AVG over partitions)
Module 6: Database Objects and Transactions
- Creating and managing views
- Indexing for performance improvement
- Understanding and managing transactions
- COMMIT and ROLLBACK operations
Module 7: Query Optimization Techniques
- Analyzing execution plans
- Best practices for writing efficient queries
- Index usage and maintenance tips
Module 8: Practical Exercises and Case Studies
- Hands-on challenges based on real-world scenarios
- Query performance tuning exercises
- Mini-project: Building optimized queries for a business case
