After doing these courses, I feel confident creating professional visualizations and dashboards
Shoebur RahmanProcurement Specialist, Giz Bangladesh
Course Description
So you've learned how to aggregate and join data from tables in your database—now what? How do you manipulate, transform, and make the most sense of your data? This intermediate-level course will teach you several key functions necessary to wrangle, filter, and categorize information in a relational database, expand your SQL toolkit, and answer complex questions. You will learn the robust use of CASE statements, subqueries, and window functions—all while discovering some interesting facts about soccer using the European Soccer Database.The videos contain live transcripts you can reveal by clicking "Show transcript" at the bottom left of the videos.
The course glossary can be found on the right in the resources section.
To obtain CPE credits you need to complete the course and reach a score of 70% on the qualified assessment. You can navigate to the assessment by clicking on the CPE credits callout on the right.
Feels like what you want to learn?
Start Course for FreeWhat you'll learn
- Extract and transform data from multiple tables using complex logical conditions with the CASE WHEN statement
- Identify appropriate techniques for creating queries given a wide variety of data structure requirements
- Structure SQL queries with multiple correlated subqueries as common table expressions using the WITH statement
- Use correlated subqueries to extract data from multiple tables in different formats within a relational database
- Use single subqueries in SELECT, FROM, and WHERE clauses to retrieve and filter information
Prerequisites
Curriculum
Course outline
1
We'll take the CASE
In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.
- We'll take the CASE50 XP
- Basic CASE statements100 XP
- CASE statements comparing column values100 XP
- CASE statements comparing two column values part 2100 XP
- In CASE things get more complex50 XP
- In CASE of rivalry100 XP
- Filtering your CASE statement100 XP
- CASE WHEN with aggregate functions50 XP
- COUNT using CASE WHEN100 XP
- Conditional selection and summation with CASE WHEN100 XP
- Using CASE and AVG to derive fractions100 XP
2
Short and Simple Subqueries
In this chapter, you will learn about subqueries in the SELECT, FROM, and WHERE clauses. You will gain an understanding of when subqueries are necessary to construct your dataset and where to best include them in your queries.
3
Correlated Queries, Nested Queries, and Common Table Expressions
In this chapter, you will learn how to use nested and correlated subqueries to extract more complex data from a relational database. You will also learn about common table expressions and how to best construct queries using multiple common table expressions.
4
Window Functions
You will learn about window functions and how to pass aggregate functions along a dataset. You will also learn how to calculate running totals and partitioned averages.
S
Data Manipulation in SQL
Course
Complete
