The PADS | Hackerrank

Problem Statement: Generate the following two result sets:

1. 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: ActorName(A), DoctorName(D), ProfessorName(P), and SingerName(S).

2. Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

Format: There are a total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of 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:

For this challenge, we must:

We will write two select queries.

Query 1:

Select concat(name, '(', left(occupation, 1), ')') 

from occupations 

order by name;

Query 2:

Select concat("There ", if(count(1) > 1, "are", "is"), 

              " a total of ", count(1), " ", lower(occupation), 

              if(count(1) > 1, "s.", "."))

from occupations

group by occupation

order by count(occupation);

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.

There are a total of 4 singers. 

There are a total of 7 professors.

Hope you learn something !!!

If you have any questions let me know in the comment section.