Get startedGet started for free

Basic CASE statements

What is your favorite team?

The European Soccer Database contains data about 12,800 matches from 11 countries played between 2011-2015! Throughout this course, you will be shown filtered versions of the tables in this database in order to better explore their contents.

In this exercise, you will identify matches played between FC Schalke 04 and FC Bayern Munich. There are 2 teams identified in each match in the hometeam_id and awayteam_id columns, available to you in the filtered matches_germany table. ID can join to the team_api_id column in the teams_germany table, but you cannot perform a join on both at the same time.

However, you can perform this operation using a CASE statement once you've identified the team_api_id associated with each team!

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

  • Create a CASE statement that identifies whether a match in Germany included FC Bayern Munich, FC Schalke 04, or neither as the home team.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Identify the home team as Bayern Munich, Schalke 04, or neither
SELECT 
	___ ___ hometeam_id = 10189 ___ 'FC Schalke 04'
        ___ hometeam_id = 9823 ___ 'FC Bayern Munich'
         ELSE 'Other' END AS home_team,
	COUNT(id) AS total_matches
FROM matches_germany
GROUP BY home_team;
Edit and Run Code