In this advanced SQL course, you’ll learn to enhance your queries with conditional logic using CASE statements and apply string functions to extract and format text with precision. You’ll also explore subqueries to dynamically retrieve and reuse data across different parts of a database.
Additionally, you’ll work with Views to query data when direct table access isn’t available, learn to use parameterized functions, and execute reusable stored procedures. By the end of this hands-on course, you’ll have mastered advanced SQL techniques and gained the expertise needed for roles in data analysis, data science, or any position involving database management and optimization.
What You Will Learn
- Write subqueries to create layered queries using single-value, multi-value, and table-value structures
- Use window functions with OVER and PARTITION BY to apply aggregate logic across rows without grouping
- Implement conditional logic with CASE and IIF statements to dynamically transform query results
- Manipulate text data using string functions like SUBSTRING, CHARINDEX, UPPER, and more
- Apply self-joins to compare records within the same table and understand their structure and use cases
- Build and query views, user-defined functions, and stored procedures to modularize your SQL code
Course Outline
Advanced Query Techniques
Subqueries
-
Subqueries: Queries within queries
-
Single-Value vs Multi-Value vs Table-Value Subqueries
Aggregate Window Functions
-
Using OVER and PARTITION BY to apply Aggregate Functions
Conditionals with CASE
-
Adding If-Else Logic with CASE
-
Simple CASE vs Searched CASE
-
IIF (Inline IF)
String Functions
-
Manipulating text using string functions
-
Using SUBSTRING, LEFT/RIGHT, CHARINDEX, UPPER, etc.
-
Combining with Inline IF statements (IIF)
Self Join
-
How a Self Join is different from other types of joins
-
Using a Self Join
Views, Functions, & Stored Procedures
Using Views
-
What are Views?
-
The Advantages & Disadvantages of using Views
-
How to Use & Query a View
User-Defined Functions with Parameters
-
Using Scalar-Value (Single-Value) Functions
-
Using Table-Value Functions
Executing Stored Procedures
-
The difference between Views, Functions, and Stored Procedures
-
Using Stored Procedures
This course is available for 30 days. You can choose when to start your access period. Once you activate, you will have 30 days to complete it (access the course materials, quizzes, projects and videos). You may request one extension of seven (7) days. Other extension requests will be evaluated on a case-by-case basis. Videos are not downloadable.
