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
First Query
- The first query wants to display the Name of the person and only the first letter of the Occupation.
- To find the first letter of occupation we can use LEFT() function of MYSQL and SUBSTR() function of Oracle.
- To give output as “Name(O)”, we can use CONCAT() function in MYSQL and CONCAT() function or concantenation operator “||” in ORACLE.
- We need to order the result by alphabetically by name, for that we can use ORDER BY NAME
MySQL
SELECT CONCAT(NAME,'(',LEFT(OCCUPATION, 1),')')
FROM OCCUPATIONS
ORDER BY NAME;
Oracle
SELECT NAME || '(' || SUBSTR(OCCUPATION, 0, 1) || ')'
FROM OCCUPATIONS
ORDER BY NAME;
Second Query
- To find the number of occurrences of each occupation, we can use the COUNT(*) function along with GROUP BY clause.
- To give output as “There are a total of 6 doctors.”, we can use CONCAT() function in MYSQL and CONCAT() function or concantenation operator “||” in ORACLE.
- And we can use LOWER() function to convert “Doctor” to “doctor”.
- To order by number of occurrences of occupation first and alphabetically by OCCUPATION second we can use ORDER BY COUNT(*), OCCUPATION.
MySQL
SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(*), OCCUPATION;
Oracle
SELECT 'There are a total of ' || COUNT(*) || ' ' || LOWER(OCCUPATION) || 's.'
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(*), OCCUPATION;
Source – Hackerrank