-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFANCY_STATEMENTS_11.sql
49 lines (43 loc) · 1.35 KB
/
FANCY_STATEMENTS_11.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- ------------------------------------
-- WHEN-ELSE STATEMENT------------------
-- ------------------------------------
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
WHEN Num > 330 THEN 'The num is greater than 330'
WHEN Num = 330 THEN 'The quantity is 330'
ELSE 'The quantity is under 330'
-- ------------------------------------
-- CASE STATEMENT--works as if-else ---
-- ------------------------------------
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
-- -----------------------------------------
-- NESTED CASE STATEMENT--works as if-else--
-- -----------------------------------------
-- given three sides, determine if its a triangle or not
-- as well as what type of triangle it is.
SELECT
CASE
WHEN (T.A + T.B <= T.C) OR (T.B + T.C <= T.A) OR (T.A + T.C <= T.B) THEN "Not A Triangle"
ELSE
CASE
WHEN (T.A = T.B) AND (T.B = T.C) THEN "Equilateral"
WHEN (T.A <> T.B) AND (T.B <> T.C) AND (T.A <> T.C) THEN "Scalene"
ELSE "Isosceles"
END
END
FROM TRIANGLES T;
-- -----------------------------------------
SELECT Name, City, Country
FROM Consumers C
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);