1SELECT CASE lower(col1)
2 WHEN 'agree' THEN 'Ok'
3 WHEN 'disagree' THEN 'Ko'
4 ELSE
5 CASE
6 WHEN col2 = 1 THEN 'Ko'
7 ELSE 'Maybe'
8 END
9END AS my_result
10FROM table_name;
1mysql> SELECT CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END;
2+-------------------------------------------------------------+
3| CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END |
4+-------------------------------------------------------------+
5| this is false |
6+-------------------------------------------------------------+
1-- LABEL MOVIE LENGTH
2select title, length,
3CASE
4 when length < 50 then 'very short film'
5 when length < 90 then 'short film'
6 when length < 120 then 'medium length film'
7 when length > 120 then 'long film'
8 else 'unknown length'
9END -- AS film_length
10from film;
11
12-- SAME AS ABOVE BUT ON A CUSTOM NAMED COLUMN
13select title, length,
14CASE
15 when length < 50 then 'very short film'
16 when length < 90 then 'short film'
17 when length < 120 then 'medium length film'
18 when length > 120 then 'long film'
19 else 'unknown length'
20END AS film_length
21from film;
22
23-- RESULT
24+-----------------------------+--------+--------------------+
25| title | length | film_length |
26+-----------------------------+--------+--------------------+
27| ACADEMY DINOSAUR | 86 | short film |
28| ACE GOLDFINGER | 48 | very short film |
29| ADAPTATION HOLES | 50 | short film |
30| AFFAIR PREJUDICE | 117 | medium length film |
31| AFRICAN EGG | 130 | long film |
32| AGENT TRUMAN | 169 | long film |
33| AIRPLANE SIERRA | 62 | short film |
34| AIRPORT POLLOCK | 54 | short film |
35| ALABAMA DEVIL | 114 | medium length film |
36| ALADDIN CALENDAR | 63 | short film |
37| ALAMO VIDEOTAPE | 126 | long film |
38| ALASKA PHANTOM | 136 | long film |
39| ALI FOREVER | 150 | long film |
40| ALICE FANTASIA | 94 | medium length film |
41| ALIEN CENTER | 46 | very short film |
42
1SELECT
2t2.company_name,
3t2.expose_new,
4t2.expose_used,
5t1.title,
6t1.status,
7 CASE status
8 when 'New' and t2.expose_new = 1 then 1
9 when 'New' and t2.expose_new = 2 then 2
10 when 'New' and t2.expose_new = 3 then 3
11 when 'Used' and t2.expose_used = 1 then 1
12 when 'Used' and t2.expose_used = 2 then 2
13 when 'Used' and t2.expose_used = 3 then 3
14END as expose
15FROM `products` t1
16join manufacturers t2 on t2.id = t1.seller
17where t1.seller = 4238
1
2 SELECT
3CASE
4 WHEN Quantity > 30
5 THEN "The quantity is greater than 30"
6 WHEN Quantity =
7 30 THEN "The quantity is 30"
8 ELSE "The quantity is
9 under 30"
10END
11FROM OrderDetails;