Hello coders, In this post, you will learn how to solve the The PADS SQl Hacker Rank Solution. This problem is a part of the SQL Hacker Rank series.
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:
- 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.
The OCCUPATIONS table is described as follows: Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
An OCCUPATIONS table that contains the following records:
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.
The PADS SQL Hacker Rank Solution
SELECT X, Y FROM FUNCTIONS F1 WHERE EXISTS(SELECT * FROM FUNCTIONS F2 WHERE F2.Y = F1.X AND F2.X = F1.Y AND F2.X > F1.X) AND (X != Y) UNION SELECT X,Y FROM FUNCTIONS F1 WHERE X = Y AND ((SELECT COUNT(*) FROM FUNCTIONS WHERE X = F1.X AND Y = F1.X) > 1) ORDER BY X;