[Day5] Oracle 5 - 오라클 연산자 & 함수

[Day5] Oracle 5 [9/29]

---복습---

-- SCOTT 0929(목) --
--1. 오류 메시지에 대해서 설명하세요 .
-- ㄱ. ORA-01438: value larger than specified precision allowed for this column
       deptno NUMBER(2) 2자리 정수
              NUMBER(p[,s])
       INSERT         ( deptno )   VALUES  ( 100 );
       UPDATE
       SET   detpno = 100
-- ㄴ. ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
                 PK_DEPT 의  유일성 제약조건                   위배된다.
       INSERT         ( deptno )   VALUES  ( 40  );

-- ㄷ. ORA-00923: FROM keyword not found where expected
            SELECT ????,???
            FROM 절   앞, 뒤 구문 잘못되었다.

-- ㄹ. ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
                FK_DEPTNO 의    무결성   제약조건 위배된다.
                자식 레코드(행) 찾았다.
                        관계형 데이터 모델 + DBMS = RDBMS(오라클)
                        소속관계
        parent                         child
        DEPT                           EMP
        deptno(PK) 고유키               empno(PK)
                                       deptno(FK)
          10,20.30,40                      10(3), 20(3), 30(6)  40(X)

       DELETE dept
       WHERE deptno = 10;  삭제 불가능
       WHERE deptno = 40;  삭제 가능
--
--2. RR과  YY의 차이점에 대해서 설명하세요 .
--   2000년대 2022/ 21세기
--   YY  '2089.01.12'  '2011.04.21'
--   RR  '1989.01.12'  '2011.04.21'
         50~99  이전년대 1900년대
         0~49   현재년대  200년대

--3. JAVA :  5 % 2  코딩을 오라클로 나머지 구해서 출력하는 쿼리를 작성하세요.
--           연산자 X
            나머지 구하는 함수 2개
            SELECT MOD( 5,2 )                         ,   REMAINDER( 5,2 )
                      절삭 FLOOR()                           반올림 ROUND()
            FROM dual;

--4. dept 테이블을 사용해서 각 DML문의 형식을 적고, 쿼리 작성하고 실행하세요.
-- ㄱ. 새로운 부서를 추가(insert)하는 쿼리 작성 및 확인
--    1) (  50, 'QC', 'SEOUL' )
INSERT INTO 테이블명 [( 컬럼명... )] VALUES ( '컬럼값', '문자', ....);
--    2) (  60, T100%, SEOUL )
--    3) (  70, T100T, BUSAN )

-- ㄴ. 60번 부서의 부서명,  지역명을 수정
--     ( 원래 부서명 뒤에 'X' 문자열 추가해서 수정, 지역명은 DAEGU 로 수정 )

INSERT INTO dept VALUES ( 50, 'SALES', 'POHANG');
SELECT *
FROM dept;

UPDATE dept
SET dname = CONCAT( dname , 'X'), loc = 'DAEGU'                           ||
WHERE deptno = 60;

질문)
———————————————
UPDATE dept
SET dname = 'S%ALES'
WHERE dname = 'SALES';
———————————————
위 쿼리는 안되나요?

-- 사원번호, 부서번호(PK)으로 수정, 삭제
UPDATE  ??
SET   email = ???
WHERE ename = '홍길동'

*주의 : 업데이트 할 때 부서번호(프라이머리키)로 해줘야 다른 데이터 안건드리면서 수정가능하기 때문에 WHERE 절에 쿼리 적었던 것

-- ㄷ. LIKE 연산자를 사용해서 부서명에 '%' 문자가 포함된 부서 정보 조회하는 쿼리 작성
      WHERE dname LIKE '%\%%' ESCAPE  '\';

-- ㄹ. REGEXP_LIKE() 함수를 사용해서 부서명에 '%' 문자가 포함된 부서 정보 조회하는 쿼리 작성
    WHERE REGEXP_LIKE( dname ,  '%' );

-- ㅁ. 부서번호 50, 60, 70번 부서 삭제
      DELETE FROM dept
WHERE deptno IN ( 50, 60,  70 ); --  유지 보수

--  PL/SQL
--  for( int i=40 ; i <= 70; i+= 10){
--     DELETE FROM dept      WHERE deptno >= i;
--  }
--
--
--5. 한글 한 문자와 알파벳 한 문자가 몇 바이트인지 출력하는 쿼리 작성( X )
        VSIZE()
--
--6. insa 테이블에서 남자는 'X', 여자는 'O' 로 성별(gender) 출력하는 쿼리 작성
--
--    NAME SSN GENDER
--    --------- -------------- ------
--    홍길동 771212 -1022432 X
--    이순신 801007 -1544236    X
--    이순애 770922 -2312547    O
--    김정훈 790304 -1788896     X
--    한석봉 811112 -1566789      X

-- 좋은 코딩은 아니지만 이렇게도 가능 --

SELECT name, ssn
      , 'X' gender
FROM insa
WHERE MOD( SUBSTR( ssn, -7, 1), 2 ) = 1 
UNION  - SET 연산자
SELECT name, ssn 
      , 'O' gender
FROM insa
WHERE MOD( SUBSTR( ssn, -7, 1), 2 ) = 0;

-- 2번째 --

SELECT t.name, t.ssn
        , REPLACE(  REPLACE( t.gender , '1', 'X' ) , '0', 'O')  gender
--        if( t.gender == 1 )  'X'  프로그램 언어의 절차적인 문법( PL ) + SQL   PL/SQL
--        else                 'O'
FROM (
         -- 인라인 뷰( Inline View )
         SELECT name, ssn
               , MOD(SUBSTR( ssn, -7, 1 ),2) gender
         FROm insa
     ) t;

-- 3번째 --

SELECT name, ssn
        -- , MOD( SUBSTR( ssn, -7, 1), 2 ) gender
        , NVL2( NULLIF(  MOD( SUBSTR( ssn, -7, 1), 2 ), 1 ) , 'O', 'X') gender
     FROM insa;

--  NULL 처리 함수 ? NVL, NVL2, *** NULLIF() ***
     NULLIF( A, B)
     A=B   NULL
     A!=B   A

ex)

SELECT ename
        , NULLIF(  ename , 'SMITH' )   // 비교하는 값 (ename과 SMITH)이 같으면 NULL 다르면 ename
FROM emp;

정렬

    -- 왼쪽 정렬 : 문자
    -- 오른쪽 정렬: 숫자
    SELECT '1', 1
    FROM dual;

--7. insa 테이블에서 2000년 이후 입사자 정보 조회하는 쿼리 작성

--    NAME                 IBSADATE
--    -------------------- --------
--    이미성 00/04/07
--    심심해 00/05/05
--    권영미 00/06/04
--    유관순 00/07/07

-- 문자 --

 SELECT name, ibsadate(문자열임)
    -- , TO_CHAR(날짜,숫자,문자) -> 형식의 문자
    -- , TO_CHAR( ibsadate, 'YYYY') ib_year
  FROM insa
  WHERE TO_CHAR( ibsadate, 'YYYY') >= '2000';   // 문자열 비교
  ORDER BY ibsadate ASC;

-- 숫자로 비교한다면?--

SELECT name, ibsadate
     -- , EXTRACT( YEAR FROM ibsadate) ib_year
  FROM insa
  WHERE EXTRACT( YEAR FROM ibsadate) >= 2000;  
  ORDER BY ibsadate ASC;

--8-1. Oracle의 날짜를 나타내는 자료형 2가지를 적으세요.
--   ㄱ.  DATE
--   ㄴ.  TIMESTAMP

--8-2. 현재 시스템의 날짜/시간 정보를 출력하는 쿼리를 작성하세요.
-- SYSDATE 함수 : 날짜 + 시간 , '22/09/29'
SELECT SYSDATE           "(ㄱ)"  --현재 시스템의 날짜 + 시간(시간,분,초)
      , CURRENT_DATE      "(ㄴ)"  --현재 세션(session)의   날짜 + 시간 (세션 : 로그인하고 끊어질때까지)
      , CURRENT_TIMESTAMP "(ㄷ)"  --                 +                ms ns
FROM dual;
--
--9. HR 계정으로 접속해서
--   SELECT * FROM scott.dept;
--   위의 쿼리를 실행하면 ORA-00942: table or view does not exist 오류가 발생한다.  ( 권한 X )
1) SYS로 가서 GRANT SELECT ON emp TO HR; // scott emp 테이블 권한 부여 먼저하고

-- 그래서 HR 계정에서도 scott.dept 테이블을 SELECT할 수 있도록 권한부여 및 arirang 이란 이름으로 시노님( SYNONYM )을 생성해서

2) SYS에서 SYNONYM 생성

--   HR 계정에서도 SELECT * FROM arirang 쿼리를 사용할 수 있도록 설정하고 테스트하는 모든 쿼리를 순서대로 작성하세요.

dual 테이블 ?  public     시노님( synonym )
     스키마.객체명
     scott.emp           -> 모든 사용자 간단하게 사용할 수 있도록 별칭 (arirang)

--10. emp 테이블에서 사원명(ename)에 검색어( 'e' ) 문자 입력 를 포함한 사원을 검색해서 아래와 같이 출력.
--
--    ENAME   SEARCH_ENAME
--    --------------------
--    ALLEN   ALL[E]N
--    JONES   JON[E]S
--    BLAKE   BLAK[E]
--    TURNER   TURN[E]R
--    JAMES   JAM[E]S
--    MILLER   MILL[E]R

:바인드변수 (콜론 붙어있는 변수)
  ㄱ. bind variable
  ㄴ. 세션(session)이 유지되는 동안 사용할 수 있는 변수

- 원래 문제 -

SELECT ename
          , REPLACE( ename ,  'E', '[E]' ) SEARCH_ENAME
FROM emp
WHERE REGEXP_LIKE(ename ,  :searchWord , 'i');
WHERE ename LIKE '%E%';

- 입력받기 -
SELECT ename
          , REPLACE( ename ,  UPPER(:searchWord)  , '[' || UPPER(:searchWord)  || ']' ) SEARCH_ENAME
FROM emp
WHERE REGEXP_LIKE(ename ,  :searchWord , 'i');
WHERE ename LIKE '%E%';

--
--11.   UPDATE 문에 WHERE 조건절이 없는 경우는 어떻게 되나 ? 모든 레코드(행) 수정
--11-2. DELETE 문에 WHERE 조건절이 없는 경우는 어떻게 되나 ? 모든 레코드(행) 삭제
--11-3. INSERT 문에 대해서 설명하세요.
          DML문에 포함됨 -> 커밋, 롤백해야 반영된다!

--11-4. dept 테이블에   부서번호 : 50, 부서명: QC 로 새로운 부서를 저장하세요.
--
--12. DML( INSERT, UPDATE, DELETE ) 문 실행 후 반드시 무엇을 해 주어야 하나 ? 커밋, 롤백 (중요!!!!!!!!!!!!!!!!)
--
--13. SQL 집합 연산자의 종류와 설명을 하세요
--       UNION [ALL] , INTERSECT , MINUS
집합연산자 조건
           컬럼수 동일
           자료형 동일

--13-2. insa 테이블에서
--   ㄱ. 출신지역(city)가 인천인 사원의 정보(name,city,buseo)를 조회하고
--   ㄴ. 부서(buseo)가 개발부인 사원의 정보(name,city,buseo)를 조회해서
--   두 결과물의 합집합(UNION)을 출력하는 쿼리를 작성하세요.
--   ( 조건 : SET(집합) 연산자 사용 )

--14. insa 테이블에서  주민번호를 아래와 같이 '-' 문자를 제거해서 출력
--    [실행결과]
--    NAME SSN SSN_2
--    홍길동 770423-1022432 7704231022432
--    이순신 800423-1544236 8004231544236
--    이순애 770922-2312547 7709222312547

    SELECT  SUBSTR(ssn, 1, 6) || SUBSTR( ssn, -7 )
            , REPLACE( ssn, '-' , '')
            , REPLACE( ssn, '-' )
    FROM insa;

(복습끝)

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

오라클 연산자

오라클 함수

오라클 자료형

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

1. 연산자( Operator ) - Where 절에서 사용.

1) 비교 연산자 : 숫자, 문자, 날짜 비교

= != <> ^= > < >= <=
WHERE deptno = 10;
WHERE deptno != 10;
WHERE ename = 'SMITH';
WHERE sal >= 1000 AND sal <= 2000;
WHERE sal BETWEEN 1000 AND 2000;
WHERE hiredate >= '1981.01.1' AND hiredate <= '1981.12.31' -- 81년도 입사

2) 논리연산자 AND OR NOT

3) SQL 연산자

BETWEEN AND
IN
LIKE
IS NULL
ANY, ALL, SOME, EXISTS : WHERE 서브쿼리 X

4)*** SET(집합) 연산자 ***

UNION 합집합 -- 첫 쿼리와 두 쿼리에서 중복되지 않게 모든 행을 추출
UNION ALL 합집합 -- 첫 쿼리와 두 쿼리에서 중복을 허용한 모든 행을 추출
INTERSECT 교집합
MINUS 차집합

ex) UNION

20 7369 SMITH CLERK
20 7566 JONES MANAGER ***
20 7902 FORD ANALYST
+
20 7566 JONES MANAGER ***
30 7698 BLAKE MANAGER
10 7782 CLARK MANAGER
=
10 7782 CLARK MANAGER
20 7369 SMITH CLERK
20 7566 JONES MANAGER ***
20 7902 FORD ANALYST
30 7698 BLAKE MANAGER
SELECT deptno, empno, ename, job
FROM emp
WHERE deptno = 20

예) 컬럼 수도 1개로 동일하고 자료형 number 동일하기 때문에 UNION 가능하다.

-- ORA-01790: expression must have same datatype as corresponding expression

-- ORA-01789: query block has incorrect number of result columns

-- 쿼리 블럭이 잘못된 결과 열(컬럼) 수가 있다.

SELECT empno -- empno
FROM emp
UNION
SELECT deptno, dname --dname --deptno
FROM dept;

문제) insa테이블에서 출생지 '인천' 이고 부서 '개발부' 사원 조회.

SELECT name, city, buseo
FROM insa
WHERE city = '인천' AND buseo = '개발부';

-- ( 기억 ) 위 아래 같음

SELECT name, city, buseo
FROM insa
WHERE city = '인천'

INTERSECT

SELECT name, city, buseo
FROM insa
WHERE buseo = '개발부';

5) multi set 연산자 - 두 개의 nested table을 하나의 nested table로 combine 하는 것 X

6) 계층적 질의 연산자 : PRIOR, CONNECT_BY_ROOT 연산자.

7) 연결 연산자 : || 하나의 컬럼과 다른 컬럼,

8) 산술 연산자

SELECT 5 + 3
, 5 - 3
, 5 * 3
--, 5 / 3 -- 1.66666666666666666666666666666666666667
-- , 5 % 3 -- ORA-00911: invalid character -> 나머지는 MOD 로 해줘야함
--, MOD( 5, 3)
-- , 5 / 0 -- ORA-01476: divisor is equal to zero // 0으로 나눌 수 없음
--, MOD( 5, 0 ) -- 5
, 5 + 'A' -- ORA-01722: invalid number
FROM dual;
FROM emp; -- emp 행 갯수 만큼 8
FROM dept; -- dept 행 갯수 만큼 8

참고)

임시 결과값 확인하는 테이블

SELECT 5+3
FROM dual;

9) 사용자 정의 연산자 CREATE OPERATOR 연산자 생성할 수 있다.

10) 연산자 우선 순위

ex) 순서

where job='SALESMAN' AND sal >= 1500 OR job='PRESIDENT';

(1) 비교연산자 각각(=,>=,=)

(2) 논리연산자 각각 (AND, OR)

11) floating-point condition ( 부동 소수점 조건 ) - IS [NOT] NAN, IS [NOT] INFINITE

IS [NOT] NULL 널 체크

WHERE mgr IS NULL;

IS NAN; -- Not A Number

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

2. 오라클 함수

1. 함수 ?

복잡한 쿼리문 + 반복(자주) 사용 -> 간단하게 처리 목적으로 사용한 것.

자바 함수 ? 선 긋기 4번( 반복 ) X 50

반복적인 코딩 -> 함수(메서드) 선언 + 호출

3가지: 기능, 매개변수, 리턴값(자료형)

2. 함수의 기능

1) 계산 함수

2) 데이터 수정

3) 그룹의 결과 출력

4) 날짜-> 형식 문자열 변경 TO_CHAR(날짜, 'YYYY')

5) 컬럼 데이터 타입을 변경 ( 형변환 ) TO_NUMBER( 문자열, 날짜 )

3. 함수의 유형(종류)

1) 단일행 함수 : 각 행마다 처리 결과.

2) 복수행 함수 : 여러 행(그룹) -> 한 개의 결과 ( == 그룹함수 )

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

4. 숫자 함수

1). ROUND( number ) : 숫자값을 [특정 위치]에서 반올림하여 리턴한다
형식)
ROUND( n [ ,m ] )

   SELECT    3.141592
           , ROUND(  3.141592 )  a -- m 생략된 경우에는 소수점 첫 번째 자리 반올림.
           , ROUND(  3.141592,  0 ) b -- m =0 경우에는 소수점 첫 번째 자리 반올림.
           , ROUND(  3.141592,  2 ) c -- m+1 위치에서 반올림 
           , ROUND(  3.141592,  -1 ) d
           , ROUND( 12345  , -1 ) e  -- 일
           , ROUND( 12345  , -2 ) f  -- 십
           , ROUND( 12345  , -3 ) g  -- 백
   FROM dual;

> TRUNC 와 FLOOR 두 절삭함수의 차이점 ?

2) TRUNC( number )  -- 특정 위치에서 절삭
형식)
   TRUNC( n  [ ,m ] )

   SELECT    3.141592
           , TRUNC(  3.141592 )  a -- m 생략된 경우에는 소수점 첫 번째 자리 절삭
           , TRUNC(  3.141592,  0 ) b -- m =0 경우에는 소수점 첫 번째 자리 절삭.
           , TRUNC(  3.141592,  2 ) c -- m+1 위치에서 절삭 
           , TRUNC(  3.141592,  -1 ) d
            , TRUNC( 12345  , -1 ) e  -- 일
            , TRUNC( 12345  , -2 ) f  -- 십
            , TRUNC( 12345  , -3 ) g  -- 백
   FROM dual;

3) CEIL : 특정위치X , 소숫점 첫 번째 자리에서만 올림 ( 정수를 리턴 )

SELECT CEIL( 3.141592 ), FLOOR( 3.59 )
FROM dual;

4) FLOOR : 특정위치X , 소숫점 첫 번째 자리에서만 절삭

5) MOD
6) ABS -- Math.abs()

SELECT 100,   ABS( 100 ), ABS( -100 )
FROM dual;

7) SIGN()  숫자값의 부호에 따라 1 , 0 , -1 의 값을 리턴한다.
    형식)    SIGN( n )
           n 이 양수이면    1
                음수이면   -1
                0이면      0

   SELECT SIGN( 100), SIGN( -100 ), SIGN( 0 )             
   FROM dual;

8) POWER() 2^3
9) SQRT() 제곱근 f

 SELECT POWER(2,3) , POWER( 2, -3)
        , SQRT( 4) , SQRT(2)
  FROM dual;

10) SIN(), COS(), TAN(), LOG(a,b), LN() 자연로그 등등

  [문제]
  총게시글수 : 235 / 한페이지   : 10
  총페이지수 ? 23페이지 + 1페이지( 5게시글 ) = 24페이지

   SELECT  CEIL( 235/10 )   "총페이지수"   -- 23.5 페이지
   FROM dual;

-> 나머지가 몇이든 무조건 올려줘야함! (게시글 하나라도 남으면 페이지 +1)


   [문제] emp 테이블에서 각 사원들의 sal 가 평균sal 보다 많으면 "많다", 보다 적으면 "적다"라고 출력
     empno, ename, sal, avg_sal, 평가
                                  많다
                                  적다
   [풀이]

 SELECT t.*
        ,  '평균보다 SAL가 ' || REPLACE(  REPLACE( SIGN( t.sal - t.avg_sal ), -1, '적다' ) , 1 , '많다' )  평가        -- 0  'O'         1  'X'  NULLIF  
   FROM (
               SELECT empno, ename, sal
                   -- , AVG( sal )
                  , (  SELECT  ROUND(  AVG(sal) , 2 )    FROM emp )  avg_sal 
               FROM emp
        ) t ;

--
아래로도 가능!

 SELECT empno, ename, sal , '평균보다 SAL가 많다'
   FROM emp
   WHERE sal > (  SELECT  ROUND(  AVG(sal) , 2 )    FROM emp ) 
   UNION
   SELECT empno, ename, sal , '평균보다 SAL가 적다'
   FROM emp
   WHERE sal < (  SELECT  ROUND(  AVG(sal) , 2 )    FROM emp );
   UNION
   SELECT empno, ename, sal , '평균보다 SAL가 같다'
   FROM emp
   WHERE sal = (  SELECT  ROUND(  AVG(sal) , 2 )    FROM emp );

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

5. 문자 함수

[ 문자 함수 ]
1) UPPER(), LOWER(), INITCAP()

SELECT ename
         , LOWER( ename )
         , INITCAP( ename )  // 첫번째만 대문자로
         , UPPER( ename )
FROM emp;

2) LENGTH() : 문자열의 길이를 반환하는 함수

SELECT DISTINCT job , LENGTH( job )
FROM emp;

--  String name ="Abc";
--  name.length()

3) CONCAT : ||랑 같은 역할

CONCAT( dname , 'X')

4) SUBSTR   == substring()랑 같은 역할
   SUBSTR( 문자열, 시작위치, 길이 )

   SELECT name, ssn
       , SUBSTR( ssn, 0, 6 )
       , SUBSTR( ssn, -7 )
       , SUBSTR( ssn, -7, 1 )  성별
       , SUBSTR( ssn, 0, 2 )  년도
       , SUBSTR( ssn, 3, 2 )  월
       , SUBSTR( ssn, 5, 2 )  일
   FROM insa;

5) INSTR - 문자열 중 지정된 문자값의 위치를 반환하는 함수
   -- JAVA:  indexOf(), lastIndexOf() -1
    형식)
   INSTR ( string, substring [, position [,occurrence] ] )
   0 찾는문자열이 없다.
   찾은 위치 가    1부터 시작한다.

ex) 부서명에   T 문자열이 있는 지 체크 + 인덱스반환, 없으면 0

  SELECT dname
        , INSTR(  dname , 'T' )
  FROM dept;

두 번째 C의 위치를 찾고 싶다면...

  SELECT dname
        , INSTR(  dname , 'O' , 2 )
  FROM dept;

  SELECT  'corporate floor'
        , INSTR(  'corporate floor', 'or'  )  -- 첫 번째 or  2
        , INSTR(  'corporate floor', 'or' , 3  ) -- 3번째 위치에서 or 찾는다   5
        , INSTR(  'corporate floor', 'or' , 3 , 2  ) -- 14
                                          -- lastIndexOf()    
        , INSTR(  'corporate floor', 'or' , -3 , 2  ) -- 2
  FROM dual;

-- 전화번호 테이블 생성 : TBL_TEL

 --      번호 컬럼
 CREATE TABLE
 ALTER TABLE
 DROP TABLE
 -- 테이블 생성 확인
 SELECT *
 FROM tabs;
 
INSERT INTO "SCOTT"."TBL_TEL" (NO, TEL) VALUES ('1', '02)123-4567');
INSERT INTO "SCOTT"."TBL_TEL" (NO, TEL) VALUES ('2', '054)7899-5645');
INSERT INTO "SCOTT"."TBL_TEL" (NO, TEL) VALUES ('3', '031)9874-2932');
COMMIT;

SELECT *
FROM tbl_tel;

[문제]  tbl_tel 테이블에서 전화번호
지역번호
전번 앞자리
전번 뒤자리
출력.

SELECT no, tel
     , INSTR( tel, ')')  ")위치"
     , INSTR( tel, '-')  "-위치"
     , SUBSTR( tel, 1, INSTR( tel, ')')-1 ) "지역번호"
     , SUBSTR( tel, INSTR( tel, ')') + 1 ,  INSTR( tel, '-') - INSTR( tel, ')') -1 ) "앞자리"
     , SUBSTR( tel, INSTR( tel, '-') + 1  ) "뒷자리"
FROM tbl_tel;

[문제]
 1)
   UPDATE tbl_tel
   SET tel =   REPLACE( tel, ')', '-' );
   COMMIT;
 2)
 SELECT *
 FROM tbl_tel;
 
  3) 지역번호,   앞자리, 뒷자리
  -- ORA-00904: "T"."F_BAR": invalid identifier
  SELECT t.no, t.tel 
     --, t.f_bar
      ,  SUBSTR( t.tel, 1, t.f_bar-1)  "지역번호"
      ,  SUBSTR( t.tel, t.f_bar+1, t."s_bar"- t.f_bar-1)  "앞자리"
      ,  SUBSTR( t.tel, t."s_bar"+1 )  "뒤자리"
  FROM (
             SELECT no, tel
                 , INSTR( tel, '-')  f_bar
                 , INSTR( tel, '-', -1)  "s_bar"
             FROM tbl_tel
       ) t ;

6) RPAD / LPAD
    지정된 길이에서 문자값을 채우고 남은 공간(우/좌)을 특정값으로 채우는 함수

    【형식】
      RPAD (expr1, n [, expr2] )

예)

   SELECT ename, sal + NVL( comm, 0) pay
            , LPAD(  '\' || ( sal + NVL( comm, 0)),  10 , '*' ) -- Left 왼쪽 남는 곳을 특정값으로 채우겠다.
            , RPAD(  '\' || ( sal + NVL( comm, 0)),  10 , '*' )
   FROM emp;

예) HR

   select last_name, RPAD(' ',salary/1000, '*') "Salary"
   from employees
   where department_id = 80
   order by last_name, "Salary";

100 기준으로 별한개 출력 그래프

   SELECT ename, sal
          , ROUND( sal/ 100 ) "#갯수"
          , RPAD( ' ', ROUND( sal/ 100 ) + 1, '#'  )
   FROM emp;

7) ASCII( char ) 지정한 숫자 또는 문자의 ASCII 코드값을 리턴하는 함수

 SELECT ASCII( 'A' ) -- 65
  , ASCII( 'a' ) -- 65
  , ASCII( 0 ) -- '0' ->  48
 FROM dual;

[문제] ename 의 두 번째 문자의 ASCII 을 출력하는 쿼리 작성하자.

  SELECT ename
    , SUBSTR( ename, 2, 1 )
    , ASCII(SUBSTR( ename, 2, 1 ))
  FROM emp;

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

6. 날짜 함수의 종류

[ 날짜 함수 ]

1) ROUND( date )  ***  정오를 기준으로 날짜를 반올림하여 리턴한다.
  형식)
   ROUND( date [, fmt ] )

   SELECT 
          SYSDATE
        , ROUND( SYSDATE ) -- 정오 X
        , ROUND( SYSDATE, 'DD' )  --  정오 X (위에랑 같음)
        -- X 요일 , ROUND( SYSDATE, 'DAY' )  -- (요일이기때문에 X)
        , ROUND( SYSDATE, 'MONTH' ) --   [15일]  29일 -> 10/1
        , ROUND( SYSDATE, 'YEAR' )  --   [6월]   9월  -> 23/01/01
   FROM dual;

2) TRUNC( date ) *** 시간.분.초,MS 00:00:00.000 생일지났니? = 시간 필요없이 날짜만 사용할때
SELECT CURRENT_TIMESTAMP
, TRUNC( CURRENT_TIMESTAMP )
FROM dual;

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

7. datetime 종류

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

8. 형 변환 함수 종류

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

9. 일반 함수 & 10. 정규 표현식 함수

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

11. 그룹 함수 == 복수형 함수

1) COUNT 함수 : 행(레코드)의 갯수를 리턴한다.
   COUNT(*)  : NULL 값을 포함한 갯수를 리턴한다.
  형식)
  COUNT( [*|DISTINCT|ALL] 컬럼명)  [ OVER 절 ]

  SELECT COUNT( empno ), COUNT( deptno ) , COUNT( sal ) , COUNT( comm )  -- NULL 포함X 갯수
   , COUNT( * )
  FROM emp;
  
  SELECT COUNT(*) "10번 사원수"
  FROM emp
  WHERE deptno = 10;

2) SUM() : NULL을 제외한 합을 리턴한다.
형식) SUM( [DISTINCT|ALL] expr ) [OVER 절]

emp 테이블에서 총 sal 합

    SELECT SUM( sal )  -- 24925
    FROM emp;

    문제] emp 테이블에서  사원의 기본급(sal)의 평균
    SELECT COUNT(*) FROM emp;  -- 12
    -- ORA-00937: not a single-group group function   팀프로젝트 에러 질문 *****
    -- 복수행 함수
    -- 단일행 함수  Single-row 함수
    -- SELECT SUM(sal) /  (  SELECT COUNT(*) FROM emp  )// 안됨! 그냥 바로 COUNT(*)라고 하면됨

  [에러 원인]
  -- ORA-00937: not a single-group group function   팀프로젝트 에러 질문 *****
  SELECT SUM(sal)    /  (  SELECT COUNT(*) FROM emp  )
  FROM emp;

2850
2450         복수행함수(그룹함수) SUM 2XXXX
5000
1500

-- ORA-00937: not a single-group group function *******
-- 그룹함수는 일반컬럼하고 사용  X
  SELECT SUM(sal) , deptno // 같이 못씀!
  FROM emp

[문제] emp 테이블  총 사원수 출력

SELECT COUNT(*), SUM( sal  + NVL( comm, 0 )  )
FROM emp;

[문제] emp 테이블에서 각 부서별 사원수 출력

SELECT *
FROM dept;
SELECT '10'  , COUNT( * ) 
FROM emp
WHERE deptno = 10
UNION ALL
SELECT '20'  ,COUNT( * ) 
FROM emp
WHERE deptno = 20
UNION ALL
SELECT '30'  , COUNT( * ) 
FROM emp
WHERE deptno = 30
UNION ALL
SELECT '40'  ,COUNT( * ) 
FROM emp
WHERE deptno = 40
UNION ALL
SELECT ''  ,COUNT( * ) 
FROM emp ;

-- GROUP BY 절 + 총합, 몇명, 평균, 집계함수
-- 부서별 사원수 조회

SELECT deptno,  COUNT(*)
FROM emp
GROUP BY deptno
ORDER BY deptno ASC;

3) AVG() NULL값을 제외한 행의 평균을 리턴
【형식】
AVG( [DISTINCT ¦ ALL] 컬럼명)
[ [OVER] (analytic 절)]

 SELECT SUM( sal ) , COUNT(*)
      , SUM( sal ) / COUNT(*) avg  -- 평균
      , AVG( sal ) 
 FROM emp;

다른 이유 : 평균값을 낼 때 sal 이 null일 경우 점수를 제외하고 SUM이 되기 때문에 COUNT에서 n명을 빼줘야함

[ 문제 ] 커미션의 평균 계산 *****
-- 왜 ? NULL 제외한 평균,합,분산,표준편차 등등

 SELECT SUM( comm )
      , ROUND(  SUM( comm ) / COUNT(*) , 2 )  -- 소수점 3번째 자리 반올림
      , SUM( comm ) / COUNT( comm )
      , AVG( comm ) comm_avg
 --SELECT comm      
 FROM emp;

[ 문제 ] emp 테이블에서 기본급이 가장 많이/적게 받는 금액?
【형식】
MAX ([{DISTINCT ¦ ALL}] expr)
[OVER(analytic_clause)]

 SELECT MAX(sal) , MIN(sal)
 FROM emp;

SELECT STDDEV( sal )   -- 표준편차 ?
    , VARIANCE( sal )  -- 분산  ?
FROM emp;

[문제] emp 테이블에서 급여(sal)를 가장 많이 받는 사원의 사원번호,사원명, 부서번호, sal 조회
-- 서브쿼리는 괄호 사용.

SELECT empno, ename, deptno, sal
FROM emp
WHERE sal = ( SELECT MAX(sal) FROM emp );

-- ORA-00934: group function is not allowed here
--SELECT empno, ename, deptno, sal
--FROM emp
--WHERE sal = MAX(sal); // 안됨~!

[문제] MAX(Sal), Min(sal)
7839 KING 10 5000
7369 SMITH 20 800

SELECT empno, ename, deptno, sal
FROM emp
WHERE sal IN ( (SELECT MIN(sal) FROM emp), (SELECT MAX(sal) FROM emp) );
WHERE sal = ( SELECT MIN(sal) FROM emp ) OR sal = ( SELECT MAX(sal) FROM emp );

아래도 가능

SELECT '최저', empno, ename, deptno,   sal
FROM emp
WHERE sal = ( SELECT MIN(sal) FROM emp ) 
UNION ALL
SELECT '최고', empno, ename, deptno, sal
FROM emp
WHERE sal = ( SELECT MAX(sal) FROM emp );

저작자표시

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

[Day7] Oracle 7 - JOIN, TON-N (ROW_NUMBER), CASE (0)	2022.10.06
[Day6] Oracle 6 - ALL, correlated subquery, 문자함수, 날짜함수, COALESCE, DECODE (0)	2022.10.05
[Day4] Oracle 4 - Subquery, CONCAT, MOD, INSERT, REPLACE, NLS, Dual, SYSDATE, RR/YY, Session, Synonym (0)	2022.09.28
[Day3] Oracle 3 - 함수, LIKE, BETWEEN, RR/YY, UPDATE (0)	2022.09.28
[Day2] Oracle 2 - SELECT, DCL(Privilege), DML, Role (0)	2022.09.27

[Day5] Oracle 5 - 오라클 연산자 & 함수

[Day5] Oracle 5 [9/29]

1. 연산자( Operator ) - Where 절에서 사용.

2. 오라클 함수

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day5] Oracle 5 [9/29]

1. 연산자( Operator ) - Where 절에서 사용.

2. 오라클 함수

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바