[240602] SQL 코딩테스트 문제풀이

1. Occupations

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

SELECT MIN( CASE WHEN occupation = 'doctor' then name END ),
    MIN( CASE WHEN occupation = 'professor' then name END ),
    MIN( CASE WHEN occupation = 'Singer' then name END),
    MIN( CASE WHEN occupation = 'Actor' then name END)
FROM ( SELECT Name, 
      Occupation, 
      row_number() Over (partition by occupation order by name) as rn
FROM OCCUPATIONS) as t
GROUP BY t.rn
ORDER BY t.rn

---

2. Top Earners

https://www.hackerrank.com/challenges/earnings-of-employees/problem?isFullScreen=true

Top Earners | HackerRank

 

SELECT t.earnings , count(*)
FROM (SELECT  a.earnings
FROM (SELECT employee_id, name, months * salary as 'earnings'
FROM Employee) as a
WHERE a.earnings = (SELECT max(months * salary)
                                    FROM Employee)) as t
GROUP BY t.earnings

---

3. Weather Observation

https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true

Weather Observation Station 5 | HackerRank

SELECT a.CITY, a.length_city
FROM (SELECT ID, CITY, LENGTH(CITY) as 'length_city',
    ROW_NUMBER() OVER (Partition by LENGTH(CITY) order by CITY ) as 'rank_number'
FROM STATION) as a
WHERE a.rank_number = 1 and 
   ( a.length_city = (SELECT MIN(LENGTH(CITY))
                                FROM STATION)
    or 
    a.length_city = (SELECT MAX(LENGTH(CITY))
                                FROM STATION)
    )

 

---

4. The Blunder

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

The Blunder | HackerRank

SELECT CEIL ( AVG(Salary) - AVG(REPLACE(Salary, 0, '')))
FROM Employees

 

---

5. Japen Population

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

Japan Population | HackerRank

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN'

 

---

6.  Population Census

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

Population Census | HackerRank

SELECT SUM(a.POPULATION)
FROM CITY as a JOIN COUNTRY as b
ON a.CountryCode = b.Code
WHERE b.CONTINENT = 'Asia'

 

---

7.  African Cities

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

African Cities | HackerRank

SELECT a.NAME
FROM CITY as a JOIN COUNTRY as b
ON a.CountryCode = b.Code
WHERE b.CONTINENT = 'Africa'

 

---

8. Average Population

• FLOOR : 버림 (rounded down) / CEIL : 올림 

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

Average Population of Each Continent | HackerRank

SELECT b.Continent, FLOOR(AVG(a.Population))
FROM CITY as a JOIN COUNTRY as b
ON a.CountryCode = b.Code
GROUP BY b.Continent

---

9. Placements

https://www.hackerrank.com/challenges/placements/problem?isFullScreen=true

Placements | HackerRank

WITH MY_sal AS (SELECT s.ID, s.Name, p.Salary
FROM Students as s
    LEFT JOIN Packages as p ON s.ID = p.ID),
    
Friend_sal AS (SELECT f.ID, f.Friend_ID, p.Salary
FROM Friends as f
    LEFT JOIN Packages as p ON f.Friend_ID = p.ID)
    
SELECT  MY_sal.Name
FROM MY_sal JOIN Friend_sal
ON MY_sal.ID = Friend_sal.ID
WHERE MY_sal.Salary < Friend_sal.Salary
ORDER BY Friend_sal.Salary

 

---

10. The Report

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

The Report | HackerRank

SELECT CASE WHEN b.Grade < 8 then 'NULL' ELSE a.Name END, 
    CASE WHEN b.Grade is NULL then 10
    ELSE b.Grade end as 'Grade', a.Marks
FROM (SELECT s.ID, s.Name, FLOOR(s.Marks/10) * 10 as 'turn_marks', s.Marks
FROM Students as s) as a LEFT JOIN Grades as b
ON a.turn_marks = b.Min_Mark
ORDER BY Grade desc, a.Name, a.Marks

 

- 인터넷 답을 보아하니 Between 사용하면 쉽게 해결가능한 문제였음 

SELECT IF(Grades.Grade>=8, Students.Name, NULL), Grades.Grade, Students.Marks
FROM Students INNER JOIN Grades
WHERE Students.Marks between Grades.Min_Mark and Grades.Max_Mark
ORDER BY Grades.Grade desc, Students.Name, Students.Marks

 

---

11. 월별 잡은 물고기 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/293260

프로그래머스

SELECT count(*) as 'FISH_COUNT', MONTH(TIME) as 'MONTH'
FROM FISH_INFO
GROUP BY MONTH(TIME)
ORDER BY MONTH(TIME)

 

---

12. 대장균의 크기에 따라 분류하기 1

https://school.programmers.co.kr/learn/courses/30/lessons/299307

프로그래머스

SELECT ID, 
    CASE WHEN SIZE_OF_COLONY <= 100 then 'LOW'
    WHEN SIZE_OF_COLONY > 100 and SIZE_OF_COLONY <= 1000 then 'MEDIUM'
    ELSE 'HIGH' END as 'SIZE'
FROM ECOLI_DATA
ORDER BY ID

 

---

13. 연도별 평균 미세먼지 오염도

https://school.programmers.co.kr/learn/courses/30/lessons/284530

프로그래머스

SELECT YEAR(YM) as 'YEAR',
    ROUND(AVG(PM_VAL1),2) as 'PM10', ROUND(AVG(PM_VAL2),2) as 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR(YM)
ORDER BY YEAR(YM)

 

---

14. 부서별 평균 연봉 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284529

프로그래머스

SELECT a.DEPT_ID, b.DEPT_NAME_EN, ROUND(AVG(a.SAL),0) as 'AVG_SAL'
FROM HR_EMPLOYEES as a LEFT JOIN HR_DEPARTMENT as b
ON a.DEPT_ID = b.DEPT_ID
GROUP BY a.DEPT_ID
ORDER BY AVG_SAL desc

 

 

---

15.  자동차 대여 기록에서 대여 중 / 대여 가능 여부 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

SELECT a.CAR_ID, IF (sum(a.tf_count) >= 1, "대여중", "대여 가능") as 'AVAILABILITY'
FROM (SELECT CAR_ID,
    CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE then 1
    ELSE 0 END 'tf_count' 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) as a
GROUP BY a.CAR_ID
ORDER BY a.CAR_ID desc

 

---

16. 조건에 부합하는 중고거래 상태 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164672

프로그래머스

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
    CASE WHEN STATUS = 'SALE' then '판매중'
    WHEN STATUS = 'RESERVED' then '예약중'
    ELSE '거래완료' END AS 'STATUS'
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID desc

 

---

17. 재구매가 일어난 상품과 회원 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

SELECT USER_ID,PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID,PRODUCT_ID
HAVING count(*) >= 2
ORDER BY USER_ID asc, PRODUCT_ID desc

---

18. 물고기 종류 별 잡은 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/293257

프로그래머스

SELECT a.type_count as 'FISH_COUNT', b.FISH_NAME as 'FISH_NAME'
FROM (SELECT FISH_TYPE, count(*) as 'type_count'
FROM FISH_INFO
GROUP BY FISH_TYPE) as a LEFT JOIN FISH_NAME_INFO as b
ON a.FISH_TYPE = b.FISH_TYPE
ORDER BY FISH_COUNT desc

 

---

19. 조건에 맞는 사용자 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164670

프로그래머스

SELECT b.USER_ID, b.NICKNAME, CONCAT(b.CITY, " ", b.STREET_ADDRESS1,
                                " ", b.STREET_ADDRESS2) as '전체주소',
    CONCAT(LEFT(TLNO,3),"-", SUBSTR(TLNO,4,4),"-", RIGHT(TLNO,4)) as '전화번호'
FROM (SELECT WRITER_ID
FROM USED_GOODS_BOARD 
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3) as a LEFT JOIN USED_GOODS_USER as b
ON a.WRITER_ID = b.USER_ID
ORDER BY b.USER_ID desc

 

---

20. 없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

프로그래머스

SELECT b.ANIMAL_ID, b.NAME
FROM ANIMAL_INS as a RIGHT JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE a.ANIMAL_ID is NULL
ORDER BY b.ANIMAL_ID, b.NAME

 

---

21. 즐겨찾기가 가장 많은 식당 정보 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131123

프로그래머스

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE desc

 

---

22. 3월에 태어난 여성 회원 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131120

프로그래머스

SELECT MEMBER_ID, MEMBER_NAME, GENDER, 
    DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") as 'DATE_OF_BIRTH'
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03 AND GENDER = 'W'
    AND TLNO is not null
ORDER BY MEMBER_ID ASC

 

---

23. 가격대별 상품 갯수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131530

프로그래머스

SELECT FLOOR(PRICE / 10000) * 10000 as 'PRICE_GROUP', count(*) as 'PRODUCTS'
FROM PRODUCT
GROUP BY  FLOOR(PRICE / 10000) * 10000
ORDER BY PRICE_GROUP

 

---

24. 조건에 맞는 사용자와 거래금액 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164668

프로그래머스

SELECT b.USER_ID, b.NICKNAME, a.TOTAL_SALES
FROM (SELECT WRITER_ID, SUM(PRICE) as 'TOTAL_SALES'
FROM USED_GOODS_BOARD
WHERE STATUS = 'DONE'
GROUP BY WRITER_ID
HAVING SUM(PRICE) >= 700000) as a LEFT JOIN USED_GOODS_USER as b
ON a.WRITER_ID = b.USER_ID
ORDER BY a.TOTAL_SALES

---

25. 루시와 엘라 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59046

프로그래머스

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

 

---

26. 대여 기록이 존재하는 자동차 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157341

프로그래머스

SELECT distinct(a.car_id) as 'CAR_ID'
FROM CAR_RENTAL_COMPANY_CAR as a JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY as b
ON a.CAR_ID = b.CAR_ID
WHERE MONTH(START_DATE) = 10 and a.car_type = '세단'
order by a.car_id desc

 

---

27. 조건별로 분류하여 주문상태 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131113

프로그래머스

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, "%Y-%m-%d") as 'OUT_DATE',
    CASE WHEN DATE_FORMAT(OUT_DATE, "%Y-%m-%d") <= '2022-05-01' THEN '출고완료'
    WHEN DATE_FORMAT(OUT_DATE, "%Y-%m-%d") > '2022-05-01' THEN '출고대기'
    ELSE '출고미정' END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC

 

---

28. 오랜 기간 보호한 동물(2)

https://school.programmers.co.kr/learn/courses/30/lessons/59411

프로그래머스

SELECT a.ANIMAL_ID, a.NAME
FROM ANIMAL_INS as a JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
ORDER BY DATEDIFF(b.DATETIME, a.DATETIME) desc
LIMIT 2

---

26. 성분으로 구분한 아이스크림 총 주문량

https://school.programmers.co.kr/learn/courses/30/lessons/133026

프로그래머스

SELECT b.INGREDIENT_TYPE, SUM(a.TOTAL_ORDER) as 'TOTAL_ORDER'
FROM FIRST_HALF as a LEFT JOIN ICECREAM_INFO as b
ON a.FLAVOR = b.FLAVOR
GROUP BY b.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER

 

---

27.  자동차 평균 대여 기간 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157342

프로그래머스

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)),1) + 1 as 'AVERAGE_DURATION'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE)) + 1 >= 7 
ORDER BY AVERAGE_DURATION desc, CAR_ID desc

 

---

28. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164671

프로그래머스

SELECT CONCAT('/home/grep/src/',b.BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT)
    AS 'FILE_PATH'
FROM (SELECT BOARD_ID
FROM USED_GOODS_BOARD
WHERE VIEWS = (SELECT MAX(VIEWS)
FROM USED_GOODS_BOARD)) as a  JOIN USED_GOODS_FILE as b
ON a.BOARD_ID = b.BOARD_ID
ORDER BY FILE_PATH desc

 

---

29. 조건에 맞는 사원 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284527

프로그래머스

SELECT b.SCORE, c.EMP_NO, c.EMP_NAME, c.POSITION, c.EMAIL
FROM (SELECT EMP_NO, SUM(SCORE) as 'SCORE'
FROM HR_GRADE
GROUP BY EMP_NO
HAVING SUM(SCORE) = 
    (SELECT MAX(a.SCORE)
    FROM (SELECT EMP_NO, SUM(SCORE) as 'SCORE'
    FROM HR_GRADE
    GROUP BY EMP_NO) as a)) as b JOIN HR_EMPLOYEES as c
ON b.EMP_NO = c.EMP_NO

 

---

30. 헤비 유저가 소유한 장소

https://school.programmers.co.kr/learn/courses/30/lessons/77487

프로그래머스

SELECT  *
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(*) >= 2)
ORDER BY ID

 

---

31. 특정 물고기를 잡은 총 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/298518 

프로그래머스

SELECT COUNT(*) as 'FISH_COUNT'
FROM FISH_INFO as a LEFT JOIN FISH_NAME_INFO as b
ON a.FISH_TYPE = b.FISH_TYPE
WHERE b.FISH_NAME IN ('BASS','SNAPPER')

---

32. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

프로그래머스

SELECT MONTH(START_DATE) as 'MONTH', CAR_ID, COUNT(*) as 'RECORDS'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN'2022-08-01' and '2022-10-31'
AND (CAR_ID) IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN'2022-08-01' and '2022-10-31'
GROUP BY CAR_ID
HAVING COUNT(CAR_ID) >= 5)
GROUP BY  MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH asc, CAR_ID desc

 

---

33. 분기별 분화된 대장균의 개체 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299308

프로그래머스

SELECT a.QUARTER, COUNT(*) as 'ECOLI_COUNT'
FROM (SELECT ID,
    CASE WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 and 3 then '1Q'
    WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 and 6 then '2Q'
    WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 and 9 then '3Q'
    ELSE '4Q' END AS 'QUARTER'
FROM ECOLI_DATA) as a
GROUP BY a.QUARTER
ORDER BY a.QUARTER

 

---

34. 대장균들의 자식의 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299305

프로그래머스

SELECT a.ID, IF (b.CHILD_COUNT is NULL, 0, b.CHILD_COUNT) as 'CHILD_COUNT'
FROM ECOLI_DATA as a LEFT JOIN (SELECT PARENT_ID, COUNT(*) as 'CHILD_COUNT'
FROM ECOLI_DATA
GROUP BY PARENT_ID
HAVING PARENT_ID IS NOT NULL) as b
ON a.ID = b.PARENT_ID
ORDER BY a.ID

 

---

35. 업그레이드 할 수 없는 아이템 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/273712

프로그래머스

SELECT a.ITEM_ID, a.ITEM_NAME, a.RARITY
FROM ITEM_INFO as a LEFT JOIN (SELECT DISTINCT(PARENT_ITEM_ID)
FROM ITEM_TREE
WHERE PARENT_ITEM_ID IS NOT NULL) as b
ON a.ITEM_ID = b.PARENT_ITEM_ID
WHERE b.PARENT_ITEM_ID IS NULL
ORDER BY a.ITEM_ID desc