Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | The PADS Solution

Also Visit:ย  Type of Triangle Solution

#### Problem

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.

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.```

Explanation

The results of the first query are formatted to the problem descriptionโs specifications.
The results of the second query are ascendingly ordered first by number of names corresponding to each profession (2 <= 2 <= 3 <= 3), and then alphabetically by profession (doctorย <=ย singer, andย actor <= professor).

## Solution โ The PADS Solution

##### MySQL Code
```SELECT concat(NAME,concat("(",concat(substr(OCCUPATION,1,1),")")))
FROM OCCUPATIONS ORDER BY NAME ASC;

SELECT "There are a total of ", count(OCCUPATION),
concat(lower(occupation),"s.") FROM OCCUPATIONS
GROUP BY OCCUPATION ORDER BY count(OCCUPATION),
OCCUPATION ASC```

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.