'SQL' ERD, JOIN, 집합 연산자 (*추이 분석)

 

ERD(Entity Relationship Diagram)

P(Primary Key) : 기본키, 주키

F(Foreign Key) : 외래키, 보조키

실선: 필수관계

점선: 선택적관계

관계차수 : 대응되는 개체 수

 

 

조인(JOIN)

SELECT A.employee_id, A.first_name, A.last_name, A.department_id, 
       B.department_id, B.department_name
FROM   employees A, departments B
WHERE  A.department_id = B.department_id
ORDER  BY A.employee_id;

INNER-JOIN

 

SELECT  A.employee_id, A.first_name, A.last_name, A.department_id, 
        B.department_id, B.department_name
FROM    employees A, departments B
WHERE   A.department_id = B.department_id(+)
ORDER   BY A.employee_id;


SELECT A.employee_id, A.first_name, A.last_name, A.department_id,
       B.department_id, B.department_name
FROM employees A LEFT OUTER JOIN departments B
ON A.department_id = B.department_id
ORDER BY A.employee_id;

OUTER-JOIN : (+) 데이터가 부족한 쪽에 기술

표준 SQL 문법 : 하단

 

집합 연산자(Set Operator)

SELECT department_id 
FROM employees
MINUS
SELECT department_id 
FROM departments 
ORDER BY department_id;

MINUS : 앞 열의 값 - 뒷 열의 값

 

--월별 매출 추이 분석
SELECT SUBSTR(A.reserv_date,1,6) BASE_MONTH, 
       SUM(B.sales) TOT_SALES, 
       SUM(DECODE(B.item_id,'M0005',B.sales,0)) STEAK_SALES
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND   A.cancel    = 'N'
AND   A.reserv_date BETWEEN '20170101' AND '20171231'
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

INNER-JOIN

M0005(스테이크) 매출 비중과 추이

 

--상품별 매출 분석
SELECT SUBSTR(A.reserv_date,1,6) AS BASE_MONTH,  
       SUM(DECODE(B.item_id,'M0001',B.sales,0))  AS SPECIAL_SET,
       SUM(DECODE(B.item_id,'M0002',B.sales,0))  AS PASTA,
       SUM(DECODE(B.item_id,'M0003',B.sales,0))  AS PIZZA,
       SUM(DECODE(B.item_id,'M0004',B.sales,0))  AS SEA_FOOD,
       SUM(DECODE(B.item_id,'M0005',B.sales,0))  AS STEAK,
       SUM(DECODE(B.item_id,'M0006',B.sales,0))  AS SALAD_BAR,
       SUM(DECODE(B.item_id,'M0007',B.sales,0))  AS SALAD,
       SUM(DECODE(B.item_id,'M0008',B.sales,0))  AS SANDWICH,
       SUM(DECODE(B.item_id,'M0009',B.sales,0))  AS WINE,
       SUM(DECODE(B.item_id,'M0010',B.sales,0))  AS JUICE
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND   A.cancel    = 'N'
AND   A.reserv_date BETWEEN '20170101' AND '20171231'
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

INNER-JOIN

- 스페셜 세트와 스테이크 메뉴의 매출이 크다

- 월말로 갈수록 매출이 증가하는 추이를 나타내고 있다

- 매출 증가 추이 또한 스페셜 세트와 스테이크가 두드러진다

 

 

 

 

 

 

 

(출처1: 핵심만 쉽게, 모두의 SQL 데이터 분석)