230720 SQL 강의

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

SELECT * FROM PROD; -- 상품 테이블
SELECT * FROM BUYER; -- 매입처 테이블
SELECT * FROM BUYPROD; -- 매입내역 테이블
SELECT * FROM CART; -- 주문(판매)현황 테이블
SELECT * FROM LPROD; -- 상품분류코드 테이블
SELECT * FROM MEMBER; -- 회원 테이블
SELECT * FROM ZIPTB; -- 주소 테이블

ALTER TABLE BUYER MODIFY (buyer_gbcd varchar2(10)); -- 길이를 10바이트로 변경.

---

UPDATE buyer SET buyer_gbcd = '우수' WHERE buyer_gbcd = 'a'; -- a를 우수로 변경
SELECT * FROM BUYER; -- 매입처 테이블

---

ALTER TABLE buyer 
ADD (Constraint fr_buyer_lprod Foreign key (buyer_lgu) 
                               References lprod(lprod_gu)); -- lprod테이블의 lprod_gu컬럼과 buyer테이블의 buyer_lgu컬럼을 fr_buyer_lprod이름의 참조키로 설정하겠다.

CREATE INDEX idx_buyer ON buyer (buyer_name, buyer_id); -- buyer 테이블에 buyer_name와 buyer_id 컬럼을 idx_buyer이름으로 인덱스를 줘라.

DROP INDEX idx_buyer; -- idx_buyer 인덱스 삭제

SELECT mem_mileage, ROUND(mem_mileage / 12) FROM member; -- 회원 테이블의 마일리지를 12로 나눈 값을 검색하시오

---

--상품 테이블의 상품코드, 상품명, 판매금액을 검색 하시오?
--판매금액은  = 판매단가 * 55 로 계산한다.
SELECT PROD_ID 상품코드, PROD_NAME 상품명, PROD_PRICE * 55 판매금액 FROM PROD ORDER BY PROD_PRICE DESC;

---

SELECT
    mem_id,
    mem_name,
    mem_bir,
    mem_mileage
FROM
    member
ORDER BY 3; -- 3번째 컬럼(mem_bir)을 오름차순으로 정렬하라.

---

SELECT
    mem_id,
    mem_name,
    mem_bir,
    mem_mileage
FROM
    member
ORDER BY mem_mileage, 1; -- mem_mileage를 먼저 오름차순 정렬하고, 1번째 컬럼(mem_id)을 오름차순 정렬하라.

-- 상품 중 판매가가 170,000원인 상품 조회
SELECT * FROM PROD; -- 상품 테이블
SELECT * FROM PROD WHERE PROD_PRICE = 170000;

---

--회원 중 76년도 1월 1일 이후에 태어난 회원을 검색하시오 ?
--  단, 주민등록번호 앞자리로 비교
--  ( Alias는 회원ID, 회원 명, 주민등록번호 앞자리 )
SELECT * FROM MEMBER; -- 회원 테이블
SELECT mem_id "회원ID", 
       MEM_NAME "회원 명", 
       MEM_REGNO1 "주민등록번호 앞자리"
  FROM MEMBER
 WHERE 1=1
   AND MEM_REGNO1 >= 760101
 ORDER BY 3;

---

--상품 중 매입가가 200,000원 이하인 상품을 검색하시오 ?
--  ( Alias는 상품코드, 상품명, 매입가 )
SELECT * FROM PROD; -- 상품 테이블
SELECT PROD_ID 상품코드, 
       PROD_NAME 상품명, 
       PROD_COST 매입가
  FROM PROD
 WHERE PROD_COST <= 200000
 ORDER BY PROD_COST DESC;

---

--상품 중 상품분류가 P201(여성 캐쥬얼)이거나 
--판매가가 170,000원인 상품 조회
SELECT * FROM PROD; -- 상품 테이블
SELECT PROD_LGU 상품분류,
       PROD_PRICE 판매가
  FROM PROD
 WHERE PROD_LGU = 'P201'
    OR PROD_COST = 170000;

---

--상품 중 상품분류가 P201(여성 캐쥬얼)도 아니고 
--판매가가 170,000원도 아닌 상품 조회
SELECT * FROM PROD; -- 상품 테이블
SELECT PROD_LGU 상품분류,
       PROD_PRICE 판매가
  FROM PROD
 WHERE NOT PROD_LGU = 'P201'
   AND NOT PROD_COST = 170000;
   
SELECT PROD_LGU 상품분류,
       PROD_PRICE 판매가
  FROM PROD
 WHERE PROD_LGU <> 'P201'
   AND PROD_COST != 170000;
   
SELECT PROD_LGU 상품분류,
       PROD_PRICE 판매가
  FROM PROD
 WHERE NOT (PROD_LGU = 'P201' OR PROD_COST = 170000);

---

-- 상품 중 판매가가 150,000원, 170,000원, 330,000원인 상품 조회
SELECT * FROM PROD; -- 상품 테이블
SELECT PROD_NAME 상품명,
       PROD_SALE 판매가
  FROM PROD
 WHERE PROD_SALE IN(150000, 170000, 330000);

---

--회원테이블에서 회원ID가 C001, F001, W001 인 회원만 검색하시오 ?
--( Alias는 회원ID, 회원명 )
SELECT * FROM MEMBER; -- 회원 테이블
SELECT MEM_ID 회원ID, 
       MEM_NAME 회원명
  FROM MEMBER
 WHERE MEM_ID IN('c001', 'f001', 'w001');

---

--상품 중 판매가가 100,000원 부터 300,000원 사이의 상품 조회
SELECT * FROM PROD; -- 상품 테이블
SELECT *
  FROM prod
 WHERE PROD_SALE BETWEEN 100000
                     AND 300000;

---

SELECT COUNT(*) FROM PROD; -- 상품 테이블
SELECT prod_id 상품코드, prod_name 상품명 FROM prod WHERE prod_name LIKE '%치';

---

SELECT prod_id 상품코드, prod_name 상품명 FROM prod WHERE prod_name LIKE '%여름%';

---

SELECT 'a' || 'bcde' AS "abcde" FROM DUAL;

---

SELECT mem_id || ' name is ' || MEM_NAME AS "나의 이름은" FROM MEMBER;

---

SELECT CONCAT('My Name is ', MEM_NAME) AS "나의 이름은" FROM MEMBER;

---

SELECT CHR(65) "CHR", ASCII('ABC') "ASCII" FROM DUAL;

---

SELECT ASCII( CHR(65) ) RESULT FROM DUAL;

---

SELECT CHR(75) "CHR", ASCII('K') "ASCII" FROM DUAL;

---

-- 회원테이블의 회원ID Column의 ASCII값을 검색하시오 ?
SELECT ASCII(MEM_ID) AS 회원ASCII,
       CHR(ASCII(MEM_ID)) AS 회원CHR
FROM MEMBER;

---

SELECT ASCII('A') "ASCII A",
       ASCII('Z') "ASCII Z",
       ASCII('a') "ASCII a",
       ASCII('z') "ASCII z"
  FROM DUAL;

---

-- 이름을 아스키 값으로 구하시오.
SELECT ASCII('홍') "홍",
       ASCII('길') "길",
       ASCII('동') "동"
  FROM DUAL;

SELECT ASCII('홍길동') "홍길동"
  FROM DUAL;

---

SELECT LOWER('DATA manipulation Language') "LOWER",
       UPPER('DATA manipulation Language') "UPPER",
       INITCAP('DATA manipulation Language') "INITCAP"
  FROM DUAL;

---

--회원테이블의 회원ID를 대문자로 변환하여 검색하시오 ?
--(Alias명은 변환 전ID, 변환 후ID)
SELECT * FROM MEMBER; -- 회원 테이블
SELECT MEM_ID "변환 전ID",
       UPPER(MEM_ID) "변환 후ID"
  FROM MEMBER;

---

SELECT LPAD ('Java', 10, '*') "LPAD", 
       RPAD ('Flex', 12, '^') "RPAD"
  FROM DUAL;

---

-- 상품테이블의 소비자가격과 소비자가격을 치환하여 다음과 같이 출력되게 하시오 ?
SELECT * FROM PROD; -- 상품 테이블
SELECT PROD_COST "소비자가격 채우기 전",
       LPAD (PROD_COST, 10, '*') "소비자가격 채운 후"
  FROM PROD;

---

-- 피라미드 만들기 
SET SERVEROUTPUT ON --출력옵션으로 ON
DECLARE 
  V_ID  NUMBER := 1; -- 변수 V_ID 초기화
  V_ID2 NUMBER := 10; -- 변수 V_ID2 초기화
BEGIN
    DBMS_OUTPUT.PUT_LINE(''); -- PUT_LINE. 다음 라인으로 넘어가라.
  WHILE V_ID < 20 LOOP
    DBMS_OUTPUT.PUT(RPAD(' ', V_ID2, ' ')); -- 맨 왼쪽에 ' ' 하나 찍어주고 나머지 칸(V_ID2 - 1)을 ' '로 채움
    DBMS_OUTPUT.PUT_LINE(RPAD('*', V_ID , '*')); -- 맨 왼쪽에 '*' 하나 찍어주고 나머지 칸(V_ID - 1)을 '*' 찍고 다음 라인으로
    V_ID  := V_ID  + 2;  -- V_ID를 1회전 할 때마다 2씩 증가
    V_ID2 := V_ID2 - 1;  -- V_ID2를 1회전 할 때마다 1씩 감소
  END LOOP;  
END;
/

-- 피라미드 만들기 
SET SERVEROUTPUT ON --출력옵션으로 ON
DECLARE 
  V_ID  NUMBER := 1; -- 변수 V_ID 초기화
  V_ID2 NUMBER := 10; -- 변수 V_ID2 초기화
BEGIN
    DBMS_OUTPUT.PUT_LINE(''); -- PUT_LINE. 다음 라인으로 넘어가라.
  WHILE V_ID < 20 LOOP
    DBMS_OUTPUT.PUT(RPAD(' ', V_ID2, 'a')); -- 맨 왼쪽에 ' ' 하나 찍어주고 나머지 칸(V_ID2 - 1)을 a로 채움
    DBMS_OUTPUT.PUT_LINE(RPAD('*', V_ID , '*')); -- 맨 왼쪽에 '*' 하나 찍어주고 나머지 칸(V_ID - 1)을 '*' 찍고 다음 라인으로
    V_ID  := V_ID  + 2;  -- V_ID를 1회전 할 때마다 2씩 증가
    V_ID2 := V_ID2 - 1;  -- V_ID2를 1회전 할 때마다 1씩 감소
  END LOOP;  
END;
/

---

SELECT '<' || LTRIM('     AAA     ') || '>' "LTRIM1",
       '<' || LTRIM('Hello World', 'He') || '>' "LTRIM2"
  FROM DUAL;

---

SELECT '<' || RTRIM('     AAA     ') || '>' "RTRIM1",
       '<' || RTRIM('Hello World', 'ld') || '>' "RTRIM2"
  FROM DUAL;

---

SELECT '<' || TRIM('     AAA     ') || '>'  TRIM1,
       '<' || TRIM(LEADING 'a' FROM 'aaAaBaAaa') || '>'  TRIM2,
       '<' || TRIM(TRAILING 'a' FROM 'aaAaBaAaa') || '>'  TRIM3,
       '<' || TRIM(BOTH 'a' FROM 'aaAaBaAaa') || '>'  TRIM4,
       '<' || TRIM( 'a' FROM 'aaAaBaAaa') || '>'  TRIM5 
  FROM DUAL;

---

SELECT SUBSTR('SQL PROJECT', 1, 3)  RESULT1, -- SQL
       SUBSTR('SQL PROJECT', 5)  RESULT2, -- PROJECT
       SUBSTR('SQL PROJECT', -7, 3) RESULT3  -- PRO
  FROM DUAL;

---

SELECT mem_id,   
       SUBSTR(mem_name, 1, 1) 성씨
  FROM member;

---

SELECT TRANSLATE('2009-02-28', '0123456789-', 'ABCDEFGHIJK') RESULT
  FROM DUAL;

---

SELECT REPLACE('SQL Project', 'SQL', 'SSQQLL') AS 문자치환1,
       REPLACE('Java Flex Via', 'a') AS 문자치환2
  FROM DUAL;

---

SELECT INSTR('hello heidi', 'he') AS RESULT1,  
       INSTR('hello heidi', 'he', 3) AS RESULT2 -- 3번째 문자열에서 he를 찾아라
  FROM DUAL;

---

-- 숫자, 영문, 띄어쓰기 : 1BYTE
-- 한글 : 3BYTE
SELECT LENGTH('SQL 프로젝트') "LENGTH",  -- 글자 (띄어쓰기 포함) 8개
       LENGTHB('SQL 프로젝트') "LENGTHB" -- SQL(3BYTE) + " "(1BYTE) + 프로젝트(12BYTE) = 16BYTE
  FROM DUAL;

---

SELECT ABS(-365) 
  FROM DUAL; -- 절대값

---

SELECT SIGN(12),
       SIGN(0),
       SIGN(-55)
  FROM DUAL; -- 양수, 음수 구분

---

SELECT POWER(3, 2),
       POWER(2, 10)  
  FROM DUAL; -- 승수 값 (n의 y승)

---

SELECT SQRT(2), 
       SQRT(9) 
  FROM DUAL; -- n의 제곱근

---

SELECT GREATEST(10, 20, 30) "큰값",
       LEAST(10, 20, 30) "작은값"
  FROM DUAL;

---

SELECT GREATEST('강아지', 256, '송아지') "큰값", -- 송아지(ASCII) 15500961 > 강아지(ASCII) 15380629 > 256
       LEAST('강아지', 256, '송아지') "작은값"  
  FROM DUAL;
  
SELECT ASCII('송아지') "송아지"
  FROM DUAL; -- 송아지(ASCII) 15500961
SELECT ASCII('강아지') "강아지"
  FROM DUAL; -- 강아지(ASCII) 15380629

---

SELECT ROUND(345.123, 0) 결과 
  FROM DUAL;

---

SELECT ROUND(345.623, -1) 결과1,
       TRUNC(345.523, -1) 결과2
  FROM DUAL;

---

--회원 테이블의 마일리지를 12로 나눈 값을 검색
--(소수3째자리 반올림, 절삭)
SELECT (ROUND(MEM_MILEAGE / 12, 3)) "회원 마일리지"
  FROM MEMBER

---

SELECT MOD(10, 3) 
  FROM DUAL; -- n으로 나눈 나머지, 10%3=1;