1

[IMPORTANT] This is an exercise from the book 'Learning SQL', so I cannot use any other method other than CASE-WHEN. I have a table as follows:

enter image description here

The question is as follows:

Rewrite the following query, which uses a simple case expression, so that the same results are 
achieved using a searched case expression. Try to use as few when clauses as possible.

SELECT name,
CASE name
WHEN 'English' THEN 'latin1'
WHEN 'Italian' THEN 'latin1'
WHEN 'French' THEN 'latin1'
WHEN 'German' THEN 'latin1'
WHEN 'Japanese' THEN 'utf8'
WHEN 'Mandarin' THEN 'utf8'
ELSE 'Unknown'
END character_set
FROM language;

I tried the following:

SELECT name,
CASE name
WHEN language.name IN ('English','Italian','French','German')
THEN 'latin'
WHEN language.name IN ('Japanese','Mandarin')
THEN 'utf8'
ELSE 'Unknown'
END character_set
FROM language;

This is giving me a result that is exactly the opposite of what is mentioned in the conditions, for example it shows 'utf8' for 'English', and 'latin1' for 'Mandarin'. What am I doing wrong?

Rajtilak Bhattacharjee
  • 124
  • 2
  • 13

1 Answers1

3

You can’t use the case expression when value style with anything other than values that are single-value expressions.

You must use the case when condition style (just remove name immediately after CASE):

SELECT name,
CASE
  WHEN name IN ('English','Italian','French','German') THEN 'latin'
  WHEN name IN ('Japanese','Mandarin') THEN 'utf8'
  ELSE 'Unknown'
END character_set
FROM language

Trimmed and formatted for readability.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • With such stupid queries, how long do you think before I can start coding in SQL comfortably? – Rajtilak Bhattacharjee Apr 02 '21 at 11:32
  • 1
    @S3ri0u58lack like most skills, the simplest parts are used most of the time. More difficult/advanced aspects of a skill are used rarely. SQL is no different:: Creating tables and inserting and selecting rows is most of what you’ll do, so you’re probably already able to do useful things with it. If you need help with something a bit more challenging, just come in back here and ask another question! – Bohemian Apr 02 '21 at 11:41