230727 SQL 강의

-- <테이블 만들기>
CREATE TABLE A
(NO         NUMBER(3) NOT NULL,
 NAME     VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_A PRIMARY KEY(NO));
 
CREATE TABLE B
(NO         NUMBER(3) NOT NULL,
 SCORE    NUMBER(3) NOT NULL,
 CONSTRAINT PK_B PRIMARY KEY(NO));
 
INSERT INTO A VALUES(1, '김철수');
INSERT INTO A VALUES(2, '박미미');
INSERT INTO A VALUES(3, '정다비');
INSERT INTO A VALUES(4, '멘토스');
INSERT INTO A VALUES(5, '박버거');

INSERT INTO B VALUES(1, 60);
INSERT INTO B VALUES(2, 71);
INSERT INTO B VALUES(5, 99);
INSERT INTO B VALUES(7, 100);

----------------------------------------------

SELECT * FROM A;

SELECT * FROM B;

---

-- 1. INNER JOIN
-- INNER JOIN은 교집합이라고 보면 된다. 
-- 또한 쿼리는 다음과 같은 방법들로 작성된다.

SELECT *
  FROM A
 INNER JOIN b
    ON A.NO = B.NO;
    
SELECT *
  FROM A, B
 WHERE A.NO = B.NO;
    
-- INNER JOIN된 결과를 보면 A 테이블과 B 테이블이 모두 가지고 있는 데이터만이 검색됨

---

----------------------------------------------

--2. OUTER JOIN
--OUTER JOIN은 FULL OUTER JOIN의 경우 빼고는 
--특정 테이블을 기준으로 데이터를 보여준다.

--OUTER JOIN은 Full OUTER JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN 
--크게 세가지 종류로 나누어 진다.

--1) LEFT OUTER JOIN
--Left Outer Join 왼쪽 테이블 기준으로 JOIN 하겠다는 것
--왼쪽 테이블 A의 모든 데이터와 A와 B 테이블의 중복데이터들이 검색됨
--쿼리는 아래와 같은 방식으로 작성

SELECT *
  FROM A
  LEFT OUTER JOIN B
    ON (A.NO = B.NO)
  ORDER BY A.NO;
    
SELECT *
  FROM A, B
 WHERE A.NO = B.NO(+)
 ORDER BY A.NO;

---

--2) RIGHT OUTER JOIN
--RIGHT OUTER JOIN은 오른쪽 테이블 기준으로 JOIN 하겠다는 것

SELECT *
  FROM A
  RIGHT OUTER JOIN B
  ON (A.NO = B.NO);
  
SELECT *
  FROM A, B
 WHERE A.NO(+) = B.NO;

---

--3) FULL OUTER JOIN
--FULL OUTER JOIN은 왼쪽 테이블과 오른쪽 테이블의 합집합을 얻는다. 
--만약 A에는 데이터가 있지만 B에 데이터가 없으면 B부분은 null이 되고 반대의 경우에는 A부분이 null이 된다.

SELECT *
  FROM A
  FULL OUTER JOIN B
    ON (A.NO = B.NO);
    
SELECT * FROM A,B WHERE A.NO = B.NO(+)
UNION
SELECT * FROM A,B WHERE A.NO(+) = B.NO;

--요약.
--• INNER JOIN은 위에 설명 했듯이 교집합이라는 개념으로 생각하면 이해하기 편할듯.
--• OUTER JOIN은 INNER JOIN에서 일치하지 않는 레코드는 모두 버렸지만 OUTER JOIN에서는 일치하지 않더라도 버리지 않고 NULL로 채워서 결과를 응답한다.

---

----------------------------------------------

--전체상품의 2020년 1월 입고수량을 검색 조회
--( Alias는 상품코드, 상품명, 입고수량 )

--1. 일반 JOIN
SELECT PROD.PROD_ID            AS "상품코드"
     , PROD.PROD_NAME          AS "상품명"
     , SUM(BUYPROD.BUY_QTY)    AS "입고수량"
  FROM PROD, BUYPROD
 WHERE PROD.PROD_ID = BUYPROD.BUY_PROD
   AND BUY_DATE BETWEEN '2020-01-01' AND '2020-01-31'
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME;

--2. OUTER JOIN 사용 확인
SELECT PROD.PROD_ID AS "상품코드"
     , PROD.PROD_NAME AS "상품명"
     , SUM(BUYPROD.BUY_QTY) AS "입고수량"
  FROM PROD
  LEFT OUTER JOIN BUYPROD ON(PROD.PROD_ID = BUYPROD.BUY_PROD)
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME
 HAVING BUYPROD.BUY_DATE BETWEEN '2020-01-01' AND '2020-01-31'
 ORDER BY PROD.PROD_ID, PROD.PROD_NAME;

--2번 쿼리는 전체상품의 결과가 조회되지 않았다. 
--- 서브쿼리를 사용하거나 ANSI 조인을 사용하여 해결 해야 한다.
--
--3. ANSI OUTER JOIN
SELECT PROD.PROD_ID AS "상품코드"
     , PROD.PROD_NAME AS "상품명"
     , SUM(BUYPROD.BUY_QTY) AS "입고수량"
  FROM PROD LEFT OUTER JOIN BUYPROD 
    ON (PROD.PROD_ID = BUYPROD.BUY_PROD
   AND BUYPROD.BUY_DATE BETWEEN '2020-01-01' AND '2020-01-31')
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME
 ORDER BY PROD.PROD_ID, PROD.PROD_NAME;

--4. OUTER JOIN 사용 확인(NULL값 제거)
SELECT PROD.PROD_ID 상품코드
     , PROD.PROD_NAME 상품명
     , SUM( NVL(BUYPROD.buy_qty, 0) ) 입고수량
  FROM PROD LEFT OUTER JOIN BUYPROD 
    ON (PROD.PROD_ID = BUYPROD.BUY_PROD
   AND BUYPROD.BUY_DATE BETWEEN '2020-01-01' AND '2020-01-31' )
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME
 ORDER BY PROD.PROD_ID, PROD.PROD_NAME;

---

--전체 상품의 2020년도 5월 5일의 입고.출고현황 조회
--(상품코드, 상품명, 입고수량의 합, 판매수량의 합)

-- 입고 확인(Equi Join)
SELECT PROD.PROD_ID         AS "상품코드"
     , PROD.PROD_NAME       AS "상품"
     , SUM(BUYPROD.BUY_QTY) AS "입고수량"
  FROM PROD, BUYPROD
 WHERE PROD.PROD_ID = BUYPROD.BUY_PROD
   AND BUYPROD.BUY_DATE = '20200505'
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME;

-- 판매 확인(Inner Join)
SELECT PROD.PROD_ID         AS "상품코드"
     , PROD.PROD_NAME       AS "상품"
     , SUM(CART.CART_QTY)   AS "판매수량"
  FROM PROD 
 INNER JOIN CART 
    ON (PROD.PROD_ID = CART.CART_PROD)
 WHERE CART.CART_NO LIKE '20200505%'
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME;

--전체 상품의 2020년도 5월 5일의 입고.출고현황 조회
--(상품코드, 상품명, 입고수량의 합, 판매수량의 합)

--일반 JOIN 
SELECT PROD.PROD_ID AS "상품코드"
     , PROD.PROD_NAME AS "상품"
     , SUM(NVL(BUYPROD.BUY_QTY,0)) AS "입고수량"
     , SUM(NVL(CART.CART_QTY,0)) AS "판매수량"
  FROM PROD
     , BUYPROD
     , CART
 WHERE PROD.PROD_ID = BUYPROD.BUY_PROD
   AND PROD.PROD_ID = CART.CART_PROD
   AND BUYPROD.BUY_DATE = '20200505'
   AND SUBSTR(CART.CART_NO,1,8) = '20200505'
 GROUP BY PROD.PROD_ID, PROD.PROD_NAME;

--위 Equi Join은 결과가 나오지 않는다.
--   OUTER JOIN 사용 확인(NULL값 제거) 검색하시오 ?
SELECT PROD.PROD_ID AS "상품코드"
     , PROD.PROD_NAME AS "상품"
     , SUM(NVL(BUYPROD.BUY_QTY,0)) AS "입고수량"
     , SUM(NVL(CART.CART_QTY,0)) AS "판매수량"
  FROM PROD
  LEFT OUTER JOIN BUYPROD ON(PROD.PROD_ID = BUYPROD.BUY_PROD AND BUYPROD.BUY_DATE = '20200505')
  LEFT OUTER JOIN CART    ON(PROD.PROD_ID = CART.CART_PROD   AND SUBSTR(CART.CART_NO,1,8) = '20200505')
  HAVING SUM(NVL(BUYPROD.BUY_QTY,0)) > 0 OR SUM(NVL(CART.CART_QTY,0)) > 0
  GROUP BY PROD.PROD_ID, PROD.PROD_NAME;

---

--회원ID가 'h001(라준호)'인 고객의 마일리지 점수보다 이상인 회원만 검색  조회
--(Alias는 회원ID, 성명, 마일리지)
SELECT B.MEM_ID AS "회원ID"
     , B.MEM_NAME AS "성명"
     , B.MEM_MILEAGE AS "마일리지"
  FROM MEMBER A
     , MEMBER B
 WHERE A.MEM_ID = 'h001'
   AND A.MEM_MILEAGE <= B.MEM_MILEAGE;

--SELF JOIN을 사용하여 지정한 사원의 이름, 부서번호, 지정한 사원과 동일한 부서에서 근무하는 사원을 출력하시오. (고영우)
SELECT * FROM EMP;

SELECT E1.ENAME 사원이름
     , E2.DEPTNO 부서번호
  FROM EMP E1, EMP E2
 WHERE E1.DEPTNO = E2.DEPTNO
   AND E2.ENAME = '고영우';

--사원 번호가 7499인 사원과 담당 업무가 같은 사원을 표시(사원 이름과 담당업무)하시오.
SELECT * FROM EMP WHERE EMPNO = '7499';

SELECT E2.ENAME 사원이름
     , E2.JOB 담당업무
  FROM EMP E1, EMP E2
 WHERE 1=1
   AND E1.JOB = E2.JOB
   AND E1.EMPNO = '7499';

--상품테이블에서 상품코드, 상품명, 분류명, 거래처 명을 조회 하시오.
SELECT * FROM PROD;
SELECT * FROM LPROD;
SELECT * FROM BUYER;

SELECT A.PROD_ID 상품코드
     , A.PROD_NAME 상품명
     , C.LPROD_NM 분류명
     , B.BUYER_NAME 거래처명
  FROM PROD A, BUYER B, LPROD C
 WHERE A.PROD_LGU = B.BUYER_LGU
   AND A.PROD_LGU = C.LPROD_GU;

---

-- 학사관리시스템에 아래 학점관리 테이블을 생성하시오.
CREATE TABLE HAKJUM
(
GRADE CHAR(2) NOT NULL,
MIN_POINT NUMBER(3) NOT NULL,
MAX_POINT NUMBER(3) NOT NULL,
CONSTRAINT PK_HAKJUM PRIMARY KEY (GRADE)
);

-- 학점관리 테이블에 성적 기준표를 삽입하시오.
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('A+', 96, 100);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('A0', 90, 95);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('B+', 85, 89);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('B0', 80, 84);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('C+', 75, 79);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('C0', 70, 74);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('D+', 65, 69);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('D0', 60, 64);
INSERT INTO hakjum (grade, min_point, max_point) 
VALUES ('F', 0, 59);

SELECT * FROM HAKJUM;

--학사관리시스템에서　수강학생들의　성적을　학점으로　나타내시오.

WITH ACADEMIC_DATA AS (
    SELECT '2008101029' AS "STD_NO",'홍길동' AS "STD_NAME", 'AB03' AS "SUB_CD", '국어' AS "SUB_NAME", 100 AS "CLS_SCORE" FROM DUAL UNION ALL
    SELECT '2009102023','김자바', 'CD12', '영어', 94 FROM DUAL UNION ALL
    SELECT '2010203063','박자바', 'EF34', '수학', 87 FROM DUAL UNION ALL
    SELECT '2011405095','이자바', 'GH56', '사회', 84 FROM DUAL UNION ALL
    SELECT '2012607021','택자바', 'IJ98', '과학', 77 FROM DUAL UNION ALL
    SELECT '2013802003','서자바', 'KL23', '과학', 74 FROM DUAL UNION ALL
    SELECT '2014902001','유자바', 'MN04', '역사', 67 FROM DUAL
)
   SELECT * FROM ACADEMIC_DATA;
   
SELECT B.STD_NO 학번
     , B.STD_NAME 학생명
     , B.SUB_CD 과목코드
     , B.SUB_NAME 과목명
     , B.CLS_SCORE 성적
     , A.GRADE 학점
  FROM HAKJUM A,
       (WITH ACADEMIC_DATA AS (
            SELECT '2008101029' AS "STD_NO",'홍길동' AS "STD_NAME", 'AB03' AS "SUB_CD", '국어' AS "SUB_NAME", 100 AS "CLS_SCORE" FROM DUAL UNION ALL
            SELECT '2000102039','김자바', 'CD12', '영어', 94 FROM DUAL UNION ALL
            SELECT '2000102039','박자바', 'EF34', '수학', 87 FROM DUAL UNION ALL
            SELECT '2000102039','이자바', 'GH56', '사회', 84 FROM DUAL UNION ALL
            SELECT '2000102039','택자바', 'IJ98', '과학', 77 FROM DUAL UNION ALL
            SELECT '2000102039','서자바', 'KL23', '과학', 74 FROM DUAL UNION ALL
            SELECT '2000102039','유자바', 'MN04', '역사', 67 FROM DUAL)
            SELECT * FROM ACADEMIC_DATA) B
 WHERE 1=1
   AND B.CLS_SCORE BETWEEN A.MIN_POINT AND A.MAX_POINT;

--학사관리시스템에서　과목별　평균성적을 구하시오.
SELECT B.SUB_CD 과목코드
     , B.SUB_NAME 과목명
     , ROUND(AVG(B.CLS_SCORE),1) 평균성적
  FROM HAKJUM A,
       (WITH ACADEMIC_DATA AS (
            SELECT '2008101029' AS "STD_NO",'홍길동' AS "STD_NAME", 'AB03' AS "SUB_CD", '국어' AS "SUB_NAME", 100 AS "CLS_SCORE" FROM DUAL UNION ALL
            SELECT '2000102039','김자바', 'AB03', '국어', 44 FROM DUAL UNION ALL
            SELECT '2000102039','자자바', 'CD12', '영어', 94 FROM DUAL UNION ALL
            SELECT '2000102039','류자바', 'CD12', '영어', 42 FROM DUAL UNION ALL
            SELECT '2000102039','박자바', 'EF34', '수학', 87 FROM DUAL UNION ALL
            SELECT '2000102039','리자바', 'EF34', '수학', 53 FROM DUAL UNION ALL
            SELECT '2000102039','이자바', 'GH56', '사회', 84 FROM DUAL UNION ALL
            SELECT '2000102039','사자바', 'GH56', '사회', 12 FROM DUAL UNION ALL
            SELECT '2000102039','택자바', 'KL23', '과학', 77 FROM DUAL UNION ALL
            SELECT '2000102039','서자바', 'KL23', '과학', 99 FROM DUAL UNION ALL
            SELECT '2000102039','유자바', 'MN04', '역사', 67 FROM DUAL UNION ALL
            SELECT '2000102039','오자바', 'MN04', '역사', 100 FROM DUAL)
            SELECT * FROM ACADEMIC_DATA) B
 GROUP BY B.SUB_CD, B.SUB_NAME
 ORDER BY ROUND(AVG(B.CLS_SCORE),1) DESC;