보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요. (링크)
SELECT animal_id
, name
, if(sex_upon_intake like '%Neutered%' or sex_upon_intake like '%Spayed%', 'O', 'X') "중성화"
FROM animal_ins
ORDER BY animal_id
;if문 조건절에 and/or를 통해 여러 조건을 설정할 수 있다
PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요. (링크)
SELECT substr(product_code, 1, 2) 'category'
, count(1) 'products'
FROM product
GROUP BY substr(product_code, 1, 2)
ORDER BY category
;사실 group by나 order by에 그냥 1로 적어도 1번 칼럼을 칭하기에 작동한다
나는 칼럼명을 다 적는 버릇이 있긴 한데, 지금처럼 칼럼명이 길다면 간단하게 칼럼번호로 적는 것도 연습해놔야 할 듯
동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요. (링크)
SELECT animal_type
, count(*) 'count'
FROM animal_ins
WHERE animal_type in ('cat', 'dog')
GROUP BY animal_type
ORDER BY animal_type
;보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다. (링크)
SELECT hour(datetime) 'hour'
, count(1) 'count'
FROM animal_outs
WHERE hour(datetime) between 09 and 19
GROUP BY hour(datetime)
ORDER BY hour(datetime)
;hour() 함수 기억하자
APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요. (링크)
SELECT mcdp_cd '진료과코드'
, count(1) '5월예약건수'
FROM appointment
WHERE year(APNT_YMD) = 2022 and month(APNT_YMD) = 05
GROUP BY mcdp_cd
ORDER BY count(1), mcdp_cd
;month() 함수도 기억하자
PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요. (링크)
SELECT pt_name
, pt_no
, gend_cd
, age
, if(tlno is null, 'NONE', tlno) 'tlno'
FROM patient
WHERE gend_cd = 'W' and age <= 12
ORDER BY age desc, pt_name
;문제를 자세히 보는 습관을 들일 필요가 있겠다
'12세 이하 여자 환자'라는 단어를 넘기고 그 뒤만 봤다가 한 번 헤맸다
상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요. (링크)
SELECT flavor
FROM first_half
ORDER BY total_order desc, shipment_id
;CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요. (링크)
SELECT car_type
, count(1) 'cars'
FROM car_rental_company_car
WHERE (options like '%통풍시트%') or (options like '%열선시트%') or (options like '%가죽시트%')
GROUP BY car_type
ORDER BY car_type
;개인적으로는 조금 어거지로 풀었다고 생각하는 코드
where절을 보다 깔끔하게 만들 수 있는 방법이 없었을까...
#참고 답안
SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE "%시트%"
GROUP BY 1
ORDER BY 1어차피 '시트'라는 글자가 조건의 3개 경우에만 있기 때문에 작성할 수 있는 코드
개인적으로는 보다 확장성을 갖추는 게 중요하다고 생각하지만, 주어진 문제에서는 확실히 이 방법이 좋을 수 있겠다
#정규식 사용
SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트' -- '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;정규표현식을 활용한 방법.
사실 정규표현식은 벌써 거의 다 까먹었기에... 구글링과 함께 해야할듯
SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE
INSTR(OPTIONS, '통풍시트') > 0 OR
INSTR(OPTIONS, '열선시트') > 0 OR
INSTR(OPTIONS, '가죽시트') > 0
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASCINSTR을 활용한 방법
사실 INSTR을 까먹어서 검색해봤다... 입력된 문자열과 일치할 때 첫번째 인덱스를 반환해주는 함수...
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다. (링크)
/*
SELECT ai.name
, ai.datetime
FROM animal_ins ai LEFT JOIN animal_outs ao
ON ai.animal_id = ao.animal_id
WHERE ao.datetime is null
ORDER BY ai.datetime
LIMIT 3
;
*/
SELECT name
, datetime
FROM (
SELECT ai.name
, ai.datetime
, row_number() over (order by ai.datetime) 'rownum'
FROM animal_ins ai LEFT JOIN animal_outs ao
ON ai.animal_id = ao.animal_id
WHERE ao.datetime is null
) aa
WHERE rownum <= 3
;괜히 LIMIT를 쓰기 싫어서 2개 방법으로 풀었다
MySQL이라면 편하게 LIMIT를 쓰도록 하자 (?)
2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 카테고리명을 기준으로 오름차순 정렬해주세요. (링크)
SELECT b.category
, sum(bs.sales) 'total_sales'
FROM book b INNER JOIN book_sales bs
ON b.book_id = bs.book_id
WHERE year(bs.sales_date) = 2022 and month(bs.sales_date) = 01
GROUP BY b.category
ORDER BY b.category
;PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요. (링크)
/*
SELECT p.product_code
, sum(p.price * os.sales_amount) 'tot_sales'
FROM product p INNER JOIN offline_sale os
ON p.product_id = os.product_id
GROUP BY p.product_code
ORDER BY sum(p.price * os.sales_amount) desc, p.product_code
;
*/
SELECT p.product_code
, sum(p.price * os.sales_amount) 'tot_sales'
FROM product p INNER JOIN offline_sale os
ON p.product_id = os.product_id
GROUP BY p.product_code
ORDER BY 2 desc, 1
;GROUP BY와 ORDER BY에 칼럼 번호를 쓰는 케이스를 일부러 사용해봤다
확실히.. 편하긴 하다...
관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다. (링크)
SELECT ai.animal_id
, ai.name
FROM animal_ins ai INNER JOIN animal_outs ao
ON ai.animal_id = ao.animal_id
WHERE ai.datetime > ao.datetime
ORDER BY ai.datetime
;날짜 데이터는 작을 수록 빠르다
입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다. (링크)
SELECT animal_id
, name
FROM (
SELECT ai.animal_id
, ai.name
, row_number() over (order by ao.datetime - ai.datetime desc) 'rownum'
FROM animal_ins ai INNER JOIN animal_outs ao
ON ai.animal_id = ao.animal_id
) aa
WHERE rownum <= 2
;역시나 LIMIT를 써서 작성할 수도 있지만, 굳이굳이 LIMIT는 안 써보는 아집
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요. (링크)
SELECT ai.animal_id
, ai.animal_type
, ai.name
FROM animal_ins ai INNER JOIN animal_outs ao
ON ai.animal_id = ao.animal_id
WHERE ai.sex_upon_intake like '%Intact%'
and (ao.sex_upon_outcome like '%Spayed%' or ao.sex_upon_outcome like '%Neutered%')
ORDER BY ai.animal_id
;역시나 where절이 마음에 안 든다
정규표현식을 사용하면 아래와 같이 된다고 한다
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE REGEXP 'Intact' AND -- 중성화 X
O.SEX_UPON_OUTCOME REGEXP 'Spayed|Neutered' -- 중성화 O
ORDER BY 1FROM절에서 인라인뷰처럼 활용해봤다
SELECT
i.animal_id
, i.animal_type
, i.name
FROM
(SELECT
animal_id
, animal_type
, name
FROM
animal_ins
WHERE
(sex_upon_intake like '%intact%')
) i
INNER JOIN
(SELECT
animal_id
FROM
animal_outs
WHERE
(SEX_UPON_OUTCOME like '%spayed%')
or (SEX_UPON_OUTCOME like '%neutered%')
) o ON i.animal_id = o.animal_id
ORDER BY
i.animal_id
;'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요. (링크)
SELECT b.book_id
, a.author_name
, date_format(b.published_date, '%Y-%m-%d') 'published_date'
FROM book b INNER JOIN author a
ON b.author_id = a.author_id
WHERE b.category = '경제'
ORDER BY b.published_date
;문제 주의사항을 문제랑 같이 안 모아둔 악질(?) 문제
출판일 칼럼의 format이 예시와 동일해야 한다는 제약이 걸려있다
FOOD_ORDER 테이블에서 2022년 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 출고여부는 2022년 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요. (링크)
SELECT order_id
, product_id
, date_format(out_date, '%Y-%m-%d') as 'out__date'
, case when out_date is null then '출고미정'
when out_date <= '2022-05-01' then '출고완료'
else '출고대기'
end as '출고여부'
FROM food_order
ORDER BY order_id
;처음에 case-end가 아니라 단순 if문 중첩으로 해결하려고 했다가 한 번 헤맸다
(그 이후엔 출고미정을 '출고 미정'으로 적어서 한참 헤맸고)
case문이 다수의 조건문을 한 번에 적용할 때 유용하니까, if문만 고집하지 말고 유연하게 적용해보자
