프로그래머스 SQL 고득점 Kit SELECT 20문항에 대한 풀이입니다. MySQL 문법 기준으로 작성했습니다! 🔥
💡 평균 일일 대여 요금 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/151136
SELECT ROUND(AVG(DAILY_FEE)) AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV';
💡 조건에 맞는 도서 리스트 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/144853
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') PUBLISHED_DATE
FROM BOOK
WHERE
DATE_FORMAT(PUBLISHED_DATE, '%Y') = '2021'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;
💡 12세 이하인 여자 환자 목록 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/132201
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME;
💡 인기있는 아이스크림
https://school.programmers.co.kr/learn/courses/30/lessons/133024
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;
💡 흉부외과 또는 일반외과 의사 목록 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/132203
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d')
FROM DOCTOR
WHERE MCDP_CD = 'CS' or MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME;
💡 조건에 부합하는 중고거래 댓글 조회하기
https://school.programmers.co.kr/learn/courses/30/lessons/164673
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, DATE_FORMAT(R.CREATED_DATE, '%Y-%m-%d')
FROM USED_GOODS_BOARD as B
INNER JOIN USED_GOODS_REPLY as R
ON B.BOARD_ID = R.BOARD_ID
WHERE DATE_FORMAT(B.CREATED_DATE, '%Y-%m') = '2022-10'
ORDER BY R.CREATED_DATE, B.TITLE;
💡 과일로 만든 아이스크림 고르기
https://school.programmers.co.kr/learn/courses/30/lessons/133025
SELECT F.FLAVOR
FROM FIRST_HALF as F
INNER JOIN ICECREAM_INFO as I
ON F.FLAVOR = I.FLAVOR
WHERE
F.TOTAL_ORDER > 3000
and I.INGREDIENT_TYPE = 'fruit_based'
ORDER BY F.TOTAL_ORDER DESC;
💡 서울에 위치한 식당 목록 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/131118
SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS, ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW A
JOIN REST_INFO B ON A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
HAVING B.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, B.FAVORITES DESC;
💡 강원도에 위치한 생산공장 목록 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/131112
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE SUBSTR(ADDRESS, 1, 3) = '강원도' # 시작위치는 1부터 시작
ORDER BY FACTORY_ID;
💡 재구매가 일어난 상품과 회원 리스트 구하기
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, PRODUCT_ID DESC;
💡 모든 레코드 조회하기
https://school.programmers.co.kr/learn/courses/30/lessons/59034
SELECT *
FROM ANIMAL_INS
ORDER BY animal_id;
💡 오프라인/온라인 판매 데이터 통합하기
https://school.programmers.co.kr/learn/courses/30/lessons/131537
SELECT
date_format(sales_date, '20%y-%m-%d') AS sales_date,
product_id,
user_id,
sales_amount
FROM
online_sale
WHERE
year(sales_date) = 2022 AND
month(sales_date) = 3
UNION ALL
SELECT
date_format(sales_date, '20%y-%m-%d') AS sales_date,
product_id,
NULL AS user_id,
sales_amount
FROM
offline_sale
WHERE
year(sales_date) = 2022 AND
month(sales_date) = 3
ORDER BY
sales_date,
product_id,
user_id;
💡 역순 정렬하기
https://school.programmers.co.kr/learn/courses/30/lessons/59035
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;
💡 아픈 동물 찾기
https://school.programmers.co.kr/learn/courses/30/lessons/59036
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick';
💡 어린 동물 찾기
https://school.programmers.co.kr/learn/courses/30/lessons/59037
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NOT INTAKE_CONDITION ="Aged";
💡 동물의 아이디와 이름
https://school.programmers.co.kr/learn/courses/30/lessons/59403
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
💡 여러 기준으로 정렬하기
https://school.programmers.co.kr/learn/courses/30/lessons/59404
SELECT ANIMAL_ID,NAME,DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC;
💡 상위 n개 레코드
https://school.programmers.co.kr/learn/courses/30/lessons/59405
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;
💡 조건에 맞는 회원수 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/131535
SELECT COUNT(*) USERS
FROM USER_INFO
WHERE YEAR(JOINED) = 2021 AND AGE BETWEEN 20 AND 29;
'🧑🏻💻 Develop > SQL' 카테고리의 다른 글
[SQL 고득점 Kit] JOIN 풀이 (0) | 2024.03.20 |
---|---|
[SQL 고득점 Kit] IS NULL 풀이 (0) | 2024.01.30 |
[SQL 고득점 Kit] SUM, MAX, MIN 풀이 (0) | 2024.01.30 |