[HackerRank] MySQL 기초 연습문제 (2)

멋쟁이 사자처럼 AI스쿨
데이터리안 스페셜 특강  SQL 첫걸음
HackerRank sql 연습문제 풀이 - 2

Solve SQL Code Challenges

 

---

 

1. Revising Aggregations - The Count Function

Revising Aggregations - The Count Function | HackerRank

Query a count of the number of cities in CITY having a Population larger than 100,000.

Input Format

The CITY table is described as follows:

SELECT COUNT(population) 
FROM city 
WHERE population > 100000

2. Revising Aggregations - The Sum Function

Revising Aggregations - The Sum Function | HackerRank

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

Input Format

The CITY table is described as follows:

SELECT SUM(population) 
FROM city 
WHERE district = "California"

3. Revising Aggregations - Averages

Revising Aggregations - Averages | HackerRank

 

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

 

Input Format

The CITY table is described as follows:

SELECT AVG(population) 
FROM city 
WHERE district = "California"

4. Average Population

Average Population | HackerRank

 

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

 

Input Format

The CITY table is described as follows:

SELECT FLOOR(AVG(population))
FROM city

• 가장 가까운 정수로 내림해라
  • FLOOR() 를 사용한다.

 

 

5. Population Density Difference

Population Density Difference | HackerRank

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

 

Input Format

The CITY table is described as follows:

SELECT MAX(population) - MIN(population)
FROM city

• MIN() 과 MAX() 의 차이를 구하면 된다.

 

 

6. Weather Observation Station 4

Weather Observation Station 4 | HackerRank

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

 

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

 

For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. -> 중복 제거하라는 말

 

The query returns 1 , because

SELECT COUNT(city) - COUNT(DISTINCT(city))
FROM station

 

• 전체 테이블의 city value 개수와 중복을 제거한 유니크한 city value 개수의 차이를 구하면 된다.
  • 예시 ) len( {'사과','사과','바나나','메론','사과'} ) - len ( {'사과','바나나','메론' } ) = 2

 

 

7. Top Earners

Top Earners | HackerRank

We define an employee's total earnings to be their monthly salary x months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table.

 

Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

 

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

 

Sample Input

Sample Output

69952 1

SELECT salary * months AS earnings, COUNT(*)
FROM employee 
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

• salary x month = earnings
  • 각 earning 별로 몇 명이 얼만큼의 소득을 봤는지 출력하는 쿼리를 작성하자.
    • GROUP BY를 통해 earning 을 묶는다.
  • earnings 중 가장 큰 값을 가져온다.
    • ORDER BY + LIMIT 1 을 넣어주면 된다.
      • 내림차순 정렬 DESC 추가

 

 

8. Type of Triangle

Type of Triangle | HackerRank

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

 

조건.

Equilateral

: It's a triangle with 3 sides of equal length.

-> 세 변이 모두 같다 (정삼각형)

Isosceles

: It's a triangle with 2 sides of equal length.

-> 최소 두 변이 같다

Scalene

: It's a triangle with 3 sides of differing lengths.

-> 세 변이 모두 다르지만 삼각형을 만들 수는 있다.

Not A Triangle

: The given values of A, B, and C don't form a triangle.

-> A, B, C 모두 다르고, 삼각형 조건 또한 만족하지 못한다.

SUM(a,b,c) - MIN(a,b,c) <= MAX(a,b,c) 일 경우 삼각형을 만들 수 없다.

 

Input Format

The TRIANGLES table is described as follows:

Each row in the table denotes the lengths of each of a triangle's three sides.

 

Sample Input

Sample Output

Isosceles

Equilateral

Scalene

Not A Triangle

SELECT CASE 
        WHEN A = B AND B = C THEN "Equilateral"
        WHEN A + B <= C OR A + C <= B OR B + C <= A THEN "Not A Triangle"
        WHEN A = B OR B = C OR A = C THEN "Isosceles"
        ELSE "Scalene"
    END
FROM Triangles

• 다중 CASE WHEN 구문을 이용해서 문제를 풀어야 한다.
  • 조건 을 만족할 경우 THEN에 있는 값을 반환한다.
    • END구문을 따로 지정해주지 않고 진행하면 NULL을 반환한다.
• 조건문 순서에 유의해서 문제를 풀어야한다.
  • 큰 조건 -> 작은 조건 순서로 처리한다.
    • • 먼저 삼각형 세 변의 길이가 다 같은지를 검사한다.
        • 위 조건을 만족하지 못할 경우, 두 변의 길이가 하나보다 작으면 삼각형이 되지 않음.
        • 첫번째 조건과 두번째 조건을 만족하지 못할 경우, 둘 중 하나만 같은 경우인지를 검사
          • 셋 다 다른 경우라면 Scalene 를 출력한다.