SQL 기초

(mcode.co.kr 'SQL 기초강의' 스터디 내용을 기록합니다.)

SQL이란

Structured Query Language, 구조화 질의어, S-Q-L
🡪 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된
특수 목적의 프로그래밍 언어
🡪 관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리,
데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해
고안
🡪 많은 수의 데이터베이스 관련 프로그램들이 SQL을 표준으로 채택하고 있다.(고로, oracle, SQL Server, MySQL, Postgre에서 조금씩 다르긴 하지만, 비슷한 모습)

데이터베이스 용어

실습

1. SELECT

✏️ 별명에 띄어쓰기가 되어 있는 경우는 "따옴표" 넣기
(번외. 이모지 window+;)

과제

-- 과제 1. StockETF_Price의 모든 테이블을 조회해보세요. (주석처리 -- 또는 #)
-- 🡪 모든 열 이름을 나열하는 방법과 문자 * 사용하는 두가지 방법 사용
SELECT *
FROM stocketf_price;

-- 과제 2. ETF_Info의 GEO_FOCUS 컬럼을 중복 제거하여 조회해보세요.
SELECT DISTINCT geo_focus
FROM etf_info;

-- 과제 3. StockETF_Price에서 30개의 행만 조회해보세요.
SELECT *
FROM stocketf_price
LIMIT 30;

2. 데이터 필터링(where)

과제

과제 1. Financial_Info 테이블에서 Total_Assets의 값이 10,000,000 이상인 값만 조회해보세요.
SELECT *
FROM financial_info
WHERE Name_ LIKE 'total_assets'
      AND
      VALUE_ >= 10000000;

과제 2. Fiancial_Info 테이블에서 Income과 관련된 항목 중 값이 5,000 ~ 10,000 사이인 데이터를 조회해보세요.
SELECT *
FROM financial_info
WHERE NAME_ LIKE '%income%'
      AND
      value_ Between 5000 and 10000;

3. 데이터 정렬(order by)

default ASC

과제

과제 1. ETF_Info 테이블에서 Asset_Type과 Name을 기준으로 정렬한 값을 조회해보세요
🡪 컬럼이름을 사용하는 방법과 순서를 입력하는 방법을 모두 활용해보세요.
SELECT *
FROM etf_info
ORDER BY asset_type, name
# 어떤 칼럼은 오름차순, 어떤 칼럼은 내림차순 하고 싶다면 칼럼명 뒤에
ASC/DESC를 입력하면 된다.

SELECT id, ticker, name, currency, asset_type, geo_focus
FROM etf_info
ORDER BY 5,3;

과제 2. Financial_Info 테이블에서 Retained_Earnings 값을 기준으로 상위 10개의 데이터만 조회해보세요.
SELECT *
FROM financial_info
WHERE NAME_ = 'Retained earnings'
ORDER BY VALUE_ DESC
LIMIT 10;

4. 여러가지 함수

1. case when
   

2.집계, 숫자

• 집계

SELECT SUM(Mkt_Capitalization_USD) TOTAL시가총액,
       COUNT(mkt_capitalization_usd) COUNT시가총액,
       MIN(mkt_capitalization_usd) MIN시가총액,
       MAX(mkt_capitalization_usd) MAX시가총액,
       AVG(mkt_capitalization_usd) AVG시가총액
FROM stock_info; 

3. 문자
   

4. 날짜, 시간
   
   

5. 데이터 그룹화(group by)

과제

-- 과제 1. Financial_Info 테이블에서 각 연도별 Operating_Income의 최소값, 최대값, 평균값을 구해보세요.
SELECT fiscalprd_,
       MIN(value_),
       MAX(value_),
	   AVG(value_)
FROM financial_info
WHERE NAME_ = 'operating income'
GROUP BY FiscalPrd_;

-- 과제 2. Stock_Info 테이블에서 각 IndCode별 시가총액 평균을 구하고 시가총액 평균값이 100,000 이상인 데이터만 조회해보세요.
SELECT indcode, AVG(Mkt_Capitalization_USD) '시가총액 평균'
FROM stock_info
GROUP BY indcode
HAVING AVG(Mkt_Capitalization_USD) > 100000
ORDER BY AVG(Mkt_Capitalization_USD) DESC;

6. 테이블 분할 & 분석

과제

-- 과제 1. Financial_Info 테이블에서 각 연도별 총자산 순위를 구해보세요.(내림차순)
<SELECT *,
       RANK() OVER(PARTITION BY fiscalprd_ ORDER BY value_ DESC) 순위
FROM financial_info
where Name_ = 'TOTAL ASSETS';

-- 과제 2. StockETF_Price 테이블에서 삼성전자의 5일 /20일 이동평균 값을 구해보세요 # 삼성전자 어떻게?
-- 삼성전자 찾기
SELECT *
FROM stock_info
WHERE NAME LIKE '%SAMSUNG ELECT%'; # id =40853

-- 풀이시작
SELECT id, marketdate, close_,
	   AVG(close_) OVER (ORDER BY marketdate
         				 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) '이동평균 5일',
	   AVG(close_) OVER (ORDER BY marketdate
         				 ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) '이동평균 20일'
FROM stocketf_price
WHERE id = 40853
ORDER BY marketdate;

-- 과제 3. Financial_Info 테이블에서 각 기업별로 전년도 대비 Pretax_Income 증감률을 확인해보세요
-- 🡪 증감률 = (이번 금액 – 이전 금액) / abs(이전 금액)
SELECT *,
       LAG(value_, 1) OVER(PARTITION BY CODE ORDER BY fiscalprd_) 전년도,
       (value_ - LAG(value_, 1) OVER(PARTITION BY CODE ORDER BY fiscalprd_)) / ABS(LAG(value_, 1) OVER(PARTITION BY CODE ORDER BY fiscalprd_)) 증감률
FROM financial_info
WHERE NAME_ = 'pretax income'
ORDER BY CODE, fiscalprd_;

7. 테이블 합치기(join)

과제

-- 과제 1. Financial_Info 테이블과 JOIN을 활용하여 산업 별 평균 세전 이익을 구해보세요.
SELECT d.indname, avg(a.value_) as '평균세전이익'
FROM financial_info a 
	JOIN stock_mapping b 
		ON a.code = b.code
    JOIN stock_info c
		ON b.id = c.id
	JOIN stock_industry d
		on d.indcode = c.indcode
WHERE NAME_ = 'pretax income'
GROUP BY c.indcode
ORDER BY 2;

-- 과제 2. ETF_Info 테이블과 JOIN을 활용하여 Asset_Type 별로 종가의 평균, 최대값, 최소값을 구해보세요.
SELECT a.asset_type, avg(b.close_) '종가 평균', max(b.close_) '종가 최대값', min(b.close_) '종가 최소값' 
FROM etf_Info a
	JOIN stocketf_price b
    ON a.id = b.id
group by a.asset_type;

8. 서브쿼리 & 임시테이블

예시

SELECT *
FROM 
(SELECT *, RANK() OVER(partition by REGION ORDER BY mkt_capitalization_usd DESC) '시가총액순위'
FROM stock_Info
) b
WHERE 시가총액순위 <= 3;

-- 단일 열 & 단일 값 테이블 활용
SELECT a.*, b.region
FROM stocketf_price a 
	JOIN stock_info b
    ON a.id = b.id
where a.id in 
	(SELECT id
	FROM stock_info

SELECT *
FROM stocketf_price
WHERE MarketDate =  # in <- 단일값일 수도 있고 아닐수도 있으니 in을 추천
	(select max(marketdate)
    from stocketf_price);

임시테이블

WITH temp_rank as(
	SELECT *, 
		RANK() OVER(partition by REGION ORDER BY mkt_capitalization_usd DESC) '시가총액순위'
	FROM stock_Info
)
SELECT *
FROM temp_rank;

9. 사용자 정의 함수

10. 테이블 & 데이터 핸들링