info_430_project/lab_ec_2_4.sql at master · datalex88/info_430_project · GitHub

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
USE UNIVERSITY
/*
Alex Davis
Feb 04 2020

If you have the letters "GH" anywhere in your last name, tblStudent, "Silent"
If you have a combination, "BG" in your last name, "Difficult"
"AS" as "Common"
"F" as "Funny"
else "Unknown"
*/
GO

SELECT COUNT(StudentID) AS CountOfAliases,
CASE
    WHEN StudentLname LIKE '%AS%' THEN 'Silent'
    WHEN StudentLname LIKE '%BG%' THEN 'Difficult'
    WHEN StudentLname LIKE '%AS%' THEN 'Common'
    WHEN StudentLname LIKE '%F%' THEN 'Funny'
    ELSE 'Unknown'

END AS QuantityOfNickNames
FROM tblSTUDENT
GROUP BY
(
    CASE
    WHEN StudentLname LIKE '%AS%' THEN 'Silent'
    WHEN StudentLname LIKE '%BG%' THEN 'Difficult'
    WHEN StudentLname LIKE '%AS%' THEN 'Common'
    WHEN StudentLname LIKE '%F%' THEN 'Funny'
    ELSE 'Unknown'
    END
)
ORDER BY QuantityOfNickNames DESC

SELECT * FROM tblSTUDENT