From the animal table with animal_name, animal_age, animal_weight, and arrival_d

Question

From the animal table with animal_name, animal_age, animal_weight, and arrival_date

List the following for animals whose arrival date is after ‘March 1, 2018’. You must use that date format and the TO_DATE function. Display name as "NAME", animal age as "AGE" with 'years' appended, and weight as "WEIGHT" with 'lbs' appended.   Note that it displays either ‘year’ or ‘years’ appropriately.

Using CASE, display the residence appropriate for the animal based on weight: Up to 15 lbs for CITY, between 15 and 40 lbs for SUBURB, and anything over 40 lbs as RURAL. Label this column as "Ideal Residence". Order by weight.

Explanation / Answer

SELECT animal_name as NAME,

CASE AGE

WHEN animal_age < 1 THEN animal_age+'year'

WHEN animal_age >1 THEN animal_age+ 'years'

END,

animal_weight+'lbs' as WEIGHT

CASE 'Ideal Residence'

WHEN animal_weight < 15 THEN 'CITY'

WHEN animal_weight>15 AND animal_weight <40 THEN 'SUBURB'

WHEN animal_weight >40 THEN 'RURAL'

END

FROM animal

where to_date(arrial_date,'MON DD,YYYY') > 'March 1,2018'

order by animal_weight

The above query displays animal_name as NAME,append of animal_age+ 'years'

as AGE depends upon age is 1 and ,animal_weight as weight,

Ideal Residence depends upon the condition given from animal table

whose is arrival date is greater than March 1st,2018 sort by animal_weight.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.