SQL 고득점 KIT 오답노트

SELECT 

SELECT > 조건에 맞는 회원 수 구하기

나이 20 ~ 29 사이 -> BETWEEN ~ AND ~

2021년에 가입한 회원 수 구하기 -> 나는 DATE에서 년도만 필요하기 때문에 YEAR() 활용

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

 

 

 

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

TLNO가 NULL이면 'NONE'으로 출력해야 한다는 조건이 있다 -> IFNULL(컬럼명, 대체문자) 활용

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME ASC;

 

 

 

SELECT> 평균 일일 대여 요금 구하기

평균을 구하고 소수 첫번째 자리에서 반올림 ->  ROUND(숫자,0)

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

 

 

 

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

DATE 를 필요한 모양으로 포맷 -> DATE_FORMAT(컬럼명, '%Y-%m-%d')

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;

 

 

SELECT> 조건에 부합하는 중고거래 댓글 구하기

조건:

1. USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 

2. 2022년 10월에 작성된 게시글의

3. 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일 조회

4. 1)댓글 작성일 오름차순 정렬  2)게시글 제목을 오름차순 정렬

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 AS A
INNER JOIN USED_GOODS_REPLY AS 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 ASC, A.TITLE ASC;

 

 

 

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

'강원도' 포함한 주소 골라내기

SUBSTR(컬럼명, 시작위치, 시작부터 몇글자)

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

 

 

 

SELECT > 3월에 태어난 여성 회원 목록 출력

전화번호가 NULL인 경우는 출력대상에서 제외 -> IS NOT NULL

SELECT 생략
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03 AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;

 

 

 

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

재구매를 확인하는법 -> 같은 조합의 (회원번호, 상품번호)가 중복으로 여러개 있어야어야 함

방법: GROUP BY로 같이 묶는다!

조건 확인 하는것 따로 출력하는 내용 따로

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;

 

---

SUM, MAX, MIN

 

두개 같은 의미임.. 최댓값 하나 구하기

 

---

GROUP BY

 

 

GROUP BY >가격대 별 상품 개수 구하기

TRUNCATE(숫자,버릴 자릿수) -> 주의! TRUNCATE는 그냥 자르는것 ROUND는 반올림

SELECT에서 필요한 것 만들고... -> 밑에서 그걸 GROUP BY

TRUNCATE(333.1234, 1)	-> 333.1
TRUNCATE(333.1234, -2)	-> 300

SELECT TRUNCATE(PRICE,-4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC;

 

 

 

GROUP BY > 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

특정 단어포함 LIKE '%단어%' 활용

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

 

 

 

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

내가 처음에 만든것 -> HAVING에 MAX(FAVORITE) 틀림 

GROUP BY로 묶으면 가장 상단에 있는 데이터들을 임의로 가져온다고 함

이거 참고하기 -> https://school.programmers.co.kr/questions/38854

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 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미='댄스');

# 취미활동을 하지 않는 사원들
SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

# 취미활동을 하는 사원들의 부서 검색
SELECT 부서
FROM 사원
WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

 

 

 

---

JOIN

 

JOIN> 주문량이 많은 아이스크림 조회하기

내가생각하기엔 full outer join 같은데 다른 자료들에는 inner 또는 left가 많아서 의하했다

왜냐면 한 테이블에만 있는 맛이 최대 판매량일 수도 있기 때문

mysql은 full outer join 이 없어서 union을 활용해야함

SELECT FLAVOR
FROM
    (SELECT * FROM JULY
    UNION
    SELECT * FROM FIRST_HALF
    ) as A
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC LIMIT 3;

 

 

 

JOIN> 그룹별 조건에 맞는 식당 목록 출력하기

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, DATE_FORMAT(B.REVIEW_DATE,"%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE AS A
INNER JOIN REST_REVIEW AS B 
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID = (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(MEMBER_ID) DESC
    LIMIT 1
 )
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC;

 

 

 

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

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(B.AMOUNT)*A.PRICE AS TOTAL_SALES
FROM FOOD_PRODUCT AS A
JOIN FOOD_ORDER AS B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE YEAR(B.PRODUCE_DATE) = 2022 AND MONTH(B.PRODUCE_DATE) = 05
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC