Basic Correlated Subqueries
Correlated subqueries are subqueries that reference one or more columns in the main query. Correlated subqueries depend on information in the main query to run, and thus, cannot be executed on their own.
Correlated subqueries are evaluated in SQL once per row of data retrieved -- a process that takes a lot more computing power and time than a simple subquery.
In this exercise, you will practice using correlated subqueries to examine matches with scores that are extreme outliers for each country -- above 3 times the average score!
This is a part of the course
“Data Manipulation in SQL”
Exercise instructions
- Select the
country_id,date,home_goal, andaway_goalcolumns in the main query. - Complete the
AVGvalue in the subquery. - Complete the subquery column references, so that
country_idis matched in the main and subquery.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select country ID, date, home, and away goals from match
___.country_id,
___,
___.home_goal,
___
FROM match AS main
WHERE
-- Filter the main query by the subquery
(home_goal + away_goal) >
(SELECT AVG((___ + sub.away_goal) * 3)
FROM match AS sub
-- Join the main query to the subquery in WHERE
WHERE main.___ = sub.___);This exercise is part of the course
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.
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.
Exercise 1: Correlated subqueriesExercise 2: Basic Correlated SubqueriesExercise 3: Correlated subquery with multiple conditionsExercise 4: Nested subqueriesExercise 5: Nested simple subqueriesExercise 6: Nest a subquery in FROMExercise 7: Common Table ExpressionsExercise 8: Clean up with CTEsExercise 9: Organizing with CTEsExercise 10: CTEs with nested subqueriesExercise 11: Deciding on techniques to useExercise 12: Get team names with a subqueryExercise 13: Get team names with correlated subqueriesExercise 14: Get team names with CTEsExercise 15: Which technique to use?What is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.