The PADS | Hackerrank

Author: neptune | 21st-Nov-2022
🏷️ #SQL #Hackerrank

Problem Statement: 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: ActorName(A), DoctorName(D), ProfessorName(P), and SingerName(S).


2. Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

Format: There are a total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of 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.


Solution:

For this challenge, we must:

We will write two select queries.


Query 1:

Select concat(name, '(', left(occupation, 1), ')') 

from occupations 

order by name;

Query 2:

Select concat("There ", if(count(1) > 1, "are", "is"), 

              " a total of ", count(1), " ", lower(occupation), 

              if(count(1) > 1, "s.", "."))

from occupations

group by occupation

order by count(occupation);


Output:

Aamina(D)

Ashley(P)

Belvet(P)

Britney(P)

Christeen(S)

Eve(A)

Jane(S)

Jennifer(A)

Jenny(S)

Julia(D)

Ketty(A)

Kristeen(S)

Maria(P)

Meera(P)

Naomi(P)

Priya(D)

Priyanka(P)

Samantha(A)

There are a total of 3 doctors.

There are a total of 4 actors.

There are a total of 4 singers. 

There are a total of 7 professors.



Hope you learn something !!!

If you have any questions let me know in the comment section.




👉 Read More
The Blunder | Hackerrank
5. Solution of Hacker Rank Weather Observation Station 8.
7.Solution of Hacker Rank The Report
Identifying the Odd One Out in a Series of Strings | Hackerrank
4. Solution of Hacker Rank Weather Observation Station 6.
3. Solution of Hacker Rank Weather Observation Station 4.
6. Solution of Hacker Rank Employee Salaries.
1. Basic SQL Select Query of Hacker Rank.
Generate Fibonacci Sequence - JavaScript | Hackerank
Team Formation Hackerrank | Julia
Modified 0-1 knapsack problem | Frsco Play Hackerrank
2. Solution of Hacker Rank Weather Observation Station 3.
Weather Observation Station 18 | Hackerrank
Solving the Ice Cream Parlor Problem | Hackerrank
AngularJS - Know Tech Frameworks | Fresco Play
PySpark Milestone Black Friday Sales Data | Fresco Play Hackerrank
30+ SQL Interview Questions
Backspace String Compare using R | Fresco Play
Top Earners | HackerRank
Python - Number Based Problem | Hackerrank
Explore more Blogs...