SQL(2) JOIN, GROUPING, ROLL UP, SUBQUERIES, SET OPERATORS

CS/데이터베이스

SQL(2) JOIN, GROUPING, ROLL UP, SUBQUERIES, SET OPERATORS

hyomee2 2024. 7. 16. 20:35

실습은 아래 코드를 이용해서 진행했다. (SQL(1) 게시글에 있는 SCRIPT도 이용)

-- Dropping existing tables if they exist
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS JOB;
DROP TABLE IF EXISTS LOCATION;
DROP TABLE IF EXISTS NATIONAL;
DROP TABLE IF EXISTS SAL_GRADE;

-- DDL for Table DEPARTMENT
CREATE TABLE DEPARTMENT (
  DEPT_ID CHAR(2), 
  DEPT_TITLE VARCHAR(35), 
  LOCATION_ID CHAR(2) NOT NULL,
  PRIMARY KEY (DEPT_ID)
);

-- Insertions for DEPARTMENT
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_TITLE, LOCATION_ID) VALUES 
('D1', '인사관리부', 'L1'),
('D2', '회계관리부', 'L1'),
('D3', '마케팅부', 'L1'),
('D4', '국내영업부', 'L1'),
('D5', '해외영업1부', 'L2'),
('D6', '해외영업2부', 'L3'),
('D7', '해외영업3부', 'L4'),
('D8', '기술지원부', 'L5'),
('D9', '총무부', 'L1');

-- DDL for Table EMPLOYEE
CREATE TABLE EMPLOYEE (
  EMP_ID VARCHAR(3), 
  EMP_NAME VARCHAR(20), 
  EMP_NO CHAR(14) NOT NULL, 
  EMAIL VARCHAR(25), 
  PHONE VARCHAR(12), 
  DEPT_CODE CHAR(2), 
  JOB_CODE CHAR(2) NOT NULL, 
  SAL_LEVEL CHAR(2) NOT NULL, 
  SALARY DECIMAL(10, 2), 
  BONUS DECIMAL(10, 2), 
  MANAGER_ID VARCHAR(3), 
  HIRE_DATE DATE, 
  ENT_DATE DATE, 
  ENT_YN CHAR(1) DEFAULT 'N',
  PRIMARY KEY (EMP_ID)
);

-- Insertions for EMPLOYEE
-- (Note: Replace 'RR/MM/DD' with '%y/%m/%d' in STR_TO_DATE for MariaDB)
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE, JOB_CODE, SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE, ENT_DATE, ENT_YN) VALUES 
('200','선동일','621235-1985634','sun_di@greedy.com','01099546325','D9','J1','S1',8000000,0.3,null,CAST('1990/02/06' AS DATE),null,'N'),
('201','송종기','631156-1548654','song_jk@greedy.com','01045686656','D9','J2','S1',6000000,null,'200',CAST('2001/09/01' AS DATE),null,'N'),
('202','노옹철','861015-1356452','no_oc@greedy.com','01066656263','D9','J2','S4',3700000,null,'201',CAST('2001/01/01' AS DATE),null,'N'),
('203','송은희','631010-2653546','song_eh@greedy.com','01077607879','D6','J4','S5',2800000,null,'204',CAST('1996/05/03' AS DATE),null,'N'),
('204','유재식','660508-1342154','yoo_js@greedy.com','01099999129','D6','J3','S4',3400000,0.2,'200',CAST('2000/12/29' AS DATE),null,'N'),
('205','정중하','770102-1357951','jung_jh@greedy.com','01036654875','D6','J3','S4',3900000,null,'204',CAST('1999/09/09' AS DATE),null,'N'),
('206','박나라','630709-2054321','pack_nr@greedy.com','01096935222','D5','J7','S6',1800000,null,'207',CAST('2008/04/02' AS DATE),null,'N'),
('207','하이유','690402-2040612','ha_iy@greedy.com','01036654488','D5','J5','S5',2200000,0.1,'200',CAST('1994/07/07' AS DATE),null,'N'),
('208','김해술','870927-1313564','kim_hs@greedy.com','01078634444','D5','J5','S5',2500000,null,'207',CAST('2004/04/30' AS DATE),null,'N'),
('209','심봉선','750206-1325546','sim_bs@greedy.com','0113654485','D5','J3','S4',3500000,0.15,'207',CAST('2011/11/11' AS DATE),null,'N'),
('210','윤은해','650505-2356985','youn_eh@greedy.com','0179964233','D5','J7','S5',2000000,null,'207',CAST('2001/02/03' AS DATE),null,'N'),
('211','전형돈','830807-1121321','jun_hd@greedy.com','01044432222','D8','J6','S5',2000000,null,'200',CAST('2012/12/12' AS DATE),null,'N'),
('212','장쯔위','780923-2234542','jang_zw@greedy.com','01066682224','D8','J6','S5',2550000,0.25,'211',CAST('2015/06/17' AS DATE),null,'N'),
('213','하동운','621111-1785463','ha_dh@greedy.com','01158456632',null,'J6','S5',2320000,0.1,null,CAST('1999/12/31' AS DATE),null,'N'),
('214','방명수','856795-1313513','bang_ms@greedy.com','01074127545','D1','J7','S6',1380000,null,'200',CAST('2010/04/04' AS DATE),null,'N'),
('215','대북혼','881130-1050911','dae_bh@greedy.com','01088808584','D5','J5','S4',3760000,null,null,CAST('2017/06/19' AS DATE),null,'N'),
('216','차태연','770808-1364897','cha_ty@greedy.com','01064643212','D1','J6','S5',2780000,0.2,'214',CAST('2013/03/01' AS DATE),null,'N'),
('217','전지연','770808-2665412','jun_jy@greedy.com','01033624442','D1','J6','S4',3660000,0.3,'214',CAST('2007/03/20' AS DATE),null,'N'),
('218','이오리','870427-2232123','lee_or@greedy.com','01022306545',null,'J7','S5',2890000,null,null,CAST('2016/11/28' AS DATE),null,'N'),
('219','임시환','660712-1212123','im_sh@greedy.com',null,'D2','J4','S6',1550000,null,null,CAST('1999/09/09' AS DATE),null,'N'),
('220','이중석','770823-1113111','lee_js@greedy.com',null,'D2','J4','S5',2490000,null,null,CAST('2014/09/18' AS DATE),null,'N'),
('221','유하진','800808-1123341','yoo_hj@greedy.com',null,'D2','J4','S5',2480000,null,null,CAST('1994/01/20' AS DATE),null,'N'),
('222','이태림','760918-2854697','lee_tr@greedy.com','01033000002','D8','J6','S5',2436240,0.35,'100',CAST('1994/01/20' AS DATE),CAST('2017/9/17' AS DATE),'Y');

-- DDL for Table JOB
CREATE TABLE JOB (
  JOB_CODE CHAR(2), 
  JOB_NAME VARCHAR(35),
  PRIMARY KEY (JOB_CODE)
);

-- Insertions for JOB
INSERT INTO JOB (JOB_CODE, JOB_NAME) VALUES 
('J1', '대표'),
('J2', '부사장'),
('J3', '부장'),
('J4', '차장'),
('J5', '과장'),
('J6', '대리'),
('J7', '사원');

-- DDL for Table LOCATION
CREATE TABLE LOCATION (
  LOCAL_CODE CHAR(2), 
  NATIONAL_CODE CHAR(2) NOT NULL, 
  LOCAL_NAME VARCHAR(40),
  PRIMARY KEY (LOCAL_CODE)
);

-- Insertions for LOCATION
INSERT INTO LOCATION (LOCAL_CODE, NATIONAL_CODE, LOCAL_NAME) VALUES 
('L1', 'KO', 'ASIA1'),
('L2', 'JP', 'ASIA2'),
('L3', 'CH', 'ASIA3'),
('L4', 'US', 'AMERICA'),
('L5', 'RU', 'EU');

-- DDL for Table NATIONAL
CREATE TABLE NATIONAL (
  NATIONAL_CODE CHAR(2), 
  NATIONAL_NAME VARCHAR(35),
  PRIMARY KEY (NATIONAL_CODE)
);

-- Insertions for NATIONAL
INSERT INTO NATIONAL (NATIONAL_CODE, NATIONAL_NAME) VALUES 
('KO', '한국'),
('JP', '일본'),
('CH', '중국'),
('US', '미국'),
('RU', '러시아');

-- DDL for Table SAL_GRADE
CREATE TABLE SAL_GRADE (
  SAL_LEVEL CHAR(2), 
  MIN_SAL DECIMAL(10, 2), 
  MAX_SAL DECIMAL(10, 2),
  PRIMARY KEY (SAL_LEVEL)
);

-- Insertions for SAL_GRADE
INSERT INTO SAL_GRADE (SAL_LEVEL, MIN_SAL, MAX_SAL) VALUES 
('S1', 6000000, 10000000),
('S2', 5000000, 5999999),
('S3', 4000000, 4999999),
('S4', 3000000, 3999999),
('S5', 2000000, 2999999),
('S6', 1000000, 1999999);

-- Committing the changes
COMMIT;

6. JOIN

: 두 개 이상의 테이블을 관련있는 컬럼을 통해 결합

- JOIN은 FROM절에 이어서 쓴다.

(1) ON과 USING

1) ON

- 컬럼명 동일 여부에 관계없이 사용 가능

2) USING

- 두 테이블에 있는 컬럼명이 동일한 경우만 사용 가능

SELECT
       a.menu_name
     , b.category_name
  FROM tbl_menu a
  JOIN tbl_category b ON a.category_code = b.category_code;


SELECT
       a.menu_name
     , b.category_name
  FROM tbl_menu a
  JOIN tbl_category b USING (category_code);

(2) JOIN의 종류

1) INNER JOIN

: 두 테이블의 교집합을 반환

- INNER는 생략하고 쓸 수 있다.

2) LEFT JOIN

: 첫번째 테이블의 모든 레코드와 두번째 테이블에서 일치하는 레코드 반환

3) RIGHT JOIN

: 두번째 테이블의 모든 레코드와 첫번째 테이블에서 일치하는 레코드 반환

4) CROSS JOIN

: 두 테이블의 가능한 모든 조합을 반환 (따라서 ON이나 USING이 필요 없다.)

5) SELF JOIN

: 같은 테이블 내에서 행과 행 사이의 관계를 찾기 위해 사용되는 유형

SELECT
       a.category_name 소분류
     , b.category_name 대분류
   FROM tbl_category a
   JOIN tbl_category b ON a.ref_Category_code = b.category_code;
   
# employeedb에서 사원명, 관리자명 self join으로 조회해보기
SELECT
       a.EMP_NAME 사원명
     , b.emp_name 관리자명
  FROM employee a
  JOIN employee b ON a.manager_ID = b.emp_ID;  # 이거 a.emp_id=b.manager_id랑 구분할 수 있어야!

7. GROUPING (GROUP BY & HAVING)

- GROUP BY: 결과 집합을 특정 열의 값에 따라 그룹화(GROUP BY는 그룹 함수와 함께 쓰임)

- HAVING: GROUP BY 절과 함께 사용하며, 그룹의 조건을 적용

# count 함수: category_code 같은 것끼리 묶어서 몇 개인지 세준다.
SELECT
       category_code
     , COUNT(*)
  FROM tbl_menu
 GROUP BY category_code;
 
 SELECT
       COUNT(*)  # 모든 행의 개수 (null이어도 포함됨)
     , COUNT(category_code)
     , COUNT(ref_category_code)  # null은 개수에 포함안함
  FROM tbl_category;

# category_code 별로 그룹지어서 category_code와, 각 그룹별 menu_price의 합 출력
SELECT
       category_code
    , SUM(menu_price)
  FROM tbl_menu
 GROUP BY category_code;

# avg 함수 사용: 평균을 나타내는 함수
SELECT 
       category_code
     , AVG(menu_price)
  FROM tbl_menu
 GROUP BY category_code;
 
# min, max 함수 사용
SELECT 
       category_code
     , min(menu_price)
  FROM tbl_menu
 GROUP BY category_code;
 
 SELECT 
       category_code
     , max(menu_price)
  FROM tbl_menu
 GROUP BY category_code;
 
# sum, avg는 숫자 데이터, min, max, count는 모든 데이터에 사용 가능

# group by에서 2개 이상의 그룹 생성하기
SELECT
       menu_price
     , category_code
     , COUNT(*)
  FROM tbl_menu
 GROUP BY menu_price, category_code;

# join과 group by 함께 사용
SELECT 
       a.category_code
     , b.category_name
     , AVG(a.menu_price)
  FROM tbl_menu a
  JOIN tbl_category b ON (a.category_code = b.category_code)
 GROUP BY a.category_code, b.category_name;

# having 절 사용: 그루핑된 조건 필터링
SELECT
       a.category_code
     , b.category_name
     , AVG(a.menu_price) 메뉴가격평균
  FROM tbl_menu a
  JOIN tbl_category b ON (a.category_code =b.category_code)
 GROUP BY a.category_code, b.category_name
HAVING AVG(a.menu_price) <= 10000;   # having 메뉴가격평균 <= 10000; 이렇게 해도 가능
# 여기 having 자리에 where는 쓸 수 없다.

- 아래 순서로 쿼리를 작성할 수 있다.

select

from

where: 테이블 행을 조건으로 필터링

group by

having: grouping 결과를 조건으로 필터링

order by: 정렬 기준

- 집계 함수 rollup

# 컬럼 1개를 활용했을 경우
SELECT
       category_code
     , SUM(menu_price)
  FROM tbl_menu
 GROUP BY category_code
WITH ROLLUP;  # rollup은 총합을 구해줌

# 컬럼 두 개를 활용했을 경우 (중간집계가 들어감)
SELECT 
       menu_price
     , category_code
     , SUM(menu_price)
  FROM tbl_menu
 GROUP BY menu_price, category_code
WITH ROLLUP;  # rollup은 총합을 구해줌

8. Subqueries

: 다른 쿼리(메인 쿼리) 내에서 실행되는 쿼리

(1) 인라인 뷰

: from 절에 쓰이는 서브쿼리

SELECT
	MAX(COUNT)
  FROM (SELECT COUNT(*) AS 'count'
        FROM tbl_menu
        GROUP BY category_code AS countmenu;
)

(2) 상관 서브쿼리

: 메인 쿼리가 서브 쿼리의 결과에 영향을 주는 형태

(메인 쿼리에 쓰인 테이블 이름 등과 상관 관계를 갖고 있어서 단독으로 실행되지 못하는 서브쿼리를 의미)

SELECT 
       menu_code
     , menu_name
     , menu_price
     , category_code
     , orderable_status
  FROM tbl_menu a
  WHERE menu_price > (SELECT AVG(menu_price)
                        FROM tbl_menu
                        WHERE category_code = a.category_code);

(3) exists

: 조회 결과가 있을 때 true, 없을 때 false

SELECT
	category_name
  FROM tbl_category a
 WHERE EXISTS (SELECT 1  # 1은 걍 형식적인거
                FROM tbl_menu b
               WHERE b.category_code = a.category_code)
 ORDER BY 1;  # 1열을 기준으로 정렬

(4) CTE(Common Table Expression)

: 인라인 뷰로 쓰이는 서브쿼리를 미리 정의해서 사용

WITH menucate AS (
   SELECT menu_name
        , category_name
      FROM tbl_menua
      JOIN tbl_category b ON a.category_code = b.category_code
)
SELECT
       *
  FROM menucate
 ORDER BY menu_name;

9. set operators

: 2개 이상의 SELECT문의 결과 집합을 결합

- UNION, UNION ALL(교집합이 2번 포함됨), INTERSECT, MINUS(INTERSECT랑 MINUS는 mariadb엔 없고 oracle에만 있다.)

(1) UNION

: 두 개 이상의 SELECT문의 결과를 결합하여 중복 레코드 제거 후 반환

(2) UNION ALL

: 두 개 이상의 SELECT 문의 결과를 결합하여 중복 레코드 제거하지 않고 반

SELECT 
       menu_code
     , menu_name
     , menu_price
     , category_code
     ,orderable_status
  from tbl_menu
 WHERE category_code = 10
UNION ALL
SELECT 
       menu_code
     , menu_name
     , menu_price
     , category_code
     ,orderable_status
  from tbl_menu
 WHERE menu_price < 9000;

(3) INTERSECT

: 두 SELECT의 결과 중 공통되는 레코드만 반환

- mysql, mariadb에서는 제공하지 않는 연산자로, INNER JOIN 또는 IN 연산자로 구현 가능

1) INNER JOIN 이용하기

SELECT 
       a.menu_code
     , a.menu_name
     , a.menu_price
     , a.category_code
     , a.orderable_status
  from tbl_menu a
  JOIN(SELECT 
               menu_code
             , menu_name
             , menu_price
             , category_code
             ,orderable_status
          from tbl_menu
         WHERE menu_price < 9000) b ON (a.menu_code = b.menu_code)  
 WHERE a.category_code = 10;

2) IN 연산자 이용하기

SELECT 
       a.menu_code
     , a.menu_name
     , a.menu_price
     , a.category_code
     , a.orderable_status
  from tbl_menu a
 WHERE a.category_code = 10
   AND a.menu_code IN(SELECT
	                        menu_code
                        FROM tbl_menu
                       WHERE menu_price < 9000);

(4) MINUS

: 첫 번쨰 SELECT문의 결과에서, 두 번째 SELECT문의 결과가 포함되는 레코드를 제외한 레코드를 반환

- mysql, mariadb는 제공하지 않는데, left join을 통해 구현 가능

SELECT 
       menu_code
     , menu_name
     , menu_price
     , category_code
     ,orderable_status
  from tbl_menu
  LEFT JOIN(SELECT 
               menu_code
             , menu_name
             , menu_price
             , category_code
             ,orderable_status
          from tbl_menu
         WHERE menu_price < 9000) b ON (a.menu_code = b.menu_code)  
 WHERE a.category_code = 10
   AND b.menu_code IS NULL;

'CS > 데이터베이스' 카테고리의 다른 글

SQL 실습 (2) (0)	2024.07.17
SQL 실습(1) (1)	2024.07.17
SQL (1) SELECT, ORDER BY, WHERE, DISTINCT, LIMIT (1)	2024.07.12
MariaDB 설치 및 기본 설정 (0)	2024.07.11
데이터베이스 개요 & MariaDB (0)	2024.07.11

현재글SQL(2) JOIN, GROUPING, ROLL UP, SUBQUERIES, SET OPERATORS

hyomee2

안냥하세요 형바오 형마이 땡미입니다 많이 놀러오세요

티스토리챌린지, CS, Spring, Java, 오블완, 드림코딩, DevOps, 백준, Collection, 자바, spring boot, 파이썬, 프로그래머스, JPA, 운영체제, 자바스크립트, SQL, springboot, Servlet, Python,

Today :
Yesterday :

hyomee2