🌎 2022.02.21 updated

Level 2️⃣

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

-- APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요.
-- 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.

select mcdp_cd as '진료과코드', count(apnt_no) as '5월예약건수'
from appointement
where apnt_ymd like '2022-05%'
group by mcdp_cd
order by count(apnt_no), mcdp_cd

• 진료과코드 별로 조회하기 위해 group by mcdp_cd 로 그룹화를 해주었다.

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

-- 상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 
-- 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.

select ingredient_type, sum(total_order) as total_order
from first_half as f, icecream_info as i
where f.flavor = i.flavor
group by ingredient_type
order by total_order

• ingredient_type별로 총 주문량을 조회하기 위해 first_half와 icecream_info 테이블을 조인하였다.

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

-- CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 
-- 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요.
-- 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

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 car_type, count(car_id) as cars
from car_rental_company_car
where options regexp '통풍시트|열선시트|가죽시트'
group by car_type
order by car_type

• LIKE 대신 REGEXP을 사용할 수 있다

  regexp: 정규식을 이용한 검색 방식

  • regexp 'A' - A를 포함한 모든 레코드 출력
  • regexp 'A|B|C|D' - A 또는 B 또는 C 또는 D를 포함한 모든 레코드를 출력

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

-- 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요.
-- 이때 고양이를 개보다 먼저 조회해주세요.

select animal_type, count(animal_id) as count
from animal_ins
where animal_type = 'Cat' or animal_type = 'Dog'
group by animal_type
order by animal_type

-- 다른 사람 코드
select animal_type, count(animal_id) as count
from animal_ins
where animal_type in ('Cat', 'Dog')
group by animal_type
order by field(ANIMAL_TYPE, 'Cat', 'Dog')

• animal_ins가 Cat 또는 Dog라는 것을 animal_type in ('Cat', 'Dog')라고 간단하게 표현할 수 있다

FIELD 함수를 이용하여 특정한 값을 우선적으로 출력할 수 있다

• ORDER BY FIELD(ANIMAL_TYPE, 'Cat', 'Dog'): ANIMAL_TYPE을 정렬, Cat 1순위, Dog 2순위, 나머지를 오름차순으로 정렬
  • 첫 번째 인자로 정렬할 컬럼을 넣고, 그 이후 인자에는 우선적으로 정렬할 값들을 적어준다

동명 동물 수 찾기

-- 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

select name, count(animal_id) as count
from animal_ins
where name is not null
group by name
having count(name) >= 2
order by name

• 동물 이름이 두 번 이상 쓰였는지는 그룹화 이후에 알 수 있으므로 having 절에 적어주어야 한다

입양 시각 구하기(1)

-- 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 
-- 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 시간대 순으로 정렬해야 합니다.

select hour(datetime) as hour, count(animal_id) as count
from animal_outs
where hour(datetime) >= 9 and hour(datetime) <= 19
group by hour
order by hour

• where절에서는 별칭(hour)을 사용하는 것이 불가능하다!

  SQL 구문 실행 순서
  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

가격대 별 상품 개수 구하기

-- PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 
-- 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 
-- 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 
-- 결과는 가격대를 기준으로 오름차순 정렬해주세요.

select truncate(price, -4) as price_group, count(product_id) as products
from product
group by price_group
order by price_group

Level 3️⃣

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

-- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 
-- 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 
-- 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 
-- 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 
-- 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

select month(start_date) as month, car_id, count(history_id) as records
from car_rental_company_rental_history
where car_id in (
    select car_id
    from car_rental_company_rental_history
    where year(start_date)=2022 and month(start_date) between 8 and 10
    group by car_id
    having count(car_id) >= 5)
and year(start_date)=2022 and month(start_date) between 8 and 10
group by month, car_id
order by month, car_id desc

카테고리 별 도서 판매량 집계하기

-- 2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요. 
-- 결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

select category, sum(sales) as total_sales
from book, book_sales
where book.book_id=book_sales.book_id
and sales_date like '2022-01%'
group by category
order by category

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

-- REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

select food_type, rest_id, rest_name, favorites
from rest_info
where (food_type, favorites) in (
    select food_type, max(favorites) as favorites
    from rest_info
    group by food_type
)
order by food_type desc

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

-- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 
-- 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

select car_id, if('2022-10-16' between start_date and end_date, '대여중', '대여 가능') as availability
from car_rental_company_rental_history
group by car_id
order by car_id desc

Level 4️⃣

식품분류별 가장 비싼 식품의 정보 조회하기

-- FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 
-- 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

select category, price as max_price, product_name
from food_product
where (category, price) in (
    select category, max(price)
    from food_product
    group by category
    having category in ('과자', '국', '김치', '식용유')
)
order by max_price desc

저자 별 카테고리 별 매출액 집계하기

-- 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요. 
-- 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

select author.author_id, author.author_name, book.category, sum(book_sales.sales*book.price) as total_sales
from book, author, book_sales
where book.book_id=book_sales.book_id
and book.author_id=author.author_id
and sales_date like '2022-01%'
group by  author.author_id, author.author_name, book.category
order by author.author_id, category desc

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

-- USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 
-- 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
-- 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

select year(sales_date) as year, month(sales_date) as month, u.gender as gender, count(distinct o.user_id) as users
from user_info as u, online_sale as o
where u.user_id=o.user_id
and gender is not null
group by year, month, gender
order by year, month, gender

• user_id를 count할 때 distinct를 붙여야 한다!

입양 시각 구하기(2)

-- 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 
-- 이때 결과는 시간대 순으로 정렬해야 합니다.
set @i = -1

SELECT (@i:=@i+1) as hour,
    (select count(*)
    from animal_outs
    where hour(datetime) = @i) as count
from animal_outs
where @i<23

-- 사용자 정의 변수를 사용해야 한다
-- 앞에 @를 붙여 변수를 사용할 수 있다.
-- 선언은 =와 := 둘 다 가능, 그 외에는 :=만 사용 가능

• 사용자 정의 변수를 사용
• 앞에 @를 붙여 변수를 사용할 수 있다
• 변수 선언은 =, := 둘 다 가능하지만 그 외에는 :=만 사용 가능하다