CASE statements comparing two column values part 2
Similar to the previous exercise, you will construct a query to determine the outcome of Barcelona's matches where they played as the away team. You will learn how to combine these two queries in chapters 2 and 3.
Did their performance differ from the matches where they were the home team?
This is a part of the course
“Data Manipulation in SQL”
Exercise instructions
- Complete the
CASEstatement to identify Barcelona's away team games (id = 8634) as wins, losses, or ties. - Left join the
teams_spaintableteam_api_idcolumn on thematches_spaintablehometeam_idcolumn. This retrieves the identity of the home team opponent. - Filter the query to only include matches where Barcelona was the away team.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Select matches where Barcelona was the away team
SELECT
m.date,
t.team_long_name AS opponent,
___ ___ ___ < ___ ___ 'Barcelona win!'
WHEN ___ > ___ ___ 'Barcelona loss :('
___ 'Tie' ___ ___ outcome
FROM matches_spain AS m
-- Join teams_spain to matches_spain
LEFT JOIN teams_spain AS t
ON m.___ = t.___
WHERE m.___ = ___;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 the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.
Exercise 1: We'll take the CASEExercise 2: Basic CASE statementsExercise 3: CASE statements comparing column valuesExercise 4: CASE statements comparing two column values part 2Exercise 5: In CASE things get more complexExercise 6: In CASE of rivalryExercise 7: Filtering your CASE statementExercise 8: CASE WHEN with aggregate functionsExercise 9: COUNT using CASE WHENExercise 10: COUNT and CASE WHEN with multiple conditionsExercise 11: Calculating percent with CASE and AVGWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.