[SQL 입문부터 활용까지] 2차 과제 (2)

 

https://aisj.tistory.com/99

[SQL 입문부터 활용까지] 2차 과제 (1)

이어서 작성

3. 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요. (join을 사용하지 않고 쿼리를 작성하세요.) (힌트: orders_status 사용, sub-query)

 

문제에서 order_status를 사용하라고 하니 먼저 관찰을 해보자

/* 2006년 3월에 주문(order)된 건의 
주문 상태(status_name)를 찾는 쿼리를 작성하세요. 
(join을 사용하지 않고 쿼리를 작성하세요.) 
(힌트: orders_status 사용, sub-query) */
select *
from orders_status

join을 사용하지 않고 sub query를 사용하라 하는데 sub query가 뭘까

 

sub-query

서브쿼리란 하나의 SQL문 안에 또 다른 SQL문이 들어 있는 것을 말한다.

#메인쿼리
select *
from db_table
where table_fk IN(
        #서브쿼리
        select table_fk from db_table_other
        where ~~)

#메인쿼리
select *
from student
where personal_id = (
        #서브쿼리
        select id
        from personal_table)

이런 식으로

order_status를 사용하라고 하니 다음 orders의 column을 사용해야 할 것 같다

이거 가져오면 될 듯

 

where x = (Select y ~~)이런 식으로 작성해보자

select status_name
 from orders_status

찾으려고 하는 것이 status_name이므로 이를 포함하고 있는 orders_startus를 main table로 가져가야 할 것 같다

 

뭐 이런 식으로 적어봤는데

 select status_name
 from orders_status as OS
 where OS.id = (select O.status_id
                from orders as O
                where month(order_date) = 3 and year(order_date) = 2006);

1개 이상의 row가 반환된다고 한다

즉, 서브쿼리를 먼저 돌려보고 main 에 녹이는 순서가 맞는 것 같다

 

select O.status_id
from orders as O
where month(order_date) = 3 and year(order_date) = 2006;

여기에서  main에 넣어보자

 select OS.id
 from orders_status as OS
 where OS.id =(select O.status_id 
               from orders as O 
               where month(order_date) = 3 and year(order_date) = 2006);

똑같은 오류가 나온다

 

Where 문에 등호를 in으로 바꿔서 오류 해결

 select OS.id
 from orders_status as OS
 where OS.id in (select O.status_id 
               from orders as O 
               where month(order_date) = 3 and year(order_date) = 2006);

당연한 결과지만 order_status를 main으로 들고가서 생긴 문제인 것 같다

"3월에 order된 건"의 라고 했으니 order가 main이 되어야 한다

 select O.id
 from orders as O
 where month(order_date) = 3 and year(order_date) = 2006;

여기서부터 시작해야 한다

 

다음과 같은 결과를 만들었는데

 

정답

 /* 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요. 
 (join을 사용하지 않고 쿼리를 작성하세요.) 
 (힌트: orders_status 사용, sub-query) */
 select O.id as ID, 
     (select OS.status_name
     from orders_status as OS
     where OS.id = O.status_id )as "주문상태"
 from orders as O
 where month(order_date) = 3 and year(order_date) = 2006;

출력

질문

우연히도 다음과 같은 결과를 얻을 수 있었지만 원래의 의도는 두번째 select의 ()에도 where (=3, =2006) 조건을 넣어야 한다고 생각을 하였는데 저렇게 적어도 sub-query가 main`-query의 where문에 영향을 받는 건가요???

select OS.status_name
from orders_status as OS
where OS.id in (select O.status_id from orders as O);

sub query가 이러면 2006년 3월이라는 조건이 없는 거 아닌가요??

 

4. 2006년 1분기 동안 세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having)

/*2006년 1분기 동안 
세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having) */
select *
from order_details;

이걸 이용해보자

(여기에 있는 product_id를 사용하면 되지 않을까?)

/*2006년 1분기 동안 
세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having) */

select product_id, count(product_id)
from order_details
group by product_id;

이렇게 해보면 출력이 다음과 같이

나오고

여기에 조건문을 추가하면

select product_id, count(product_id)
from order_details
where count(product_id) > 2
group by product_id;

오류가 난다 이를 해결하기 위해서는 where 과 having 의 차이를 알아야 하는데

 

Where vs Having

• Where

select * from 테이블 
where 조건절;

where은 기본적인 조건절로써 언제나 from절 뒤에 존재한다.

• Having

SELECT * FROM 테이블 GROUP BY 필드 HAVING 조건절;

 

having은 언제나 group by 뒤에 존재

 

 

• Where vs Having 차이

둘다 필드에 조건을 줄 수 있다는 것은 동일하나,

WHERE은 기본적으로 모든 필드에 조건을 둘 수 있지만

HAVING은 GROUP BY로 그룹화 된 필드에 조건을 줄 수 있다.

(HAVING에서 조건을 줄 필드는 SELECT에 반드시 명시되어 있어야 한다.)

 

• Where vs Having 같이 사용할 경우

만약 두 조건절을 함께 사용한다면 아래와 같은 형태가 된다.

select * from 테이블명 
    where 조건절 group by 컬럼 
    having 조건절;

 

이를 적용해보면

select product_id, count(product_id)
from order_details
group by product_id
having count(product_id) > 2;

짠

이제 문제 조건을 계속 추가해보자

product id로 나타내니 별로 이쁘지 않아서 products table의 product_name을 사용해서 나타내보자

/*2006년 1분기 동안 
세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having) */

select product_id,P.product_name ,count(product_id)
from order_details as OD
left join products as P
ON OD.product_id = P.id
group by product_id
having count(product_id) > 2;

좀 더 보기 좋아졌다 

 

이제 2006년 1분기라는 조건을 달기위해 order_date가 있는 orders table을 join 시켜주자

/*2006년 1분기 동안 
세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having) */

select product_id,P.product_name ,count(product_id) as '주문 수'
from order_details as OD
inner join orders as O on OD.id = O.id
left join products as P on OD.product_id = P.id
where year(O.order_date) and quarter(O.order_date)
group by product_id
having count(product_id) > 2;

결과를 보았을 때 inner join을 쓴거랑 left join을 쓴 것이랑 차이가 없지만

누락 문제가 발생할 수 있다고 생각이 들어 inner join이 아닌 left join을 사용했습니다

 

정답

/*2006년 1분기 동안 
세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having) */

select product_id,P.product_name ,count(product_id) as '주문 수'
from order_details as OD
left join orders as O on OD.id = O.id
left join products as P on OD.product_id = P.id
where year(O.order_date) and quarter(O.order_date)
group by product_id
having count(product_id) > 2;

출력

질문

left join orders as O on OD.id = O.id
left join products as P on OD.product_id = P.id

이 부분에 있어서 inner join을 권유하셨지만 inner join이 아닌 left join을 사용해야 누락문제가 없지 않나요??

그런 생각을 하며 추가적으로 어떨 때 left join보다 inner join을 사용하면 좋은 경우를 알고 싶습니다

 

Reference

https://amaranth1ne.tistory.com/52

[SQL] WHERE 과 HAVING 의 차이