23.03.10 ~ 23.03.16 동안 부트캠프에서 배웠던 내용을 정리한 학습일지입니다.

4주차에는 다 못한 SQL 강의와 Python 강의를 들었습니다!

SQL 노션 링크: https://solar-geology-c0d.notion.site/SQL-a72bb0e3ea6844baa3d18681090fb14d

부트캠프 4주차 - 2023.03.10 ~ 2023.03.16* "Business Analyst를 위한 핵심 SQL 실전2"*

4. JOIN

: 각각의 테이블을 연결하여 한 번에 출력해 주는 구문

join 종류

EQUI JOIN: EQUI(=) 조건으로 JOIN
Non EQUI JOIN: EQUI(=) 조건이 아닌 다른 조건(Between, >, < 등)으로 JOIN

4.1 INNER JOIN

: JOIN 조건에 충족하는 데이터만 출력되는 것으로 교집합에 해당

4.2 OUTER JOIN

: 두 테이블 간의 교집합 + 한쪽 테이블에만 있는 데이터 조회

OUTER JOIN을 수행하는 방법은 LEFT OUTER JOIN, RIGTH OUTER JOIN 두 종류가 있지만 보통 LEFT OUTER JOIN만 사용

4.3 CROSS JOIN

: 각 테이블 간 조합할 수 있는 모든 경우를 출력

# 상품과 배송사가 연결될 수 있는 모든 case에 대해 상품id, 상품명, 배송사 id, 배송사 이름, 배송사 전화번호 출력
select p.ProductID, p.ProductName, s.*
from Products p 
cross join Shippers s


# *를 사용하면 특정 테이블의 모든 컬럼 출력할 수 있음

4.4 SELF JOIN

: 자기 자신과 join 하는 방식

# 사원id, lastname, firstname과 그 사원의 직속 상사 id, lastname, firstname 출력
## (자신의 사번보다 1이 적은 사번이 직속 상사라 가정)

select e.EmployeeID, e.LastName, e.FirstName, 
	   E.EmployeeID as BossID, E.LastName as Boss_FN, E.FirstName as BossFN
from Employees e, Employees E
where e.EmployeeID = (E.EmployeeID+1)

# 상품 정보를 같은 카테고리끼리 연결하여 출력 
##(상품명 2개, 카테고리 id 1개 출력, 단 상품명 2개가 동일한 데이터는 제외)

select a.ProductName, 
       b.ProductName as PN_b, b.CategoryID as CI_b
from Products a, Products b
where a.CategoryID = b.CategoryID 
  and  a.ProductName != b.ProductName

5. Subquery

: 하나의 쿼리 안에 존재하는 또 다른 쿼리

SELECT 절	스칼라(Scalar) 서브쿼리	하나의 컬럼 값만 출력되야 함
FROM 절	인라인 뷰 (Inliner View)	하나의 테이블 생성 (별칭이 무조건 필요)
WHERE 절, HAVING 절	중첩(Neasted) 서브쿼리	하나의 컬럼값을 출력

5.1 스칼라 서브쿼리 (Scalar SubQuery)

: 주로 slelect 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에서 사용 가능하며 반드시 하나의 컬럼값만 가져와야 함

#1. 상품명, 판매단위, 카테고리 이름 출력
select p.ProductName, p.Unit,
	   (select c.CategoryName from Categories c where p.CategoryID= c.CategoryID) as CategoryName
from Products p;

#2. 주문id, 주문일자, 고객이름, 배송사 전화번호 출력
select o.OrderID,
	o.OrderDate, 
	(select c.CustomerName from Customers c where o.CustomerID = c.CustomerID) as '고객이름', 
        (select s.Phone from Shippers s where s.ShipperID = o.ShipperID) as '전화번호'
from Orders o


#3. 주문 수량이 가장 많은 상품의 상품 ID, 상품명, 총 수량
select o.ProductID,
	  (select p.ProductName from Products p where p.ProductID = o.ProductID) as ProductName,
       sum(o.Quantity) as Quatity
from OrderDetails o
group by o.ProductID
order by Quatity desc



#4. 주문 id, 고객이름, 사원이름(lastname + firstname), 배송사 이름 추출
select o.OrderID, 
	   (select c.CustomerName from Customers c where c.CustomerID = o.CustomerID) as CustomerName,
       (select concat(e.LastName, ' ' , e.FirstName) from Employees e where e.EmployeeID = o.EmployeeID) as e_name,
       (select s.ShipperName from Shippers s where s.ShipperId = o.ShipperID) as S_name
from Orders o 



#5. 주문 id 별 total 주문 금액 추출
select t.OrderID,
	   sum(t.Quantity * t.Price) as TotalPrice
from (select od.OrderID,
		od.Quantity,
		(select p.Price from Products p where p.ProductID = od.ProductID) as Price
       from OrderDetails od ) t
group by t.OrderID;

5.2 인라인 뷰(Inline View)

: From 절 등 테이블명이 올 수 있는 위치에 사용

# 1996-07-10에 상품을 판매하지 못한 직원명 출력

select e.EmployeeID, e.FirstName  
from Employees e  
left outer join  (select OrderID, EmployeeID     
                   from Orders    
                   where OrderDate = '1996-07-10') o    
on e.EmployeeID = o.EmployeeID 
where o.OrderID is null;

#1. 전체 상품 데이터를 출력하되 공급사가 일본에 위치한 상품 공급사 id를 표시하고 나머진 null
select p.ProductID,
        p.ProductName,
        s.SupplierID,
        p.CategoryID,
        p.Unit,
        p.Price
from Products p
left outer join (select SupplierID from Suppliers 
				 where Country = 'Japan') s
on p.SupplierID = s.SupplierID;



# 2 전체 직원employeeid, lastname, firstname, orderYN 출력
## (1997년 2월 판매 이력이 있는 경우 orderyn을 y로 아니면 n로 표시 )
select e.EmployeeID, e.LastName, e.FirstName, 
	case when o.OrderDate is not null  then 'Y'
        else 'N'
        end OrderYN
from Employees e
left outer join 
(select * from Orders
where OrderDate like '1997-02-%') o
on e.EmployeeID = o.EmployeeID




#3 모든 주문 데이터의 주문 id, 주문일자, 고객이름, 주소를 조회하되 고객의 국가가 스페인일 경우만 고객 이름과 주소를 표시 (스페인 고객의 정보가 위로 오도록 출력)
select o.OrderID, o.OrderDate, c.CustomerName, c.Address  
from Orders o
left outer join 
     (select CustomerID, CustomerName, Address 
      from Customers where Country = 'Spain') c
on o.CustomerID = c.CustomerID
order by c.Address desc

#mysql은 null값을 최대값으로 인정



#4. 카테고리명과 해당 카테고리 상품 중 가장 비싼 상품의 가격 출력
select c.CategoryName,p.max
from Categories c, (select CategoryID, max(Price) as max 
                    from Products
                    group by CategoryID ) p
where c.CategoryID = p.CategoryID

5.3 중첩 서브쿼리 (Nseted Subquery)

: where절과 having 절에 사용 가능

비연관 서브쿼리: 메인 쿼리와 관계를 맺고 있지 않음
연관 서브쿼리: 메인 쿼리와 관계를 맺고 있음

# 가장 가격이 비싼 제품의 정보를 가져오기 (비연관 서브쿼리 사용)
select * from Products
where Price = (select max(Price) from Products);


# 상품 id별로 가장 적은 수량의 주문 제품 정보 추출 (연관 서브쿼리 사용)
select * from OrderDetails a
where Quantity = (select min(Quantity) from OrderDetails b
				  where a.ProductID = b.ProductID)
order by a.ProductID

#1. 주문 이력이 없는 고객의 모든 정보 추출
select * from Customers
where CustomerID not in (select CustomerID from Orders);


#2. 전화번호가 '3199'로 끝나는 배송사로 배송되는 주문건의 모든 정보 추
select *
from Orders 
where ShipperID = (select ShipperID  from Shippers
					where Phone like '%3199');



#3. 동일 주문건에서 가장 주문 수량이 많은 상품의 주문 데이터만 추출 (주문id, 상품 id, 주문수량)
select a.OrderID, a.ProductID, a.Quantity
from OrderDetails a
where a.Quantity = (select max(Quantity)
					from OrderDetails b
                    where b.OrderID = a.OrderID)



#4 50명 이상의 고객에게 판매를 성공한 사원의 모든 정보 추출
select *
from Employees 
where EmployeeID in (select EmployeeID 
					from Orders
					group by EmployeeID
					having count(distinct CustomerID) >= 50)

5.4 EXISTS

: 서브쿼리에 해당하는 데이터가 있는지 확인하는 구문. 하나 이상의 레코드가 존재하는 경우 TRUE (반드시 서브쿼리와 함께 사용해야 함)

# 가격이 20 미만인 상품의 공급자 ID 추출

select SupplierName
from Suppliers s
where exists (select 1 # 이 로우가 있는지 없는지만 판단하겠다 (로우가 있으면 1 출력 없으면 no data)
			  from Products p
              where p.SupplierID = s.SupplierID
              and p.Price < 20)

#1 가격이 22인 상품을 공급하는 공급사의 이름과 전화번호 추출
select SupplierName, Phone
from Suppliers s
where exists (select 1
			  from Products p
              where Price = 22 
              and  p.SupplierID = s.SupplierID)
              

#2 1997년 1월 1일에 주문 이력이 있는 고객 정보 추출
select * 
from Customers c
where exists (select 1
				from Orders o
                where c.CustomerID = o.CustomerID


#3 1996년 12월에 판매 실적이 없는 사원의 모든 정보 추출
select * 
from Employees e
where not exists (select 1
			  from Orders o
              where e.EmployeeID = o.EmployeeID
              and o.OrderDate like '1996-12-%')


#4 상품 id 가 51인 상품이 주문된 날짜를 최근순으로 추출
select o.OrderDate
from Orders o
where exists (select 1
			  from OrderDetails od
              where o.OrderID = od.OrderID 
                and od.ProductID = 51 )
order by o.OrderDate desc;

6. UNION & UNION ALL

: 두 개 이상의 select 결과 집합을 결합하여 출력. join의 경우 테이블을 세로로 붙인다면 union의 경우 가로로 붙이는 형식. 그러므로 union을 할 때는 각 테이블의 컬럼 수와 데이터 타입이 동일해야 함.

UNION: 공통된 row 제외하고 출력
UNION ALL : 테이블 상의 모든 row를 출력

#1. 주문 일자와 고객 이름 + 주문 일자와 사원 이름 출력 (주문 일자로 오름차순)

select o.OrderDate, c.CustomerName as Name
from Orders o 
inner join Customers c on o.CustomerID = c.CustomerID

union all

select o.OrderDate ,concat(e.LastName, ' ', e.FirstName)as Name 
from Orders o 
inner join Employees e  on e.EmployeeID = o.EmployeeID
order by OrderDate

#2 고객의 도시과 공급사의 도시를 합하면 총 몇군데인지 출력
select count(a.City)
from (select City from Customers
	  union 
	  select City from Suppliers
       ) a


#3 배송사와 공급사 데이터 중 전화번호가 동일한 곳의 전화번로 출력
select t.Phone
from (select Phone 
      from Suppliers 
      union all
      select Phone 
      from Shippers 
      ) t
group by t.Phone
having count(*) > 1

7. 그 외

7.1 WITH ROLLUP

:그룹별 집계를 반환

#1 국가별 고객의 수와 전체 고객의 수 출력
select Country, count(*)
from Customers
group by Country with rollup

#2 상품별 주문 수량의 합과 total 주문 수향 출력
select ProductID, sum(Quantity) 
from OrderDetails
group by ProductID with rollup


#3 국가별, 도시별 고객의 수와 국가별 고객의 수와 전체 고객의 수 출렷
select Country, City, count(*) 
from Customers
group by Country, City with rollup 


#4 카테고리별 상품의 평균 가격과 전체 상품의 평균 가격을 출력
## (카테고리는 이름으로 출력, 평균가격은 반올림하여 소수점 아래 둘째 자리까지 표시))
select c.CategoryName , round(avg(p.Price), 2) as avg
from Products p
inner join Categories c on p.CategoryID = c.CategoryID
group by c.CategoryName with rollup

7.2 WINDOW FUNCTION

: 행과 행간의 관계를 정의하기 위해 제공되는 함수. 윈도우 함수를 통해 순위, 합계, 평균 등을 조작할 수 있음

[구조]

select window_function('인수') over (partition by 컬럼 order by 컬럼)
from [테이블명];

[순위 함수]

함수명	출력 순위
rank()	1, 2, 2, 4, 5, 5 ,7
dense_rank()	1, 2, 2, 3, 4, 4, 5
row_number()	1, 2, 3, 4, 5, 6, 7

[집계 함수]

sum(인수	파티션 별로 합계 계산
avg(인수)	파티션 별로 평균 계산
count(인수)	파티션 별로 행 수를 계산
min, max(인수)	파티션 별로 최솟값, 최댓값 계산

7.3 PIVOT

: 데이터의 행과 열을 90º 회전

[문제] 고객 수가 많은 국가 랭킹 5위까지 피벗해서 출력

[setp1] 고객 수가 많은 국가 랭킹 5위 구하기

select Country, count(*) from Customers
group by Country
order by count(*) desc
limit 5;

[setp2] case when문을 이용해 피벗하기

select sum(case when Country = 'USA' then 1 end) 'USA',
	   sum(case when Country = 'Germany' then 1 end) 'Germany',
       sum(case when Country = 'France' then 1 end) 'France',
       sum(case when Country = 'Brazil' then 1 end) 'Brazil',
       sum(case when Country = 'UK' then 1 end) 'UK'
from Customers;

🔥어려운 문제 집중탐구🔥

[문제 1] 주문 ID별 total 주문금액 추출 (인라인뷰, 스칼라 서브쿼리 사용)

select t.OrderID,
	sum(t.Quantity * t.Price) as total_price
from (select od.OrderID,
	od.Quantity,
	(select p.Price from Products p 
         where p.ProductID = od.ProductID) as Price
         from OrderDetails od ) t
group by t.OrderID;

[문제 2] 전체 직원의 EmployeeID, LastName, FirstName, OrderYN 출력 (1997년 2월 판매 이력이 있는 경우 OrderYN ‘Y’로 표시) (인라인뷰 사용)

select e.EmployeeID,
	e.LastName, 
	e.FirstName, 
	case when o.OrderDate is not null  then 'Y'
	else 'N'
	end OrderYN
from Employees e
left outer join (select * from Orders
		 where OrderDate like '1997-02-%') o
on e.EmployeeID = o.EmployeeID

[문제 3] 카테고리명과 해당 카테고리 상품 중 가장 비싼 상품의 가격 출력 (인라인 뷰 사용)

select c.CategoryName, 
	max(p.Price) as max
from Categories c
inner join (select CategoryID, Price 
	   from Products) p
on c.CategoryID = p.CategoryID
group by c.CategoryName;

------- 아래 위 동일

select c.CategoryName, 
	p.max
from Categories c
inner join
(select CategoryID, max(Price) as max 
from Products
group by CategoryID ) p
on c.CategoryID = p.CategoryID

[문제 4] 주문 이력이 없는 고객의 모든 정보 추출 (중첩 서브쿼리 사용)

select *
from Customers 
where CustomerID not in (select CustomerID from Orders)

→ Orders 테이블에 CustomerID가 없다면 주문을 한 번도 하지 않은 고객을 의미. (두 테이블 간의 관계 파악에서 어려움을 느낀 듯)

[문제 5] 동일 주문 건에서 가장 주문 수량이 많은 상품의 주문 데이터만 추출 (주문 ID, 상품 ID, 주문 수량 ) (중첩 서브쿼리 사용)

1. 틀린 코드: 서브쿼리 사용 x

select OrderID, ProductID, Quantity
from OrderDetails
group by OrderID
having Quantity = max(Quantity);

결과) 444건 출력

코드가 틀린 이유: 고유한 주문 아이디의 수가 830건인데, 그 보다 아래값이 출력되면 안 됨.

2. 정답 코드

select a.OrderID, a.ProductID, a.Quantity 
from OrderDetails a
where a.Quantity = (select max(b.Quantity)
		   from OrderDetails b
                   where a.OrderID = b.OrderID)

결과) 906건 출력

정답 코드가 906건인 이유: 한 주문 건에 다른 제품을 동일하게 max개 구매하는 경우가 있음으로, 830건 보다 많이 추출됨

+ 추가 풀이

w3school에서는 window function을 지원하지 않아서 정답 코드인지 확인하지 못하였으나 window function을 사용해서도 풀 수 있을 거 같아 생각해 봤습니다.

select t.OrderID, t.ProductID, t.rank
from (select OrderID, 
	     ProductID, 
             rank() over(partition by OrderID order by Quantity desc) as rank
      from OrderDetails) t
where rank = 1

[문제 6] 배송사와 공급사 데이터 중 전화번호가 동일한 곳의 전화번호 출력

SELECT a.Phone
FROM (SELECT Phone FROM Shippers
	UNION ALL
	SELECT Phone FROM Suppliers) a
GROUP BY a.Phone
HAVING COUNT(*) > 1;

+ SQL에 관한 정리가 끝났습니다 👏👏

복습하면서 문제를 다시 풀어봤는데 틀렸던 문제는 또 틀리는 걸 발견...ㅠ

그래서 마지막 파트에 어려웠던 문제만 집중적으로 파보는 시간을 가졌습니다!!

어떤 강사님께서 SQL은 개인마다 유독 약한 부분이 있으니

어려웠던 문제, 틀린 문제는 따로 정리해서 주기적으로 풀어보라고 말해주셨는데 확실히 맞는 거 같아요.

문제 풀면서 left outer join이랑 inline view + scalar 같이 쓰는 문제는 정말....

아이패드에 테이블명이랑 컬럼명 다 리스트업 해서 어떤 식으로 접근해야 할지 일일이 적으면서 해야 겨우겨우 풀었답니다...^^

계속 반복해서 결국 제 걸로 만들겠습니다 (๑•̀ㅂ•́)و✧

'데이터 분석 > 데이터 분석 부트캠프' 카테고리의 다른 글

[패스트캠퍼스] 데이터 분석 부트캠프 8기 6주차 학습일지 (0)	2023.03.30
[패스트캠퍼스] 데이터 분석 부트캠프 8기 5주차 학습일지 (0)	2023.03.23
[패스트캠퍼스] 데이터 분석 부트캠프 8기 3주차 학습일지 (0)	2023.03.09
[패스트캠퍼스] 데이터 분석 부트캠프 8기 2주차 학습일지 (2)	2023.03.02
[패스트캠퍼스] 데이터 분석 부트캠프 8기 1주차 학습일지 (0)	2023.02.23

select passion from d1

[패스트캠퍼스] 데이터 분석 부트캠프 8기 4주차 학습일지

부트캠프 4주차 - 2023.03.10 ~ 2023.03.16* "Business Analyst를 위한 핵심 SQL 실전2"*

4. JOIN

4.1 INNER JOIN

4.2 OUTER JOIN

4.3 CROSS JOIN

4.4 SELF JOIN

5. Subquery

5.1 스칼라 서브쿼리 (Scalar SubQuery)

5.2 인라인 뷰(Inline View)

5.3 중첩 서브쿼리 (Nseted Subquery)

5.4 EXISTS

6. UNION & UNION ALL

7. 그 외

7.1 WITH ROLLUP

7.2 WINDOW FUNCTION

7.3 PIVOT

🔥어려운 문제 집중탐구🔥

'데이터 분석 > 데이터 분석 부트캠프' 카테고리의 다른 글

티스토리툴바

[패스트캠퍼스] 데이터 분석 부트캠프 8기 4주차 학습일지

부트캠프 4주차 - 2023.03.10 ~ 2023.03.16 "Business Analyst를 위한 핵심 SQL 실전2"

4. JOIN

4.1 INNER JOIN

4.2 OUTER JOIN

4.3 CROSS JOIN

4.4 SELF JOIN

5. Subquery

5.1 스칼라 서브쿼리 (Scalar SubQuery)

5.2 인라인 뷰(Inline View)

5.3 중첩 서브쿼리 (Nseted Subquery)

5.4 EXISTS

6. UNION & UNION ALL

7. 그 외

7.1 WITH ROLLUP

7.2 WINDOW FUNCTION

7.3 PIVOT

🔥어려운 문제 집중탐구🔥

'데이터 분석 > 데이터 분석 부트캠프' 카테고리의 다른 글

'데이터 분석/데이터 분석 부트캠프' Related Articles

티스토리툴바

부트캠프 4주차 - 2023.03.10 ~ 2023.03.16* "Business Analyst를 위한 핵심 SQL 실전2"*