Hello Programmers/Coders, Today we are going to share solutions of Programming problems of HackerRank of Programming Language SQL. At Each Problem with Successful submission with all Test Cases Passed, you will get an score or marks. And after solving maximum problems, you will be getting stars. This will highlight your profile to the recruiters.
In this post, you will find the solution for New Companies in SQL-HackerRank Problem. We are providing the correct and tested solutions of coding problems present on HackerRank. If you are not able to solve any problem, then you can take help from our Blog/website.
Use “Ctrl+F” To Find Any Questions Answer. & For Mobile User, You Just Need To Click On Three dots In Your Browser & You Will Get A “Find” Option There. Use These Option to Get Any Random Questions Answer.
Introduction To SQL
SQL stands for Structured Query Language. SQL is used to create, remove, alter the database and database objects in a database management system and to store, retrieve, update the data in a database. SQL is a standard language for creating, accessing, manipulating database management system. SQL works for all modern relational database management systems, like SQL Server, Oracle, MySQL, etc.
- It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
- All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language.
- SQL allows users to query the database in a number of ways, using English-like statements.
Link for the Problem – New Companies SQL – Hacker Rank Solution
New Companies SQL – Hacker Rank Solution
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.
Note:
- The tables may contain duplicate records.
- The company_code is 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.
Input Format
The following tables contain company data:
- Company: The company_code is the code of the company and founder is the founder of the company.
- Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.
- Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
- Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
-
- Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
-
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
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.
New Companies SQL – Hacker Rank Solution
SELECT C.COMPANY_CODE,
C.FOUNDER,
(SELECT COUNT(DISTINCT LEAD_MANAGER_CODE)
FROM LEAD_MANAGER L
WHERE L.COMPANY_CODE = C.COMPANY_CODE),
(SELECT COUNT(DISTINCT SENIOR_MANAGER_CODE)
FROM SENIOR_MANAGER S
WHERE S.COMPANY_CODE = C.COMPANY_CODE),
(SELECT COUNT(DISTINCT MANAGER_CODE)
FROM MANAGER M
WHERE M.COMPANY_CODE = C.COMPANY_CODE),
(SELECT COUNT(DISTINCT EMPLOYEE_CODE)
FROM EMPLOYEE E
WHERE E.COMPANY_CODE = C.COMPANY_CODE)
FROM COMPANY C
ORDER BY C.COMPANY_CODE ASC;
