FAQ: Queries - Case

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises
Community%20FAQs%20on%20Codecademy%20Exercises1000×208 141 KB

This community-built FAQ covers the “Case” exercise from the lesson “Queries”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise Case

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

2

How or where do I input ORDER BY, as I’d like to see Chill first then Intense?

5 Likes
3

Insert the line after FROM movies and end that line with the semi-colon, not this one.

4

Thanks but it’s not working. Can’t get all the "Chills’ together. This seems to always sort by genre:

SELECT name,

 CASE
  WHEN genre = 'romance' THEN 'Chill'
  WHEN genre = 'comedy'  THEN 'Chill'
  ELSE 'Intense'
 END AS 'Mood'

FROM movies
WHERE genre IS NOT NULL 
ORDER BY 'Mood';

If it is using the genre to order rows, how can I bring up ‘comedy’ then ‘romance’ then the rest in that order?

5

Column headings are not strings.

ORDER BY Mood;

It is not in this instance. The two columns are name and Mood.

If we wanted the names to be sorted as well as the mood,

ORDER BY Mood, name;

First, examine the Schema

Database Schema
movies 230 rows
id	INTEGER
name	TEXT
genre	TEXT
year	INTEGER
imdb_rating	REAL

If we wish to sort by genre, then we need to include that column in the selection.

SELECT name, genre,

and in the sort line,

ORDER BY Mood, genre, name;
4 Likes
6

Hi,

I wonder why there has to be a comma after ‘SELECT name’ from the code below.
And also curious about why ‘FROM movie’ has to be at the end.

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END AS ‘Review’
FROM movies;

Thanks,
Sihyeon

3 Likes
7

Because it separates the name from the CASE clause, which is also a parameter of the SELECT clause. Everything before FROM is part of SELECT.

3 Likes
8

Aha! Thank you soooo much!:slight_smile:

1 Like
9

In this example, is it not possible to use OR along with the CASE statement. Something like below.
SELECT name,
CASE
WHEN genre = ‘romance’ OR genre = ‘comedy’ THEN ‘Chill’
ELSE ‘Intense’
END AS ‘Mood’
FROM movies;

10

Hi, I’m very curious, why this code:
SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END
FROM movies;

print a column with “CASE WHEN imdb_rating > 8 THEN 'Fantastic’ WHEN imdb_rating > 6 THEN ‘Poorly Received’ ELSE 'Avoid at All Costs’ END AS ‘Review’ “ as column name?

11

You need to add the AS statement after END.

12

HELLO,

Why do we write 2 when conditions and not just one with a OR condition? :

WHEN genre = ‘romance’ THEN ‘Chill’
WHEN genre = ‘comedy’ THEN ‘Chill’

WHEN genre = ‘romance’ OR 'comedy; THEN ‘Chill’

13

@mtf may you explain more on how the syntax ‘,’ the commas, need to be used by giving few more examples?

14

SQL SELECT

15

seems to work for me. Did you try it yourself?

16

No reason. Both is possible. Just please replace that semicolon with a single quote (’) in your code !

17

I am getting an Error when I wish to Limit the records using LIMIT query for the following SQL code:-

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END
FROM movies;

Whenever I use LIMIT 10; it gives an error.

18

image

  • If the rating is above 8, then it is Fantastic.
  • If the rating is above 6, then it is Poorly Received.
  • Else, Avoid at All Costs.

A rating of 9 is both above 6 and 8. Shouldn’t the conditions be x>8, 6<x<=8? Or does the order of coding matter?

Thanks

19

It’s either greater than eight or it’s not. Failing that it’s either greater than six, or it’s not. Everything falls through naturally without any between tests.

1 Like
20

SELECT name,

Why do i need to put the comma after the name ? I tried by SELECT id,name -it still required a comma after the last column name. Why is that so ?