PGMS - SQL 2/4

LEVEL 2

어린 동물 찾기

• 필드에서 특정 값 제외하기 : !=

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

NULL 처리하기

• IFNULL(필드명, "대체할 내용") : NULL 이면 내용을 대체한다.

SELECT ANIMAL_TYPE,
IFNULL(NAME, "No name"),
SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

나이 정보가 없는 회원 수 구하기

• WHERE : 조건 - 나이 정보가 NULL
• COUNT : 개수 세기

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

입양 시각 구하기(1)

• HOUR(DATETIME) : 시각
• WHERE, AND

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

입양 시각 구하기(2)

https://pig-programming.tistory.com/17

• DATE_FORMAT(DATETIME, "형식") : 날짜 형식 변경

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
HAVING ANIMAL_TYPE = "Cat" OR ANIMAL_TYPE = "Dog"
ORDER BY ANIMAL_TYPE

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

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

가격이 제일 비싼 식품의 정보 출력하기

• SELECT에서 MAX로 해놓으면 제일 큰 가격과 상품 가격이 매칭이 안된다고 한다. 여러 항목이 나온다는 듯 하다.

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY , PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

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

• WHERE, LIKE : 와일드카드(%, _) 사용하여 구하기

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

진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD AS 진료과코드, COUNT(APNT_NO) AS 5월예약건수
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY COUNT(APNT_NO) ASC, MCDP_CD ASC

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

• GROUP BY로 묶고,
  • USER_ID, PRODUCT_ID로 묶으면, USER_ID, PRODUCT_ID가 같은 것들끼리 묶임
• HAVING : GROUP BY 한 것의 조건
  • COUNT(*) : 총 개수

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

성분으로 구분한 아이스크림 총 주문량

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS F JOIN ICECREAM_INFO AS I ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

루시와 엘리 찾기

• 리스트 안에 있는지 보려면 ()로 리스트 만들기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS 
WHERE NAME IN ("Lucy", "Ella", "Pickle", "Rogan", "Sabrina", "Mitty")

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

• FROM 테이블1 JOIN 테이블2 ON 테이블1.필드명1 = 테이블2.필드명2

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, "%Y-%m-%d") AS PUBLISHED_DATE
FROM BOOK AS B JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE CATEGORY = "경제"
ORDER BY PUBLISHED_DATE

• 조건부 : CASE, WHEN, THEN, ELSE, END

LEVEL 3

있었는데요 없었습니다

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME

헤비 유저가 소유한 장소

• HOST_ID로 GROUP BY하고, NAME, ID에 대해 모든 항목을 출력해야 함.
• 테이블 하나를 두 개처럼 생각하고 JOIN을 함 - JOIN 할 때 GROUP BY를 함

SELECT P.ID, P.NAME, P.HOST_ID
FROM PLACES AS P
JOIN (SELECT ID, NAME, HOST_ID 
      FROM PLACES 
      GROUP BY HOST_ID
      HAVING COUNT(*) >= 2) AS H
ON P.HOST_ID = H.HOST_ID
ORDER BY P.ID

없어진 기록 찾기

• LEFT JOIN. OUTS에 있고, INS에 없는 목록 찾기

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS LEFT JOIN ANIMAL_INS AS INS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

오랜 기간 보호한 동물 (1)

• LIMIT : 출력할 때, 항목 개수 제한

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS AS INS LEFT JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME ASC
LIMIT 3

오랜 기간 보호한 동물 (2)

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NOT NULL
ORDER BY OUTS.DATETIME - INS.DATETIME DESC
LIMIT 2

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

• WHERE에서 테이블을 새롭게 지정할 수도 있다.

SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO R
WHERE FAVORITES = (SELECT MAX(FAVORITES)
                   FROM REST_INFO N
                   WHERE  R.FOOD_TYPE = N.FOOD_TYPE)
ORDER BY R.FOOD_TYPE DESC

취소되지 않은 진료 예약 조회하기

• 여러 테이블 JOIN 하기

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, D.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A
JOIN DOCTOR D ON A.MDDR_ID = D.DR_ID
JOIN PATIENT P ON A.PT_NO = P.PT_NO
WHERE A.APNT_CNCL_YN = "N" 
AND DATE_FORMAT(A.APNT_YMD, "%Y-%m-%d") = "2022-04-13"
AND D.MCDP_CD = "CS"
ORDER BY A.APNT_YMD

년, 월, 성별 별 상품 구매 회원 수 구하기

• DISTINCT를 생각하지 못했다.

SELECT  YEAR(SALES_DATE) AS YEAR, 
MONTH(SALES_DATE) AS MONTH, GENDER, 
COUNT(DISTINCT U.USER_ID) AS USERS
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER
ORDER BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER

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

• GROUP BY 생각 못했다.

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 ADDRESS LIKE "서울%"
GROUP BY I.REST_ID
ORDER BY SCORE DESC, I.FAVORITES DESC