[패스트캠퍼스 SQLD] 1. 데이터베이스 실습 (Oracle)

<설치>

1. Oracle Database 12cr2 설치

2. DBeaver 설치 후 접속

3. SQLPlus = 오라클 터미널 창 접속

 * C:\>sqlplus 관리자명/비밀번호

4. 실습 계정 생성 및 권한 주기

5. schema 파일 실행 

 > 파일 있는 C드라이브로 디렉토리 이동, ot 계정으로 접속 > @sql파일명 = 실행

 > NLS_LANGUAGE 변경, NLS_DATE_FORMAT 변경. > sql 파일 실행 > 데이터 있는지 확인 

---

<SQL문 기본>

## SELECT문 ##

#오라클에서 제공하는 DUAL 테이블 조회
SELECT *
FROM DUAL ; 

#DUAL 테이블 이용해 SELECT절에서 다양한 연산 처리
SELECT (10+5)/2 AS VAL
FROM DUAL ;

#오름차순, 내림차순 정렬
SELECT *
FROM CUSTOMERS
ORDER BY NAME ASC; #(오름차순)

#중복 제외하고 고유값만 보기
SELECT DISTINCT FIRST_NAME, QUANTITY #(성과 수량이 같은 값은 제외하고 보여줘)
FROM FIRST_NAME
ORDER BY FIRST_NAME;

#조건에 맞는 한정된 집합만 뽑고싶을때
SELECT *
FROM PRODUCTS
WHERE LIST_PRICE > 500
AND CATEGORY_ID = 4; #(500원 이상, 카테고리 id가 4인 제품만)
-- WHERE LIST_PRICE BETWEEN 650 AND 680 (650이상 그리고 680이하)
-- WHERE CATEGORY_ID IN (1,4) (ID가 1 또는 4)
-- WHERE PRODUCT_NAME LIKE 'Asus%' (Asus%로 시작하는)

## INSERT문 ##

#테이블 생성
DROP TABLE DISCOUNTS;

CREATE TABLE DISCOUNTS
(
  DISCOUNT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY
, DISCOUNT_NAME VARCHAR2(255) NOT NULL
, AMOUNT NUMBER(3,1) NOT NULL
, START_DATE DATE NOT NULL
, EXPIRED_DATE DATE NOT NULL
);

#데이터 삽입
INSERT INTO
 DISCOUNTS (
 			  DISCOUNT_NAME
            , AMOUNT
            , START_DATE
            , EXPIRED_DATE
            )
    VALUES ( 'Summer promotion'
    		, 9.5
            , DATE '2017-05-01' #(최근 날짜 CURRENT_DATE)
            , DATE '2017-08-31'
            ) ;
            
#커밋(혹은 롤백)
COMMIT;

#데이터 확인 
SELECT *
FROM DISCOUNTS;

## UPDATE문 ##

CREATE TABLE PARTS
(
PART_ID NUMBER GENERATED BY DEFAULT AS IDENTITY
, PART_NAME VARCHAR(50) NOT NULL
, LEAD_TIME NUMBER(2, 0) NOT NULL
, COST NUMBER(9, 2) NOT NULL
, STATUS NUMBER(1, 0) NOT NULL
, PRIMARY KEY (PART_ID)
);

INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('sed dictum',5,134,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('tristique neque',3,62,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('dolor quam,',16,82,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('nec, diam.',41,10,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('vitae erat',22,116,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('parturient montes,',32,169,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('metus. In',45,88,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('at, velit.',31,182,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('nonummy ultricies',7,146,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('a, dui.',38,116,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('arcu et',37,72,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('sapien. Cras',40,197,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('et malesuada',24,46,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('mauris id',4,153,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('eleifend egestas.',2,146,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('cursus. Nunc',9,194,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('vivamus sit',37,93,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('ac orci.',35,134,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('arcu. Aliquam',36,154,0);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('at auctor',32,56,1);
INSERT INTO PARTS (PART_NAME,LEAD_TIME,COST,STATUS) VALUES ('purus, accumsan',33,12,1);

COMMIT;

#UPDATE 실행 및 데이터 확인

UPDATE PARTS 
SET COST = 130
WHERE PART_ID=1;
COMMIT;

UPDATE PARTS
SET LEAD_TIME = 30
, COST = 120
WHERE PART_ID = 5;
COMMIT;

UPDATE PARTS 
SET COST = COST*1.05;
COMMIT; #(전체 항목 COST 한번에 업데이트)

## DELETE문 ##

CREATE TABLE SALES AS
SELECT
A.ORDER_ID
, B.ITEM_ID
, B.PRODUCT_ID
, B.QUANTITY
, B.UNIT_PRICE
, A.STATUS
, A.ORDER_DATE
, A.SALESMAN_ID
FROM ORDERS A
, ORDER_ITEMS B
WHERE A.ORDER_ID = B.ORDER_ID;

SELECT * FROM SALES;

DELETE 
FROM SALES 
WHERE ORDER_ID =1
AND ITEM_ID=1;
COMMIT;

## VIEW ##

-- 연도별 각 고객의 매출 금액 구하기 뷰 생성
CREATE OR REPLACE VIEW CUSTOMER_SALES AS
SELECT 
	C.NAME AS CUSTOMER
,	TO_CHAR(A.ORDER_DATE, 'YYYY') AS YEAR
,	SUM(B.QUANTITY * B.UNIT_PRICE) SALES_AMOUNT
FROM ORDERS A
,	ORDER_ITEMS B
,	CUSTOMERS C
WHERE A.STATUS = 'Shipped'
AND A.ORDER_ID = B.ORDER_ID 
AND A.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.NAME, TO_CHAR(A.ORDER_DATE, 'YYYY')
ORDER BY C.NAME ;

-- 고객별 2017년도 매출 구하기
SELECT CUSTOMER, SALES_AMOUNT
FROM CUSTOMER_SALES
WHERE YEAR = 2017
ORDER BY SALES_AMOUNT DESC;

-- 고객별 전체 매출 합계 구하기
SELECT CUSTOMER, SUM(SALES_AMOUNT) AS SUM_SALES_AMOUNT
FROM CUSTOMER_SALES
GROUP BY CUSTOMER
ORDER BY SUM_SALES_AMOUNT DESC;

## 서브 쿼리 ## 

SELECT PRODUCT_ID, PRODUCT_NAME, LIST_PRICE
FROM PRODUCTS
WHERE LIST_PRICE = (SELECT MAX(LIST_PRICE) FROM PRODUCTS);

## 스칼라 서브 쿼리 : SELECT 절 안에 있는 서브 쿼리 ##

SELECT PRODUCT_ID, PRODUCT_NAME, LIST_PRICE, 
       ROUND((SELECT AVG(K.LIST_PRICE) 
              FROM PRODUCTS K 
              WHERE K.CATEGORY_ID=A.CATEGORY_ID), 2) AVG_LIST_PRICE
FROM PRODUCTS A
WHERE LIST_PRICE = (SELECT MAX(LIST_PRICE) FROM PRODUCTS);

## 인라인 뷰 서브 쿼리 : FROM 절 안에 있는 서브 쿼리 ##

SELECT ORDER_ID, ORDER_VALUE
FROM (
		SELECT ORDER ID, SUM(QUANTITY * UNIT_PRICE) ORDER_VALUE
        FROM ORDER_ITEMS
        GROUP BY ORDER_ID
        ORDER BY ORDER_VALUE DESC
      )
WHERE ROWNUM <= 10 ; #(상위 10건만 보여줌)

 

<연습문제>

-- 문제 1 : 제품 테이블에서 가격이 평균 가격보다 큰 행의 칼럼 구하기 -- 
...

-- 문제 2 : 고객 테이블에서 신용한도가 가장 큰 10건의 행 구하기 --
SELECT *
FROM (SELECT * FROM CUSTOMERS ORDER BY CREDIT_LIMIT DESC, NAME ASC)
WHERE ROWNUM <=10;

 

---

<테이블 설계 : ERD> : DA# 프로그램 설치 후 작성

 * Barker 표기법 : 오라클사에 기본 표기법으로 채택.

 - 외래키 참조무결성조건 : 참조하는 키가 없으면 못만들어.

---

<실행 계획 : 준비>

SELECT *
FROM CUSTOMERS A
	, ORDERS B
WHERE A.CUSTOMER_ID = A.CUSTOMER_ID 
AND A.CUSTOMER_ID = 17;

--실습환경 구축:CUSTOMERS TABLE 1000배 복제--
CREATE TABLE TB_EXECUTION_TEST
AS
SELECT D.NO, E.*
FROM OT.CUSTOMERS E
	, (SELECT ROWNUM NO
		FROM DUAL 
		CONNECT BY LEVEL <= 1000) D;
		
--인덱스 생성--
CREATE INDEX IDX_TB_EXECUTION_TEST_01 ON TB_EXECUTION_TEST(CUSTOMER_ID);
CREATE INDEX IDX_TB_EXECUTION_TEST_02 ON TB_EXECUTION_TEST(NAME, ADDRESS);

--통계정보 생성--

 

<실행 계획 : 확인하기>

인덱스가 있는 행(컬럼) 조회

인덱스가 없는 행(컬럼) 조회