Oracle (오라클) 실습 저장

1. 도메인 무결성 제약, PK, FK 실습

-- 1. Domain Constraint, 도메인 무결성 제약)
 
CREATE TABLE KOPO_PRODUCT_COLUMN_TEST 
(
    CHARCOL VARCHAR2 (100),
    NUMCOL NUMBER NOT NULL
);
 
-- 자료형에 위배되는 데이터는 데이터는 들어갈 수 없다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1','TEST2')
 
-- NULL을 허용하지 않는다 하여 NULL이 들어갈 수 없다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',NULL)
 
-- 도메인 무결성 제약을 위배하지 않는 데이터는 정상적으로 들어간다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',10)
 
SELECT * FROM KOPO_PRODUCT_COLUMN_TEST
 
-- 추가로 넣어도 또 들어간다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',10)
 
DROP TABLE KOPO_PRODUCT_COLUMN_TEST
 
 
-- 2. PK(Primary Key Constraint, 개체 무결성 제약), FK(Foreign Key Constrain, 참조 무결성 제약)
 
-- 모든 테이블 조회
SELECT * FROM TABS
 
-- 부모 테이블 생성 및 조회 (행사 정보 테이블)
CREATE TABLE KOPO_EVENT_INFO_FOREIGN
(
    EVENTID VARCHAR2(20),
    EVENTPERIOD VARCHAR2(20),
    PROMOTION_RATIO NUMBER,
    CONSTRAINT PK_KOPO_EVENT_INFO_FOREIGN PRIMARY KEY (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / PK (지정할 컬럼)
);
 
SELECT * FROM KOPO_EVENT_INFO_FOREIGN
 
DESC KOPO_EVENT_INFO_FOREIGN
 
 
-- 자식 테이블 생성 및 조회 (실적 정보 테이블)
CREATE TABLE KOPO_PRODUCT_VOLUME_FOREIGN
(
    REGIONID VARCHAR2(20),
    PRODUCTGROUP VARCHAR2(20),
    YEARWEEK VARCHAR2(8),
    VOLUME NUMBER NOT NULL,
    EVENTID VARCHAR2(20),
    CONSTRAINT PK_KOPO_PRODUCT_VOLUME_FOREIGN PRIMARY KEY (REGIONID, PRODUCTGROUP, YEARWEEK),
    CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID) REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / FK (지정할 컬럼) ref '부모테이블'(의 컬럼명)
);
 
SELECT * FROM KOPO_PRODUCT_VOLUME_FOREIGN
 
DESC KOPO_PRODUCT_VOLUME_FOREIGN
 
-- 자식 테이블에서 참조키 설정하기 (테이블 생성 후 FK 설정하기)(부모 테이블을 강제 삭제하기 위해 CASCADE 옵션을 주면 자식 테이블의 FK가 삭제된다.)
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
ADD CONSTRAINTS FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID)
REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID)
--ON DELETE CASCADE -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 row를 전부 삭제)
--ON DELETE SET NULL -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 FK를 NULL처리하고 row 자체를 삭제하진 않는다.)
 
-- 자식 테이블에서 참조키 드랍하기
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
DROP CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN;
 
-- 부모 테이블에 데이터 입력
INSERT INTO KOPO_EVENT_INFO_FOREIGN
VALUES ('A01','20',0.1)
 
-- 자식 테이블에 데이터 입력 (부모 테이블에 'A01'이 이미 만들어져 있음)
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A01')
 
-- 자식 테이블에 데이터 입력 (부모 테이블에 'A02'가 아직 없음)
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A02')
 
-- 부모 테이블에서 데이터 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DELETE FROM KOPO_EVENT_INFO_FOREIGN
WHERE EVENTID = 'A01'
 
-- 부모 테이블을 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DROP TABLE KOPO_EVENT_INFO_FOREIGN
 
-- 부모 테이블을 강제 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DROP TABLE KOPO_EVENT_INFO_FOREIGN CASCADE CONSTRAINT -- 테이블 삭제를 위해 CONSTRAINT 제약 조건(PK, FK)을 삭제시키는 옵션을 준다.
 
DROP TABLE KOPO_EVENT_INFO_FOREIGN DELETE SET NULL CONSTRAINT
 
-- ON DELETE SET NULL 실습 후 해당 row 데이터 삭제
DELETE FROM KOPO_PRODUCT_VOLUME_FOREIGN
WHERE REGIONID = 'SEOUL'

2020/05/02 - [개발자/Database] - Oracle (오라클) Primary Key (기본키) 실습

Oracle (오라클) Primary Key (기본키) 실습

 

 

2. Relation Algebra (관계 대수) - 특정 row 조회(Selection), 특정 column 조회(Projection), 집합 연산자(Union) 릴레이션 합연산

-- 1. Relation Algebra(관계 대수)
-- 모든 테이블 조회하기
SELECT * FROM TABS
 
-- 해당 테이블데이터의 모든 데이터 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
 
SELECT
    REGIONID,       -- 지역정보
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME
 
-- 특정 컬럼만 조회하기.
SELECT
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME
 
SELECT YEARWEEK
FROM KOPO_PRODUCT_VOLUME
 
-- 컬럼명 변경하기 (VOLUME -> VOLUME2)
ALTER TABLE KOPO_PRODUCT_VOLUME
RENAME COLUMN VOLUME TO VOLUME2
 
-- 컬럼명 다시 되돌리기 (VOLUME2 -> VOLUME)
ALTER TABLE KOPO_PRODUCT_VOLUME
RENAME COLUMN VOLUME2 TO VOLUME
 
-- 1.1 데이터 조회 (Selection) : 특정 row 조회하기
-- KOPO_PRODUCT_VOLUME 테이블에서 물량이 800,000 이상인 데이터만 조회하세요.
SELECT
    REGIONID,       -- 지역정보
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME
WHERE VOLUME >= 800000
    
-- KOPO_PRODUCT_VOLUME 테이블에서 201601주차 이상이면서, ST0001인 상품 데이터를 조회하세요.
SELECT
    REGIONID,       -- 지역정보
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME
WHERE YEARWEEK >= '201601' AND PRODUCTGROUP = 'ST0001'
 
-- 1.2 데이터 조회 (Projection) : 특정 column 조회하기
-- KOPO_PRODUCT_VOLUME 테이블에서 모든 상품을 조회하세요.
SELECT
    PRODUCTGROUP   -- 상품정보
FROM KOPO_PRODUCT_VOLUME
 
-- 1.3 집합 연산자 (Union) : 2개의 릴레이션을 합치기
-- A02 테이블을 하나 생성하고 데이터를 불러온다.
CREATE TABLE KOPO_PRODUCT_VOLUME_A02
(
    REGIONID VARCHAR2(20),       -- 지역정보
    PRODUCTGROUP VARCHAR2(20),   -- 상품정보
    YEARWEEK VARCHAR2(6),        -- 주차정보
    VOLUME NUMBER,               -- 판매량   
    CONSTRAINT PK_KOPO_PRODUCT_VOLUME_A02 PRIMARY KEY(REGIONID, PRODUCTGROUP, YEARWEEK)
);
 
SELECT * FROM KOPO_PRODUCT_VOLUME_A02
 
DESC KOPO_PRODUCT_VOLUME_A02
 
-- A01 지점과 A02 지점의 실적을 합치기. (데이터 Union 하기) (속성 수와 컬럼 정보가 같아야한다.)
SELECT
    REGIONID,       -- 지역정보
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME
UNION ALL           -- 중복 제거 안 함
--UNION               -- 중복 제거 및 정렬
SELECT
    REGIONID,       -- 지역정보
    PRODUCTGROUP,   -- 상품정보
    YEARWEEK,       -- 주차정보
    VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME_A02

2020/05/03 - [개발자/Database] - Relational Algebra(관계 대수)와 SQL

Relational Algebra(관계 대수)와 SQL

 

 

3. Expression 수식 사용하기 (수식을 통해 별칭을 달거나 계산을 한다)

-- 1. Expression 수식 사용하기 (수식 AS 컬럼명)(별칭 달기)
-- '실적정보'라는 문자열 수식을 'MEASURE_NAME'이라는 컬럼에 담는다.
SELECT '실적정보' AS MEASURE_NAME, A.*
FROM KOPO_PRODUCT_VOLUME A
 
-- 위와 동일.
SELECT
    '실적정보' AS MEASUER_NAME,
    A.REGIONID,       -- 지역정보
    A.PRODUCTGROUP,   -- 상품정보
    A.YEARWEEK,       -- 주차정보
    A.VOLUME          -- 판매량
FROM KOPO_PRODUCT_VOLUME A
 
-- '상수 문자열'이 아닌 컬럼 정보를 이용할 수도 있다.
SELECT
    A.REGIONID || '_' || A.PRODUCTGROUP AS PLANID,    -- REGIONID + '_' + PRODUCTGROUP 을 'PLANID'라는 컬럼에 담는다.
    A.REGIONID          -- 지역코드
FROM KOPO_PRODUCT_VOLUME A
 
 
-- 2. Expression 수식 사용하기 (수식을 통해 계산)(계산)
SELECT
    A.YEARWEEK + A.VOLUME AS TEST,    -- (YEARWEEK + VOLUME) 을 'TEST라는 컬럼에 담는다다.
    A.*
FROM KOPO_PRODUCT_VOLUME A
 
-- Expression 을 사용해 조회한 데이터를 저장까지 하고 싶은 경우
CREATE TABLE STABLE AS
SELECT
    YEARWEEK+VOLUME AS TEST,
    A.*
FROM KOPO_PRODUCT_VOLUME A
 
-- 위에서 생성한 STABLE 조회
SELECT * FROM STABLE
 
-- 원본 테이블의 무결성 제약 조건을 살펴본다.(PK)
DESC KOPO_PRODUCT_VOLUME
 
-- 이렇게 저장한 데이터는 값은 저장되지만 '권한' 설정까지 복제하지는 못 한다.
DESC STABLE
 
-- 위에서 생성한 STABLE 삭제
DROP TABLE STABLE
 
SELECT * FROM TABS

2020/05/03 - [개발자/Database] - Expression 수식 사용하기 (별칭 달기, 계산)

Expression 수식 사용하기 (별칭 달기, 계산)

 

 

4. DISTINCT 사용하기 (중복값 제거)

SELECT * FROM KOPO_CHANNEL_RESULT
 
-- ACCOUNTNAME에 어떤 것들이 있나 확인할 수 있다.
SELECT
    DISTINCT ACCOUNTNAME
FROM KOPO_CHANNEL_RESULT
 
-- 상품군(PRODUCTGROUP)에 대한 DISTINCT 구현 후 컬럼 이름을 PRODUCT로 변경하세요.
SELECT
    DISTINCT PRODUCTGROUP AS PRODUCT
FROM KOPO_CHANNEL_RESULT

 

 

5. DB 링크 사용하기, IN, NOT IN, BETWEEN, 메타 문자 % 활용해 조회하기

-- 1.1 DB 링크 생성 (접속 엔진)
CREATE DATABASE LINK DEVSERVER
CONNECT TO kopo  -- ID
IDENTIFIED BY kopo -- PW
USING '
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'
  
CREATE DATABASE LINK DEVSERVER
CONNECT TO kopo  -- ID
IDENTIFIED BY kopo -- PW
USING 'SERVER112'
  
    
  
-- 1.2 DB 링크 전체 조회 (엔진 생성이 잘 되었나 확인)
SELECT * FROM ALL_DB_LINKS
 
-- 1.3 DB 링크 삭제하기. (실습 후 1.2로 삭제된 것을 확인하고 위 1.1로 가서 다시 생성한다.)
DROP DATABASE LINK DEVSERVER
 
 
-- 2.1 내 XE에서 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 조회하라 -> 없어서 안 나옴.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW
 
-- 2.2 엔진을 이용해 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 조회하라 -> 엔진을 이용해 학교 서버에 있는 DB를 조회.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW@DEVSERVER
 
 
-- 3.1 엔진을 이용해 내 XE에 데이터베이스를 복사해오자.
-- 내 XE에 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 만들어라. -> AS는 엑셀의 수식 기능 = 이랑 같다고 보면 된다.
CREATE TABLE KOPO_CHANNEL_RESULT_NEW AS
SELECT * FROM KOPO_CHANNEL_RESULT_NEW@DEVSERVER
 
-- 3.2 이제 내 XE에서 'KOPO_CHANNEL_RESULT_NEW' 테이블을 조회할 수 있다.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW
 
-- 3.3 생성된 테이블 목록도 확인해보자.
SELECT * FROM TABS
 
 
 
-- 실습 1) DEVSERVER 엔진을 이용해 학교 서버에서 'KOPO_REGION_MST' 테이블을 복사해온 다음 조회하세요.
CREATE TABLE KOPO_REGION_MST AS
SELECT * FROM KOPO_REGION_MST@DEVSERVER
 
SELECT * FROM KOPO_REGION_MST
 
 
 
-- 실습 2) 112 서버에서 'KOPO_PRODUCT_VOLUME' 테이블을 복사해오고, ST0002 상품의 연주차가 201514, 201516 인 제품만 조회하세요.
-- 1. 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME@DEVSERVER
 
-- 2. DB 복사해오기
CREATE TABLE KOPO_PRODUCT_VOLUME AS
SELECT * FROM KOPO_PRODUCT_VOLUME@DEVSERVER
 
-- 3.1 자료형 확인하기
DESC KOPO_PRODUCT_VOLUME
 
-- 3.2 데이터 조회하기 (AND OR 이용)
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND (YEARWEEK = '201514' OR YEARWEEK = '201516')
 
-- 3.2 데이터 조회하기 (IN 이용) YEARWEEK가 '201514','201516'인 것만 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK IN ('201514','201516')
 
-- 3.3 YEARWEEK가 '201514','201516'이 아닌 것만 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK NOT IN ('201514','201516')
 
-- 3.4 YEARWEEK가 '201514 ~ 201516'인 것을 조회하기 / 201514, 201515, 201516이 조회된다.
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK BETWEEN '201514' AND'201516'
 
 
 
-- 실습 3) 112 서버에서 'KOPO_CHANNEL_RESULT'테이블을 복사해오고, 상품명이 'UN55~~'로 시작하는 제품만 조회하세요.
-- 1. DB 복사해오기
CREATE TABLE KOPO_CHANNEL_RESULT AS
SELECT * FROM KOPO_CHANNEL_RESULT@DEVSERVER
 
-- 2. 자료형 확인하기
DESC KOPO_CHANNEL_RESULT
 
-- 3. ITEM이 'UN55~~'로 시작하는 상품 조회하기
SELECT * FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND ITEM LIKE 'UN55%'
 
 
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE '%KOPO%'
 
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE 'KOPO%'
 
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE '%KOPO'
 
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE 'KOPO'

 

 

6. 산술연산자, 결측값, 변수(&) 입력 받기

SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
 
 
SELECT DISTINCT SALESNAME
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND SALESNAME LIKE '%CO'
 
 
SELECT DISTINCT AP1ID
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND AP1ID IN ('401737','407373','402383')
 
CREATE TABLE AP1IDLIST
(
    AP1ID VARCHAR2(100),
    AP2NAME VARCHAR2(100)
)
 
INSERT INTO AP1IDLIST VALUES ('401737','폴리텍강서')
 
INSERT INTO AP1IDLIST VALUES ('401373','폴리텍정수')
 
INSERT INTO AP1IDLIST VALUES ('402383','폴리텍융기원')
 
SELECT * FROM AP1IDLIST
 
 
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
 
 
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK IN (201728, 201732)
 
 
-- 산술연산자
SELECT QTY * 1.5 AS QTY2
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
 
-- 반올림
SELECT ROUND(QTY * 1.23, 2) AS QTY2
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
 
-- 실습 1) KOPO_PROMOTION 테이블에서 조회를 통해 PMAP에 10% 할인된 가격을 ROUND 처리하여 'PMAP10'이라는 컬럼을 생성하세요.
-- 1. SERVER112에서 DB 불러와 저장하기
CREATE TABLE KOPO_PROMOTION AS
SELECT * FROM KOPO_PROMOTION@DEVSERVER
 
-- 2. 읽어온 DB 조회하기
SELECT *
FROM KOPO_PROMOTION
 
-- 3. EXPRESSION 수식 사용해서 조회하기
SELECT
    A.REGIONID,
    A.ITEM,
    A.TARGETWEEK,
    A.PLANWEEK,
    A.MAP_PRICE,
    A.IR,
    A.PMAP,
    A.PRODUCT,
    ROUND(A.PMAP * 0.9, 0) AS PMAP10
--    ROUND(A.PMAP - (A.PMAP * 0.1), 0) AS PMAP10
FROM KOPO_PROMOTION A
WHERE 1=1
 
 
-- 실습 2) KOPO_PROMOTION 테이블에서 PMAP/MAP_PRICE 비율을 구해서 PERCENT 컬럼을 생성하세요.
SELECT
    A.REGIONID,
    A.ITEM,
    A.TARGETWEEK,
    A.PLANWEEK,
    A.MAP_PRICE,
    A.IR,
    A.PMAP,
    A.PRODUCT,
    ROUND(A.PMAP / A.MAP_PRICE, 3) AS PERCENT
FROM KOPO_PROMOTION A
WHERE 1=1
 
 
-- 실습 3) KOPO_PRODUCT_VOLUME 테이블에서 연산자를 활용하여 'ST0001'의 201544 ~ 201548 주차 실적만 조회하세요.
SELECT * FROM KOPO_PRODUCT_VOLUME
 
SELECT
    A.REGIONID,
    A.PRODUCTGROUP,
    A.YEARWEEK,
    A.VOLUME
FROM KOPO_PRODUCT_VOLUME A
WHERE 1=1
AND PRODUCTGROUP = 'ST0001'
AND YEARWEEK BETWEEN '201544' AND '201548'
 
 
 
-- 결측값 조회하기
CREATE TABLE KOPO_PROMOTION_HK AS
SELECT * FROM KOPO_PROMOTION_HK@DEVSERVER
 
SELECT *
FROM KOPO_PROMOTION_HK
WHERE 1=1
AND MAP_PRICE IS NULL
--AND MAP_PRICE IS NOT NULL     -- 비어있지 않은 값을 조회
 
 
-- 변수를 입력 받아 조회하기
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = &PROD
 
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = 'MOBILE'

 

 

7. 함수편!  정렬, UNION, CONCAT, FORMATTING(포맷팅) 등...

4. SQL 실습-1 보고 나중에 정리할 것 (수정중)

 

Oracle (오라클) 6.17 join... 이거랑

Oracle (오라클) 함수 - 정렬, UNION, CONCAT, 포맷팅, GROUP BY, 형변환 이거 정리해야함

 

 

 

 

 

 

KOPO SQL v1.0.sql

0.01MB

 

TIP

특정 스키마(계정)의 테이블로 지정하고 싶다면 다음과 같이 입력할 수 있다.

KOPO.KOPO_PRODUCT_VOLUME

KOPO 계정의 KOPO_PRODUCT_VOLUME 테이블