Oracle (오라클) 산술연산자, 결측값, 변수 입력 받기

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'