프로그래머스 - SQL

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

코드

-- 코드를 입력하세요
SELECT B.WRITER_ID AS USER_ID, F.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD B RIGHT JOIN USED_GOODS_USER F ON B.WRITER_ID = F.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY B.WRITER_ID
HAVING TOTAL_SALES >= 700000Å
ORDER BY TOTAL_SALES ASC;

조건에 맞는 도서 리스트 출력하기

코드

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, "%Y-%m-%d") AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE) = 2021
ORDER BY PUBLISHED_DATE;

가격대 별 상품 개수 구하기

코드

SELECT FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

모든 레코드 조회하기

코드

SELECT  *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

평균 일일 대여 요금 구하기

코드

SELECT ROUND(SUM(DAILY_FEE)/COUNT(DAILY_FEE)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV'
GROUP BY CAR_TYPE;

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

코드

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) = 3 AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;

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

코드

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

오답코드

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
HAVING MAX(FAVORITES)
ORDER BY FOOD_TYPE DESC;

없어진 기록 찾기

코드

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I RIGHT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

식품분류별 가장 비싼 식품의 정보 조회하기

코드

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY HAVING CATEGORY='과자' OR CATEGORY='국' OR CATEGORY='김치' OR CATEGORY='식용유')
ORDER BY PRICE DESC;

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

코드

SELECT C.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR C LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
WHERE C.CAR_TYPE = '세단' AND MONTH(H.START_DATE) = 10
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

5월 식품들의 총매출 조회하기

코드

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE*SUM(O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P LEFT JOIN FOOD_ORDER O ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE YEAR(O.PRODUCE_DATE)=2022 AND MONTH(O.PRODUCE_DATE)=5
GROUP BY O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

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

코드

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC;

# (SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID)

과일로 만든 아이스크림 고르기

코드

SELECT I.FLAVOR
FROM FIRST_HALF F RIGHT JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
WHERE F.TOTAL_ORDER > 3000 AND I.INGREDIENT_TYPE='fruit_based'
ORDER BY F.TOTAL_ORDER DESC;

최댓값 구하기

코드

SELECT MAX(DATETIME) AS 시간
FROM ANIMAL_INS;

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

코드

SELECT U.USER_ID, U.NICKNAME, concat(U.CITY, concat(' ', concat(U.STREET_ADDRESS1, concat(' ', U.STREET_ADDRESS2)))) AS 전체주소, concat(substr(U.TLNO,1,3),'-',substr(U.TLNO,4,4),'-',substr(U.TLNO,8,4))AS 전화번호
FROM USED_GOODS_BOARD B RIGHT JOIN USED_GOODS_USER U ON B.WRITER_ID = U.USER_ID
GROUP BY B.WRITER_ID
HAVING COUNT(B.WRITER_ID) >= 3
ORDER BY U.USER_ID DESC;