Question

How can you produce a list of facilities, with each labelled as 'cheap' or 'expensive' depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.
Schema reminder
DB schema

Expected Results

name cost
Tennis Court 1 expensive
Tennis Court 2 expensive
Badminton Court cheap
Table Tennis cheap
Massage Room 1 expensive
Massage Room 2 expensive
Squash Court cheap
Snooker Table cheap
Pool Table cheap

Your Answer Hint Help Save Run Query


              

Answers and Discussion Show

select name, 
	case when (monthlymaintenance > 100) then
		'expensive'
	else
		'cheap'
	end as cost
	from cd.facilities;          

This exercise contains a few new concepts. The first is the fact that we're doing computation in the area of the query between SELECT and FROM. Previously we've only used this to select columns that we want to return, but you can put anything in here that will produce a single result per returned row - including subqueries.

The second new concept is the CASE statement itself. CASE is effectively like if/switch statements in other languages, with a form as shown in the query. To add a 'middling' option, we would simply insert another when...then section.

Finally, there's the AS operator. This is simply used to label columns or expressions, to make them display more nicely or to make them easier to reference when used as part of a subquery.

Try looking up the SQL CASE statement.

Keyboard shortcuts:


Other hints: