Filtering your CASE statement

Let's generate a list of matches won by Italy's Bologna team! There are quite a few additional teams in the two tables, so a key part of generating a usable query will be using your CASE statement as a filter in the WHERE clause.

CASE statements allow you to categorize data that you're interested in -- and exclude data you're not interested in. In order to do this, you can use a CASE statement as a filter in the WHERE statement to remove output you don't want to see.

Here is how you might set that up:

SELECT *
FROM table
WHERE 
    CASE WHEN a > 5 THEN 'Keep'
         WHEN a <= 5 THEN 'Exclude' END = 'Keep';

In essence, you can use the CASE statement as a filtering column like any other column in your database. The only difference is that you don't alias the statement in WHERE.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Use the CASE statement in the WHERE clause to filter where hometeam_id is 9857 and home goals exceed away goals.
Use the CASE statement in the WHERE clause to filter where awayteam_id is 9857 and away goals exceed home goals.
End the CASE statement by excluding games that Bologna did not win.

Hands-on interactive exercise

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

SELECT 
	season,
    date,
	home_goal,
	away_goal
FROM matches_italy
WHERE 
	-- Find games where home_goal is more than away_goal
	___ ___ hometeam_id = 9857 ___ home_goal ___ away_goal ___ 'Bologna Win'
		-- Find games where away_goal is more than home_goal
        ___ awayteam_id = 9857 ___ away_goal ___ home_goal ___ 'Bologna Win' 
        -- Exclude games not won by Bologna
		___ IS NOT ___;

Edit and Run Code