1) SQL Syntax

SELECT
FROM
WHERE
GROUP
BY
HAVING

2) SELECT

SELECT 상품번호 FROM DB명.PRODUCT;
    
* 컬럼 조회
SELECT 컬럼명 FROM DB명.테이블명;
    
* 개수 확인
SELECT COUNT(컬럼명) FROM DB명.테이블명;
SELECT SUM(AMOUNT), COUNT(CHECKNUMBER) FROM CLASSICMODELS.PAYMENTS;

* 모든 결과 조회
SELECT * FROM DB명.테이블 명;
SELECT 컬럼명1,컬럼명2 FROM DB명.테이블 명;

* 별명 AS 
SELECT 컬럼명1 AS 변경 컬럼명 FROM DB명.테이블 명;
SELECT COUNT(PRODUCTCODE) AS N_PRODUCTS,
COUNT(PRODUCTCODE) N_PRODUCTS FROM CLASSICMODELS.PRODUCTS;
    
* 중복제거 테이블조회 DISTINCT 
SELECT DISTINCT 제조국가 FROM FROM DB명.테이블 명;

3) FROM

SELECT 컬럼명 FROM DB명.SALES;

USE DB명;
SELECT 컬럼명 FROM SALES;

4) WHERE

SELECT 상품번호 FROM DB명.PRODUCT WHERE 판매국가='미국';

- BETWEEN
SELECT 상품번호 FROM DB명.PRODUCT WHERE 출시연도 BETWEEN 2010 AND 2014;
SELECT * FROM CLASSICMODELS.ORDERDETAILS WHERE PRICEEACH BETWEEN 30 AND 50;
    
SELECT * FROM CLASSICMODELS.ORDERDETAILS WHERE PRICEEACH >=30;
<> 같지않다
    
- IN
SELECT CUSTOMERNUMBER FROM CLASSICMODELS.CUSTOMERS WHERE COUNTRY IN ('USA','CANADA');
    
- NOTIN
SELECT CUSTOMERNUMBER FROM CLASSICMODELS.CUSTOMERS WHERE COUNTRY NOT IN ('USA','CANADA');

- IS NULL
SELECT EMPLOYEENUMBER FROM CLASSICMODELS.EMPLOYEES WHERE REPORTSTO IS NULL;

- LIKE '%TEXT%'
SELECT ADDRESSLINE1 FROM CLASSICMODELS.CUSTOMERS WHERE ADDRESSLINE1 LIKE '%ST%';

5) GROUP BY

SELECT COUNTRY, CITY, COUNT(CUSTOMERNUMBER) N_CUSTOMERS FROM CLASSICMODELS.CUSTOMERS GROUP BY COUNTRY, CITY
AUG(), COUNT(), SUM()
    
SELECT SUM(CASE WHEN 국가='한국' THEN 1 ELSE 0 END) KOREA_CNT FROM TABLE;
    
- USA거주자의 수를 계산하고 비중 구하기 
SELECT SUM(CASE WHEN COUNTRY='USA' THEN 1 ELSE 0 END) N_USA,
       SUM(CASE WHEN COUNTRY='USA' THEN 1 ELSE 0 END)/COUNT(*) USA_PORTION FROM CLASSICMODELS.CUSTOMERS;

6) JOIN

- LEFT JOIN : ORDER TABLE 기준으로 병합
SELECT * FROM ORDER LEFT JOIN CUSTOMERS ON ORDER.고객ID = CUSTOMER.고객ID 

SELECT A.ORDERNUMBER, B.COUNTRY FROM CLASSICMODELS.ORDERS A LEFT JOIN CLASSICMODELS.CUSTOMERS B ON A.CUSTOMERSNUMBER = B.SUCTOMERNUMER

- INNER JOIN : 교집합
SELECT * TABLE_A INNER JOIN TABLE_B ON TABLE_A.COLUMN1=TABLEB.COLUMN2

- FULL JOIN : 합집합
SELECT * FROM TABLE_A FULL JOIN TABLE_B ON TABLE_A.COLUMN 1=TABLE_B.COLUMN2

7) CASE WHEN

조건에 해당 되는 데이터에 필요 데이터 출력시

SELECT CASE WHEN 연령 BETWEEN 20 AND 29 THEN '20대' 
            WHEN 연령 BETWEEN 40 AND 49 THEN '40대'
            WHEN 연령 BETWEEN 50 AND 59 THEN '50대' END FROM DB명.테이블명;

- 북미, 비북미 출력하는 컬럼 생성, 북미, 비북미 거주 고객의 수 계산
SELECT CASE WHEN COUNTRY IN ('USA','CANADA') THEN 'NORTH AMERICA' ELSE 'OTHERS' END AS REGION,
COUNT(CUSTOMERNUMBER) N_CUSTOMERS FROM CLASSICMODELS.CUSTOMERS 
GROUP BY CASE WHEN COUNTRY IN ('USA','CANADA') THEN 'NORTH AMERICA' ELSE 'OTHERS' END

8) RANK 오름차순, DENSE_RANK 내림차순, ROW_NUMBER 오름차순

SELECT *
ROW_NUMBER() OVER(ORDER BY 구매금액) ROWNUMBER,
RANK() OVER(ORDER BY 구매금액) RANK,
DENSE_RANK() OVER(ORDER BY 구매금액) DENSE_RANK FROM TABLE;

9) SUBQUERY

- NYC에 거주하는 고객들의 주문번호 조회
SELECT ORDERNUMBER FROM CLASSICMODELS.ORDERS 
WHERE CUSTOMERNUMNER IN (SELECT CUSTOMERNUMBER FROM CLASSICMODELS.CUSTOMERS WHERE CITY='NYC'); 

- USA 거주자와 주문번호 출력
SELECT ORDERNUMBER FROM CLASSICMODELS.ORDERS 
WHERE CUSTOMERNUMBER IN (SELECT CUSTOMERSNUMBER FROM CLASSICMODELS.CUSTOMERS WHERE COUNTRY='USA');