SQL Basics - Monsters using CASE

You have access to two tables named top_half and bottom_half, as follows:

top_half schema

• id
• heads
• arms

bottom_half schema

• id
• legs
• tails

You must return a table with the format as follows:

output schema

• id
• heads
• legs
• arms
• tails
• species

The IDs on the tables match to make a full monster. For heads, arms, legs and tails you need to draw in the data from each table.

For the species, if the monster has more heads than arms, more tails than legs, or both, it is a 'BEAST' else it is a 'WEIRDO'. This needs to be captured in the species column.

All rows should be returned (10).

Tests require the use of CASE. Order by species.

 

Solution:

SELECT 
  *,
  CASE WHEN heads > arms THEN 'BEAST'
  WHEN tails > legs THEN 'BEAST'
  ELSE 'WEIRDO'
  END AS species
FROM 
  top_half t 
JOIN 
  bottom_half b ON t.id = b.id
ORDER BY species

 

Result:

id	heads	arms	legs	tails	species
10	6349	395	27936	6096	BEAST
1	367	88502	228	558	BEAST
3	199	314	1688	46602	BEAST
4	896	998	109	867	BEAST
5	998	4106	665	50349	BEAST
6	238	1867	4183	32453	BEAST
7	8330	733	8116	517	BEAST
8	9387	586	33200	79905	BEAST
9	95384	6440	82463	7845	BEAST
2	180	9824	5506	291	WEIRDO