[2021/08/09] SQL 주식 데이터 쿼리2

# 조건절 연습

 

# history_dt import 부터
SELECT *
FROM STOCK;


# SELECT는 필요한 컬럼만 입력가능
SELECT STK_CD, STK_NM
FROM STOCK;


SELECT STK_CD, STK_NM, EX_CD
FROM STOCK
ORDER BY STK_NM;


# 오름차순 내림차순
SELECT STK_CD, STK_NM
FROM STOCK
ORDER BY STK_NM ASC;


SELECT STK_CD, STK_NM
FROM STOCK
ORDER BY STK_NM DESC;

# 문구류만 조회
SELECT *
FROM STOCK
WHERE SEC_NM = '문구류'
ORDER BY STK_NM;


SELECT *
FROM STOCK
WHERE STK_NM = 'SK';

# 조건연산식
SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_NM = '삼성물산'
ORDER BY STK_CD DESC;


SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_NM >= '효성화학';


SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_CD <= '000100'
ORDER BY STK_CD DESC;


SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_CD < '000100'
ORDER BY STK_CD DESC;


SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE SEC_NM = '문구류'
AND EX_CD = 'KD'
ORDER BY STK_CD;


SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE STK_CD >= '100000'
AND STK_CD <= '101000'
AND SEC_NM = '기계와장비'
AND EX_CD = 'KP'
ORDER BY STK_CD;

# 문구류거나 이름이 대교
SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE SEC_NM = '문구류'
OR STK_NM = '대교'
ORDER BY STK_NM;

# 담배 or 주류 제조업을 하는 기업 조회
SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE SEC_NM = '담배'
OR SEC_NM = '주류제조업'
ORDER BY STK_NM;


SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE EX_CD = 'KD'
AND (SEC_NM = '담배' OR SEC_NM = '주류제조업')
ORDER BY STK_CD ASC;


SELECT STK_NM, STK_CD, SEC_NM, EX_CD
FROM STOCK
WHERE EX_CD = 'KD'
AND SEC_NM = '담배' 
OR SEC_NM = '주류제조업'
ORDER BY STK_CD ASC;

# LIKE를 사용해 'LG'로 시작하는 STK_NM을 가진 모든 데이터를 조회하는 SQL이다
SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_NM LIKE 'LG%'
ORDER BY STK_CD ASC;

# IN
SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE SEC_NM IN ('담배', '주류제조업', '문구류')
ORDER BY STK_NM ASC;

# 같은 결과
SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE (SEC_NM = '담배'
		OR SEC_NM = '문구류'
        OR SEC_NM = '주류제조업')
ORDER BY STK_NM ASC;

# NOT IN으로 제외시키고 조회
SELECT STK_NM, STK_CD, EX_CD, SEC_NM
FROM STOCK
WHERE STK_NM LIKE '삼성%'
AND SEC_NM NOT IN ('보험', '금융', '증권')
ORDER BY STK_NM;

# BETWEEN 200000 ~ 200500
SELECT STK_CD, STK_NM
FROM STOCK
WHERE STK_CD >= '200000'
AND STK_CD <= '200500'
ORDER BY STK_CD;


SELECT STK_CD, STK_NM
FROM STOCK
WHERE STK_CD BETWEEN '200000' AND '200500'
ORDER BY STK_CD;


SELECT STK_CD, STK_NM, SEC_NM
FROM STOCK
WHERE STK_NM BETWEEN '삼성' AND '삼아'
AND SEC_NM IN ('보험', '제약바이오')
ORDER BY STK_NM;

# 여러 컬럼의 ORDER BY
SELECT STK_CD, SEC_NM, STK_NM
FROM STOCK
WHERE SEC_NM IN ('자동차', '통신업')
ORDER BY SEC_NM ASC, STK_NM ASC;


SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM
FROM STOCK T1
WHERE T1.STK_NM = '삼성전자'
ORDER BY T1.STK_CD ASC;


SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM
FROM STOCK as T1
WHERE T1.STK_NM = '삼성전자'
ORDER BY T1.STK_CD ASC;

# 컬럼 별칭
SELECT T1.STK_CD 종목코드, T1.STK_NM 종목명
FROM STOCK T1
WHERE T1.STK_NM = '삼성전자'
ORDER BY T1.STK_CD ASC;


SELECT T1.STK_CD 종목코드, T1.STK_NM 종목명
FROM STOCK T1
WHERE T1.STK_NM = '삼성전자'
ORDER BY 종목명 ASC;

# ORDER BY는 SELECT보다 늦게 처리되므로 별칭사용 가능 WHERE절은 불가능 - 에러
SELECT T1.STK_CD 종목코드, T1.STK_NM 종목명
FROM STOCK T1
WHERE 종목명 = '삼성전자'
ORDER BY T1.STK_CD ASC;