( SELECT CONCAT(Name, '(', SUBSTRING(Occupation, 1, 1), ')') AS CustomOccupation FROM OCCUPATIONS ORDER BY CustomOccupation ASC ) UNION ALL ( SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT(Occupation) ASC, LOWER(Occupation) ASC ) ORDER BY CustomOccupation ASC;

FOR ORACLE Solution

SELECT name || '(' || SUBSTR(occupation, 1, 1) || ')' AS output FROM occupations ORDER BY name;

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

In case you're stuck

SELECT CONCAT(name,"(", LEFT(occupation, 1), ")")
FROM occupations
ORDER BY name;

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

Dear HackerRank Support Team, I've identified an inconsistency in the "Query Solved" challenge involving the OCCUPATIONS table. When examining the data using simple queries, I've found that the occupation assignments in the database don't match the expected output shown in the sample results. For example:

When I run: SELECT Name, Occupation FROM OCCUPATIONS; I get results showing Ashley as Professor, Samantha as Actor, etc. But when I run: SELECT Occupation FROM OCCUPATIONS; I get just a list of occupations that doesn't seem to align with the names in the expected output. The test case shows that Jane should be (S) for Singer, but in the data I can access, Jane appears to have a different occupation.

This inconsistency makes it impossible to produce the exact output specified in the sample results. The query structure appears correct: