Count the employees in SQL hackerrank github

Last update on August 19 2022 21:51:35 (UTC/GMT +8 hours)

Write a query to get the department name and number of employees in the department.

Sample table: employees

Sample table: departments

Code:

SELECT department_name AS 'Department Name', COUNT(*) AS 'No of Employees' FROM departments INNER JOIN employees ON employees.department_id = departments.department_id GROUP BY departments.department_id, department_name ORDER BY department_name;

Sample Output:

Department Name No of Employees Accounting 2 Administration 1 Executive 3 Finance 6 Human Resources 1 IT 5 Marketing 2 Public Relations 1 Purchasing 6 Sales 34 Shipping 45

MySQL Code Editor:

Structure of 'hr' database :

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a query to find the name (first_name, last_name) and hire date of the employees who was hired after 'Jones'.
Next:Write a query to find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90 from job history.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

• Exercises: Weekly Top 16 Most Popular Topics
• SQL Exercises, Practice, Solution - JOINS
• SQL Exercises, Practice, Solution - SUBQUERIES
• JavaScript basic - Exercises, Practice, Solution
• Java Array: Exercises, Practice, Solution
• C Programming Exercises, Practice, Solution : Conditional Statement
• HR Database - SORT FILTER: Exercises, Practice, Solution
• C Programming Exercises, Practice, Solution : String
• Python Data Types: Dictionary - Exercises, Practice, Solution
• Python Programming Puzzles - Exercises, Practice, Solution
• C++ Array: Exercises, Practice, Solution
• JavaScript conditional statements and loops - Exercises, Practice, Solution
• C# Sharp Basic Algorithm: Exercises, Practice, Solution
• Python Lambda - Exercises, Practice, Solution
• Python Pandas DataFrame: Exercises, Practice, Solution
• Conversion Tools
• JavaScript: HTML Form Validation

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.

Sample Input

Company Table:

Lead_Manager Table:

Senior_Manager Table:

Manager Table:

Employee Table:

Sample Output

Logic:

The output table have company_code and founder, and the count for lead_manager, senior_manager, manager, and employee.

Hence, we have to use the company_code and founder in the Company table, and then join the Company table with the other four tables.

Solution:

There are two solutions I have read about. One is to use “select … where …” clause, while the other is to use “join…on…”. I prefer the latter.

Question Link

Sources

SQL problem page: //www.hackerrank.com/domains/sql

1. Revising the Select Query I

//www.hackerrank.com/challenges/revising-the-select-query/problem

select * from CITY where COUNTRYCODE='USA' and POPULATION>100000;

2. Revising the Select Query II

//www.hackerrank.com/challenges/revising-the-select-query-2/problem

select NAME from CITY where COUNTRYCODE='USA' and POPULATION>120000;

3. Select all

//www.hackerrank.com/challenges/select-all-sql/problem

select * from CITY;

4. Select by ID

//www.hackerrank.com/challenges/select-by-id/problem

select * from city where id=1661;

5. Japanese Cities’ Attributes

//www.hackerrank.com/challenges/japanese-cities-attributes/problem

select * from city where countrycode='JPN';

6. Japanese Cities’ Names

//www.hackerrank.com/challenges/japanese-cities-name/problem

select name from city where countrycode='JPN';

7. Weather Observation Station 1

//www.hackerrank.com/challenges/weather-observation-station-1/problem

select city, state from station;

8. Weather Observation Station 3

//www.hackerrank.com/challenges/weather-observation-station-3/problem

select distinct city from station where mod(id, 2) = 0;

9. Weather Observation Station 4

//www.hackerrank.com/challenges/weather-observation-station-4/problem

select count(city) - count(distinct city) from station;

10. Weather Observation Station 5

//www.hackerrank.com/challenges/weather-observation-station-5/problem

select * from(select distinct city,length(city) from station order by length(city) asc,city asc) where rownum=1 union select * from(select distinct city,length(city) from station order by length(city) desc,city desc) where rownum=1;

11. Weather Observation Station 6

//www.hackerrank.com/challenges/weather-observation-station-6/problem

select distinct city from station where regexp_like(city, '^[aeiouAEIOU]');

12. Weather Observation Station 7

//www.hackerrank.com/challenges/weather-observation-station-7/problem

select distinct city from station where regexp_like(city, '*[aeiouAEIOU]$');

13. Weather Observation Station 8

//www.hackerrank.com/challenges/weather-observation-station-8/problem

select distinct city from station where regexp_like(city, '^[aeiouAEIOU].*[aeiouAEIOU]$');

14. Weather Observation Station 9

//www.hackerrank.com/challenges/weather-observation-station-9/problem

select distinct city from station where regexp_like(city, '^[^aeiouAEIOU]');

15. Weather Observation Station 10

//www.hackerrank.com/challenges/weather-observation-station-10/problem

select distinct city from station where regexp_like(city, '*[^aeiouAEIOU]$');

16. Weather Observation Station 11

//www.hackerrank.com/challenges/weather-observation-station-11/problem

select distinct city from station where regexp_like(city, '^[^aeiouAEIOU]|*[^aeiouAEIOU]$');

17. Weather Observation Station 12

//www.hackerrank.com/challenges/weather-observation-station-12/problem

select distinct city from station where regexp_like(city, '^[^aeiouAEIOU].*[^aeiouAEIOU]$');

18. Employee Names

//www.hackerrank.com/challenges/name-of-employees/problem

select name from employee order by name;

19. Employee Salaries

//www.hackerrank.com/challenges/salary-of-employees/problem

select name from employee where salary > 2000 and months < 10 order by employee_id;

20. Type of Triangle

//www.hackerrank.com/challenges/what-type-of-triangle/problem

SELECT CASE WHEN A + B > C THEN CASE WHEN A = B AND B = C THEN 'Equilateral' WHEN A = B OR B = C OR A = C THEN 'Isosceles' WHEN A != B OR B != C OR A != C THEN 'Scalene' END ELSE 'Not A Triangle' END FROM TRIANGLES;

21. Revising Aggregations – The Count Function

//www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

select count(countrycode) from city where population > 100000;

22. Revising Aggregations – The Sum Function

//www.hackerrank.com/challenges/revising-aggregations-sum/problem

select sum(population) from city where district = 'California';

23. Revising Aggregations – Averages

//www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

select avg(population) from city where district='California';

24. Average Population

//www.hackerrank.com/challenges/average-population/problem

select floor(avg(population)) from city;

25. Higher Than 75 Marks

//www.hackerrank.com/challenges/more-than-75-marks/problem

select name from students where marks>75 order by substr(name, -3), id;

26. Japan Population

//www.hackerrank.com/challenges/japan-population/problem

select sum(population) from city where countrycode='JPN';

27. Population Density Difference

//www.hackerrank.com/challenges/population-density-difference/problem

select max(population) - min(population) from city;

28. The Blunder

//www.hackerrank.com/challenges/the-blunder/problem

select ceil(avg(salary) - avg(to_number(replace(to_char(salary), '0')))) from employees;

29. Top Earners

//www.hackerrank.com/challenges/earnings-of-employees/problem

select max(months * salary), count(months * salary) from Employee where (months * salary) = (select max(months * salary) from Employee);

30. Weather Observation Station 2

//www.hackerrank.com/challenges/weather-observation-station-2/problem

select round(sum(lat_n), 2), round(sum(long_w), 2) from station;

31. Weather Observation Station 13

//www.hackerrank.com/challenges/weather-observation-station-13/problem

select round(sum(lat_n), 4) from station where lat_n > 38.7880 and lat_n < 137.2345;

32. Weather Observation Station 14

//www.hackerrank.com/challenges/weather-observation-station-14/problem

select round(max(lat_n), 4) from station where lat_n < 137.2345;

33. Weather Observation Station 15

//www.hackerrank.com/challenges/weather-observation-station-15/problem

select round(long_w, 4) from station where lat_n = (select max(lat_n) from station where lat_n < 137.2345);

34. Weather Observation Station 16

//www.hackerrank.com/challenges/weather-observation-station-16/problem

select round(min(lat_n), 4) from station where lat_n > 38.7780;

35. Weather Observation Station 17

//www.hackerrank.com/challenges/weather-observation-station-17/problem

select round(long_w, 4) from station where lat_n = (select min(lat_n) from station where lat_n > 38.7780);

36. Asian Population

//www.hackerrank.com/challenges/asian-population/problem

select sum(city.population) from city, country where city.countrycode = country.code and continent='Asia';

37. African Cities

//www.hackerrank.com/challenges/african-cities/problem

select city.name from city, country where city.countrycode = country.code and country.continent='Africa';

38. Average Population of Each Continent

//www.hackerrank.com/challenges/average-population-of-each-continent/problem

select country.continent, floor(avg(city.population)) from city, country where city.countrycode = country.code group by country.continent;