-
Notifications
You must be signed in to change notification settings - Fork 0
/
the-pads.sql
47 lines (40 loc) · 1.53 KB
/
the-pads.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
-- Difficulty: Medium
-- Language: Oracle
-- Generate the following two result sets:
-- Query an alphabetically ordered list of all names in OCCUPATIONS,
-- immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses).
-- For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
-- Query the number of ocurrences of each occupation in OCCUPATIONS.
-- Sort the occurrences in ascending order, and output them in the following format:
-- There are a total of [occupation_count] [occupation]s.
-- where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name.
-- If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.
-- Note: There will be at least two entries in the table for each type of occupation.
SELECT NAME || '(' || SUBSTR(upper(OCCUPATION), 1, 1) || ')'
FROM OCCUPATIONS
ORDER BY NAME, OCCUPATION;
SELECT 'There are a total of ' || COUNT(NAME) || ' ' || lower(OCCUPATION) || 's.'
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(NAME), OCCUPATION;
-- Expected Output:
-- Aamina(D)
-- Ashley(P)
-- Belvet(P)
-- Britney(P)
-- Christeen(S)
-- Eve(A)
-- Jane(S)
-- Jennifer(A)
-- Jenny(S)
-- Julia(D)
-- Ketty(A)
-- Kristeen(S)
-- Maria(P)
-- Meera(P)
-- Naomi(P)
-- Priya(D)
-- Priyanka(P)
-- Samantha(A)
-- There are a total of 3 doctors.
-- There are a total of 4 actors. {-truncated-}