프로그래머스 SQL 고득점 Kit - SELECT

1. 평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE),0) as AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = 'SUV'

 

- ROUND( 숫자|컬럼 등 , 자릿수 ) : 반올림하여 소숫점 아래 해당 자릿수까지 표

- AVG 함수 : 평균 구하기, 평균 구할 때 NULL 있다면 IFNULL(DAILY_FEE,0) 이런 식으로 넣어주어야 함

- 그룹별 평균 구하기 : SELECT job, AVG(sal) as avg_sal FROM emp GROUP BY job  , 참고로 as는 컬럼명 지정

- 조건절 다양한 방법들 : WHERE CAR_TYPE IN ('SUV') , LIKE 'SUV' , REGEXP '^SUV$'

 

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

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 ASC;

 

- 날짜 데이터에서 일부만 추출하기: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

- DATETIME 타입은 YYYY-MM-DD hh:mm:ss 형식으로 반환하므로, 적절하게 출력 포맷 변경 필요

- %y하면 연도 두자리, %Y하면 연도 풀, %p는 오전오후, %h는 시간, %i는 분

 

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

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;

 

- ISNULL은 단일 인자를 받고 NULL이면 TRUE(또는 1), 아니면 FALSE(또는 0) 반환

- IFNULL은 첫번째 인자가 NULL이면 두번째 인자 값을 반환

 

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

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE NOT ISNULL(TLNO) AND GENDER = 'W' AND MONTH(DATE_OF_BIRTH) = 3
ORDER BY MEMBER_ID ASC

 

- TLNO IS NULL / IS NOT NULL 이런 문법 사용해도 됨

 

5. 인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

 

6. 흉부외과 또는 일반외과 의사 목록 출력하기

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, "%Y-%m-%d") AS DATE_FORMAT
FROM DOCTOR
WHERE MCDP_CD in ("CS", "GS")
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

 

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

SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, DATE_FORMAT(R.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD B INNER JOIN USED_GOODS_REPLY R
ON B.BOARD_ID = R.BOARD_ID
WHERE B.CREATED_DATE BETWEEN DATE('2022-10-01') AND DATE('2022-10-31')
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC

 

- JOIN 사용할 때 테이블 별 별칭 사용 가능 (바로 이어서 써도 되고, AS ~~ 해도 됨)

- ON 키워드를 통해 조인할 두 테이블 간 관계를 지정함, 이 조건이 참인 행만 결과로 만환됨

- JOIN (= INNER JOIN): 두 테이블 모두에 존재하는 매칭되는 행만 검색

- LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 매칭되는 행을 검색

ex) 모든 직원과 그들이 속할수도 있는 부서의 이름을 검색

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

- RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이에서 매칭되는 행을 검색

ex) 모든 부서와 그 부서에 속할수도 있는 직원의 이름을 검색

SELECT employees.name, departments.department_name
FROM departments
RIGHT JOIN employees ON employees.department_id = departments.department_id;

-  FULL JOIN: 두 테이블의 모든 행을 검색, 한쪽 테이블에만 존재하는 행도 포함,  MySQL에서는 UNION 활용

ex) 모든 직원과 모든 부서를 검색, 어떤 부서는 직원이 없을 수 있고 어떤 직원은 부서에 속하지 않을수도 있음

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.name, departments.department_name
FROM departments
LEFT JOIN employees ON employees.department_id = departments.department_id;

- UNION은 중복 제거, UNION ALL은 중복 제거 x

- BETWEEN ~ AND ~ 통해서 날짜 범위 지정 가능, DATE('2022-10-01') 해도 되고 '2022-10-01' 해도 날짜 형식의 문자열을 자동으로 날짜 타입으로 인식하여 상관 없음

 

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

SELECT F.FLAVOR
FROM FIRST_HALF F INNER 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

 

- 기본키(Primary key): 데이터를 구분할 수 있는 식별자, 중복될 수 없으며 NULL이 들어갈 수 없음

- 외래키: 두 테이블 사이의 관계를 연결, 다른 테이블의 기본 키와 연결됨

 

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

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE LEFT(ADDRESS, 3) = '강원도'
ORDER BY FACTORY_ID ASC

 

- 문자열 자르는 함수 SUSTRING(문자열, 시작위치, 길이) , 인덱스는 1부터 시작함

- LEFT(문자열, 길이) 와 RIGHT(문자열, 길이) 로 잘라낼 수도 있음  ex) LEFT(문자열, 3) = SUBSTRING(문자열, 1, 3)

- WHERE ADDRESS LIKE '%강원도%'  혹은 WHERE ADDRESS REGEXP '^강원도'

 

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

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

 

- 처음에는 LEFT JOIN을 생각했음. 식당별 리뷰들 조합을 뽑으려 했으나 일부 식당에는 리뷰가 없는 경우가 존재함

- (INNER) JOIN을 해야됨

- WHERE I.ADDRESS LIKE '서울%' 해도 되고, HAVING I.ADDRESS LIKE '서울%'(GROUP BY 뒤에) 해도 됨

- '%서울%' 하면 안되는게 경기도 고양시 서울대로 이런 것까지 다 포함해버리기 때문에 서울 식당 제대로 필터링 못함

 

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

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC

 

- GROUP BY를 통해 두 컬럼 조합의 결과를 그룹화함 (+ 중복 제거)

- HAVING COUNT(*)를 통해 그룹화된 각 조합의 등장횟수를 계산, SUM, AVG, MAX, MIN 등의 집계 함수도 사용 가능

ex) SELECT name, SUM(quantity) FROM t1 GROUP BY name;

- GROUP BY에서 조건을 주려면 HAVING 절을 사용해야 함

- SELECT 쿼리 조회 순서는 FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

 

12. 모든 레코드 조회하기

SELECT ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

 

- DATE_FORMAT(DATETIME, "%Y-%m-%d %h:%i:%s")

 

13. 오프라인/온라인 판매 데이터 통합하기

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 ASC, PRODUCT_ID ASC, USER_ID ASC

 

- UNION ALL로 중복 삭제하지 않고 온/오프라인 판매 테이블을 합침

- ORDER BY 절은 결합된 모든 결과에 대해 최종 정렬을 적용함, UNION 각각의 쿼리에 별도로 ORDER BY 적용 불가

- 서브쿼리 이용해서 두 테이블을 합친 뒤 거기서 날짜 필터링과 정렬 과정을 적용해도 됨

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE
    UNION ALL
    SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
    FROM OFFLINE_SALE
) AS COMBINED_SALE
WHERE SALES_DATE BETWEEN "2022-03-01" AND "2022-03-31"
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

 

14. 역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

 

15. 아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'sick'
ORDER BY ANIMAL_ID ASC

 

16. 어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID ASC

 

- 같지 않다 연산자로 != 와 <> 이 두 가지를 사용할 수 있다

 

17. 동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

18. 여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC

 

19. 상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1

 

- 처음 10개의 데이터만 가져오기 위해서는 LIMIT 10

- Offset 값은 0부터 시작, 11행부터 20행까지의 데이터를 가져오기 위해서는 LIMIT 10,10;

 

20. 조건에 맞는 회원수 구하기

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE (JOINED BETWEEN '2021-01-01' AND '2021-12-31') AND (AGE BETWEEN 20 AND 29)

 

- YEAR(JOINED) = 2021 해도 됨