Data Manipulation in SQL
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
Start Course for Free4 Hours15 Videos55 Exercises
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.Loved by learners at thousands of companies
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.
- 1
We'll take the CASE
FreeIn 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 xpBasic CASE statements100 xpCASE statements comparing column values100 xpCASE statements comparing two column values part 2100 xpIn CASE things get more complex50 xpIn CASE of rivalry100 xpFiltering your CASE statement100 xpCASE WHEN with aggregate functions50 xpCOUNT using CASE WHEN100 xpCOUNT and CASE WHEN with multiple conditions100 xpCalculating percent with CASE and AVG100 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.
WHERE are the Subqueries?50 xpFiltering using scalar subqueries100 xpFiltering using a subquery with a list100 xpFiltering with more complex subquery conditions100 xpSubqueries in FROM50 xpJoining Subqueries in FROM100 xpBuilding on Subqueries in FROM100 xpSubqueries in SELECT50 xpAdd a subquery to the SELECT clause100 xpSubqueries in Select for Calculations100 xpSubqueries everywhere! And best practices!50 xpALL the subqueries EVERYWHERE100 xpAdd a subquery in FROM100 xpAdd a subquery in SELECT100 xp - 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.
Correlated subqueries50 xpBasic Correlated Subqueries100 xpCorrelated subquery with multiple conditions100 xpNested subqueries50 xpNested simple subqueries100 xpNest a subquery in FROM100 xpCommon Table Expressions50 xpClean up with CTEs100 xpOrganizing with CTEs100 xpCTEs with nested subqueries100 xpDeciding on techniques to use50 xpGet team names with a subquery100 xpGet team names with correlated subqueries100 xpGet team names with CTEs100 xpWhich technique to use?50 xp - 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.
It's OVER50 xpThe match is OVER100 xpWhat's OVER here?100 xpFlip OVER your results100 xpOVER with a PARTITION50 xpPARTITION BY a column100 xpPARTITION BY multiple columns100 xpSliding windows50 xpSlide to the left100 xpSlide to the right100 xpBringing it all together50 xpSetting up the home team CTE100 xpSetting up the away team CTE100 xpPutting the CTEs together100 xpAdd a window function100 xp
Collaborators
Prerequisites
Joining Data in SQLMona Khalil
See More
Data Scientist, Greenhouse Software
As a senior data scientist at Greenhouse, Mona answers questions related to how the hiring process can be improved to find better candidates quicker and reduce bias in the hiring process. They previously worked in education, marketing, and local government. They also co-host Bad Methods, a podcast that brings a fun and interesting lens to critically evaluating science. You can find them on twitter at mona_kay_.
FAQs
Join over 12 million learners and start Data Manipulation in SQL today!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.