[HackerRank/MySQL] 8월 22일 SQL 스터디 10문제 (Lv Easy. 9개 / Lv Medium. 1개)

 

 

 

1. The PADS  

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true 

The PADS | HackerRank

lv. medium

문제

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).'

 

2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Note: There will be at least two entries in the table for each type of occupation. 

 

 

CODE

SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')') FROM OCCUPATIONS
ORDER BY NAME;

SELECT  CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), Occupation;

 

• 해커랭크는 쿼리를 두 개 날리기도 한다. 마지막에 세미콜론 붙이는 게 중요하다는 의미!
• 직업을 모두 소문자로 나타내기 위해 LOWER 함수를 사용

 

---

 

2. Population Census 

https://www.hackerrank.com/challenges/asian-population/problem?isFullScreen=true 

Population Census | HackerRank

lv. easy

문제

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

CODE

select sum(city.population)
from city
join country on city.countrycode = country.code
where country.continent = 'asia';

 

---

 

3. African Cities 

https://www.hackerrank.com/challenges/african-cities/problem?isFullScreen=false

African Cities | HackerRank

lv. easy

문제

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.

CODE

select city.name from city
    join country on city.countrycode = country.code
where country.continent = 'Africa';

 

---

 

4. Average Population of Each Continent 

https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?isFullScreen=false 

Average Population of Each Continent | HackerRank

lv. easy

문제

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. 

CODE

select country.continent, FLOOR(AVG(CITY.Population))
from city join country on city.countrycode = country.code
group by country.continent

• their respective average city populations (CITY.Population) rounded down to the nearest integer. 이라는 표현으로 인해 내림 연산이 필요했다. FLOOR 함수를 사용했다. 

 

---

 

5. Revising Aggregations - The Sum Function  

https://www.hackerrank.com/challenges/revising-aggregations-sum?isFullScreen=true 

Revising Aggregations - The Sum Function | HackerRank

lv. easy

문제

Query the total population of all cities in CITY where District is California.

 

CODE

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

 

---

 

6. Revising Aggregations - Averages 

https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem?isFullScreen=true 

Revising Aggregations - Averages | HackerRank

lv. easy

문제

Query the average population of all cities in CITY where District is California.

CODE

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

 

---

 

7. Average Population 

https://www.hackerrank.com/challenges/average-population/problem?isFullScreen=true 

Average Population | HackerRank

lv. easy

문제

Query the average population for all cities in CITY, rounded down to the nearest integer. 

CODE

select floor(avg(population)) from city

• rounded down to the nearest integer. -> FLOOR 

 

---

 

8. Japan Population 

https://www.hackerrank.com/challenges/japan-population/problem?isFullScreen=true 

Japan Population | HackerRank

lv. easy

문제

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

CODE

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

 

---

 

9. Population Density Difference 

https://www.hackerrank.com/challenges/population-density-difference/problem?isFullScreen=true 

Population Density Difference | HackerRank

lv. easy

문제

Query the difference between the maximum and minimum populations in CITY. 

CODE

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

 

---

 

10. The Blunder  

https://www.hackerrank.com/challenges/the-blunder/problem?isFullScreen=true

The Blunder | HackerRank

lv. easy

문제

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's  0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.:  actual - miscalculated, average monthly salaries), and round it up to the next integer.

 

 

CODE

select CEIL(AVG(salary) - AVG(REPLACE(salary, '0', '')))
from employees;

• round it up to the next integer. -> 올림연산 CEIL 사용
• replace로 숫자를 일부 제거 후(string 처럼 접근 후) 평균 연산이 가능함을 알게 됐다.

 

- 끝! -