[Programmers]SQL 고득점 Kit
Coding Test Study SQL
💻230216~230222 (Level 1~3)
1. 동물의 아이디와 이름 Level 1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
2. 조건에 맞는 도서 리스트 출력하기 Level 1
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = '2021'
AND CATEGORY='인문'
ORDER BY PUBLISHED_DATE
3. 아픈 동물 찾기 Level 1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION='Sick'
4. 12세 이하인 여자 환자 목록 출력하기 Level 1
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD='W'
ORDER BY AGE DESC, PT_NAME ASC
5. 인기있는 아이스크림 Level 1
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC
6. 상위 n개 레코드 Level 1
SELECT NAME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME)
FROM ANIMAL_INS
)
7. 특정 옵션이 포함된 자동차 리스트 구하기 Level 1
SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC
8. 나이 정보가 없는 회원 수 구하기 Level 1
SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE IS NULL
9. 과일로 만든 아이스크림 고르기 Level 1
SELECT A.FLAVOR
FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE A.TOTAL_ORDER >= 3000 AND B.INGREDIENT_TYPE = 'fruit_based'
10. 조건에 맞는 회원수 구하기 Level 1
SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE YEAR(JOINED)=2021 AND AGE BETWEEN 20 AND 29
11. 이름이 없는 동물의 아이디 Level 1
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
12. 이름이 있는 동물의 아이디 Level 1
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
13. 역순 정렬하기 Level 1
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC
14. 강원도에 위치한 생산공장 목록 출력하기 Level 1
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
15. 경기도에 위치한 식품창고 목록 출력하기 Level 1
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
16. 어린 동물 찾기 Level 1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
17. 여러 기준으로 정렬하기 Level 1
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC
18. 가장 비싼 상품 구하기 Level 1
SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT
19. 자동차 대여 기록에서 장기/단기 대여 구분하기 Level 1
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') as START_DATE,
DATE_FORMAT(END_DATE, '%Y-%m-%d') as END_DATE,
IF(DATEDIFF(END_DATE, START_DATE)+1>=30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE)=2022 AND MONTH(START_DATE)=9
ORDER BY HISTORY_ID DESC
20. 평균 일일 대여 요금 구하기 Level 1
SELECT ROUND(AVG(DAILY_FEE))
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV'
21. 흉부외과 또는 일반외과 의사 목록 출력하기 Level 1
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD='CS' OR MCDP_CD='GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC
22. 모든 레코드 조회하기 Level 1
SELECT *
FROM ANIMAL_INS
23. 최댓값 구하기 Level 1
SELECT MAX(DATETIME) as 시간
FROM ANIMAL_INS
24. 자동차 평균 대여 기간 구하기 Level 2
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 AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC
25. NULL 처리하기 Level 2
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
26. DATETIME에서 DATE로 형 변환 Level 2
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
27. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 Level 2
SELECT CAR_TYPE, COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE
28. 동물 수 구하기 Level 2
SELECT COUNT(*) AS count
FROM ANIMAL_INS
29. 이름에 el이 들어가는 동물 찾기 Level 2
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE='Dog' AND NAME LIKE '%EL%'
ORDER BY NAME
30. 동명 동물 수 찾기 Level 2
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT >= 2 AND NAME IS NOT NULL
ORDER BY NAME
31. 진료과별 총 예약 횟수 출력하기 Level 2
SELECT MCDP_CD AS 진료과코드, COUNT(*) AS 5월예약건수
FROM APPOINTMENT
WHERE YEAR(APNT_YMD)=2022 AND MONTH(APNT_YMD)=5
GROUP BY MCDP_CD
ORDER BY 5월예약건수, MCDP_CD
32. 가격이 제일 비싼 식품의 정보 출력하기 Level 2
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE)
FROM FOOD_PRODUCT)
33. 조건에 맞는 도서와 저자 리스트 출력하기 Level 2
SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d')
FROM BOOK A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE
34. 카테고리 별 상품 개수 구하기 Level 2
SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
35. 재구매가 일어난 상품과 회원 리스트 구하기 Level 2
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC
36. 3월에 태어난 여성 회원 목록 출력하기 Level 2
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d')
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3 AND GENDER='W' AND TLNO IS NOT NULL
37. 가격대 별 상품 개수 구하기 Level 2
SELECT TRUNCATE(PRICE/10000, 0)*10000 AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
38. 중성화 여부 파악하기 Level 2
SELECT ANIMAL_ID, NAME,
IF(SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') AS 중성화
FROM ANIMAL_INS
39. 성분으로 구분한 아이스크림 총 주문량 Level 2
SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
40. 고양이와 개는 몇 마리 있을까 Level 2
SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
41. 중복 제거하기 Level 2
SELECT COUNT(DISTINCT NAME) AS count
FROM ANIMAL_INS
42. 입양 시각 구하기(1) Level 2
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 20
ORDER BY HOUR
43. 최솟값 구하기 Level 2
SELECT DATETIME AS 시간
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME)
FROM ANIMAL_INS)
44. 상품 별 오프라인 매출 구하기 Level 2
SELECT A.PRODUCT_CODE, SUM(B.SALES_AMOUNT)*A.PRICE AS SALES
FROM PRODUCT A INNER JOIN OFFLINE_SALE B ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.PRODUCT_CODE
ORDER BY SALES DESC, A.PRODUCT_CODE ASC
45. 루시와 엘라 찾기 Level 2
# . : 문자 하나를 나타낸다.
# * : 앞에 나온 문자의 0개 이상 반복을 나타낸다.
# ^ : 문자열의 처음을 나타낸다.
# $ : 문자열의 끝을 나타낸다.
# [.] : 괄호 안의 문자열 일치를 확인한다.
# {.} : 반복을 나타낸다.
# | : or 를 나타낸다.
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME REGEXP '^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$'
ORDER BY ANIMAL_ID
46. 조건별로 분류하여 주문상태 출력하기 Level 3
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
IF(OUT_DATE IS NULL, '출고미정', IF(OUT_DATE>'2022-05-01', '출고대기', '출고완료')) AS 출고여부
FROM FOOD_ORDER
47. 없어진 기록 찾기 Level 3
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID
FROM ANIMAL_INS)
48. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 Level 3
SELECT CAR_ID,
IF(SUM(IF(START_DATE<='2022-10-16' AND END_DATE>='2022-10-16', 1, 0))>0, '대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
49. 오랜 기간 보호한 동물(1) Level 3
SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID NOT IN (SELECT ANIMAL_ID
FROM ANIMAL_OUTS)
ORDER BY A.DATETIME
LIMIT 3
50. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 Level 3
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(CAR_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT A.CAR_ID
FROM (SELECT *
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31') A
GROUP BY CAR_ID
HAVING COUNT(*) >= 5)
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC
51. 즐겨찾기가 가장 많은 식당 정보 출력하기 Level 3
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE FAVORITES IN (SELECT MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE)
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC
52. 있었는데요 없었습니다 Level 3
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME < A.DATETIME
ORDER BY A.DATETIME
53. 대여 기록이 존재하는 자동차 리스트 구하기 Level 3
SELECT A.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
WHERE A.CAR_TYPE = '세단'
AND MONTH(B.START_DATE) = 10
GROUP BY A.CAR_ID
ORDER BY A.CAR_ID DESC
54. 카테고리 별 도서 판매량 집계하기 Level 3
SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK A JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID
WHERE B.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.CATEGORY
ORDER BY A.CATEGORY
55. 오랜 기간 보호한 동물(2) Level 3
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2
💻230223~230225 (Level 4)
56. 자동차 대여 기록 별 대여 금액 구하기 Level 4
SELECT B.HISTORY_ID,
CASE
WHEN DATEDIFF(B.END_DATE,B.START_DATE)+1 < 7
THEN ROUND((A.DAILY_FEE)*(DATEDIFF(B.END_DATE,B.START_DATE)+1), 0)
WHEN DATEDIFF(B.END_DATE,B.START_DATE)+1 < 30
THEN ROUND((A.DAILY_FEE*0.95)*(DATEDIFF(B.END_DATE,B.START_DATE)+1), 0)
WHEN DATEDIFF(B.END_DATE,B.START_DATE)+1 < 90
THEN ROUND((A.DAILY_FEE*0.92)*(DATEDIFF(B.END_DATE,B.START_DATE)+1), 0)
ELSE ROUND((A.DAILY_FEE*0.85)*(DATEDIFF(B.END_DATE,B.START_DATE)+1), 0)
END AS FEE
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE = '트럭'
GROUP BY B.HISTORY_ID
ORDER BY FEE DESC, B.HISTORY_ID DESC
57. 서울에 위치한 식당 목록 출력하기 Level 4
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS,
ROUND(AVG(B.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO A JOIN REST_REVIEW B ON A.REST_ID = B.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY A.REST_ID
ORDER BY SCORE DESC, A.FAVORITES DESC
58. 입양 시각 구하기(2) Level 4
테이블 새로 생성 어떻게 하는지 모르겠음
59. 보호소에서 중성화한 동물 Level 4
SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, B.NAME
FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' AND SEX_UPON_OUTCOME NOT LIKE 'Intact%'
60. 5월 식품들의 총매출 조회하기 Level 4
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, A.PRICE*SUM(B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE B.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID
61. 오프라인/온라인 판매 데이터 통합하기 Level 4
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID
62. 취소되지 않은 진료 예약 조회하기 Level 4
SELECT C.APNT_NO, A.PT_NAME, A.PT_NO, C.MCDP_CD, B.DR_NAME, C.APNT_YMD
FROM APPOINTMENT C JOIN PATIENT A ON C.PT_NO = A.PT_NO JOIN DOCTOR B ON C.MDDR_ID = B.DR_ID
WHERE C.APNT_YMD LIKE '2022-04-13%' AND C.APNT_CNCL_YN = 'N'
ORDER BY C.APNT_YMD
63. 주문량이 많은 아이스크림들 조회하기 Level 4
SELECT A.FLAVOR
FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR
GROUP BY FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) DESC
LIMIT 3
64. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 Level 4
65. 저자 별 카테고리 별 매출액 집계하기 Level 4
66. 그룹별 조건에 맞는 식당 목록 출력하기 Level 4
67. 년, 월, 성별 별 상품 구매 회원 수 구하기 Level 4
68. 식품분류별 가장 비싼 식품의 정보 조회하기 Level 4
69. 상품을 구매한 회원 비율 구하기 Level 5
댓글남기기