[프로그래머스/SQL 고득점 Kit] SELECT ALL SOLVE

평균 일일 대여 요금 구하기

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

프로그래머스

SELECT round(avg(DAILY_FEE)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE LIKE 'SUV'

 

 

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

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

프로그래머스

SELECT BOOK_ID, date_format(PUBLISHED_DATE, "%Y-%m-%d") as PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021 and CATEGORY like "%인문%"
ORDER BY PUBLISHED_DATE

 

* YEAR(x): x(datetime)에서 년도만 가져옴

 

12세 이하인 여자 환자 목록 출력하기

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

프로그래머스

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

 

* IFNULL(x, y): x가 null이면 다른 값(y) 출력

 

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

 

* date_format(date, "%Y-%m-%d"): 원하는대로 날짜&시간 출력

 

인기있는 아이스크림

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") as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD like "%CS%"
    or MCDP_CD like "%GS%"
ORDER BY HIRE_YMD desc, DR_NAME

 

* x OR y: 조건 x나 y중 하나라도 참이면 True

 

 

 조건에 부합하는 중고거래 댓글 조회하기

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

프로그래머스

SELECT A.TITLE, 
    A.BOARD_ID, 
    B.REPLY_ID, 
    B.WRITER_ID, 
    B.CONTENTS, 
    DATE_FORMAT(B.CREATED_DATE, "%Y-%m-%d") as CREATED_DATE
FROM USED_GOODS_BOARD A
    JOIN USED_GOODS_REPLY B
    ON A.BOARD_ID = B.BOARD_ID
WHERE YEAR(A.CREATED_DATE) = 2022
    AND MONTH(A.CREATED_DATE) = 10
ORDER BY B.CREATED_DATE, A.TITLE

 

* 기본 JOIN은 INNER JOIN으로 동작

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

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

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

프로그래머스

SELECT A.FLAVOR
FROM FIRST_HALF A
    JOIN ICECREAM_INFO B
    ON A.FLAVOR = B.FLAVOR
WHERE A.TOTAL_ORDER > 3000
    AND B.INGREDIENT_TYPE Like "fruit_based"
ORDER BY A.TOTAL_ORDER desc

 

 

서울에 위치한 식당 목록 출력하기

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

프로그래머스

SELECT R.REST_ID, 
    I.REST_NAME, 
    I.FOOD_TYPE, 
    I.FAVORITES,
    I.ADDRESS, 
    round(avg(R.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW R
    JOIN REST_INFO I 
    ON R.REST_ID = I.REST_ID
GROUP BY I.REST_ID
    HAVING I.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, I.FAVORITES DESC

 

* ROUND(x, n): x를 소수 n+1의 자리에서 반올림

 

 

강원도에 위치한 생산공장 목록 출력하기

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

프로그래머스

SELECT FACTORY_ID, 
    FACTORY_NAME, 
    ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "강원도%"
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, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03-%'

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 LIKE '2022-03-%'

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 LIKE "Sick"
ORDER BY ANIMAL_ID

 

 

어린 동물 찾기

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

프로그래머스

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION NOT LIKE "Aged"
ORDER BY ANIMAL_ID

 

 

동물의 아이디와 이름

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

 

* LIMIT n: 상위 n개만 출력

 

 

조건에 맞는 회원수 구하기

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

프로그래머스

SELECT COUNT(*) as USERS
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
AND AGE BETWEEN 20 AND 29