CASE statements comparing column values
Barcelona is considered one of the strongest teams in Spain's soccer league.
In this exercise, you will be creating a list of matches in the 2011/2012 season where Barcelona was the home team. You will do this using a CASE statement that compares the values of two columns to create a new group -- wins, losses, and ties.
In 3 steps, you will build a query that identifies a match's winner, identifies the identity of the opponent, and finally filters for Barcelona as the home team. Completing a query in this order will allow you to watch your results take shape with each new piece of information.
The matches_spain table currently contains Barcelona's matches from the 2011/2012 season, and has two key columns, hometeam_id and awayteam_id, that can be joined with the teams_spain table. However, you can only join teams_spain to one column at a time.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Create a
CASEstatement to identify matches as home wins, home losses, or ties.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
date,
-- Identify home wins, losses, or ties
___ ___ home_goal > away_goal ___ 'Home win!'
___ home_goal < away_goal ___ 'Home loss :('
___ 'Tie' ___ ___ outcome
FROM matches_spain;