[HackerRank][SQL(Oracle)] The PADS

 

※  The OCCUPATIONS table is described as follows: 

Occupationwill only contain one of the following values:Doctor,Professor,SingerorActor.

 

■  [Advanced Select]  The PADS

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true 

Q.

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: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

2. 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.There are a total of [occupation_count] [occupation]s.

Note: There will be at least two entries in the table for each type of occupation.

A.

SELECT		NAME || '(' || SUBSTR(OCCUPATION,1,1) || ')' AS RES
FROM 		OCCUPATIONS
ORDER BY 	NAME;
SELECT 		'There are a total of ' || COUNT(*) || ' ' || LOWER(OCCUPATION) || 's.' AS RES
FROM 		OCCUPATIONS
GROUP BY 	OCCUPATION
ORDER BY 	COUNT(*), OCCUPATION;

(+)

WITH
	NM_AND_OCP AS (
		SELECT	NAME || '(' || LPAD(OCCUPATION,1) || ')' AS RES
		FROM 	OCCUPATIONS
		ORDER BY NAME
	),
	TOT_CNT_AND_OCP AS (
		SELECT	'There are a total of ' || CNT || ' ' || LOWER(OCCUPATION) || 's.' AS RES
		FROM 	(
					SELECT 		OCCUPATION, COUNT(*) AS CNT
					FROM 		OCCUPATIONS
					GROUP BY 	OCCUPATION
				)
		ORDER BY CNT, OCCUPATION
	)
SELECT RES FROM NM_AND_OCP
UNION
SELECT RES FROM TOT_CNT_AND_OCP;