HackerRank SQL
The PADS
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)
, andASingerName(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.
Input Format
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Sample Input
An OCCUPATIONS table that contains the following records:
Sample Output
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.
Solution(MySQL):
select concat(name, '(', substring(occupation, 1, 1), ')') as name
from occupations
order by nameselect concat('There are a total of', ' ', count(occupation), ' ',
lower(occupation), 's.') as profession
from occupationsgroup by occupation
order by profession
;
Solution(MS SQL):
with cte as
(
select rank() over (partition by occupation order by name) as rnk,
case when occupation = 'Doctor' then name else null end as Doctor,
case when occupation = 'Professor' then name else null end as Professor,
case when occupation = 'Singer' then name else null end as Singer,
case when occupation = 'Actor' then name else null end as Actor
from occupations
)select min(Doctor), min(Professor), min(Singer), min(Actor)
from cte
group by rnk
;