I am trying to solve HackerRank SQL - The PADS question.

The Question is:

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.

My Solution is:

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

OP:
ERROR at line 4:
ORA-00933: SQL command not properly ended
(It seems, we cannot use ORDER BY BEFORE UNION)

I revised my code to:

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

OP:
ERROR at line 7:
ORA-00904: "NAME": invalid identifier

Please, help me out here.

CodePudding user response：

Generate the following two result sets

You are NOT generating two result sets. You are performing two SELECTs and trying to merge them into a single result set using UNION and that is not what the question asks for. Stop using UNION and use two queries.

The first result set would be:

SELECT NAME || '(' || SUBSTR(OCCUPATION,1,1) || ')' 
FROM   OCCUPATIONS 
ORDER BY NAME;

The second result set would be:

SELECT 'There are a total of ' || COUNT(OCCUPATION) || ' ' || LOWER(OCCUPATION) || 's.' 
FROM OCCUPATIONS 
GROUP BY OCCUPATION

and then you need to ORDER BY the number of occurrences AND then by the occupation name (which I leave to you to solve).

CodePudding user response：

Since you're wanting to output two ordered sets of data in one query, the easiest way is to assign an identifier to each query and then order by that and the column you want to order by, e.g.:

SELECT info
FROM   (SELECT 1 qry, NAME || '(' || SUBSTR(OCCUPATION,1,1) || ')' info
        FROM   OCCUPATIONS
        UNION ALL
        SELECT 2 qry, 'There are a total of ' || COUNT(OCCUPATION) || ' ' ||  LOWER(OCCUPATION) || 's.' info
        FROM OCCUPATIONS
        GROUP BY OCCUPATION)
ORDER BY qry, info;

Note that, because the two queries aren't going to return the same rows, I've used a UNION ALL, since a UNION does a DISTINCT on the resultant data set, whereas UNION ALL doesn't. Also, I'm assuming that if you had two different people with the same name and occupation (e.g. different birth dates), you should output both rows, rather than one row?

Note also that when you have a UNION/UNION ALL query, the output columns inherit the column name from the first query, which is why your second query was giving you the invalid identifier error (you hadn't given your column an alias!).