CODEWITHSUNDEEP
sqlcasebetween
Emily
Emily

Reputation: 1

CASE statement using between

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

Upvotes: 0

Views: 153

Answers (1)

jeffreyohene
jeffreyohene

Reputation: 606

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.

Upvotes: 1

Related Questions