[MySQL] SQL 문제 모음

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

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

SELECT count(*)
FROM USER_INFO
WHERE AGE IS NULL

 

59407 이름이 있는 동물의 아이디 

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

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

 

131120 select where

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
FROM MEMBER_PROFILE
WHERE DATE_OF_BIRTH like '%-03-%' and TLNO is not null and GENDER like 'W'
ORDER BY MEMBER_ID ASC

 

59036 아픈 동물 찾기

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

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

 

59035 역순 정렬하기

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

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

 

59404 여러개를 기준으로 정렬하기

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

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

 

59405 상위 n개 레코드

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

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

 

131114 if, case when

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

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, CASE WHEN FREEZER_YN IS NULL THEN 'N' ELSE FREEZER_YN END
FROM FOOD_WAREHOUSE
WHERE ADDRESS like '경기도%'
ORDER BY WAREHOUSE_ID

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IF(FREEZER_YN is null, 'N', FREEZER_YN)
FROM FOOD_WAREHOUSE
WHERE ADDRESS like '경기도%'
ORDER BY WAREHOUSE_ID

 

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

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN B.STATUS='SALE' THEN '판매중' WHEN B.STATUS='RESERVED' THEN '예약중' WHEN B.STATUS='DONE' THEN '거래완료' END AS STAT
FROM USED_GOODS_BOARD B
WHERE CREATED_DATE like '2022-10-05%'
ORDER BY BOARD_ID DESC

case가 여러개인 경우 위와 같이 case when을 여러개 중첩하고 반드시 끝에 end

 

132201 중첩 조건 

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

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IF(TLNO is null, 'NONE', TLNO)
FROM PATIENT
WHERE GEND_CD like 'W' AND AGE<=12
ORDER BY AGE DESC, PT_NAME

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IF(TLNO is null, 'NONE', TLNO)
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE<=12
ORDER BY AGE DESC, PT_NAME

 

59415 SUM, MAX, MIN (날짜(Date)에도 적용 가능)

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

SELECT MAX(DATETIME), MIN(DATETIME), COUNT(DATETIME)
FROM ANIMAL_INS

 

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

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

 

151136 AVG, ROUND, TRUNCATE

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 = 'SUV'

TRUNCATE(DAILY_FEE / 10000, 0) 정수만

 

소수 첫번째 자리에서 반올림이면 정수로 출력 ("에서" 니까)

 

 

151138 DATE_FORMAT, DATEDIFF

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

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, 
DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, 
IF (DATEDIFF(END_DATE, START_DATE) + 1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE like '2022-09%'
ORDER BY HISTORY_ID DESC

DATE 형식으로 지정: DATE_FORMAT(날짜, 날짜 형식)

날짜 기간 구함: DATEDIFF(끝 날짜, 시작 날짜)

시간 차이: TIMEDIFF

 

 

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

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)), 1) + 1 AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION>=7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

대여 기간 같은 날짜를 구할 때는 항상 +1 하기 (0일도 포함)

 

59406, 59408  COUNT, DISTINCT

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

SELECT count(*)
FROM ANIMAL_INS

 

전체 행 갯수는 count(*)

특정 행 갯수는 count(칼럼명) 이때 NULL 값은 세지 않음 중복도 포함함

중복을 없애기: count(distinct 칼럼명)

 

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

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS

 

 

 

59040 GROUP BY, HAVING

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

SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
HAVING ANIMAL_TYPE='Cat' or ANIMAL_TYPE='Dog'
ORDER BY ANIMAL_TYPE

 

다음과 같이 쓴다.

1. 조건 처리 후에 그룹화 후에 

2. 그룹화 된 컬럼의 조건을 처리하고 정렬

-- 1.
SELECT 컬럼
FROM 테이블
WHERE 조건식
GROUP BY 그룹화할 컬럼
-- 2.
HAVING 조건식
ORDER BY 컬럼

 

 

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

SELECT NAME, COUNT(NAME) AS cnt
FROM ANIMAL_INS
GROUP BY NAME
HAVING cnt>=2
ORDER BY NAME

 

 

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

SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR, COUNT(*) AS cnt
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR

WHERE는 테이블 전체에 대해 찾으니까 SELECT를 하고 GROUP BY 를 한거랑 중첩이 안됨 할거면 따로 해야함

 

 

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

SELECT TRUNCATE(PRICE / 10000, 0) * 10000 AS PRICE_GRP, COUNT(*)
FROM PRODUCT
GROUP BY PRICE_GRP
ORDER BY PRICE_GRP

 

 

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

SELECT I.INGREDIENT_TYPE AS ING, SUM(F.TOTAL_ORDER) AS TOTAL
FROM FIRST_HALF AS F, ICECREAM_INFO AS I
WHERE F.FLAVOR=I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL

 

 

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

SELECT MCDP_CD AS '진료과코드', count(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD like '2022-05%'
GROUP BY MCDP_CD
ORDER BY count(MCDP_CD), MCDP_CD

컬럼명을 한글로 하면 정렬 다른 곳에서 별칭으로 쓸 수 없음

 

 

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

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

 

59046 OR 조건 나열: IN

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

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

 

131533 INNER JOIN: JOIN {table} ON {조건}

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

SELECT PRODUCT.PRODUCT_CODE, sum(OFFLINE_SALE.SALES_AMOUNT * PRODUCT.PRICE) AS TOTAL
FROM PRODUCT
JOIN OFFLINE_SALE on PRODUCT.PRODUCT_ID=OFFLINE_SALE.PRODUCT_ID
GROUP BY PRODUCT.PRODUCT_ID
ORDER BY TOTAL DESC, PRODUCT_CODE ASC

 

144854 INNER JOIN: JOIN {table} ON {조건}

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

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