The Pads HackerRank Solution | By KodeSrc

In this post we will see The Pads HackerRank Solution

Question:

We are given the following question where we have to generate 2 SQL Queries.

1. Generate a query to alphabetically sort the occupations tables bases on the name and format it with the occupation.
2. Find out the count of each profession and sort them in ascending order.

Table Schema:

Sample Data Points:

Solution for The Pads HackerRank Question:

1st Query

The first query is simple, we need to sort the names and append the name with the occupation of that person.

So to append the Occupation to the Name we will use CONCAT() method of MySql.

Since we only need the First letter of the Profession we will slice the occupation and get the first letter by using the SUBSTRING() method.

SQL Statement:

    SELECT CONCAT(Name, '(', SUBSTRING(Occupation, 1, 1), ')') FROM OCCUPATIONS ORDER BY Name;

  

2nd Query

The Second query needs some level of thinking to figure out the solution.

Approach :

• First we need the Count of  professions , so this is simple we will use COUNT() method here.
• Now we need the Count of not all profession but count of each professions, So we will use GROUP BY Occupation along with count. So this will group the records based on professions and then apply the count on it.
• Also we need to sort them based on Count , so we will use ORDER BY clause here. And If count is same we need to sort it alphabetically.
• Finally we need to output in a proper format given in the question, So we will use the CONCAT() and LOWER() methods to create well formatted Output.

SQL Statement:

    SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT(Occupation), LOWER(Occupation);

  

Combined SQL Statement for The Pads HackerRank Question:

    
    SELECT CONCAT(Name, '(', SUBSTRING(Occupation, 1, 1), ')') FROM OCCUPATIONS ORDER BY Name;

    SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT(Occupation), LOWER(Occupation);

  

Result:

Conclusion:

In this post we have successfully solved The Pads hackerrank question and understood the approach for the solution.

Also See:

Revising the Select Query 1 Hackerrank Solution | By KodeSrc

Revising the Select Query 2 Hackerrank Solution | By KodeSrc