HackerRank 'The Pads' MySQL

PROBLEM

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 in parentheses. For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
Query the number of occurrences 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 occupation type.

Input Format

Column	Type
Name	String
Occupation	String

The OCCUPATIONS table is described as follows:

Name	Occupation
Samantha	Doctor
Juia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Prya	Singer

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

SELECT 
    CONCAT(
        name,
        '(', 
        SUBSTR( 
            occupation,
            1,
            1),
        ')'
    )
FROM occupations
ORDER BY 
    name ASC;
SELECT
    CONCAT(
        'There are a total of',
        ' ',
        COUNT(occupation),
        ' ',
        LOWER(occupation),
        's.'
    )
FROM occupations
GROUP BY 
    occupation
ORDER BY 
    COUNT(
        occupation
        ) ASC;

EXPLANATION
The problem requires generating two result sets based on the given table, which contains two columns: Name and Occupation.
The Occupation column can only have four possible values: Doctor, Professor, Singer, or Actor.
In the first query, list all the names from the OCCUPATION table in alphabetical order, and for each name, append the first letter of the occupation in parentheses.
In the second query, we need to count the number of occurrences of each occupation type and list them in ascending order by count. If multiple occupations have the same count, they should be ordered alphabetically by occupation name.

The output should follow this format: There are a total of [n] [occupation]s.

The exercise expects two separate queries to be written.

First Query

SELECT 
    CONCAT(
        name,
        '(', 
        SUBSTR( 
            occupation,
            1,
            1),
        ')'
    )
FROM occupations
ORDER BY 
    name ASC;

This returns all the names with the initial of their occupation in parentheses, sorted alphabetically by name.
The SUBSTR function extracts specific characters from each name, and the CONCAT function combines all the strings.

Second Query

SELECT
    CONCAT(
        'There are a total of',
        ' ',
        COUNT(occupation),
        ' ',
        LOWER(occupation),
        's.'
    )
FROM occupations
GROUP BY 
    occupation
ORDER BY 
    COUNT(
        occupation
    ) ASC;

The COUNT function counts the number of people in each profession.
The LOWER function converts each occupation to lowercase.
Here, we need not only to count how many people belong to each occupation but also to display these results in a specific order, sorted by the number of people in each occupation and in ascending ASC order.
If there’s a tie, such as having 3 doctors and 3 actors, the results should be sorted alphabetically by the occupation name.
The ORDER BY COUNT( ) ASC clause sorts these groups in ascending order based on the number of people. The occupation column serves as the tiebreaker; if two professions have the same count, the results are sorted alphabetically by occupation name.