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

https://aisj.tistory.com/99

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

https://aisj.tistory.com/100

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

이어서 작성

5-1. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query, inner join)

 

앞에서 깨달은 방법을 적용해보고자 해보았다

직원(employee) "을" 찾는 쿼리 이므로 employee가 있는 table을 main table로 가져와 보자

이쯤 생기는 감 -> employee_id columns이 있는 order관련 table이 있을 것이다

정답

먼저 이 두개를 연결 시켜보자

select *
from employees

select *
from orders

개인적인 의심으로 인하여 정말 employees의 id 와 orders table의 employee_id가 동일한지를 확인하고자 하였다

(left join이 아닌 inner join을 해도 상관이 없을지)

employee table의 id (1~ 9)

orders table의 employee_id (1 ~ 9)

둘이 동일히기 때문에 inner join을 해도 상관이 없을 것 같다

하지만 inner join을 한 결과 다음과 같은 결과를 얻게 되었다

employees table의 row 수가 9 개

order table의 row 수가 

48 개 인점을 감안하면

두개의 곱한 값인  9 * 48 =432가 만들어진 것으로 확인이 된다

 

즉, inner join을 하는 것이 아니고

order table을 기준으로 employee_id에 대해 employeetable을 추가해주는 느낌으로 접근을 해야한다

(left join)

/* 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query, inner join) */

select *
from orders as O
left join employees as E on O.employee_id = E.id;

48개의 order 정보가 유지된 모습을 볼 수 있다

이 테이블에서 order date를 이용하여 1분기 2분기 연속으로 주문을 받은 사람을 뽑아내보자

select *
from orders as O
left join employees as E on O.employee_id = E.id
where year(O.order_date)=2006 and quarter(O.order_date)=1;

이걸로 1분기를 뽑고

select *
from orders as O
left join employees as E on O.employee_id = E.id
where year(O.order_date)=2006 and quarter(O.order_date)=2;

이걸로 2분기를 뽑을 순 있겠는데 이 두개를 어떻게 연결해야 할지...

 

inner join으로 최대한 해보자

 

select *
from ((select O.employee_id
      from orders as O
      left join employees as E on O.employee_id = E.id
      where year(O.order_date) and quarter(O.order_date)=1)
inner join 
      (select O.employee_id 
      from orders as O 
      left join employees as E on O.employee_id = E.id 
      where year(O.order_date)=2006 and quarter(O.order_date)=2))

 

다음과 같이 두개의 query를 만든 다음 inner join을 해주면 되지 않을까???

여기서 alias란 별명

즉, 이름을 말한다. 

에러가 발생한 이유는 서브쿼리에 alias(이름)이 지정되지 않았기 때문이다.

에러를 수정하는 방법은 서브쿼리에 alias(이름)을 지정하면 된다.

 

select *
from ((select O.employee_id
      from orders as O
      left join employees as E on O.employee_id = E.id
      where year(O.order_date) and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      left join employees as E on O.employee_id = E.id 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b)

에러는 해결이 되었지만 row가 1089개가 나와버렸다

 

이는 inner join의 조건을 주지않아서 생긴 문제사항같아서 ON 조건문을 추가를 해주면

실행해본 결과 left join과 inner join의 결과 차이는 없지만 문제에서 inner join을 힌트로 줬으므로 inner join을 사용하자

select *
from ((select O.employee_id
      from orders as O
      left join employees as E on O.employee_id = E.id
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join 
      (select O.employee_id 
      from orders as O 
      left join employees as E on O.employee_id = E.id 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id)

다음 출력과 같이 중복이 엄청 많으므로 중복을 제거해주자

 

SELECT DISTINCT -> 중복제거

select distinct *
from ((select O.employee_id
      from orders as O
      left join employees as E on O.employee_id = E.id
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      left join employees as E on O.employee_id = E.id 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id)

하지만 분명히 전체 출력을 했음에도 불구하고 left join으로 사용했던 employee관련 column이 보이지 않는 것을 확인할 수 있다 

이는 inner join을 할 때 서브 쿼리에서 select를 employee id로 가져와서 생긴 문제 같으므로 이를 바깥으로 빼주면

select distinct *
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id
left join employees as E on a.employee_id = E.id);

잘 들어온 것을 확인할 수 있다

 

※나중에 확인한 사실※

a query의 O.order_date =1을 2로 유지한 채로 사용하여서 과정중의 출력이 잘못되어 있습니다 

 

이제 여기서 이름만 뽑아낸 다면

정답

/* 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query, inner join) */
select distinct first_name, last_name
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id
left join employees as E on a.employee_id = E.id);

출력

질문

다음과 같이 inner join을 한 table에 alias(c)를 달아서 left join의 조건으로 그 alias(c.employee_id)를 사용하고 싶은데

/* 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query, inner join) */
select distinct first_name, last_name
from (((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id) c
left join employees as E on c.employee_id = E.id);

다음과 같은 오류가 나서 하지를 못하였습니다 방법이 없을까요???

 

 

 

5-2. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 2006년 월별 주문 수를 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query 중첩, date_format() )

 

위에 작성한 쿼리에 기반으로

select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id);

여기서 부터 시작

where O.employee_id in (select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id));

where 의 조건 안으로 넣어서 전체 쿼리의 조건을 걸어주고

쿼리를 완성해주자

select *
from orders as O
where O.employee_id in (select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id));

조건에 걸린대호 employee_id 2, 5는 빠져있는 모습이다

여기에 Group by를 추가해 주고 select 문을 group by에 맞게 수정해주자

/*2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 
2006년 월별 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query 중첩, date_format() ) */
select count(id)
from orders as O
where O.employee_id in (select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id))
group by employee_id;

여기에 group by로 묶은 애들을 출력을 해주고

group by를 employee_id 말고도 date_format으로 조건을 추가해주자

select employee_id, count(id) as "주문수"
from orders as O
where O.employee_id in (select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id))
group by employee_id, date_format(O.order_date, '%Y-%m');

이렇게만 적으면 어떤 월인지 알 수가 없어서 select문에 월을 출력할 수 있도록 추가해주었다

select employee_id,date_format(O.order_date,'%m') as '월' ,count(id) as "주문수"
from orders as O
where O.employee_id in (select distinct a.employee_id
from ((select O.employee_id
      from orders as O
      where year(O.order_date)=2006 and quarter(O.order_date)=1) a
inner join  
      (select O.employee_id 
      from orders as O 
      where year(O.order_date)=2006 and quarter(O.order_date)=2) b
on a.employee_id = b.employee_id))
group by employee_id, date_format(O.order_date, '%Y-%m');

Group by 는 %Y-%m으로 했으면서 어떻게 본인더러 %m을 하라고 하냐고 따지는 느낌이다

생각을 해보면 어쩌피 sub query조건으로 2006을 주었으므로 %Y를 빼주자

정답

/*2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 
2006년 월별 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query 중첩, date_format() ) */
select O.employee_id,date_format(O.order_date,'%m') as '월' ,count(id) as "주문수"
from orders as O 
where O.employee_id in (select distinct a.employee_id
                        from ((select O.employee_id
                               from orders as O
                               where year(O.order_date)=2006 and quarter(O.order_date)=1) a
                            inner join  
                               (select O.employee_id 
                                from orders as O 
                                where year(O.order_date)=2006 and quarter(O.order_date)=2) b
                            on a.employee_id = b.employee_id))
group by O.employee_id, date_format(O.order_date, '%m')

출력

질문

 보기 좋게 위에서 했던 것과 같이 id를 이름으로 바꿔주기 위해 다음과 같이 left join을 추가했습니다

/*2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 
2006년 월별 주문 수를 찾는 쿼리를 작성하세요. 
(order_status는 신경쓰지 않으셔도 됩니다.) 
(힌트: sub-query 중첩, date_format() ) */
select O.employee_id,date_format(O.order_date,'%m') as '월' ,count(id) as "주문수"
from orders as O 
     left join employees as E 
     on O.employee_id = E.id

where O.employee_id in (select distinct a.employee_id
                        from ((select O.employee_id
                               from orders as O
                               where year(O.order_date)=2006 and quarter(O.order_date)=1) a
                            inner join  
                               (select O.employee_id 
                                from orders as O 
                                where year(O.order_date)=2006 and quarter(O.order_date)=2) b
                            on a.employee_id = b.employee_id))
group by O.employee_id, date_format(O.order_date, '%m')

alias도 잘써주고 employee에도 id가 있는데 왜 이 오류가 나오는지 모르겠습니다 ㅠㅠ

 

 

Reference

https://lelecoder.com/83

MySQL "Every derived table must have its own alias" 에러 처리