[패캠 부트캠프8기] SQL, Python

※ 본 내용은 [패스트캠퍼스 : 데이터 분석 부트캠프 8기 - Business Analyst를 위한 핵심 SQL 실전 및 Python] 강의 내용을 기반으로 작성하였습니다. 내용 복사 및 인용하여 게시글 작성 시 출처 작성 부탁드립니다.

SQL

Cross 조인, Self 조인

-- 상품과 배송사가 연결될 수 있는 모든 case 에 대해 상품ID, 상품명, 배송사ID, 배송사 이름, 배송사 전화번호 출력
SELECT p.ProductID
	, p.ProductName
    	, s.*
FROM Products p
CROSS JOIN Suppliers s;

-- 사원ID, LastName, FirstName 과 그 사원의 직속 상사ID, LastName, FirstName 출력(자신의 사번보다 1이 적은 사번이 직속상사라고 가정)
SELECT a.EmployeeID AS EmployeeID
	, a.LastName AS LastName
    , a.FirstName AS FirstName
    , b.EmployeeID AS BossID
    , b.LastName AS BossLN
    , b.FirstName AS BossFN
FROM Employees a, Employees b
WHERE a.EmployeeID = b.EmployeeID+1;

-- 상품 정보를 같은 카테고리끼리 연결하여 출력(상품명2개, 카테고리ID 1개, 상품명 2개가 동일한 데이터는 제외)
SELECT a.CategoryID, b.ProductName, b.ProductName
FROM Products a, Products b
WHERE a.CategoryID = b.CategoryID
AND a.ProductName <> b.ProductName;

---

서브쿼리

스칼라 서브쿼리

-- 상품명, 판매 단위, 카테고리 이름 출력
SELECT p.ProductName
	, p.Unit
    , (SELECT c.CategoryName FROM Categories c WHERE p.CategoryID = c.CategoryID) AS CategoryName
FROM Products p;

-- 주문 ID, 주문일자, 고객 이름, 배송사 전화번호 출력
SELECT  o.OrderID
	, o.OrderDate
    , (SELECT c.CustomerName FROM Customers c WHERE c.CustomerID = o.CustomerID) AS CustomerName
    , (SELECT s.Phone FROM Shippers s WHERE s.ShipperID = o.ShipperID) AS ShipperPhone
FROM Orders o;

-- 주문 수량이 가장 많은 상품의 상품 ID, 상품명, 총 주문 수량 추출
SELECT o.ProductID
	, (SELECT p.ProductName FROM Products p WHERE p.ProductID = o.ProductID) AS ProductName
	, SUM(o.Quantity) AS Quantity
FROM OrderDetails o
GROUP BY o.ProductID
ORDER BY o.ProductID DESC;

 

-- 주문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 EmployeeName
    , (SELECT s.ShipperName FROM Shippers s WHERE s.ShipperID = o.ShipperID) AS ShipperName
FROM Orders o;

-- 주문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;

 

인라인 뷰

-- 전체 상품 데이터를 출력 하되 공급사가 일본에 위치한 상품만 공급사 ID를 표시하
고 나머지는 NULL로 출력

SELECT p.ProductId
	, p.ProductName
    , s.SupplierID
    , p.CategoryID
    , p.Unit
    , p.Price
FROM Products p
LEFT OUTER JOIN (SELECT s.SupplierID FROM Suppliers s WHERE s.Country = 'Japan') AS s
ON s.SupplierID = p.SupplierID;

-- 전체 직원의 EmployeeID, LastName, FirstName, OrderYN 출력 (1997년 2월
판매 이력이 있는 경우 OrderYN ‘Y’로 표시)

SELECT e.EmployeeID
	, e.LastName
    , e.FirstName
    , CASE WHEN o.OrderID IS NOT NULL THEN 'Y' ELSE 'N' END AS OrderYN
FROM Employees e
LEFT OUTER JOIN
	(SELECT OrderID, EmployeeID
    	FROM Orders
        WHERE OrderDate LIKE '1997-02%') o
ON o.EmployeeID = e.EmployeeID;

-- 모든 주문 데이터의 주문 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 Address DESC;

-- 카테고리명과 해당 카테고리 상품 중 가장 비싼 상품의 가격 출력

SELECT c.CategoryName, p.Price
FROM Categories c,
(SELECT CategoryID, Max(Price) as Price
FROM Products
GROUP BY CategoryID) p
WHERE c.CategoryID = p.CategoryID;

---

EXISTS

-- 가격이 22인 상품을 공급하는 공급사의 이름과 전화번호 추출

SELECT s.SupplierName, s.Phone
FROM Suppliers s
WHERE EXISTS (
	SELECT 1
    FROM Products p
    WHERE p.Price = 22.00
    AND p.SupplierID = s.SupplierID
);

-- 1997년 1월 1일 주문 이력이 있는 고객 정보 추출

SELECT *
FROM Customers c
WHERE EXISTS (
	SELECT 1
    FROM Orders o
    WHERE o.OrderDate LIKE '1997-01-01'
    AND o.CustomerID = c.CustomerID
);

-- 1996년 12월에 판매 실적이 없는 사원의 모든 정보 추출

SELECT *
FROM Employees e
WHERE NOT EXISTS (
	SELECT 1
    FROM Orders o
    WHERE o.OrderDate LIKE '1996-12%'
    AND o.EmployeeID = e.EmployeeID
);

-- 상품 ID가 51인 상품이 주문된 날짜를 최근순으로 추출

SELECT *
FROM Orders o
WHERE EXISTS (
	SELECT 1
    FROM OrderDetails od
    WHERE od.ProductID = 51
    AND od.OrderID = o.OrderID
)
ORDER BY OrderDate DESC;

---

UNION & UNION ALL 

두 개 이상의 SELECT 결과 집합을 결합하여 출력

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

SELECT o.OrderDate, c.CustomerName FROM Orders o, Customers c WHERE o.CustomerId = c.CustomerID
UNION ALL
SELECT o.OrderDate, CONCAT(e.LastName, ' ' ,e.FirstName) FROM Orders o, Employees e WHERE o.EmployeeId = e.EmployeeId
ORDER BY OrderDate;

-- 고객의 도시와 공급사의 도시를 합하면 총 몇 군데인지 출력 

SELECT COUNT(*)
FROM (
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
) AS Total;

-- 배송사와 공급사의 데이터 중 전화번호가 동일한 곳의 전화번호 출력

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

---

WITH ROLLUP

그룹별 집계를 반환 (합계, 집계, 평균 등 반환)

-- 국가별 고객의 수와 전체 고객의 수 출력

SELECT City, COUNT(*)
FROM Customers
GROUP BY City WITH ROLLUP;

-- 싱픔별 주문 수량의 합과  Total 주문 수량 출력

SELECT ProductID, SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID WITH ROLLUP;

-- 국가별, 도시별 고객의 수와 국가별 고객의 수와 전체 고객의 수 출력

SELECT Country, City, COUNT(*)
FROM Customers
GROUP BY Country, City WITH ROLLUP;

-- 카테고리 별 상품의 평균 가격과 전체 상품의 가격을 출력
(카테고리는 이름으로 출력, 평균 가격은 반올림하여 소수점 아래 둘째 자리까지 표시)

SELECT (SELECT c.CategoryName FROM Categories c WHERE c.CategoryId = p.CategoryId) AS cateName
	, ROUND(AVG(p.Price),2) AS productAvg
FROM Products p
GROUP BY cateName WITH ROLLUP;

 

---

PIVOT

데이터의 행과 열을 90도 회전

-- 출생년도 별 사원의 수를 피벗하여 출력

SELECT FROM GROUP
SELECT
FROM
YEAR(BirthDate), COUNT(*) Employees
BY YEAR(BirthDate);
SUM(CASE WHEN YEAR(BirthDate) = '1952' THEN 1 END) as "1952 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1955' THEN 1 END) as "1955 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1958' THEN 1 END) as "1958 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1960' THEN 1 END) as "1960 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1963' THEN 1 END) as "1963 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1968' THEN 1 END) as "1968 ", 
SUM(CASE WHEN YEAR(BirthDate) = '1969' THEN 1 END) as "1969 " 
Employees;

---

WINDOW 함수

행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

참고 URL : https://mizykk.tistory.com/121

[MySQL] 윈도우함수(Window Function)

 

---

뷰(View)

특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트, 가상 테이블

실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 SELECT 문만 저장

---

DML

데이터를 입력하고 입력된 데이터를 수정, 삭제, 조회하는 명령

DDL

데이터를 정의하는 SQL

 

---

Python

생산성 향상과 유지 관리비용 절감을 위해 사용

 

자료형

• 문자 : String (문자열)
• 숫자 : int (정수), float (실수), complex (복소수)
• 논리 : boolean (true/ false)
• 사칙연산 : +, - , *, /, %(나머지), // (몫)

변수 (variable)

객체를 가리키는 이름, 대입 연산자라고도 한다.

제어문

• if : 만약 ...라면
• elif : 그렇지 않고 만약 ... 라면
• else : 그렇지 않으면 ...앞 조건에 해당되지 않으면 전부

# if, elif, else 코드 구조
# 조건 작성 후 마지막에는 `:` 을 작성하여 마친다.
# 조건에 실행할 명령블록은 들여쓰기 하여 작성한다. 

if [조건] :
	[조건이 참 일시 실행되는 명령어]
elif 조건 :
	[조건이 참 일시 실행되는 명령어]
else :
	[앞 조건에 해당하지 않을시 실행되는 명령어]

반복문

• for
• while

# for, while 문 조건 마지막에는 `:` 로 마무리 한다.
# 조건에 실행할 명령블록은 들여쓰기 하여 작성한다.

# for 문
for [반복할 개체를 담을 임의의 항목] in [반복할 개체] :
	[반복 실행할 명령어]

# while 문
while [반복할 조건] :
	[조건이 참 일시 실행할 명령어]

반복문에서 빠져나가야 할때 실행할 명령어

• continue : 반복문이나 조건문에서 조건이 충족될 시 해당 실행코드를 건너뛰고 반복문을 실행한다.
• break : 아예 반복문에서 빠져나온다.

반복문에서 자주 사용하는 함수

• range() : 연속적인 숫자 객체를 만들어서 반환해주는 함수이다.

참고 URL : https://blockdmask.tistory.com/519

[python] 파이썬 range 함수 정리 및 예제 (숫자 구하기)

 

---

함수 (function)

호출될 때만 기능을 수행하기 위해 실행되는 코드 블록

• 인자(argument, parameter 등) 라고 하는 데이터를 함수에 전달할 수 있다.
• 함수는 결과로 데이터를 반환(return) 할 수 있다.