Write a query to get the department name and number of employees in the department. Sample table: employees Sample table: departments Code: Sample Output: 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'.
What is the difficulty level of this exercise?
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: 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: https://www.hackerrank.com/domains/sql 1. Revising the Select Query Ihttps://www.hackerrank.com/challenges/revising-the-select-query/problem select * from CITY where COUNTRYCODE='USA' and POPULATION>100000;2. Revising the Select Query IIhttps://www.hackerrank.com/challenges/revising-the-select-query-2/problem select NAME from CITY where COUNTRYCODE='USA' and POPULATION>120000;3. Select allhttps://www.hackerrank.com/challenges/select-all-sql/problem select * from CITY;4. Select by IDhttps://www.hackerrank.com/challenges/select-by-id/problem select * from city where id=1661;5. Japanese Cities’ Attributeshttps://www.hackerrank.com/challenges/japanese-cities-attributes/problem select * from city where countrycode='JPN';6. Japanese Cities’ Nameshttps://www.hackerrank.com/challenges/japanese-cities-name/problem select name from city where countrycode='JPN';7. Weather Observation Station 1https://www.hackerrank.com/challenges/weather-observation-station-1/problem 8. Weather Observation Station 3https://www.hackerrank.com/challenges/weather-observation-station-3/problem select distinct city from station where mod(id, 2) = 0;9. Weather Observation Station 4https://www.hackerrank.com/challenges/weather-observation-station-4/problem select count(city) - count(distinct city) from station;10. Weather Observation Station 5https://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 6https://www.hackerrank.com/challenges/weather-observation-station-6/problem select distinct city from station where regexp_like(city, '^[aeiouAEIOU]');12. Weather Observation Station 7https://www.hackerrank.com/challenges/weather-observation-station-7/problem select distinct city from station where regexp_like(city, '*[aeiouAEIOU]$');13. Weather Observation Station 8https://www.hackerrank.com/challenges/weather-observation-station-8/problem select distinct city from station where regexp_like(city, '^[aeiouAEIOU].*[aeiouAEIOU]$');14. Weather Observation Station 9https://www.hackerrank.com/challenges/weather-observation-station-9/problem select distinct city from station where regexp_like(city, '^[^aeiouAEIOU]');15. Weather Observation Station 10https://www.hackerrank.com/challenges/weather-observation-station-10/problem select distinct city from station where regexp_like(city, '*[^aeiouAEIOU]$');16. Weather Observation Station 11https://www.hackerrank.com/challenges/weather-observation-station-11/problem select distinct city from station where regexp_like(city, '^[^aeiouAEIOU]|*[^aeiouAEIOU]$');17. Weather Observation Station 12https://www.hackerrank.com/challenges/weather-observation-station-12/problem select distinct city from station where regexp_like(city, '^[^aeiouAEIOU].*[^aeiouAEIOU]$');18. Employee Nameshttps://www.hackerrank.com/challenges/name-of-employees/problem select name from employee order by name;19. Employee Salarieshttps://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 Trianglehttps://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 Functionhttps://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem select count(countrycode) from city where population > 100000;22. Revising Aggregations – The Sum Functionhttps://www.hackerrank.com/challenges/revising-aggregations-sum/problem select sum(population) from city where district = 'California';23. Revising Aggregations – Averageshttps://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem 24. Average Populationhttps://www.hackerrank.com/challenges/average-population/problem select floor(avg(population)) from city;25. Higher Than 75 Markshttps://www.hackerrank.com/challenges/more-than-75-marks/problem select name from students where marks>75 order by substr(name, -3), id;26. Japan Populationhttps://www.hackerrank.com/challenges/japan-population/problem select sum(population) from city where countrycode='JPN';27. Population Density Differencehttps://www.hackerrank.com/challenges/population-density-difference/problem select max(population) - min(population) from city;28. The Blunderhttps://www.hackerrank.com/challenges/the-blunder/problem select ceil(avg(salary) - avg(to_number(replace(to_char(salary), '0')))) from employees;29. Top Earnershttps://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 2https://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 13https://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 14https://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 15https://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 16https://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 17https://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 Populationhttps://www.hackerrank.com/challenges/asian-population/problem select sum(city.population) from city, country where city.countrycode = country.code and continent='Asia';37. African Citieshttps://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 Continenthttps://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; |