African Cities, Average Population of Each Continent, Weather Observation Station 5, Binary Tree Nodes, New Companies
African Cities 📝
https://www.hackerrank.com/challenges/african-cities/problem?isFullScreen=true
➡️ city,country 테이블
- 출력 : name 출력 (city.name or country.name)
- 조건 1 : 테이블 JOIN city.countrycode = country.code
- 조건 2 : continent = 'Africa'
Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Input Format
The CITY and COUNTRY tables are described as follows:
SELECT city.name
FROM city JOIN country ON city.countrycode = country.code
WHERE continent = 'Africa';
Average Population of Each Continent 📝
https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?isFullScreen=true
➡️ city,country 테이블
- 출력 : counrty.continent,city.population 평균(정수로 버림)
- 조건 1 : 테이블 JOIN city.countrycode = country.code
- 조건 2 : country.continent로 group
Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Input Format
The CITY and COUNTRY tables are described as follows:
SELECT country.continent,FLOOR(AVG(city.population))
FROM city JOIN country ON city.countrycode = country.code
GROUP BY country.continent;
Weather Observation Station 5📝
https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true
➡️ station 테이블
- 출력 : city 길이 중 가장 짧은 것 + 가장 긴 것
- 조건 1 : 동일한 city 길이를 가질 경우 알파벳으로 정렬해서 제일 위에 있는 것 추출
Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.
Sample Input
For example, CITY has four entries: DEF, ABC, PQRS and WXY.
Sample Output
ABC 3
PQRS 4
Explanation
When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths and . The longest name is PQRS, but there are options for shortest named city. Choose ABC, because it comes first alphabetically.
Note
You can write two separate queries to get the desired output. It need not be a single query.
-- SQL
SELECT city,LENGTH(city) FROM STATION ORDER BY LENGTH(city),city LIMIT 1;
SELECT city,LENGTH(city) FROM STATION ORDER BY LENGTH(city) DESC,city LIMIT 1;-- 오라클
SELECT * FROM (SELECT city,LENGTH(city) FROM STATION ORDER BY LENGTH(city),city) WHERE ROWNUM<=1;
SELECT * FROM (SELECT city,LENGTH(city) FROM STATION ORDER BY LENGTH(city) DESC,city) WHERE ROWNUM<=1;
Binary Tree Nodes 📝
https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true
➡️ bst 테이블
- 첫번쨰로 Root와 (Leaf & Inner) 둘을 나누는 기준 : b.p null인 경우 -> Root, b.p가 Null이 아닌 경우 -> Leaf & Inner
- 두번째로 Leaf와 Inner 둘을 나누는 기준
b.n = p인 값들의 개수 = 0 -> b.n이 Inner가 될 수 없고 Leaf.
b.n = p인 값들의 개수 0이아닌 경우 -> Inner
You are given a table, BST, containing two columns: N and P, where Nrepresents the value of a node in Binary Tree, and P is the parent of N.
Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:
- Root: If node is root node.
- Leaf: If node is leaf node.
- Inner: If node is neither root nor leaf node.
Sample Input
Sample Output
1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf
Explanation
The Binary Tree below illustrates the sample:
SELECT b.n,
CASE
WHEN b.p IS NOT null THEN
CASE
WHEN (SELECT COUNT(*) FROM bst WHERE p = b.n)=0 THEN 'Leaf'
ELSE 'Inner'
END
ELSE
'Root'
END
FROM bst b
ORDER BY b.n;
New Companies 📝
https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true
➡️ company,lead_manger,senior_manager, manager, employee 테이블
- company,lead_manger,senior_manger,mangager,employee 테이블 INNER JOIN
- company_code,founder로 group 지은 후 직원들의 수를 count
- 조건 : order by company_code (데이터타입 : string ) -> C1,C10,C100 이런식으로 오름차순 정리
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 seniormanagers, 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.
SELECT c.company_code,c.founder,
COUNT(DISTINCT l.lead_manager_code),
COUNT(DISTINCT s.senior_manager_code),
COUNT(DISTINCT m.manager_code),
COUNT(DISTINCT e.employee_code)
FROM company c
LEFT JOIN lead_manager l ON l.company_code = c.company_code
LEFT JOIN senior_manager s ON s.lead_manager_code = l.lead_manager_code
LEFT JOIN manager m ON m.senior_manager_code = s.senior_manager_code
LEFT JOIN employee e ON e.manager_code = m.manager_code
GROUP BY c.company_code,c.founder
ORDER BY c.company_code;