[데이터 엔지니어링] 1-3 SQL(2)

1. HAVING

group by 상태에서 출력 조건을 입력하고 싶다면 HAVING을 써야한다

2. INSERT

# 생성된 테이블에 데이터 추가하기
INSERT INTO <테이블 이름> VALUES(1, 'C100', 50);

# 원하는 컬럼에만 값을 넣고자 하는 경우
INSERT INTO <테이블 이름> (컬럼2, 컬럼5) VALUES (40, 100);

3. DROP

# 생성된 테이블을 삭제하기 
DROP TABLE Teacher

4. SQL 실행순서

① FROM
② WHERE
③ GROUP BY
④ HAVING
⑤ SELECT
⑥ ORDER BY

5. 집계함수

COUNT()
SUM()
AVG()
MAX()
MIN()

**그외
RANK() OVER (ORDER BY 컬럼명) → <컬럼명>을 기준으로 랭킹을 매겨줘 
RANK() OVER (PARTITION BY 컬럼명1 ORDER BY 컬럼명2) → <컬럼명1>을 기준으로 그룹을 나누고, <컬럼명2>를 기준으로 각 그룹 내의 랭킹을 매겨줘

5. CASE

SQL에서도 파이썬의 if문과 같은 기능을 사용할 수 있다.

SELECT CASE
WHEN CustomerId <= 25 THEN 'GROUP 1'
WHEN CustomerId <= 50 THEN 'GROUP 2'
ELSE 'GROUP 3'
END AS 'Group'
FROM customers;

6. SUBQUERY

쿼리 문을 작성할 때 다른 쿼리문을 포함하는 것. 서브쿼리는 개별값 또는 테이블을 반환할 수 있다. 

서브쿼리는 다른 테이블에서 정보를 가져오기 위해 쓴다. 그런면에서 JOIN과 기능이 비슷하다. JOIN을 쓸 지, 서브쿼리를 이용할지는 각 상황마다 직접 결정해야.

# 예시
SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6;

# 서브쿼리가 필요한 이유
# 컴퓨터는 아래 쿼리를 이해하지 못한다
SELECT Total
FROM invoices
WHERE Total > AVG(Total)

# ----->

SELECT Total
FROM invoices
WHERE Total > (SELECT AVG(Total) FROM invoces)
# 이와 같이 수정해야 컴퓨터가 이해할 수 있다

7. 활용 예시

* 사용한 데이터는 chinook.db (첨부)

chinook.zip

0.29MB

 

- customers 테이블에서 각 고객의 'CustomerId' 칼럼과 고객의 도시와 나라를 대문자로 합친 문자열 칼럼을 조회해야 합니다. 도시와 나라 사이에는 한 칸을 띄웁니다. 예를 들어, 도시가 'Seoul' 이고 나라가 'South Korea' 인 경우에는 'SEOUL SOUTH KOREA' 로 합칩니다.

SELECT CustomerId, (UPPER(City) || ' ' || UPPER(Country)) AS '새로운 컬럼'
FROM customers;

- 새로운 customer 아이디를 만들어봅니다. 새로운 아이디는 customer의 FirstName 의 첫 4 글자와 LastName 의 첫 2 글자를 합친 소문자입니다.

SELECT LOWER(SUBSTRING(FirstName,1,4) || SUBSTRING(LastName,1,2)) As '새로운 컬럼'
FROM customers;

- 직원 (employee) 중에서 회사에서 2020년 1월 1일 기준으로 7년 넘게 (>) 근무한 직원들의 EmployeeId 를 조회해야 합니다. 조회된 결과는 LastName 을 기준으로 오름차순으로 정렬합니다.

SELECT e.EmployeeId
FROM employees e 
WHERE ('2020.01.01' - HireDate) > 7
ORDER BY e.LastName;

- 새로운 고객 주문 번호를 만들어 봅니다. 새로운 주문 번호는 각 고객의 FirstName과 LastName과 InvoiceId 를 합쳐서 만듭니다. 각 고객의 새로운 주문 번호를 FirstName, LastName, InvoiceId 순으로 오름차순 정렬하세요.

SELECT c.FirstName || c.LastName || i.InvoiceId 
FROM customers c 
JOIN invoices i ON c.CustomerId = i.CustomerId  
ORDER BY c.FirstName, c.LastName, i.InvoiceId ;

- 서브쿼리를 이용해서 앨범타이틀이 'Unplugged' 이거나 'Outbreak' 인 Track의 Name을 모두 출력하세요

SELECT t.Name
FROM tracks t 
WHERE t.AlbumId in (SELECT a.AlbumId 
FROM albums a 
WHERE a.Title in ('Unplugged', 'Outbreak'));