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.
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer, or Actor.
An OCCUPATIONS table that contains the following records:
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.
For this challenge, we must:
We will write two select queries.
Select concat(name, '(', left(occupation, 1), ')')
order by name;
Select concat("There ", if(count(1) > 1, "are", "is"),
" a total of ", count(1), " ", lower(occupation),
if(count(1) > 1, "s.", "."))
group by occupation
order by count(occupation);
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.