230721 SQL 강의

-- FLOOR(n) : n과 같거나 작은 수 중에 가장 큰 정수
-- CEIL(n) : n과 같거나 큰 수 중에 가장 작은 정수
SELECT FLOOR(1332.69), CEIL(1332.69) FROM DUAL; -- 양수 내림, 올림

SELECT FLOOR(-1332.69), CEIL(-1332.69) FROM DUAL; -- 음수 내림, 올림

---

-- REMAINDER(c, n) : n으로 나눈 나머지, MOD 함수와 유사
SELECT MOD(10, 3), REMAINDER(10, 3)  FROM DUAL; -- 10-3.7*FLOOR(2.702702…,0) : 버림

SELECT MOD(10, 3.7) , REMAINDER(10, 3.7) FROM DUAL; -- 10-3.7*ROUND(2.702702…,0) : 반올림

---

SELECT WIDTH_BUCKET(88, 0, 100, 10)  FROM DUAL; -- (c, min, max, b) min에서 max의 범위로 설정하고 b구간으로 나누어 c가 어느 구간에 속하는지 리턴

-- 회원 테이블에서 회원이름,  마일리지,  등급을 출력하시오
-- (단, 등급은 마일리지를 500부터 9000까지 5등급으로 한다.) 
-- 9000-500 = 8500, 8500/5 = 1700씩 증가.
SELECT MEM_ID, 
       MEM_NAME, 
       MEM_MILEAGE, 
       WIDTH_BUCKET(MEM_MILEAGE, 9000, 500, 5) MILEAGE_GROUP
  FROM MEMBER
 ORDER BY MILEAGE_GROUP;

---

SELECT ROUND(345.123 - 0.05, 1) AS "RESULT1"   --소수점 둘째자리 버림
       ,ROUND(345.123 - 0.5, 0) AS "RESULT2"   --소수점 첫째자리 버림
       ,ROUND(345.123 - 5, -1) AS "RESULT3"    --첫번째자리 버림
    FROM DUAL;

--100 / 9  결과 값을 십의 자리까지 나타내고 일의 자리는 버리시오 
SELECT ROUND(100 / 9 - 5, -1) RESULT 
  FROM DUAL;

---

-- FLOOR함수 활용
SELECT 10000 / 7 FROM DUAL;
SELECT FLOOR(10000 / 7 * 1000) / 1000 FROM DUAL; --소수점3째자리
SELECT FLOOR(10000 / 7 * 100) / 100 FROM DUAL; --소수점2째자리
SELECT FLOOR(10000 / 7 * 10) / 10 FROM DUAL; --소수점1째자리
SELECT FLOOR(10000 / 7 ) FROM DUAL; --자연수
SELECT FLOOR(10000 / 7 / 10) * 10 FROM DUAL; --10째자리
SELECT FLOOR(10000 / 7 / 100) * 100 FROM DUAL; --100째자리
SELECT FLOOR(10000 / 7 / 1000) * 1000 FROM DUAL; --1000째자리

---

---

-- 기본적인 DATE 조회
SELECT SYSDATE FROM DUAL;
-- to_char => 날짜타입을 문자타입으로 변환
-- to_char(date타입 , 원하는 포멧 형식)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 날짜 FROM DUAL;
-- 년월일에 '-' 를 빼고 변환
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') 날짜 FROM DUAL;
-- to_date => 문자타입을 날짜 타입으로 변환
-- 시간도 넣어보기
SELECT TO_DATE('20190131', 'yyyy-mm-dd') FROM DUAL;
SELECT TO_DATE('20190131191420', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

---

SELECT TO_DATE('20211212', 'YYYYMMDD')
       , TO_DATE('20211212171000', 'YYYYMMDDHH24MISS') 
  FROM dual;

---

SELECT TO_DATE('88/12/11', 'RR/MM/DD') "1900년대"
       , TO_DATE('02/12/11', 'RR/MM/DD') "2000년대" 
  FROM dual;

---

SELECT * 
  FROM nls_session_parameters
 WHERE parameter = 'NLS_DATE_FORMAT';

---

SELECT ADD_MONTHS(SYSDATE, 3) 삼개월후,                        -- 현재시간 + 3개월 뒤 
	   LAST_DAY(SYSDATE) 금월마지막일,                          -- 해당월 마지막 일자 계산 
	   NEXT_DAY(SYSDATE, '일요일') 담주일요일,                   -- 다음 주 일요일 계산 
	   ROUND(MONTHS_BETWEEN(SYSDATE, SYSDATE-100)) 삼개월,     -- 개월 수 반환
       ROUND(MONTHS_BETWEEN(SYSDATE, '2023-02-25')) 오개월     -- 개월 수 반환
  FROM DUAL;

---

-- 1시간 20분 = 1과 20/60분(3.333...)
SELECT ROUND((TO_DATE('16:30', 'HH24:MI') - TO_DATE('15:10', 'HH24:MI')) * 24, 1) 시간으로차이계산
  FROM dual;

---

SELECT ROUND((TO_DATE('16:30', 'HH24:MI') - TO_DATE('15:10', 'HH24:MI'))*24*60, 1) 분으로시간차이계산
  FROM dual;

---

SELECT ROUND((TO_DATE('16:30', 'HH24:MI') - TO_DATE('15:10', 'HH24:MI'))*24*60*60, 1) 초로시간차이계산
  FROM dual;

---

-- 결과 : 연도
select to_char(sysdate, 'year') from dual;

-- 결과 : 화요일
select to_char(sysdate, 'day') from dual;

-- 결과 : 화
select to_char(sysdate, 'dy') from dual;

-- 결과 : 3 (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토)
select to_char(sysdate, 'd') from dual;

-- 오늘날짜 20230526 기준 절삭시
SELECT TRUNC(SYSDATE, 'Y') FROM DUAL; -- 결과 2023/01/01 00:00:00
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; -- 결과 2023/03/01 00:00:00
SELECT TRUNC(SYSDATE,'DD') FROM DUAL; -- 결과 2023/03/22 00:00:00

--nls_date_language=american : AM, PM
--nls_date_language=korean : 오전, 오후
SELECT TO_CHAR(SYSDATE, 'AM', 'nls_date_language=american') AS AMERICAN , TO_CHAR(SYSDATE, 'AM', 'nls_date_language=korean') AS KOREAN FROM dual;

---

SELECT to_char(to_date('20230322181045', 'YYYYMMDDHH24MISS'), 'YYMMDD HH:MI:SS AM') 오전오후 
  FROM DUAL;

---

SELECT TO_CHAR(SYSDATE, 'AD YYYY, CC"세기" ' )
  FROM DUAL;

---

SELECT ROUND(SYSDATE - TO_DATE('19890504', 'YYYYMMDD'), 0)
  FROM DUAL;

---

SELECT EXTRACT(YEAR FROM SYSDATE) AS "년도"
       , EXTRACT(MONTH FROM SYSDATE) AS "월"
       , EXTRACT(DAY FROM SYSDATE) AS "일"
  FROM DUAL;

---

SELECT  MEM_ID AS "회원ID"
        , MEM_NAME AS "회원명"
        , MEM_BIR AS "생일"
  FROM MEMBER
 WHERE EXTRACT(MONTH FROM MEM_BIR) = 3;

SELECT  MEM_ID AS "회원ID"
        , MEM_NAME AS "회원명"
        , MEM_BIR AS "생일"
  FROM MEMBER
 WHERE SUBSTR(MEM_BIR, 6, 2) = '03';

---

SELECT '[' || CAST('Hello' AS CHAR(30)) || ']'  AS "형변환"
  FROM DUAL;

---

SELECT CAST('1997/12/25' AS DATE) 
  FROM DUAL;

---

SELECT MEM_NAME 회원이름
       , MEM_BIR 회원생일 
       , MEM_NAME || '님은 ' || TO_CHAR(MEM_BIR,'YYYY"년" MM"월" DD"일 출생이고 태어난 요일은 "DAY')
  FROM MEMBER;

---

SELECT TO_CHAR( 1234.6, '99,999.00')
  FROM DUAL;

---

SELECT TO_CHAR( 100000000.6, 'L999,999,999.00')
  FROM DUAL;

---

SELECT TO_CHAR( -1234.6, 'L9999.00PR')
  FROM DUAL;

---

SELECT TO_CHAR(255, 'XXX') 
  FROM DUAL;

---

--  회원테이블에서 이쁜이회원의 회원Id 2~4 문자열을 숫자형으로 
--  치환한 후 10을 더하여 새로운 회원ID로 조합하시오 ?
--   (Alias는 회원ID, 조합회원ID)
--
--           회원ID      조합회원ID 
--         ------------------------------
--            b001         b011
--
--        (1 row(s) affected)

SELECT MEM_ID 회원ID
     , SUBSTR(MEM_ID,1,1) 조합회원ID
     , SUBSTR(MEM_ID,2) "기본형"
     , TRIM(TO_CHAR(SUBSTR(MEM_ID,2) + 10,'000')) "1번째 방법"
     , LPAD(SUBSTR(MEM_ID,2) + 10,3,'0') "2번째 방법"
     , SUBSTR((1000+TO_NUMBER(SUBSTR(MEM_ID,2))+10),2) "3번째 방법"
     , REPLACE(SUBSTR(MEM_ID,2) + 10,'11','011') "4번째 방법"
     , SUBSTR(MEM_ID,1,1) || TRIM(TO_CHAR(SUBSTR(MEM_ID,2) + 10,'000')) "조합회원ID+번호 1"
     , SUBSTR(MEM_ID,1,1) || LPAD(SUBSTR(MEM_ID,2) + 10,3,'0') "조합회원ID+번호 2"
     , SUBSTR(MEM_ID,1,1) || SUBSTR((1000+TO_NUMBER(SUBSTR(MEM_ID,2))+10),2) "조합회원ID+번호 3"
     , SUBSTR(MEM_ID,1,1) || REPLACE(SUBSTR(MEM_ID,2) + 10,'11','011') "조합회원ID+번호 4"
  FROM MEMBER
 WHERE MEM_NAME='이쁜이';

---

SELECT ROUND(AVG(DISTINCT PROD_COST),1) "중복값제외"
       , ROUND(AVG(ALL PROD_COST),1) "모든값포함(DEFALT)"
       , ROUND(AVG(PROD_COST),1) "매입가평균"
  FROM PROD;

---

SELECT COUNT(DISTINCT prod_cost), 
       COUNT(ALL prod_cost),
       COUNT(prod_cost), 
       COUNT(*)
  FROM prod;

---

SELECT MAX(DISTINCT PROD_COST)
     , MAX(PROD_COST)
     , MIN(DISTINCT PROD_COST)
     , MIN(PROD_COST)
  FROM PROD;

---

SELECT SUM(DISTINCT PROD_COST)
     , SUM(PROD_COST)
  FROM PROD;

---

SELECT SUM(PROD_SALE) AS "상품 판매가 총합계"
  FROM PROD;

---

SELECT PROD_LGU
     , SUM(PROD_SALE) AS "분류별 판매가 합계" 
  FROM PROD
 GROUP BY PROD_LGU;

---

SELECT BUY_PROD AS "상품"
     , SUM(BUY_QTY) AS "입고수량합계" 
  FROM BUYPROD
 GROUP BY BUY_PROD;