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

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

3주차에는 정미나 강사님께서 진행하시는 SQL을 교육을 수강해서 이번 학습일지는 SQL에 관한 내용입니다.

개인적으로 SQLD 자격증 준비하면서 기본 문법을 공부하기도 했고

그 후로도 프로그래머스랑 코멘토 등을 통해 접할 기회가 많아서 복습 개념으로 듣기 좋았습니다!

(본 코드는 MySQL 환경에서 작성되었습니다.)

 

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

---

 

 

 

부트캠프 3주차 - 2023.03.03 ~ 2023.03.09  "Business Analyst를 위한 핵심 SQL 실전"

 

 

SQL (Structured Query Language)

• 관계형 데이터베이스 관리시스켐의 데이터 관리를 위해 설계된 특수 목적으로 된 프로그래밍 언어
• 데이터베이스 스키마 생성 및 수정
• 자료의 검색 및 관리
• 데이터베이스 객체 접근 조정 관리

 

실습 환경

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all

 

- DB 종류

 

- Table 소개

    1) Customers: 고객 정보를 담은 테이블

    2) Categories: 상품의 카테고리 정보를 담은 테이블

    3) Employees: 직원 정보를 담은 테이블

    4) OrderDetailes: 주문 상세 정보를 담은 테이블

    5) Orders: 주문 정보를 담은 테이블

    6) Products: 상품 정보를 담은 테이블

    7) Shippers: 배송자 정보를 담은 테이블

    8) Suppliers: 공급자 정보를 담은 테이블

 

 

1. 데이터 조회하기

- 작성 순서

💡 SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

 

- 실행 순서

💡 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

 

 

 

1.1 SELECT

: 데이터를 조회하고자 할 때 사용하는 명령어

• DISTINCT: 컬럼명 앞에 지정하여 중복값을 제거
• ALIAS: 테이블명 or 컬럼명을 간략하게 할 때 사용

 

#주문을 한 고객 명단 추출
select distinct CustomerID from Orders;

#상품의 카테고리 ID는 몇 종류인지 추출
select count(distinct CategoryID) as cnt from Products;

#상품의 카테고리 ID 별로 매핑된 공급자 Id 추출
select distinct CategoryID, SupplierID from Products
order by CategoryID, SupplierID;

 

1.2 FROM

: 데이터를 가져올 테이블 명 입력

 

 

1.3 WHERE

: 쿼리에서 반환되는 행에 대한 특정 조건을 지정

 

 

*️⃣ where절에서 사용 가능한 연산자

 

Ⅰ) 비교 연산자: =, >, <, ≥, ≤

Ⅱ) 부정 비교 연산자: !=, ^=, <> ; 같지 않은 것 조회

Ⅲ) 논리 연산자

    · AND: 모든 조건이 충족되는 행 반환

    · OR: 조건 중 하나 이상이 충족되는 행 반환

    · NOT: 부정 연산자/ NOT True = False

 

 

#고객의 ContactNAme, Address 추출
select ContactNAme, Address from Customers
where CustomerName = 'Ernst Handel';

#Michael이란 FirstName을 가진 사원의 생일 추출
select BirthDate from Employees
where FirstName = 'Michael';

#'Aniseed Syrup' 상품의 가격 추출
select Price from Products
where ProductNAme = 'Aniseed Syrup';

#Tokyo Traders 공급사의 전화번호 추출
select Phone from Suppliers
where SupplierName = 'Tokyo Traders';

#'Beverages', 'Seafood' 카테고리의 Description 추출
select Description from Categories
where CategoryName = 'Beverages' or  CategoryName = 'Seafood';

#Robert King 사원의 모든 정보 추출
select * from Employees
where FirstName = 'Robert' and LastName = 'King';

#일본 도쿄에 있는 공급사의 이름, 전화번호 추출
select SupplierName, Phone from Suppliers
where City = 'Tokyo' and Country = 'Japan';

#가격이 55이상인 상품의 ID와 이름, 가격 정보 추출
select ProductID, ProductName, Price from Products
where Price >= 55;

#1960년대 이후에 태어난 사원의 모든 정보 추출
select * from Employees
where BirthDate >= '1960-01-01';

#Condiments와 Dairy Products를 제외한 모든 카테고리 정보 추출
select * from Categories
where CategoryName != 'Condiments' and CategoryName != 'Dairy Products';

''' 동일 <> not= 에 해당하는 기호로 <> 이걸 가장 많이 씀
select * from Categories
where CategoryName <> 'Condiments' and CategoryName <> 'Dairy Products';
'''

 

Ⅳ) Like문

 

      · 지정된 컬럼 데이터가 특정 문자열을 포함하는 행 반환

      · 와일드카드를 사용하여 데이터 조회

         - '%': 조건을 포함하는 모든 문자 (0개 이상)

         - '_': 조건을 포함하는 한 글자

 

#Description 에 and가 들어있지 않는 카테고리 정보 추출 
select * from Categories
where Description not like '%and%' ;

#판매 단위에 box가 포함되는 상품 정보 추출
select * from Products
where Unit like '%box%';

#Lastname이 P로 시작하거나 Firstname이 M으로 시작하는 사원 저보 추출
select * from Employees
where LastName like 'P%' or FirstName like 'M%';

#카테고리 이름이 C로 시작하고 S로 끝나는 카테고리 정보 출력
select * from Categories
where CategoryName like 'C%S';

 

 

 

Ⅴ) IN(LIST)

     

      · or을 의미하며, 지정된 컬럼 데이터가 list 값 중 하나 이상 일치하는 행 출력

 

#파리와 런던에 사는 고객 추출
select * from Customers
where City in ('Paris', 'London');

#보스턴과 시드니에 위치한 공급사 정보 추출
select * from Suppliers
where City in ('Boston', 'Sydney');

 

 

 

Ⅵ) BETWEEN A AND B

 

     · 지정된 컬럼 데이터가 A와 B의 사이인 행 출력

 

#가격이 50이상 100이하인 상품 정보 추출
select * from Products
where Price Between 50 and 100;

#80번대 고객ID를 가진 고객들의 주문건수 추출
select count(OrderID) from Orders
where CustomerID between 80 and 89;

 

 

 

Ⅶ) NULL

 

     · NULL값을 가진 데이터를 조회할 때 사용 

     · NULL은 0이나 공백인 데이터와는 다름

 

# 주소가 null값인 고객의 이름 추출
select * from CustomerName, Address from Customers
where Address is null;

# 주소가 null값이 아닌 고객의 이름 추출
select * from CustomerName, Address from Customers
where Address is not null;

 

 

 

1.4 GROUP BY

: 데이터 그룹화

• GROUP BY를 사용하여 특정 컬럼을 기준으로 그룹핑할 경우, 반드시 SELECT절에 명시해야 함
• GROUP BY를 사용한 경우, SELECT절에는 그룹화 기준 컬럼, 집계함수 만 사용 가능

 

#도시별 고객 수 추출
select City,count(*) from Customers
group by City;

#주문일자 별 주문 건수 추출
select OrderDate, count(*) from Orders
group by OrderDate;

#주문월 별 주문 건수 추출
select Month(OrderDate), Count(*) from Orders
group by Month(OrderDate);

#주문연, 월 별 주문건수 추출
select year(OrderDate), month(OrderDate), count(*) from Orders
group by year(OrderDate), month(OrderDate);

# 주문 ID 별 주문 수량의 평균값 추출
select OrderID, avg(Quantity) from OrderDetails
group by OrderID;

 

 

 

1.5 HAVING

: GROUP BY절과 함께 사용되며, 집계함수로 조건비교 가능 (GROUP BY의 WHERE절)

 

*️⃣ 집계함수 종류 ; select절과 having절에 사용 가능

 

# 주문ID별 최대 주문수량이 70보다 큰 경 추출
select OrderID, max(Quantity) from OrderDetails
group by OrderID
having max(Quantity) > 70;

#10번 이상 주문한 고객의 고객아이디와 주문횟수 추출
select CustomerID, count(*) from Orders
group by CustomerID
having count(OrderID) >= 10;

#2개 이상의 공급사가 있는 국가 추출
select Country, count(*) from Suppliers
group by Country
having count(*) >= 2;

 

# 판매왕 랭킹 구하기 (가장 많이 물건을 판매한 직원 5명) 
select EmployeeID, count(*) 
from Orders
group by EmployeeID
order by count(*) desc
limit 5;

# 주문일자, 직원별 주문건수를 구한 다음 주문건수가 1이 아닌 데이터만 추출
select OrderDate, EmployeeID,count(*) 
from Orders
group by OrderDate, EmployeeID
having count(*) != 1;


#Description의 길이가 제일 긴 카테고리명과 Description 길이 추출
select CategoryName, length(Description) 
from Categories 
order by length(Description) desc
limit 1;
#SELECT CategoryName, MAX(LENGTH(Description)) from Categories;



#사원의 이름을 하나의 컬럼으로 출력
select concat(LastName, ' ', FirstName) 
from Employees;

# 가장 여러번 주문된 상품의 아이디와 주문건수 추출
select ProductID, count(*) 
from OrderDetails
group by ProductID
order by count(*) desc
limit 1;


#공급사의 전화번호에서 지역번호만 추출
select substr(Phone, instr(Phone, '(') + 1, instr(Phone, ')')-2) as areaCD
from Suppliers;

 

 

1.6 ORDER BY

: 데이터 정렬 기능

• ASC(Ascending): 오름차순 ; default
• DESC(Descending): 내림차순

#상품 정보를 최고가 순으로 정렬하여 추출
select * from Products
order by Price desc;

#주문 정보를 주문일자, 고객 Id 순으로 정렬하여 추출
select * from Orders
order by OrderDate, CustomerID;

#사원 정보를 가장 나이가 어린순으로 정렬하여 추출
select * from Employees
order by BirthDate desc;

 

 

 

1.7 LIMIT

: 출력 레코드 수 설정 기능

 

#고객 ID가 71인 고객의 주문내역의 모든 정보를 최근 순으로 5개 추출
SELECT * FROM Orders
where CustomerID = 71
order by OrderDate desc
limit 5;

#상품명과 가격을 최저가순으로 10위까지 추출
select ProductName,Price from Products
order by Price 
limit 10;

#주문건중 주문 수량이 제일 많은 건 조회
select Quantity from OrderDetails
order by Quantity desc
limit 1;

select max(Quantity) from OrderDetails;


#최초로 주문이 발생한 일자가 언제인지 조회
select * from Orders
order by OrderDate 
limit 1;

#가격의 최소 최대값
select min(Price), max(Price) from Products;


'''
limit 0, 5 : 0부터 5개를 가져온다
limit 5, 5 : 6부터 5개를 가져온다
limit 10, 5 : 10부터 5개를 가져온다

(마지막 데이터가 13까지 있다면 13까지만 잘라서 추출)
'''

 

 

 

 

---

 

 

 

 

2. 함수

2.1 문자열 함수

• LOWER(문자열) : 문자열을 소문자로 변환
• UPPER(문자열): 문자열을 대문자로 변환
• INSTR(기준, 부분): 기준 문자열에서 부분 문자열 찾아 시작 위치 반환, 없는 경우 0 반환 (1부터 시작)
• TRIM() : 문자열의 왼쪽, 오른쪽 공백 제거
• LTRIM(), RTRIM(): 문자열 왼쪽/오른쪽 공백 제거 (중간중간 공백 제거 X)
• SUBSTR(문자열, 시작, 길이): 문자열을 시작 위치부터 길이만큼 잘라 반환  (1부터 시작)
• LENGTH(): 문자열의 길이 반환
• REPLCE(문자열, 대상, 바꿀 문자열): 문자열에서 대상을 바꿀 문자열로 변환
• CONCAT(문자열1, 문자열2, …): 문자열을 이어서 반환

 

 

2.2 숫자형 함수

• ABS(): 절댓값 반환
• SIGN(): 양수, 음수, 0을 구분하여 각각 1,0,-1로 반환
• MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지 반환
• TRUNCATE(숫자, n): 숫자를 소수 n자리에서 잘라서 버림
• ROUND(숫자, n): 숫자를 소수 n자리에서 반올림하여 반환
• CEIL(): 올림
• FLOOR(): 버림

 

 

2.3 날짜형 함수

• SYSDATE(), NOW() : 시스템상 현재 날짜, 시각 반환 (단, SYSDATE의 경우 함수가 실행되는 시점의 시간을 반환하고 NOW의 경우 쿼리가 시작되는 시점의 시간 반환)
• ADDDATE(기준, n ): 기준 날짜에 n일을 더한 날 반환
• YEAR(), MONTH(), DAY(): 날짜의 년, 월, 일을 반환
  • EXTRACT(YEAR FROM NOW())
  • EXTRACT(MOTH FROM NOW())
  • EXTRACT(DAY FROM NOW())
• DATE_FORMAT(날짜, 형식) : 날짜를 지정한 형식으로 출력

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS DATE FROM DUAL;
# 2023-03-09 출력

SELECT DATE_FORMAT(NOW(),'%Y %M %d') AS DATE FROM DUAL
# 2023 March 09 출력

 

 

2.4 Null 함수

• IFNULL(A, B): A가 null인 경우 B로 출력
• COALESCE(A, B): A가 null인 경우 B로 출력

 

 

 

 

 

 

---

 

 

 

 

3. CASE

: 데이터 조회 시 컬럼의 조건에 따라 다른 컬럼의 값을 업데이트해야 하는 경우 사용

 

• 기본 구조

# 형식1
CASE 
	WHEN 조건식1 THEN 결과값1
	WHEN 조건식2 THEN 결과값2
	ELSE 결과값3
END AS 반환컬럼명

# 형식2
CASE 컬럼명
	 WHEN 조건값1 THEN 결과값1
	 WHEN 조건값2 THEN 결과값2
	 ELSE 결과값3
END AS 반환컬럼명

 

#시티가 런던인 경우 한글로 출력
select CustomerName, 
	case when City = 'London' then '런던'
   	else City
    end as City
from Customers;


# 고객의 국가정보가 Mexico인 경우 멕시코로 아닌 경우 그대로 출력
select CustomerName, 
	case when Country = 'Mexico' then '멕시코'
   		 else Country
         end Country
from Customers;


# 사원의 LastName, FirstName, type을 추출 
##(type은 60년 이후 출생 'young', 이전에 태어난 경우 'old')) 
select LastName, FirstName, BirthDate,
	case when year(BirthDate) >= 1960 then 'young'
    	 else 'old'
         end Type
from Employees;


#카데고리 이름과 Description을 추출 
##(단, Description 길기가 30자를 넘는 경우 30까지만 표시하고 나머지는 '...' 로 대체)
select CategoryName,Description,
	case when length(Description) > 30 then concat(substr(Description, 1,30), '...')
    	else Description
        end Description
from Categories;

 

 

 

 

---

 

 

 

 

 

4. JOIN

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

 

- join 종류

 

   · EQUI JOIN: EQUI(=) 조건으로 JOIN

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

 

 

 

 

4.1 INNER JOIN

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

 

# 주문 ID, 고객이름, 주문일자 추출
select o.OrderID, c.CustomerName, o.OrderDate
from Orders o 
inner join Customers c on o.CustomerID = c.CustomerID;


# 주문 ID, 사원이름, 주문 일자 추출
select o.OrderID, e.FirstName, e.LastName, o.OrderDate
from Orders o
inner join Employees e on o.EmployeeID = e.EmployeeID


# 상품명, 카테고리 이름, 판매 단위, 상품 가격 추춯
select p.ProductName, c.CategoryName, p.Unit, p.Price
from Products p
inner join Categories c on p.CategoryID = c.CategoryID


# 주문 ID, 배송사 전화번호 추출
select o.OrderID, s.Phone
from Orders o
inner join Shippers s on o.ShipperID = s.ShipperID;


# 상품명, 공급사명, 카테고리 이름, 판매단위, 상품가격 추출
select p.ProductName, s.SupplierName, c.CategoryName, p.Unit, p.Price
from Products p
inner join Suppliers s on p.SupplierID = s.SupplierID
inner join Categories c on p.CategoryID = c.CategoryID;


# 주문 ID, 고객이름, 사원이름, 주문일자 추
select o.OrderID, c.CustomerName, e.FirstName, e.LastName, o.OrderDate
from Orders o
inner join Employees e on o.EmployeeID = e.EmployeeID
inner join Customers c on o.CustomerID = c.CustomerID;

 

 

 

4.2 OUTER JOIN

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

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

 

# 주문 ID, 고객 ID, 주문 상품 ID, 주문수량 추출
select o.OrderID, o.CustomerID, od.ProductID, od.Quantity
from Orders o
left outer join OrderDetails od on o.OrderID = od.OrderID;


# 고가순으로 5개의 상품의 이름, 가격, 카테고리 이름 추춣
select p.ProductName, p.Price, c.CategoryName
from Products p
left outer join Categories c on p.CategoryID = c.CategoryID
order by p.Price desc
limit 5;


# 주문 이력이 없는 고객의 고객 ID와 고객이름 추출
select c.CustomerID, c.CustomerName
from Customers c
left outer join Orders o on c.CustomerID = o.CustomerID
where o.OrderID is null;


# 1996-07-10일에 상품을 팔지 못한 사원의 ID,와 FirstName 추출
select e.EmployeeID, e.FirstName
from Employees e
left outer join Orders o on e.EmployeeID = o.EmployeeID and o.OrderDate = '1996-07-10'
where o.OrderDate is null;


# 고객 별 주문건수을 구하여 고객 ID, 고객 이름, 주문 건수를 주문건수로 내림차순하여 추출
select c.CustomerID, c.CustomerName, count(o.OrderID) 
from Customers c
left outer join Orders o on c.CustomerID = o.CustomerID
group by c.CustomerID, c.CustomerName
order by  count(o.OrderID)  desc;

 

 

 

 

 

---

 

 

 

 

 

 

+ 4주차에 SQL 강의가 남아서 배운 내용까지 정리했습니다.

join 파트에서 다루지 못한 나머지 join과 서브쿼리에 관한 내용인데,

개인적으로 sql은 조인과 서브쿼리가 꽃이라고 생각합니다!!

 

남은 시간 동안 어려웠던 문제와 못 풀었던 문제 2개 정도가 있는데 차근차근 다시 봐야겠습니다 :)

 

 

 

 

 

 

 

 

---

 

 

 

(+ 23.03.11 추가)

어려웠던 문제 집중탐구

 

 

1) 가격이 상위 10까지인 상품들의 평균 가격 추출

select avg(a.Price)
from (select * from Products
	    order by Price Desc
	    limit 10) a;

 

틀리게 작성한 코드

옳바른 코드

 

2) 공급사의 전화번호에서 지역번호만 추출

select substr(Phone, instr(Phone, '(') + 1, instr(Phone, ')')-2) as areaCD
from Suppliers;

 

틀렸던 이유)

substr(문자열, 시작위치, 가져올 수) 함수의 인수가 시작위치, 가져올 수인데

문제 풀이 당시 시작위치, 끝나는 위치라 생각함

 

 

 

 

3) 1996-07-10일에 상품을 팔지 못한 사원의 ID,와 FirstName 추출

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