[SQL] Practice 3

📌 개인적인 공간으로 공부를 기록하고 복습하기 위해 사용하는 블로그입니다.
정확하지 않은 정보가 있을 수 있으니 참고바랍니다 :😸
[틀린 내용은 댓글로 남겨주시면 복받으실거에요]

sql - 추가로 알게 된 것

• DATETIME 시분초까지 나옴.
• TEXT 줄넘김 가능, 길이제한 없음
• Between A and B 의 경우 B는 포함되지 않음.
• ✨coalesce( 왼쪽, 오른쪽) ⇒ coalesce는 null이 아닐 때는 왼쪽, null일 때는 오른쪽 값을 반환함.

레스토랑 예약시스템

문제 - 고급2

1. 문제

   특정 기간 동안의 총 예약 수를 계산하세요. (예: 2024년 5월 1일부터 5월 31일까지)

2. 나의 쿼리

    SELECT COUNT(r.reservation_id) 
    from reservations r
    where r.reservation_time
    BETWEEN '2024-05-01' AND '2024-05-31';
   

   Between A and B 의 경우 B는 포함되지 않으므로 BETWEEN ‘2024-05-01’ AND ‘2024-06-01’ 로 변경해야함.

문제 - 고급4

1. 문제

   특정 고객(예: 고객 ID가 3)의 총 주문 금액을 계산하세요.

2. 나의 쿼리

    select c.name,sum(m.price*o2.quantity)
    from customers c 
    join reservations r 
    on r.customer_id =c.customer_id 
    join orders o 
    on r.reservation_id =o.order_id 
    join orderdetails o2 
    on o2.order_id = o.order_id 
    join menuitems m 
    on m.menu_item_id =o2.menu_item_id 
    where c.customer_id =3
    group by c.name ;
   

   총 주문 금액만 계산하면 되므로 굳이 customer 까지 join 할 필요 없이 reservation에 customer_id가 있어서 reservation까지 조인하면 됨.

   

3. Reference

    select sum(mi.price * od.quantity) as total_spend 
    from orders o 
    join orderdetails od
    on od.order_id = o.order_id 
    join menuitems mi
    on mi.menu_item_id = od.menu_item_id 
    where o.reservation_id in (select r.reservation_id from reservations r 
    where r.customer_id = 3);
   

   마지막 join 대신에 where절에서 서브쿼리로 조건 걸어서 풀 수 있음, 결과는 나와 동일하게 나온다!

   

문제 - 고급7 , 틀림 ⚡

1. 문제

   모든 주문에서 가장 많이 주문된 메뉴 아이템의 이름과 총 주문 수량을 조회하세요.

2. 나의 쿼리

    select m.name, sum(o.quantity)  
    from menuitems m
    join orderdetails o
    on o.menu_item_id = m.menu_item_id 
    where o.quantity in (select max(o.menu_item_id) 
    					from orderdetails o2)
    group by o.menu_item_id ;
   

   중복 최대값은 조회못하기때문에 서브쿼리 사용해야함

   →풀이 중에 다시 보니 max값의 기준이 잘못되어 있어서 아래와 같이 쿼리문을 수정했다.

    select m.name, sum(od.quantity)as total_order
    from menuitems m
    join orderdetails od
    on od.menu_item_id = m.menu_item_id 
    where od.quantity in (select max(o2.quantity) 
    					from orderdetails o2)
    group by od.menu_item_id ;
   

   

3. Reference

    select m.name,sum(od.quantity)as total_order from menuitems m
    join orderdetails od on od.menu_item_id = m.menu_item_id 
    group by m.name
    having total_order = (select max(qt) from (select sum(od2.quantity) as qt 
    						from orderdetails od2
    						group by menu_item_id)as subquery);
   

   실행결과는 수정한 풀이와 동일하다.

문제 - 고급9 (아우터 조인 안해서 틀림)

1. 문제

   각 고객별로 모든 주문에 대한 평균 주문 금액을 계산하세요.

2. 나의 쿼리

    select c.name, avg(mi.price*od.quantity) 
    from customers c 
    left join reservations r 
    on c.customer_id = r.customer_id 
    left join orders o 
    on r.reservation_id = o.reservation_id 
    left join orderdetails od
    on od.order_id = o.order_id
    left join menuitems mi 
    on mi.menu_item_id =od.menu_item_id 
    group by c.name;
   

   고객이 null 값을 가질 때도 결과가 나와야하므로 아우터 조인 사용해야 함.

   → 아우터 조인 사용시 null 값 발생

   

3. Reference

    select c.name, coalesce(avg(mi.price * od.quantity), 0) 
    from customers c 
    left join reservations as r
    on r.customer_id = c.customer_id 
    left join orders o 
    on o.reservation_id = r.reservation_id 
    left join orderdetails as od
    on od.order_id = o.order_id 
    left join menuitems as mi
    on mi.menu_item_id = od.menu_item_id
    group by c.name;
   

   coalesce사용해서 값 변경 가능.

   ✨coalesce( 왼쪽, 오른쪽) : coalesce는 null이 아닐 때는 왼쪽, null일 때는 오른쪽 값을 반환함.

Database 카테고리 내 다른 글 보러가기