HackerRank MySQL - The PADS
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), and ASingerName(S).
Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
- /*
- Enter your query here and follow these instructions:
- 1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
- 2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"
- 3. Type your code immediately after comment. Don't leave any blank line.
- */
-
- SELECT NAME || '(' || SUBSTR(OCCUPATION, 1, 1) || ')'
- FROM OCCUPATIONS
- ORDER BY NAME;
-
-
- SELECT 'There are a total of ' || X.O_COUNT || ' ' || LOWER(X.OCCUPATION) || 's.'
- FROM
- (SELECT OCCUPATION,
- COUNT(OCCUPATION) AS O_COUNT
- FROM OCCUPATIONS
- GROUP BY OCCUPATION) X
- ORDER BY X.O_COUNT,
- X.OCCUPATION;
