CASE statement using between

Question

I am playing the "lost at sql" game, specifically the "challenge CASE" the problem requires to use the CASE statement to find put the "impostor". The game gives guide line on what tables I have and what columns.

The dataset has the following columns:

species_name (string): the name of the species

common_name (string): the common name of the species

length (float): the length of the species in meters

weight (float): the weight of the species in kilograms

habitat_type (string): the type of habitat the species is found in (e.g. coral reef, rocky shore, kelp forest)

You suspect that the creature that was brought on board may have changed its name to avoid detection.- The guide lines for the different fish are: -Clownfish are between 3-7 inches in length, weigh between 0.2 and 0.8 pounds, and live in the coral reef.

Octopuses (Octopus vulgaris) is 12 to 36 inches long and weighs 6.6 to 23 pounds. They live in coastal marine waters.

Starfish are from 0.5 to 40 inches across, between 3.3 and 6.6 pounds, and you found them in the kelp forest.-

I am using the case statement with between as:

SELECT *,
CASE 
WHEN
common_name = "clownfish" 
 AND length between 3 AND 7 
 AND weight between 0.2 AND 0.8 
 AND habitat_type = "coral reef"
 THEN 'not impostor'
 ELSE 'impostor'
 END AS impostor_status
FROM marine_life 

it returns all the lenght and weight under or over the given numbers and only gives 'impostor' in the 'impostor_status' I was expecting the server to return, in the 'impostor_status' column, clownfish between the given range as 'not impostor' and clownfish with different numbers than the one given as 'impostor' instead, it is returning all the results as ' impostor' even though length and weight are over or under the given numbers

• John Gordon: Perhaps this is a silly capitalization issue? Is the actual value for common_name in the table clownfish or Clownfish ?
• John Gordon: Also this may not matter, but I would use single quotes for string values, instead of double quotes as you have for "clownfish" and "coral reef".

Answer 1

based on your conditions, this may help you properly assign the impostor status well:

SELECT *,
 CASE
 WHEN common_name = 'clownfish'
 AND length BETWEEN 3 AND 7
 AND weight BETWEEN 0.2 AND 0.8
 AND habitat_type = 'coral reef'
 THEN 'not impostor'
 WHEN common_name = 'octopus' -- Assuming the common name for octopus is 'octopus'
 AND length BETWEEN 12 AND 36
 AND weight BETWEEN 6.6 AND 23
 AND habitat_type = 'coastal marine waters'
 THEN 'not impostor'
 WHEN common_name = 'starfish'
 AND length BETWEEN 0.5 AND 40
 AND weight BETWEEN 3.3 AND 6.6
 AND habitat_type = 'kelp forest'
 THEN 'not impostor'
 ELSE 'impostor'
 END AS impostor_status
FROM marine_life;

This separates WHEN clauses for each animal based on the specified conditions. This way, each condition will be evaluated independently, and the CASE statement will assign the appropriate 'impostor_status' based on the conditions met.

• Emily: Thank you for your help, your SQL is mostly correct, the only thing I had to change was to use OR instead of AND, and the column for the fish name, which was the species_name not the common_name.