My quest to learn SQL

Photo by Avel Chuklanov on Unsplash

I recently took a HackerRank coding challenge and failed to correctly link and query three tables to pull and display the requested data. As a result, I decided to continue doing HackerRank SQL problems to continue learning.

Here is my solution to the most recent problem I solved and a breakdown of how I got to this solution.

The problem:

Amber’s conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Tables:

Company

Lead_Manager

Senior_Manager

Manager

Employee

Note:

• The tables may contain duplicate records.
• The company_code is a string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

Sample Input

Company Table:

Lead_Manager Table:

Senior_Manager Table:

Manager Table:

Employee Table:

Sample Output

C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2

Remember that our output should have the following columns in this order:

company_code | founder name | total number of lead managers | total number of senior managers | total number of managers |total number of employees

Explanation

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

Spoiler — my solution comes next, so pause here if you would like to solve this on your own first.

Photo by Olav Ahrens Røtne on Unsplash

The basic syntax for a SQL query:

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

My Solution:

Let’s break this down.

The company code and founder name are the first two columns in our output. We are pulling these two columns from the company table.

As a side note, In order to keep our code short and readable, we can use a table alias for each table name, rather than typing the entire table name.

To assign an alias to a table, we can use the following syntax:

table_name AS table_alias

In this syntax, the AS keyword is optional, so we can omit it like this:

table_name table_alias

In our problem, we can use the following table aliases: Company = c, Lead_Manager = l, Senior_Manager = s, Manager = m, Employee = e.

In addition to the first two columns, company_code and founder name, which pull from the company table, we need to select the remainder of our columns:

#3: total number of lead managers from the Lead_Manager table (l)
#4: total number of senior managers from the Senior_Manager table (s)
#5: total number of managers from the Manager table (m)
#6: total number of employees from the Employee table (e)

Here we are counting the total number of distinct entries for each column in order to not count duplicate entries. We are pulling these fields from their respective tables and assigning the table aliases.

Now we need to join the tables together.

There are many ways to join tables. One way is to join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.

Now let’s group our data by the company code and the founder. The GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

Finally, we need to order the output by ascending company_code. The ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Unless otherwise requested, the ORDER BY clause will automatically sort in ascending order.

Let’s see our output:

Success!

Photo by Austin Schmid on Unsplash