Loading
Lesson 41
Courses / HackerRank SQL Problem Solving Solution Challenges
The PADS | SQL Advanced Select | HackerRank Solution

Summary

SQL HackerRank Exercises Summary

Overview

The presenter tackles a set of SQL exercises from HackerRank, emphasizing the importance of breaking down complex problems into smaller parts. The session covers two main queries related to a table named occupations.

Query 1: Listing Names with Occupation Initials

  • Objective: Retrieve a list of all names in the occupations table, accompanied by the first letter of each person's occupation in parentheses.
  • Steps:
    1. Select the name column from the occupations table.
    2. Use the LEFT function to extract the first letter of the occupation column.
    3. Combine the name and the first letter of the occupation using the CONCAT function, placing the letter in parentheses.
    4. Sort the results alphabetically by name.

SQL Code Example

SELECT CONCAT(name, ' (', LEFT(occupation, 1), ')') 
FROM occupations 
ORDER BY name;

Query 2: Count of Each Occupation

  • Objective: Count the occurrences of each occupation and display a formatted message.
  • Steps:
    1. Select the occupation column and use COUNT to get the occurrences of each occupation.
    2. Use GROUP BY to group the results by occupation.
    3. Format the output message with the CONCAT function: "There are a total of X occupations".
    4. Sort the results first by the count of occurrences in ascending order, then alphabetically by occupation if counts are the same.

SQL Code Example

SELECT CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's') 
FROM occupations 
GROUP BY occupation 
ORDER BY COUNT(occupation), occupation;

Conclusion

The presenter successfully demonstrates how to approach SQL problems methodically, resolving each query step by step and ensuring proper syntax for accurate results.

Video Transcript

Hey everybody, I will do the pads and exercise from the section I've been selected of SQL HackerRank. So this problem is actually very simple, but there's a lot of things going on, a lot of parts. So because it's a big problem, with many things we have to solve, whenever we have a big problem, you got to break it down to smaller parts. So let's do it little by little until we get to the final solution. So we have two queries. So query number one and query number two. Let's start with the first one, query number one. So we have the table occupations, it has the column's name and occupation, for example, the name of a person and what they do as their vocation. So query, unophobetically, or the list of all names in occupations. Okay, how can we do this? Let's start with just this sentence. I'm going to choose my SQL. So we need the list of all names in occupations. So I'm going to say select the name column from occupations table. Okay, we got that down. Now immediately followed by the first letter of each profession as a parenthetical in closed parentheses. What does that mean? Okay, so if you have like a name John and he is an architect, we're going to take the letter A, that's the first letter of the profession. We're going to put that in parentheses like this, and the rest with the scarred. Okay, and we just add it to the right of the name. That's what it means. Okay, how can we do that? Well, we can use combine the name with the occupation column value with just the first letter in parentheses. So let's just do the occupation, right? So we had occupation. How can we just extract the first letter of occupation? We can say use a function left comma one here. Left means you're going to take starting from the left, take one character of the occupation, in this case, the very first letter. That's fine, but we need to put that in parentheses. So how can we do that? So I'm going to combine the that with the name, I'm going to use concat function. So that way it takes the name. And then immediately after it's going to put the very first letter, but we need the open parentheses and the closing one. So in between, I'm going to add open parentheses as a string comma, and then after the left occupation commas one, close parenthesis there, I'm going to add a string with the quotes, single quotes, and then close parentheses like so. Okay. And let's run the code and see what we have. Okay, so that's what it looks like. Let's sort alphabetically. So alphabetically, by the names, we're going to do order by the name. Okay. Now, that solves the very first query. Now let's go on to the next query. So the next query here query the number of occurrences of each occupation occupations. You're going to select from occupations table. Now the number of occurrences of each occupation. So if you just select the occupation itself, you're going to get a list of names of occupations. Some people might have the same occupation, right? So what we have to do is group by, okay, group by that occupation. And then we need to get the count of each. So I'm going to do your count of occupation. And at the end here, I'm going to just group by occupation. Let's see what we get. And let me just comment out with dash dash space for the time being temporarily. So we just see the second query. Okay. So as you can see, we come, we grouped all the rows so that we can get account for each of the professions. But it doesn't want us to do that. It wants us to display this message here for each of these rows. How can we do that? Well, we're going to go to the select part here, and we're going to make this message with the concat function we learned before. So let's just start with concat here. Enclose everything in concat. Now I'm going to start with a string. I'm going to add single quotes, single quotes, comma, I'm going to say there are a total of space. That's the first part of this thing. And then it follows by the count of occupation. We got that here. So I'm going to cut and place it right after the comma so that when we do the concatination, it's going to say the count right after the space, the of space. And then after that, there's another space. So after the count, comma, space, comma, space under quotes, and then occupation s. So we have occupation there. Now we've got to add to the end s. Now, occupation is in lowercase. How can we make occupation lowercase? We call the function lower on the occupation. Like that. Now it asks us to sort the occurrences in ascending order. Okay. So I'm going to add a sort here, order by, right? Let me add an order by before the group by here. Order by. Now I'm going to order by the count of occupation. Okay? Now they say if more than one occupation has the same name, the same count, right? This should be ordered alphabetically. So that means if the count is the same, I add a new second set of order by here. That's going to be by occupation itself by the name. So that if you have like architect three, programmer three, because they have the same count, we're going to order them by the name, which architect comes first and then programmer afterwards because a comes before p. Okay. And we have an error in the syntax because let me add the order by after group by. Maybe that's the problem. There you go. So there are total three doctors for actors for singers, some professors, as you can see, you got everything the count space and then the name and then followed by s dot. Now let's uncomment the very first query. And I'm going to run the code. And there you go.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: