2/DB

학교에서 했던 Oracle 기본 문제

poni3 2017. 7. 15. 22:43

 

 

새로운 계정 만들기
1단계) DBA 접속

ID  : system
PWD : manager


ID  : sys
PWD : sys

2단계 ) 개인계정
1) 다른 사용자 - 사용자생성


기본 테이블스페이스  users
임시 테이블스페이스 temp

2) 부여된 롤에서
    connect
    resource 선택
3)시스템 권한
  create any view
  drop any view 선택
4) 적용
(여러문장일경우 다 선택해서 해야함)

 

5 select * (all)  distinct 열이름, 집단함수
                  sum(열이름),
                  avg(열이름),
                  max(열이름),
                  min(열이름),
                  count(열이름)
1 from  테이블
2 where 레코드에 제한을 가할 때
        열이름  연산자  비교값,열이름....부속질의(subquery)
        열이름 in (값,,,,,,)
        열이름 between 작은값 and 큰값
        열이름 like '%_'
                     % 0 ~ 무한대 ==> *
                     _ 1바이트    ==> ?
 열이름 is null
 열이름 is not null
3 group by ~별로 집계낼때
     대상 열이름

4 having ~별로 제한을 가할때
   집단함수 연산자 비교값,,,,
6 order by 정렬할때
    열이름   asc
    열이름   desc


relation
1 attribute 원자성 (atomic value) => 1NF
2 attribute 무순서성(물리적 독립성)
3 tupple 유일성 => 기본키(primary key)
                    -> 개체무결성
4 tupple 무순서성

 

 

테이블 생성 및 데이터

 

CREATE TABLE DEPT

       (DEPTNO NUMBER(2),

        DNAME VARCHAR2(14),

        LOC VARCHAR2(13),

        constraint dept_pk primary key (deptno));

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

 

CREATE TABLE BONUS

        (ENAME VARCHAR2(10),

         JOB   VARCHAR2(9),

         SAL   NUMBER,

         COMM  NUMBER);

 

CREATE TABLE SALGRADE

        (GRADE NUMBER,

         LOSAL NUMBER,

         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

 

CREATE TABLE EMP

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2),

        constraint emp_pk primary key (empno),

        constraint emp_fk foreign key (deptno)

                    references dept(deptno));

INSERT INTO EMP VALUES

        (7369, 'SMITH',  'CLERK',     7902,

        TO_DATE('17-12월-1980', 'DD-MON-YYYY'),  800, NULL, 20);

INSERT INTO EMP VALUES

        (7499, 'ALLEN',  'SALESMAN',  7698,

        TO_DATE('20-2월-1981', 'DD-MON-YYYY'), 1600,  300, 30);

INSERT INTO EMP VALUES

        (7521, 'WARD',   'SALESMAN',  7698,

        TO_DATE('22-2월-1981', 'DD-MON-YYYY'), 1250,  500, 30);

INSERT INTO EMP VALUES

        (7566, 'JONES',  'MANAGER',   7839,

        TO_DATE('2-4월-1981', 'DD-MON-YYYY'),  2975, NULL, 20);

INSERT INTO EMP VALUES

        (7654, 'MARTIN', 'SALESMAN',  7698,

        TO_DATE('28-9월-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES

        (7698, 'BLAKE',  'MANAGER',   7839,

        TO_DATE('1-5월-1981', 'DD-MON-YYYY'),  2850, NULL, 30);

INSERT INTO EMP VALUES

        (7782, 'CLARK',  'MANAGER',   7839,

        TO_DATE('9-6월-1981', 'DD-MON-YYYY'),  2450, NULL, 10);

INSERT INTO EMP VALUES

        (7788, 'SCOTT',  'ANALYST',   7566,

        TO_DATE('09-4월-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES

        (7839, 'KING',   'PRESIDENT', NULL,

        TO_DATE('17-11월-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES

        (7844, 'TURNER', 'SALESMAN',  7698,

        TO_DATE('8-9월-1981', 'DD-MON-YYYY'),  1500,    0, 30);

INSERT INTO EMP VALUES

        (7876, 'ADAMS',  'CLERK',     7788,

        TO_DATE('12-5월-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES

        (7900, 'JAMES',  'CLERK',     7698,

        TO_DATE('3-12월-1981', 'DD-MON-YYYY'),   950, NULL, 30);

INSERT INTO EMP VALUES

        (7902, 'FORD',   'ANALYST',   7566,

        TO_DATE('3-12월-1981', 'DD-MON-YYYY'),  3000, NULL, 20);

INSERT INTO EMP VALUES

        (7934, 'MILLER', 'CLERK',     7782,

        TO_DATE('23-1월-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

 

COMMIT;

 

 

 


1. 사원 테이블의 모든 것을 보여라.

select *
from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 82/04/09       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 83/05/12       1100                    20
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

 

2. 직무를 보이시오.

select job
from emp;

JOB    
---------
CLERK    
SALESMAN 
SALESMAN 
MANAGER  
SALESMAN 
MANAGER  
MANAGER  
ANALYST  
PRESIDENT
SALESMAN 
CLERK    
CLERK    
ANALYST  
CLERK    

select distinct job                        //distinct 중복제거
from emp;

JOB    
---------
CLERK    
SALESMAN 
PRESIDENT
MANAGER  
ANALYST

3. 사번, 사원명(열머리글을 사 원 명), 급여, 급여 * 12(열별칭 tot)

select empno, ename "사 원 명", sal, sal*12 tot
from emp;

     EMPNO 사 원 명             SAL        TOT
   ---------- ---------- ---------- ----------
         7369 SMITH             800       9600
         7499 ALLEN            1600      19200
         7521 WARD             1250      15000
         7566 JONES            2975      35700
         7654 MARTIN           1250      15000
         7698 BLAKE            2850      34200
         7782 CLARK            2450      29400
         7788 SCOTT            3000      36000
         7839 KING             5000      60000
         7844 TURNER           1500      18000
         7876 ADAMS            1100      13200
         7900 JAMES             950      11400
         7902 FORD             3000      36000
         7934 MILLER           1300      15600

4. 사원수, 보너스가 지정된 사원수, 급여평균, 보너스 평균을 보이시오.

select count(*), count(comm), avg(sal), avg(nvl(comm,0))           
from emp;

  COUNT(*) COUNT(COMM)   AVG(SAL) AVG(NVL(COMM,0))
---------- ----------- ---------- ----------------
        14           4 2073.21429       157.142857


// count(comm)널값이 아닌 값만 수를 셈

5. select empno, ename                   // #을 뒤에 붙이면 열별칭 가능
   from emp;


     EMPNO ENAME   
---------- ----------
      7369 SMITH     
      7499 ALLEN     
      7521 WARD      
      7566 JONES     
      7654 MARTIN    
      7698 BLAKE     
      7782 CLARK     
      7788 SCOTT     
      7839 KING      
      7844 TURNER    
      7876 ADAMS     
      7900 JAMES     
      7902 FORD      
      7934 MILLER

select empno emp#, ename
   from emp;


     EMP# ENAME   
---------- ----------
      7369 SMITH     
      7499 ALLEN     
      7521 WARD      
      7566 JONES     
      7654 MARTIN    
      7698 BLAKE     
      7782 CLARK     
      7788 SCOTT     
      7839 KING      
      7844 TURNER    
      7876 ADAMS     
      7900 JAMES     
      7902 FORD      
      7934 MILLER  

select empno "#emp", ename
from emp; 


      #emp ENAME   
---------- ----------
      7369 SMITH     
      7499 ALLEN     
      7521 WARD      
      7566 JONES     
      7654 MARTIN    
      7698 BLAKE     
      7782 CLARK     
      7788 SCOTT     
      7839 KING      
      7844 TURNER    
      7876 ADAMS     
      7900 JAMES     
      7902 FORD      
      7934 MILLER  

6. select empno, ename, empno || ename, ename || '님'         // || 스트링값을 연결해서 표시해줌
   from emp

     EMPNO ENAME      EMPNO||ENAME                                       ENAME||'님'
---------- ---------- -------------------------------------------------- -----------
      7369 SMITH      7369SMITH                                          SMITH님    
      7499 ALLEN      7499ALLEN                                          ALLEN님    
      7521 WARD       7521WARD                                           WARD님     
      7566 JONES      7566JONES                                          JONES님    
      7654 MARTIN     7654MARTIN                                         MARTIN님   
      7698 BLAKE      7698BLAKE                                          BLAKE님    
      7782 CLARK      7782CLARK                                          CLARK님    
      7788 SCOTT      7788SCOTT                                          SCOTT님    
      7839 KING       7839KING                                           KING님     
      7844 TURNER     7844TURNER                                         TURNER님   
      7876 ADAMS      7876ADAMS                                          ADAMS님    
      7900 JAMES      7900JAMES                                          JAMES님    
      7902 FORD       7902FORD                                           FORD님     
      7934 MILLER     7934MILLER                                         MILLER님


7. 급여가 1000 이상이면서 사원명에 s를 포함하는 사원에 대해 사번, 사원명(님을 붙이시오), 급여를 보이시오.

select empno, ename || '님' "사원명", sal
from emp
where sal >= 1000 and lower(ename) like '%s%'

     EMPNO 사원명                SAL
---------- ----------- ----------
      7566 JONES님           2975
      7788 SCOTT님           3000
      7876 ADAMS님           1100

8. 급여가 1000 이하이거나 2000 이상이면서 부서번호가 10 또는 30 인 사원에 대해 사원수, 급여최대값, 급여최소값을 보이시오.

select count (*) 사원수, max(sal) 급여최대값, min(sal) 급여최소값
from emp
where sal not between 1000 and 2000 and deptno in (10,30)


       사원수      급여최대값      급여최소값
---------- ---------- ----------
         4       5000        950

9. 입사일자가 1981년 12월 3일에 입사한 사원에 대해 사번, 사원명, 입사일자를 보이시오.

select sysdate
from dual;

SYSDATE
--------
15/03/04

select emno, ename, hiredate
from emp
where hiredate = '81/12/03'


     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7900 JAMES      81/12/03
      7902 FORD       81/12/03


10. 부서번호가 10 또는 30 이면서 입사일자가 81년 1월 1일 이후에 입사한 사원수, 급여합계를 보이시오.

select count(*), sum(sal)
from emp
where deptno in (10,30) and hiredate >= '81/01/01';


  COUNT(*)   SUM(SAL)
---------- ----------
         9      18150


11. 부서번호가 30에 근무하는 사원들에 대해 사번, 사원명, 입사일자, 시스템 날짜 - 입사일짜를 보이시오.


select empno, ename, hiredate, sysdate - hiredate
from emp
where deptno = 30


     EMPNO ENAME      HIREDATE SYSDATE-HIREDATE
---------- ---------- -------- ----------------
      7499 ALLEN      81/02/20       12437.3865
      7521 WARD       81/02/22       12435.3865
      7654 MARTIN     81/09/28       12217.3865
      7698 BLAKE      81/05/01       12367.3865
      7844 TURNER     81/09/08       12237.3865
      7900 JAMES      81/12/03       12151.3865

 6개의 행이 선택됨


select sysdate +100, sysdate - 100
from dual;

SYSDATE+100 SYSDATE-100
----------- -----------
15/06/19    14/12/01    

// 날짜보다 100일 후나 100일 전


12. 사원명에 s를 포함하고  보너스가 지정되지 않은 사원에 대해 사번, 사원명, 직무와 부서번호를 연결해서 보이시오.

select empno, ename, job || deptno  직무부서
from emp
where lower(ename) like '%s%' and comm is null


     EMPNO ENAME      직무부서                                  
---------- ---------- -------------------------------------------------
      7369 SMITH      CLERK20                                          
      7566 JONES      MANAGER20                                        
      7788 SCOTT      ANALYST20                                        
      7876 ADAMS      CLERK20                                          
      7900 JAMES      CLERK30    

select
from
where lower(ename) like '%$_test' escape '$';

13. 부서번호별로 사원수, 급여합계를 보이시오.

select deptno, count(*), sum(sal)
from emp
group by deptno

    DEPTNO   COUNT(*)   SUM(SAL)
---------- ---------- ----------
        30          6       9400
        20          5      10875
        10          3       8750

 

14. 부서번호, 직무별로 급여평균, 사원수를 보이시오.

select deptno, job, avg(sal), count(*)
from emp
group by deptno, job

    DEPTNO JOB         AVG(SAL)   COUNT(*)
---------- --------- ---------- ----------
        20 CLERK            950          2
        30 SALESMAN        1400          4
        20 MANAGER         2975          1
        30 CLERK            950          1
        10 PRESIDENT       5000          1
        30 MANAGER         2850          1
        10 CLERK           1300          1
        10 MANAGER         2450          1
        20 ANALYST         3000          2

 9개의 행이 선택됨

15. 관리자사번별로 사원수, 급여의 최대값을 사원수가 3명 이상인 관리자만 보이시오.

select mgr, count(*), max(sal)
from emp
group by mgr
having count(*) >= 3;

       MGR   COUNT(*)   MAX(SAL)
---------- ---------- ----------
      7839          3       2975
      7698          5       1600


16. 입사일자가 81년 5월 1일 이후에 입사한 사원들에 대해 부서번호별로 사원수, 급여평균을 급여평균이 2000 이상인 부서만 보이시오.

select deptno, count(*), avg(sal)
from emp
where hiredate >= '81/05/01'
group by deptno
having avg(sal) >= 2000


    DEPTNO   COUNT(*)   AVG(SAL)
---------- ---------- ----------
        20          3 2366.66667
        10          3 2916.66667

17. 사번, 사원명, 급여, 보너스, 급여 + 보너스 (열별칭 tot)를 tot가 큰값부터 보이시오.

select empno, ename, sal,comm, nvl(sal,0)+nvl(comm,0) tot
from emp
order by tot desc

     EMPNO ENAME             SAL       COMM        TOT
---------- ---------- ---------- ---------- ----------
      7839 KING             5000                  5000
      7902 FORD             3000                  3000
      7788 SCOTT            3000                  3000
      7566 JONES            2975                  2975
      7698 BLAKE            2850                  2850
      7654 MARTIN           1250       1400       2650
      7782 CLARK            2450                  2450
      7499 ALLEN            1600        300       1900
      7521 WARD             1250        500       1750
      7844 TURNER           1500          0       1500
      7934 MILLER           1300                  1300
      7876 ADAMS            1100                  1100
      7900 JAMES             950                   950
      7369 SMITH             800                   800

 14개의 행이 선택됨


18. 부서번호별로 사원수, 급여합계를 급여합계가 적은 것부터 보이시오.
                                     
select deptno, count(*), sum(sal) 급여합
from emp
group by deptno
order by 급여합 asc

    DEPTNO   COUNT(*)        급여합
---------- ---------- ----------
        10          3       8750
        30          6       9400
        20          5      10875


19. union (합집합)       // 두테이블의 도메인이 같아야 한다
create table yemp
as
   select empno, ename, deptno, sal, comm
   from   emp
   where  deptno in (10,20);

사원테이블에서 부서번호가 10인 사번, 사원명, 급여와 yemp에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 보이시오


select empno, ename, sal
from emp
where deptno = 10

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300

select empno, ename, sal
from emp
where deptno = 10
union
select empno, ename, sal
from yemp
where sal >= 1000

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7902 FORD             3000
      7934 MILLER           1300


20. 19번을 급여가 많은 것부터 보이시오.              //마지막 select절에만 order by를 사용!
select empno, ename, sal
from emp
where deptno = 10
union
select empno, ename, sal
from yemp
where sal >= 1000
order by sal desc

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7788 SCOTT            3000
      7902 FORD             3000
      7566 JONES            2975
      7782 CLARK            2450
      7934 MILLER           1300
      7876 ADAMS            1100


21. 19번에서 중복된 튜플도 모두 보이시오.                 //중복된 열도 다 보는법 union all

select empno, ename, sal
from emp
where deptno = 10
union all
select empno, ename, sal
from yemp
where sal >= 1000


     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300
      7566 JONES            2975
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7902 FORD             3000
      7934 MILLER           1300

 10개의 행이 선택됨


22. minus (차집합)
사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 제외한 사원만 보이시오.

select empno, ename, sal
from emp
where deptno = 10
minus
select empno, ename, sal
from yemp
where sal >= 1000

선택된 행 없음               //작은거에서 큰거빼서 없음

22-1 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 제외한 사원만 보이시오.


select empno, ename, sal
from yemp
where sal >= 1000
minus
select empno, ename, sal
from emp
where deptno = 10

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7788 SCOTT            3000
      7876 ADAMS            1100
      7902 FORD             3000

23. intersect (교집합)
사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에 동시에 존재하는 사원을 보이시오.

select empno, ename, sal
from emp
where deptno = 10
intersect
select empno, ename, sal
from yemp
where sal >= 1000

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300


**함수
**문자함수

24.아스키코드 65번인 문자출력
select chr(65)
from dual;

CHR(65)
-------
A      

25. 첫글자를 대문자로해서 표시
select initcap(ename)
from emp;

INITCAP(ENAME)
--------------
Smith         
Allen         
Ward          
Jones         
Martin        
Blake         
Clark         
Scott         
King          
Turner        
Adams         
James         
Ford          
Miller  

26. 이름뒤에 is a를 붙이기 // ||와 같은 원리
select concat(ename, ' is a ')
from emp;


CONCAT(ENAME,'ISA')
-------------------
SMITH is a         
ALLEN is a         
WARD is a          
JONES is a         
MARTIN is a        
BLAKE is a         
CLARK is a         
SCOTT is a         
KING is a          
TURNER is a        
ADAMS is a         
JAMES is a         
FORD is a          
MILLER is a 

select concat(concat(ename, ' is a '),job)
from emp;

CONCAT(CONCAT(ENAME,'ISA'),JOB)
-------------------------------
SMITH is a CLERK               
ALLEN is a SALESMAN            
WARD is a SALESMAN             
JONES is a MANAGER             
MARTIN is a SALESMAN           
BLAKE is a MANAGER             
CLARK is a MANAGER             
SCOTT is a ANALYST             
KING is a PRESIDENT            
TURNER is a SALESMAN           
ADAMS is a CLERK               
JAMES is a CLERK               
FORD is a ANALYST              
MILLER is a CLERK   


27.15칸으로 맞추구 왼쪽 나머지공간에 *로 채워라
select lpad('Page 1', 15, '*')
from dual;

LPAD('PAGE1',15,'*')
--------------------
*********Page 1 

28. 오른쪽 나머지공간을 *로 채움 15개.
select rpad('Page 1', 15, '*')
from dual;

RPAD('PAGE1',15,'*')
--------------------
Page 1*********    


29.왼쪽에서부터  xy중복된문자열 제거(공백제거할때 많이 사용)
select ltrim('xyxyxyLast Word', 'xy')
from dual;

LTRIM('XYXYXYLASTWORD','XY')
----------------------------
Last Word   

30.오른쪽에서부터 xy중복된 문자열 제거
select rtrim('turnxyxyx','xy')
from dual;


RTRIM('TURNXYXYX','XY')
-----------------------
turn        


31.양쪽다 제거할때 사용
select trim('xyxyxyturxyxyxy', 'xy')
from dual;

명령의 1 행에서 시작하는 중 오류 발생 -
select trim('xyxyxyturxyxyxy', 'xy')
from dual
오류 발생 명령행: 1 열: 30
오류 보고 -
SQL 오류: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*Action:


32.대치하기. J를 EL로 바꾸기

select replace('JACK and JUE', 'J', 'EL')
from dual;

REPLACE('JACKANDJUE','J','EL')
------------------------------
ELACK and ELUE   


select loc, replace(loc, ' ' , '')
from dept;


LOC           REPLACE(LOC,'','')
------------- ------------------
NEW YORK      NEWYORK           
DALLAS        DALLAS            
CHICAGO       CHICAGO           
BOSTON        BOSTON   

// 중간에 공백 제거!!

33.앞에서 두번째 두글자 ,-는 뒤에서 두번째 두글자  substr(열이름, 시작위치, 얻어오는 바이트수)
select ename, substr(ename, 2, 2)
from emp;

ENAME      SUBSTR(ENAME,2,2)
---------- -----------------
SMITH      MI               
ALLEN      LL               
WARD       AR               
JONES      ON               
MARTIN     AR               
BLAKE      LA               
CLARK      LA               
SCOTT      CO               
KING       IN               
TURNER     UR               
ADAMS      DA               
JAMES      AM               
FORD       OR               
MILLER     IL               

 14개의 행이 선택됨

select ename, substr(ename, -2, 2)
from emp;

ENAME      SUBSTR(ENAME,-2,2)
---------- ------------------
SMITH      TH                
ALLEN      EN                
WARD       RD                
JONES      ES                
MARTIN     IN                
BLAKE      KE                
CLARK      RK                
SCOTT      TT                
KING       NG                
TURNER     ER                
ADAMS      MS                
JAMES      ES                
FORD       RD                
MILLER     ER                

 14개의 행이 선택됨

34.instr(열이름, 찾는문자열, 시작위치, 몇번째) 위치값
select instr('CORPORATE FLOOR', 'OR', 3, 2)
from dual;                           //3번째 자리이후에 2번째 or문자가 14번째자리에 or이있다

INSTR('CORPORATEFLOOR','OR',3,2)
--------------------------------
                              14


35.  34번문제 반대의 개념
select instr('CORPORATE FLOOR', 'OR', -3, 2)
from dual;

INSTR('CORPORATEFLOOR','OR',-3,2)
---------------------------------
                                2


36.사원명의 첫바이트부터 'LL'의 위치가 3 이상인 사원들에 대해 사번, 사원명을 보이시오. instr

select empno, ename
from emp
where instr(upper(ename), 'LL', 1,1) >= 3


     EMPNO ENAME   
---------- ----------
      7934 MILLER    

 


37. 사번, 사원명, 사원명의 가운데 바이트를 보이시오.
length, /2, substr

                                                                                    //  length 문자열길이 구하는 함수
select empno, ename, substr(ename, (length(ename))/2+1,1)
from emp;

    EMPNO ENAME      SUBSTR(ENAME,(LENGTH(ENAME))/2+1,1)
---------- ---------- -----------------------------------
      7369 SMITH      I                                  
      7499 ALLEN      L                                  
      7521 WARD       R                                  
      7566 JONES      N                                  
      7654 MARTIN     T                                  
      7698 BLAKE      A                                  
      7782 CLARK      A                                  
      7788 SCOTT      O                                  
      7839 KING       N                                  
      7844 TURNER     N                                  
      7876 ADAMS      A                                  
      7900 JAMES      M                                  
      7902 FORD       R                                  
      7934 MILLER     L                                  

 14개의 행이 선택됨


38. 관리자사번에서 처음부터 2바이트별로 사원수, 급여합계를 보이시오.
substr

select substr(mgr,1,2), count(*), sum(sal)
from emp
group by mgr


SUBSTR(MGR,1,2)   COUNT(*)   SUM(SAL)
--------------- ---------- ----------
78                       3       8275
                         1       5000
77                       1       1300
76                       5       6550
79                       1        800
75                       2       6000
77                       1       1100

 7개의 행이 선택됨


select substr(mgr, 1,2), count(*), sum(sal)
from emp
group by substr(mgr,1,2);


SUBSTR(MGR,1,2)   COUNT(*)   SUM(SAL)
--------------- ---------- ----------
                         1       5000
75                       2       6000
78                       3       8275
76                       5       6550
77                       2       2400
79                       1        800

 6개의 행이 선택됨

 

 

39. 사번, 사원명, 급여, 보너스, 보너스가 null이면 2000으로 간주해서 급여 중 큰 값을 보이시오.
nvl, greatest


select empno, ename, greatest(sal), nvl(comm, 2000)
from emp

     EMPNO ENAME      GREATEST(SAL) NVL(COMM,2000)
---------- ---------- ------------- --------------
      7369 SMITH                800           2000
      7499 ALLEN               1600            300
      7521 WARD                1250            500
      7566 JONES               2975           2000
      7654 MARTIN              1250           1400
      7698 BLAKE               2850           2000
      7782 CLARK               2450           2000
      7788 SCOTT               3000           2000
      7839 KING                5000           2000
      7844 TURNER              1500              0
      7876 ADAMS               1100           2000
      7900 JAMES                950           2000
      7902 FORD                3000           2000
      7934 MILLER              1300           2000

 14개의 행이 선택됨

////////////////////////////////////////위에꺼 틀림


select empno, ename,sal,comm,greatest( nvl(comm, 2000),sal)
from emp

    EMPNO ENAME             SAL       COMM GREATEST(NVL(COMM,2000),SAL)
---------- ---------- ---------- ---------- ----------------------------
      7369 SMITH             800                                    2000
      7499 ALLEN            1600        300                         1600
      7521 WARD             1250        500                         1250
      7566 JONES            2975                                    2975
      7654 MARTIN           1250       1400                         1400
      7698 BLAKE            2850                                    2850
      7782 CLARK            2450                                    2450
      7788 SCOTT            3000                                    3000
      7839 KING             5000                                    5000
      7844 TURNER           1500          0                         1500
      7876 ADAMS            1100                                    2000
      7900 JAMES             950                                    2000
      7902 FORD             3000                                    3000
      7934 MILLER           1300                                    2000

 14개의 행이 선택됨

 

40. 직무별로 사원명의 최대값, 사원명의 최소값을 사원명 최대값에 대해 첫번째부터 2바이트가 'ss' 이상 인 직무만 보이시오.


select job, max(ename),min(ename)
from emp
group by job
having max(ename) >= instr(lower(ename), 'ss', 1,2) 

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


select job, max(ename), min(ename)
from emp
group by job
having lower(substr(max(ename),1,2)) >= 'ss'


JOB       MAX(ENAME) MIN(ENAME)
--------- ---------- ----------
SALESMAN  WARD       ALLEN    

 

41. length(열이름) 문자열의 길이
lengthb(열이름)

 

select length('가나다라'), lengthb('가나다라')
from dual;

LENGTH('가나다라') LENGTHB('가나다라')               
-------------- ---------------
             4              12

 


select length('1234'), lengthb('1234')
from dual;


LENGTH('1234') LENGTHB('1234')
-------------- ---------------
             4               4


42. greatest(열이름..................)  //열이름들중에 큰값

    least(열이름...........)            //열이름들중에 작은값

 

**********************************************************시스템 함수

43. user
    uid

select user, uid
from dual;


USER                                  UID
------------------------------ ----------
SYSTEM                                  5


// 접속한 이름과 접속햇을때 프로세서 아이디

 

**************************************************************숫자함수

 

44. abs(열이름)     //절대값
select abs(-15), abs(15)
from dual;

  ABS(-15)    ABS(15)
---------- ----------
        15         15

 

45. ceil(열이름)    //무조건 소수점, 올림

select ceil(15.7), ceil(15.3)
from dual;

CEIL(15.7) CEIL(15.3)
---------- ----------
        16         16


46. floor(열이름)    //소수점 버림
select flooar(15.7), floor(15.0), floor(15.2)
from dual;

FLOOR(15.7) FLOOR(15.0) FLOOR(15.2)
----------- ----------- -----------
         15          15          15

 


47. mod(열이름, 나눌값)                       // 나누고 나머지값

select mod(6,2), mod(5,2), mod(7,3)
from dual;

  MOD(6,2)   MOD(5,2)   MOD(7,3)
---------- ---------- ----------
         0          1          1

 


48. power(열이름, 몇승)    //제곱승 2의 3승

select power(2,3), power(3,2)
from dual;


POWER(2,3) POWER(3,2)
---------- ----------
         8          9

 

49. round(열이름, 반올림할 자릿수)               소수점을 기준으로 

select round(1234.5678, 0), round(1234.5678,2), round(1234.5678, -2)
from dual;


ROUND(1234.5678,0) ROUND(1234.5678,2) ROUND(1234.5678,-2)
------------------ ------------------ -------------------
              1235            1234.57                1200

 

50. trunc(열이름, 내림할 자릿수)                소수점을 기준으로

 

select trunc(1234.5678, 0), trunc(1234.5678,2), trunc(1234.5678, -2)
from dual;


TRUNC(1234.5678,0) TRUNC(1234.5678,2) TRUNC(1234.5678,-2)
------------------ ------------------ -------------------
              1234            1234.56                1200

 

 


51. 사번, 사원명, 급여, 급여*12를 십의 자리에서 반올림해서 보이시오.

 

select empno, ename, sal, round(sal*12, -2)
from emp


     EMPNO ENAME             SAL ROUND(SAL*12,-2)
---------- ---------- ---------- ----------------
      7369 SMITH             800             9600
      7499 ALLEN            1600            19200
      7521 WARD             1250            15000
      7566 JONES            2975            35700
      7654 MARTIN           1250            15000
      7698 BLAKE            2850            34200
      7782 CLARK            2450            29400
      7788 SCOTT            3000            36000
      7839 KING             5000            60000
      7844 TURNER           1500            18000
      7876 ADAMS            1100            13200
      7900 JAMES             950            11400
      7902 FORD             3000            36000
      7934 MILLER           1300            15600

 14개의 행이 선택됨

 

52. 급여에서 2000을 뺀 값의 절대값이 1000 이상인 사원들에 대해 사번, 사원명, 급여, 급여 -2000(열별칭 mi) 를 mi 의 내림차순으로 보이시오.

select empno, ename, sal, sal-2000 mi
from emp
where abs(sal-2000) >= 1000
order by mi asc


     EMPNO ENAME             SAL         MI
---------- ---------- ---------- ----------
      7839 KING             5000       3000
      7788 SCOTT            3000       1000
      7902 FORD             3000       1000
      7900 JAMES             950      -1050
      7369 SMITH             800      -1200

 


53. 사번의 짝,홀수별로 사원수, 급여평균을 보이시오.

select mod(empno,2), count(*), avg(sal)
from emp
group by mod(empno,2)

MOD(EMPNO,2)   COUNT(*)   AVG(SAL)
------------ ---------- ----------
           1          4     2162.5
           0         10     2037.5

 

비교 )

case  when 조건 then 결과값
 when 조건 then 결과값
 ...............
     else 결과값
end

select case when mod(empno,2)=0 then '짝수'
      else '홀수'
 end 구분,
  count(*), avg(sal), avg(nvl(sal,0))
from emp
group by mod(empno,2)


구분   COUNT(*)   AVG(SAL) AVG(NVL(SAL,0))
-- ---------- ---------- ---------------
홀수          4     2162.5          2162.5
짝수         10     2037.5          2037.5

 


54. 부서번호가 10 또는 30인 사원에 대해 사번의 두번째자리에서 1바이트별로 사원수, 급여합계, 급여평균(10자리에서 내림)을 보이시오.

select substr(empno, 2,1), count(*), sum(sal), trunc(avg(sal),-2)
from emp
where empno in (10,30)
group by substr(empno, 2,1)


SUBSTR(EMPNO,2,1)   COUNT(*)   SUM(SAL) TRUNC(AVG(SAL),-2)
----------------- ---------- ---------- ------------------
6                          2       4100               2000
8                          2       6500               3200
5                          1       1250               1200
7                          1       2450               2400
9                          2       2250               1100
4                          1       1600               1600

 6개의 행이 선택됨

 

 

55. 사원명의 길이가 5 이상이면서 사번을 5로 나눈 나머지가 2이상인 사원들에 대해 사번, 사원명, 급여, 급여 *5(백의자리에서 반올림)를 보이시오.


select empno, ename, sal, round((sal*5),-3)
from emp
where length(ename) >= 5 and mod(empno,5) >=2

 

     EMPNO ENAME             SAL ROUND((SAL*5),-3)
---------- ---------- ---------- -----------------
      7369 SMITH             800              4000
      7499 ALLEN            1600              8000
      7654 MARTIN           1250              6000
      7698 BLAKE            2850             14000
      7782 CLARK            2450             12000
      7788 SCOTT            3000             15000
      7844 TURNER           1500              8000
      7934 MILLER           1300              7000

 8개의 행이 선택됨


select empno, ename, sal, round((sal*5),-3) 반올림한거, (sal*5) 반올림안한거
from emp
where length(ename) >= 5 and mod(empno,5) >=2

 


     EMPNO ENAME             SAL      반올림한거     반올림안한거
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800       4000       4000
      7499 ALLEN            1600       8000       8000
      7654 MARTIN           1250       6000       6250
      7698 BLAKE            2850      14000      14250
      7782 CLARK            2450      12000      12250
      7788 SCOTT            3000      15000      15000
      7844 TURNER           1500       8000       7500
      7934 MILLER           1300       7000       6500

 8개의 행이 선택됨

 

********날짜 함수

56. sysdate

select sysdate
from dual;

날짜 - 숫자 ==> 날짜 !!!!!!!!!!!이전                1900년 이후로해서 숫자로 날짜를 가지고 잇음
날짜 + 숫자 ==> 날짜 !!!!!!!!!!!이후

날짜 - 날짜 ==> 숫자(일수)//소수점은 시간을 의미

 

57. 사번, 사원명, 입사일자, 입사일자 100일후, 일사일자 100일전, 현재 날짜-입사일자를 보이시오.

select empno, ename, hiredate, hiredate +100 일후 , hiredate - 100 일전, sysdate-hiredate
from emp


     EMPNO ENAME      HIREDATE 일후       일전       SYSDATE-HIREDATE
---------- ---------- -------- -------- -------- ----------------
      7369 SMITH      80/12/17 81/03/27 80/09/08        12516.483
      7499 ALLEN      81/02/20 81/05/31 80/11/12        12451.483
      7521 WARD       81/02/22 81/06/02 80/11/14        12449.483
      7566 JONES      81/04/02 81/07/11 80/12/23        12410.483
      7654 MARTIN     81/09/28 82/01/06 81/06/20        12231.483
      7698 BLAKE      81/05/01 81/08/09 81/01/21        12381.483
      7782 CLARK      81/06/09 81/09/17 81/03/01        12342.483
      7788 SCOTT      82/04/09 82/07/18 81/12/30        12038.483
      7839 KING       81/11/17 82/02/25 81/08/09        12181.483
      7844 TURNER     81/09/08 81/12/17 81/05/31        12251.483
      7876 ADAMS      83/05/12 83/08/20 83/02/01        11640.483
      7900 JAMES      81/12/03 82/03/13 81/08/25        12165.483
      7902 FORD       81/12/03 82/03/13 81/08/25        12165.483
      7934 MILLER     82/01/23 82/05/03 81/10/15        12114.483

 14개의 행이 선택됨

 

select empno, ename, hiredate, hiredate + 100 일후 , hiredate - 100 일전, sysdate-hiredate 입현, hiredate-sysdate
from emp

 

     EMPNO ENAME      HIREDATE 일후       일전               입현 HIREDATE-SYSDATE
---------- ---------- -------- -------- -------- ---------- ----------------
      7369 SMITH      80/12/17 81/03/27 80/09/08 12516.4839      -12516.4839
      7499 ALLEN      81/02/20 81/05/31 80/11/12 12451.4839      -12451.4839
      7521 WARD       81/02/22 81/06/02 80/11/14 12449.4839      -12449.4839
      7566 JONES      81/04/02 81/07/11 80/12/23 12410.4839      -12410.4839
      7654 MARTIN     81/09/28 82/01/06 81/06/20 12231.4839      -12231.4839
      7698 BLAKE      81/05/01 81/08/09 81/01/21 12381.4839      -12381.4839
      7782 CLARK      81/06/09 81/09/17 81/03/01 12342.4839      -12342.4839
      7788 SCOTT      82/04/09 82/07/18 81/12/30 12038.4839      -12038.4839
      7839 KING       81/11/17 82/02/25 81/08/09 12181.4839      -12181.4839
      7844 TURNER     81/09/08 81/12/17 81/05/31 12251.4839      -12251.4839
      7876 ADAMS      83/05/12 83/08/20 83/02/01 11640.4839      -11640.4839
      7900 JAMES      81/12/03 82/03/13 81/08/25 12165.4839      -12165.4839
      7902 FORD       81/12/03 82/03/13 81/08/25 12165.4839      -12165.4839
      7934 MILLER     82/01/23 82/05/03 81/10/15 12114.4839      -12114.4839

 14개의 행이 선택됨

 

58. systimestamp

 

select systimestaamp
from dual;


SYSTIMESTAMP                    
----------------------------------
15/03/25 11:37:40.847000000 +09:00

//////////////////현재 시간 자세히

 


59.add_months(열이름//날짜형, 더할 개월수)

  **열이름의 자료형은 date형이다

 

select add_months(sysdate,20)
from dual;


ADD_MONTHS(SYSDATE,20)
----------------------
16/11/25              

 

 

select add_months(sysdate,-20)
from dual;

 

ADD_MONTHS(SYSDATE,-20)
-----------------------
13/07/25               

 

//////////////////////////날짜형으로 나옴

 

60. last_day(열이름)


select last_day(sysdate)
from dual


LAST_DAY(SYSDATE)
-----------------
15/03/31       

 

61. 지금부터 일을하면 몇 일을 인가?

select last_day(sysdate) - sysdate
from dual

 

LAST_DAY(SYSDATE)-SYSDATE
-------------------------
                        6


62. next_day(열이름, 형식)
 형식 '월','화',,,,,'일'
 형식 1,2...............7    /// 1 일요일 2월요일 3 화요일 4수요일.......7토요일
select next_day(sysdate, '월'),next_day(sysdate, '토')
from dual;

NEXT_DAY(SYSDATE,'월') NEXT_DAY(SYSDATE,'토')
--------------------- ---------------------
15/03/30              15/03/28             

 

//////////////////다가올 요일을 구하기

 


63. months_between(열이름, 열이름)

select ename, hiredate, months_between(sysdate, hiredate), months_between(hiredate, sysdate)
from emp;


ENAME      HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) MONTHS_BETWEEN(HIREDATE,SYSDATE)
---------- -------- -------------------------------- --------------------------------
SMITH      80/12/17                       411.496172                      -411.496172
ALLEN      81/02/20                       409.399398                      -409.399398
WARD       81/02/22                       409.334882                      -409.334882
JONES      81/04/02                       407.980043                      -407.980043
MARTIN     81/09/28                       402.141333                      -402.141333
BLAKE      81/05/01                              407                             -407
CLARK      81/06/09                       405.754236                      -405.754236
SCOTT      82/04/09                       395.754236                      -395.754236
KING       81/11/17                       400.496172                      -400.496172
TURNER     81/09/08                       402.786495                      -402.786495
ADAMS      83/05/12                       382.657462                      -382.657462
JAMES      81/12/03                       399.947785                      -399.947785
FORD       81/12/03                       399.947785                      -399.947785
MILLER     82/01/23                       398.302624                      -398.302624

 14개의 행이 선택됨


//과거기준으로 계산해서 - 나옴


64. 현재기준으로 근무개월수(소수점이하 반올림)가 400이상인 사원들에 대해 사번, 사원명, 입사일자를 보이시오.

select empno, ename, hiredate
from emp
where round(months_between(sysdate, hiredate), 0) >= 400            //미래를 먼저 씀

 

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      80/12/17
      7499 ALLEN      81/02/20
      7521 WARD       81/02/22
      7566 JONES      81/04/02
      7654 MARTIN     81/09/28
      7698 BLAKE      81/05/01
      7782 CLARK      81/06/09
      7839 KING       81/11/17
      7844 TURNER     81/09/08
      7900 JAMES      81/12/03
      7902 FORD       81/12/03

 11개의 행이 선택됨

 

65. 현재기준으로 근무개월수별로(일의자리 버림) 사원수, 급여합계, 급여평균을 급여평균이 2200 이상인 사원들만 보이시오.


select trunc(months_between(sysdate, hiredate), -1) 근무개월수, count(*) 사원수, sum(sal) 급여합계, avg(nvl(sal,0)) 급여평균
from emp
group by trunc(months_between(sysdate, hiredate), -1)
having avg(nvl(sal,0)) >= 2200


     근무개월수        사원수       급여합계       급여평균
---------- ---------- ---------- ----------
       400          8      18875   2359.375


66. 현재기준으로 400 개월이전에 입사한 사원들에 대해 사번, 사원명, 입사일자를 입사일자가 최근인 사원부터 보이시오.

select empno, ename, hiredate
from emp
where months_between(sysdate, hiredate) >= 400
order by hiredate desc

 

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7839 KING       81/11/17
      7654 MARTIN     81/09/28
      7844 TURNER     81/09/08
      7782 CLARK      81/06/09
      7698 BLAKE      81/05/01
      7566 JONES      81/04/02
      7521 WARD       81/02/22
      7499 ALLEN      81/02/20
      7369 SMITH      80/12/17

 9개의 행이 선택됨


select empno, ename, hiredate
from emp
where add_months(sysdate, -400) >= hiredate
order by hiredate desc


     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7839 KING       81/11/17
      7654 MARTIN     81/09/28
      7844 TURNER     81/09/08
      7782 CLARK      81/06/09
      7698 BLAKE      81/05/01
      7566 JONES      81/04/02
      7521 WARD       81/02/22
      7499 ALLEN      81/02/20
      7369 SMITH      80/12/17

 9개의 행이 선택됨

 

*************변환함수


to_char(숫자 또는 날짜형 열이름, '형식')   문자형으로
to_date(문자형 또는 숫자형 열이름, '형식') 날짜형으로
to_number(문자형 열이름, '형식')           숫자형으로

 

////////////////////////////////////날짜형식 요소

mm 월두자리

 / 나 - 넣어서 구분가능

mon 영어타입으로는 월의3자리 문자. 한글타입은 월

month 월의 풀네임(january), 한글타입은 월

dd 날짜, 일수

d 요일을 숫자로 표현한것(ex 일요일 1 월요일 2 화요일 3 ............토요일 7)    **정렬하기위해 사용**

dy 요일이름을 앞자리만. 영어는 3글자

day 요일이름

yyyy 년도 4자리

yy 년도 마지막2자리

ww 년도의 몇주차

w 는 월의 몇주차

q 쿼터 분기별로 보기 1사분기 2사분기 3사분기 4사분기

ddd 올해의 몇일 되었나 일수.

hh 시 나타냄

hh12 12시간단위로 표현

hh24 24시간단위로 표현

mi 분

ss 초

am or pm 오전 오후 표기

/////////////////////////////////////////////////////////////////

 

67. select to_char(sysdate, 'mm/mon - month dd d dy day yyyy yy w ww q ddd'), to_char(sysdate,'hh hh12:hh24 mi ss am')
from dual;

 

TO_CHAR(SYSDATE,'MM/MON-MONTHDDDDYDAYYYYYYYWWWQDDD')             TO_CHAR(SYSDATE,'HHHH12:HH24MISSAM')
---------------------------------------------------------------- ------------------------------------
04/4월  - 4월  01 4 수 수요일 2015 15 1 13 2 091                  10 10:10 10 09 오전        

 

 


숫자형식

to_char('99999') 문자형식으로 자릿수 만듬 부족하면 ####으로 나옴

to_char(sal, '$99,999)  달러 붙여서 표현


to_char(sal/3, '9999.99') 소수점 표현

to_char(sal, 'L099,999') L원화 표시(지역화폐로 표시.일본은 엔화 유럽은 유로), 0은 의미없이 자릿수맞춰서 0으로 채워짐


68.
select empno, to_char(sal, '99999'), to_char(sal, '99,999'), to_char(sal, '$99,999), to_char(sal/3, '9999.99'), to_char(sal, 'L099,999')
from emp;

 

     EMPNO TO_CHAR(SAL,'99999') TO_CHAR(SAL,'99,999') TO_CHAR(SAL,'$99,999') TO_CHAR(SAL/3,'9999.99') TO_CHAR(SAL,'L099,999')
---------- -------------------- --------------------- ---------------------- ------------------------ -----------------------
      7369    800                   800                   $800                 266.67                         ₩000,800      
      7499   1600                 1,600                 $1,600                 533.33                         ₩001,600      
      7521   1250                 1,250                 $1,250                 416.67                         ₩001,250      
      7566   2975                 2,975                 $2,975                 991.67                         ₩002,975      
      7654   1250                 1,250                 $1,250                 416.67                         ₩001,250      
      7698   2850                 2,850                 $2,850                 950.00                         ₩002,850      
      7782   2450                 2,450                 $2,450                 816.67                         ₩002,450      
      7788   3000                 3,000                 $3,000                1000.00                         ₩003,000      
      7839   5000                 5,000                 $5,000                1666.67                         ₩005,000      
      7844   1500                 1,500                 $1,500                 500.00                         ₩001,500      
      7876   1100                 1,100                 $1,100                 366.67                         ₩001,100      
      7900    950                   950                   $950                 316.67                         ₩000,950      
      7902   3000                 3,000                 $3,000                1000.00                         ₩003,000      
      7934   1300                 1,300                 $1,300                 433.33                         ₩001,300      

 14개의 행이 선택됨

 


69. 입사일자가 1981년 12월 1일 이전에 입사한 사원수, 급여합계를 보이시오.

select count(*) 사원수, sum(sal) 급여합계
from emp
where to_char(hiredate, 'yyyy mm dd') <= '1981 12 01'


       사원수       급여합계
---------- ----------
         9      19675

70.입사일자가 화요일, 급요일에 입사한 사원들에 대해 사번, 사원명, 입사일자(1980/12/01 토요일)를 요일의 오름차순으로 보이시오.

select empno, ename, to_char(hiredate, 'yyyy/mm/dd day') 입사일자
from emp
where to_char(hiredate, 'day') in ('화요일','금요일')
order by to_char(hiredate, 'd') asc

 

     EMPNO ENAME      입사일자                 
---------- ---------- -----------------------
      7782 CLARK      1981/06/09 화요일      
      7839 KING       1981/11/17 화요일      
      7844 TURNER     1981/09/08 화요일      
      7788 SCOTT      1982/04/09 금요일      
      7698 BLAKE      1981/05/01 금요일      
      7499 ALLEN      1981/02/20 금요일      

 6개의 행이 선택됨

 

71. 입사일자에 대해 분기별로 사원수, 최대급여($12,999), 최소급여(\012,111)를 보이시오.

select to_char(hiredate, 'q') 분기, count(*) 사원수, max(to_char(sal, '$99,999')) 최대급여, min(to_char(sal, 'L099,999')) 최소급여
from emp
group by to_char(hiredate, 'q')


분기        사원수 최대급여     최소급여            
-- ---------- -------- ------------------
1           3   $1,600         ₩001,250 
3           2   $1,500         ₩001,250 
4           4   $5,000         ₩000,800 
2           5   $3,000         ₩001,100 


72. 입사년도별로 사원수, 급여합계를 사원수가 2명 이상인 년도만 사원수가 많은 것부터 보이시오.

select to_char(hiredate, 'yyyy') 입사년도, count(*) 사원수, sum(sal) 급여합계
from emp
group by to_char(hiredate, 'yyyy')
having count(*) >= 2
order by 사원수 asc

입사년도        사원수       급여합계
---- ---------- ----------
1981         10      22825
1982          2       4300


73. 입사월이 홀수월에 입사한 사원들에 대해 사번, 사원명, 입사일자(1980-01-01 수요일 12:12:01 오전)를 보이시오.


select empno, ename , to_char(hiredate, 'yyyy-mm-dd mon hh12:mi:ss am') 입사일자
from emp
where mod(to_char(hiredate, 'mm'),2) = 1


     EMPNO ENAME      입사일자                             
---------- ---------- -----------------------------------
      7654 MARTIN     1981-09-28 9월  12:00:00 오전      
      7698 BLAKE      1981-05-01 5월  12:00:00 오전      
      7839 KING       1981-11-17 11월 12:00:00 오전      
      7844 TURNER     1981-09-08 9월  12:00:00 오전      
      7876 ADAMS      1983-05-12 5월  12:00:00 오전      
      7934 MILLER     1982-01-23 1월  12:00:00 오전      

 6개의 행이 선택됨


74. 입사일자가 상반기, 하반기별로 사원수를 상반기, 하반기 순으로 보이시오.


select case when to_char(hiredate,'q') <= 2 then '상반기'
                                      else '하반기'
  end 분기, count(*)
from emp
group by case when to_char(hiredate,'q') <= 2 then '상반기'
                                      else '하반기'
  end

order by 분기 asc;

 

분기    COUNT(*)
--- ----------
상반기          8
하반기          6

 


기타형식요소

 of "문자열"


 select sysdate, to_char(sysdate, 'dd "of" mon')
from dual;


SYSDATE  TO_CHAR(SYSDATE,'DD"OF"MON')
-------- ----------------------------
15/04/01 01 of 4월                   

 


sp (one,two,three...)
th (1th...)
spth (frist,secont,....)

 

select sysdate, to_char(sysdate,'ddspth'),to_char(sysdate,'ddth'),to_char(sysdate,'ddsp')
from dual;


SYSDATE  TO_CHAR(SYSDATE,'DDSPTH') TO_CHAR(SYSDATE,'DDTH') TO_CHAR(SYSDATE,'DDSP')
-------- ------------------------- ----------------------- -----------------------
15/04/01 first                     01st                    one     

 


*******trim 함수

 

75.
select ename, trim(leading 'A' from ename), trim(trailing 'N' from ename), trim(both 'S' from ename)
from emp;

 

ENAME      TRIM(LEADING'A'FROMENAME) TRIM(TRAILING'N'FROMENAME) TRIM(BOTH'S'FROMENAME)
---------- ------------------------- -------------------------- ----------------------
SMITH      SMITH                     SMITH                      MITH                  
ALLEN      LLEN                      ALLE                       ALLEN                 
WARD       WARD                      WARD                       WARD                  
JONES      JONES                     JONES                      JONE                  
MARTIN     MARTIN                    MARTI                      MARTIN                
BLAKE      BLAKE                     BLAKE                      BLAKE                 
CLARK      CLARK                     CLARK                      CLARK                 
SCOTT      SCOTT                     SCOTT                      COTT                  
KING       KING                      KING                       KING                  
TURNER     TURNER                    TURNER                     TURNER                
ADAMS      DAMS                      ADAMS                      ADAM                  
JAMES      JAMES                     JAMES                      JAME                  
FORD       FORD                      FORD                       FORD                  
MILLER     MILLER                    MILLER                     MILLER                

 14개의 행이 선택됨

 

leading 왼쪽에서 '?'로 시작하는 문자 제거
trailing 오른쪽 '?'로 시작하는 문자 제거
both '?'의 양쪽의 문자를 제거


select ename, trim(leading 'A' from lower(ename)), trim(trailing 'N' from lower(ename)), trim(both 'S' from lower(ename))
from emp;


ENAME      TRIM(LEADING'A'FROMLOWER(ENAME)) TRIM(TRAILING'N'FROMLOWER(ENAME)) TRIM(BOTH'S'FROMLOWER(ENAME))
---------- -------------------------------- --------------------------------- -----------------------------
SMITH      smith                            smith                             smith                        
ALLEN      allen                            allen                             allen                        
WARD       ward                             ward                              ward                         
JONES      jones                            jones                             jones                        
MARTIN     martin                           martin                            martin                       
BLAKE      blake                            blake                             blake                        
CLARK      clark                            clark                             clark                        
SCOTT      scott                            scott                             scott                        
KING       king                             king                              king                         
TURNER     turner                           turner                            turner                       
ADAMS      adams                            adams                             adams                        
JAMES      james                            james                             james                        
FORD       ford                             ford                              ford                         
MILLER     miller                           miller                            miller                       

 14개의 행이 선택됨

 

76.
select empno, ename
from emp
where regexp_like(ename, '^A');


     EMPNO ENAME   
---------- ----------
      7499 ALLEN     
      7876 ADAMS   

////////// reagexp_like(ename,'^A') 문자열의 시작되는 철자가 동일하면 모두 검색

 


77.
select regexp_instr('But, soft! What light through yonder window breaks', 'l[[:alpha:]]{4}') result
from dual;

    RESULT
----------
        17

 

///  l[[:alpha:]]{4}  l 이후로4자리의 단어가 시작하는 위치 light에서 l의 위치가 17


select regexp_instr('lut, softl! What light through yonder window breaks', 'l[[:alpha:]]{4}') result
from dual;


    RESULT
----------
        18


첫번째 lut는 l 이후로 2자리이므로 안찾음, 두번째 softl!은 뒤에 특수문자!  가 있기 때문에 안찾음 세번째 light는 l 이후로 4자리이므로 이것을 찾게된다.


78.
select rank(2000) within group (order by sal asc), dense_rank(2000) within group (order by sal asc)
from emp;

RANK(2000)WITHINGROUP(ORDERBYSALASC) DENSE_RANK(2000)WITHINGROUP(ORDERBYSALASC)
------------------------------------ ------------------------------------------
                                   9                                          8

 


select ename, sal
from emp
order by sal asc;


ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000

 14개의 행이 선택됨

 

******rank는 중복포함


/////////////////   dense_rank는 중복제외

 

79.
select empno, sal, row_number() over (order by sal asc)
from emp;

     EMPNO        SAL ROW_NUMBER()OVER(ORDERBYSALASC)
---------- ---------- -------------------------------
      7369        800                               1
      7900        950                               2
      7876       1100                               3
      7521       1250                               4
      7654       1250                               5
      7934       1300                               6
      7844       1500                               7
      7499       1600                               8
      7782       2450                               9
      7698       2850                              10
      7566       2975                              11
      7788       3000                              12
      7902       3000                              13
      7839       5000                              14

 14개의 행이 선택됨

 

**상위 top10을 보이시오.

select rk, empno, sal
from
       (select empno, sal, row_number() over (order by sal desc) rk
        from emp)
where rk <= 10;


        RK      EMPNO        SAL
---------- ---------- ----------
         1       7839       5000
         2       7788       3000
         3       7902       3000
         4       7566       2975
         5       7698       2850
         6       7782       2450
         7       7499       1600
         8       7844       1500
         9       7934       1300
        10       7521       1250

 10개의 행이 선택됨


80.
select empno, ename, sal, avg(sal)
from emp;

///오류남


select empno, ename, sal, avg(sal) over() avg1
from emp;

     EMPNO ENAME             SAL       AVG1
---------- ---------- ---------- ----------
      7369 SMITH             800 2073.21429
      7499 ALLEN            1600 2073.21429
      7521 WARD             1250 2073.21429
      7566 JONES            2975 2073.21429
      7654 MARTIN           1250 2073.21429
      7698 BLAKE            2850 2073.21429
      7782 CLARK            2450 2073.21429
      7788 SCOTT            3000 2073.21429
      7839 KING             5000 2073.21429
      7844 TURNER           1500 2073.21429
      7876 ADAMS            1100 2073.21429
      7900 JAMES             950 2073.21429
      7902 FORD             3000 2073.21429
      7934 MILLER           1300 2073.21429

 14개의 행이 선택됨

///////////전체 평균


select empno, ename, sal, avg(sal) over(order by sal asc) avg1
from emp;

     EMPNO ENAME             SAL       AVG1
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7900 JAMES             950        875
      7876 ADAMS            1100        950
      7521 WARD             1250       1070
      7654 MARTIN           1250       1070
      7934 MILLER           1300 1108.33333
      7844 TURNER           1500 1164.28571
      7499 ALLEN            1600    1218.75
      7782 CLARK            2450 1355.55556
      7698 BLAKE            2850       1505
      7566 JONES            2975 1638.63636
      7788 SCOTT            3000 1848.07692
      7902 FORD             3000 1848.07692
      7839 KING             5000 2073.21429

 14개의 행이 선택됨


//////////////////////// 다음행과의 평균을 순차적으로 나타냄

select deptno, empno, ename, sal, avg(sal) over(partition by deptno) avg1
from emp;

    DEPTNO      EMPNO ENAME             SAL       AVG1
---------- ---------- ---------- ---------- ----------
        10       7782 CLARK            2450 2916.66667
        10       7839 KING             5000 2916.66667
        10       7934 MILLER           1300 2916.66667
        20       7566 JONES            2975       2175
        20       7902 FORD             3000       2175
        20       7876 ADAMS            1100       2175
        20       7369 SMITH             800       2175
        20       7788 SCOTT            3000       2175
        30       7521 WARD             1250 1566.66667
        30       7844 TURNER           1500 1566.66667
        30       7499 ALLEN            1600 1566.66667
        30       7900 JAMES             950 1566.66667
        30       7698 BLAKE            2850 1566.66667
        30       7654 MARTIN           1250 1566.66667

 14개의 행이 선택됨


////////////////// 부서별로(deptno) 평균을 내타냄


select deptno, empno, ename, sal, avg(sal) over(partition by deptno order by sal desc) avg1
from emp;

    DEPTNO      EMPNO ENAME             SAL       AVG1
---------- ---------- ---------- ---------- ----------
        10       7839 KING             5000       5000
        10       7782 CLARK            2450       3725
        10       7934 MILLER           1300 2916.66667
        20       7788 SCOTT            3000       3000
        20       7902 FORD             3000       3000
        20       7566 JONES            2975 2991.66667
        20       7876 ADAMS            1100    2518.75
        20       7369 SMITH             800       2175
        30       7698 BLAKE            2850       2850
        30       7499 ALLEN            1600       2225
        30       7844 TURNER           1500 1983.33333
        30       7654 MARTIN           1250       1690
        30       7521 WARD             1250       1690
        30       7900 JAMES             950 1566.66667

 14개의 행이 선택됨

/////////// 부서별로 순차적인 평균을 나타냄    (5000+2450)/2는 3725,,,, 중복데이터 계산안함


select deptno, empno, ename, sal, avg(sal) over(partition by deptno order by sal desc rows between unbounded preceding and current row) avg1
from emp;


    DEPTNO      EMPNO ENAME             SAL       AVG1
---------- ---------- ---------- ---------- ----------
        10       7839 KING             5000       5000
        10       7782 CLARK            2450       3725
        10       7934 MILLER           1300 2916.66667
        20       7788 SCOTT            3000       3000
        20       7902 FORD             3000       3000
        20       7566 JONES            2975 2991.66667
        20       7876 ADAMS            1100    2518.75
        20       7369 SMITH             800       2175
        30       7698 BLAKE            2850       2850
        30       7499 ALLEN            1600       2225
        30       7844 TURNER           1500 1983.33333
        30       7654 MARTIN           1250       1800
        30       7521 WARD             1250       1690
        30       7900 JAMES             950 1566.66667

 14개의 행이 선택됨


///////////////// (1300+2450)/2는 1875  (1875+5000)/2 는 2916.66667 부서가 바뀌게 되면 다시 처음부터 시작.  중복데이터 계산함


81.
select empno,ename, sal, decode(job, 'ANALYST', sal*1.1, 'CLERK', sal*1.15, 'MANAGER', sal*1.2, sal) 보너스
from emp;

     EMPNO ENAME             SAL        보너스
---------- ---------- ---------- ----------
      7369 SMITH             800        920
      7499 ALLEN            1600       1600
      7521 WARD             1250       1250
      7566 JONES            2975       3570
      7654 MARTIN           1250       1250
      7698 BLAKE            2850       3420
      7782 CLARK            2450       2940
      7788 SCOTT            3000       3300
      7839 KING             5000       5000
      7844 TURNER           1500       1500
      7876 ADAMS            1100       1265
      7900 JAMES             950     1092.5
      7902 FORD             3000       3300
      7934 MILLER           1300       1495

 14개의 행이 선택됨

///decode(검색컬럼, 조건-1 , 결과 값-1,
      조건-2,  결과 값-2, ........., 기본 값)


82. group by 절에만 사용가능한 함수

select deptno, job, sum(sal), avg(sal)
from emp
group by deptno, job;

    DEPTNO JOB         SUM(SAL)   AVG(SAL)
---------- --------- ---------- ----------
        20 CLERK           1900        950
        30 SALESMAN        5600       1400
        20 MANAGER         2975       2975
        30 CLERK            950        950
        10 PRESIDENT       5000       5000
        30 MANAGER         2850       2850
        10 CLERK           1300       1300
        10 MANAGER         2450       2450
        20 ANALYST         6000       3000

 9개의 행이 선택됨


select deptno, job, sum(sal), avg(sal)
from emp
group by rollup(deptno, job);

    DEPTNO JOB         SUM(SAL)   AVG(SAL)
---------- --------- ---------- ----------
        10 CLERK           1300       1300
        10 MANAGER         2450       2450
        10 PRESIDENT       5000       5000
        10                 8750 2916.66667
        20 CLERK           1900        950
        20 ANALYST         6000       3000
        20 MANAGER         2975       2975
        20                10875       2175
        30 CLERK            950        950
        30 MANAGER         2850       2850
        30 SALESMAN        5600       1400
        30                 9400 1566.66667
                          29025 2073.21429

 13개의 행이 선택됨

////////////////// 부서별과 직무별로  합계, 평균을 나타냄


select deptno, job, sum(sal), avg(sal)
from emp
group by cube(deptno, job);

    DEPTNO JOB         SUM(SAL)   AVG(SAL)
---------- --------- ---------- ----------
                          29025 2073.21429
           CLERK           4150     1037.5
           ANALYST         6000       3000
           MANAGER         8275 2758.33333
           SALESMAN        5600       1400
           PRESIDENT       5000       5000
        10                 8750 2916.66667
        10 CLERK           1300       1300
        10 MANAGER         2450       2450
        10 PRESIDENT       5000       5000
        20                10875       2175
        20 CLERK           1900        950
        20 ANALYST         6000       3000
        20 MANAGER         2975       2975
        30                 9400 1566.66667
        30 CLERK            950        950
        30 MANAGER         2850       2850
        30 SALESMAN        5600       1400

 18개의 행이 선택됨


///////////// 직무별, 부서별 각각의 합계, 평균


83. grouping sets

select deptno, job, mgr, sum(sal)
from emp
group by grouping sets((deptno, job), (job, mgr));


    DEPTNO JOB              MGR   SUM(SAL)
---------- --------- ---------- ----------
           CLERK           7902        800
           PRESIDENT                  5000
           CLERK           7698        950
           CLERK           7788       1100
           CLERK           7782       1300
           SALESMAN        7698       5600
           MANAGER         7839       8275
           ANALYST         7566       6000
        20 CLERK                      1900
        30 SALESMAN                   5600
        20 MANAGER                    2975
        30 CLERK                       950
        10 PRESIDENT                  5000
        30 MANAGER                    2850
        10 CLERK                      1300
        10 MANAGER                    2450
        20 ANALYST                    6000

 17개의 행이 선택됨


///group set 절을 사용하면 이전 방법보다 sql문이 간단해지고 또한 실행 시 성능이 빨라짐


select deptno, job, null 관리자, sum(sal)
from emp
group by deptno, job
union
select null, job, mgr, sum(sal)
from emp
group by job, mgr;

    DEPTNO JOB              관리자   SUM(SAL)
---------- --------- ---------- ----------
        10 CLERK                      1300
        10 MANAGER                    2450
        10 PRESIDENT                  5000
        20 ANALYST                    6000
        20 CLERK                      1900
        20 MANAGER                    2975
        30 CLERK                       950
        30 MANAGER                    2850
        30 SALESMAN                   5600
           ANALYST         7566       6000
           CLERK           7698        950
           CLERK           7782       1300
           CLERK           7788       1100
           CLERK           7902        800
           MANAGER         7839       8275
           PRESIDENT                  5000
           SALESMAN        7698       5600

 17개의 행이 선택됨

 

84.
select ename, level, empno, mgr
from emp
start with ename = 'KING'
connect by prior empno = mgr;

ENAME           LEVEL      EMPNO        MGR
---------- ---------- ---------- ----------
KING                1       7839           
JONES               2       7566       7839
SCOTT               3       7788       7566
ADAMS               4       7876       7788
FORD                3       7902       7566
SMITH               4       7369       7902
BLAKE               2       7698       7839
ALLEN               3       7499       7698
WARD                3       7521       7698
MARTIN              3       7654       7698
TURNER              3       7844       7698
JAMES               3       7900       7698
CLARK               2       7782       7839
MILLER              3       7934       7782

 14개의 행이 선택됨

 


select substr(lpad('', 3*level-3) ||ename, 1,20) 사원명, level, empno, mgr 관리자사번
from emp
start with ename = 'KING'
connect by prior empno = mgr;


사원명                       LEVEL      EMPNO      관리자사번
-------------------- ---------- ---------- ----------
KING                          1       7839           
JONES                         2       7566       7839
SCOTT                         3       7788       7566
ADAMS                         4       7876       7788
FORD                          3       7902       7566
SMITH                         4       7369       7902
BLAKE                         2       7698       7839
ALLEN                         3       7499       7698
WARD                          3       7521       7698
MARTIN                        3       7654       7698
TURNER                        3       7844       7698
JAMES                         3       7900       7698
CLARK                         2       7782       7839
MILLER                        3       7934       7782

 14개의 행이 선택됨


85.
select ename, connect_by_iscycle Iscycle, level, sys_connect_by_path(ename,'/') path
from emp
start with ename = 'KING'
connect by nocycle prior empno = mgr;

 

ENAME         ISCYCLE      LEVEL PATH

--------------------------------------------------------------------------------------------------------------
KING                0          1 /KING  

JONES               0          2 /KING/JONES  
SCOTT               0          3 /KING/JONES/SCOTT    
ADAMS               0          4 /KING/JONES/SCOTT/ADAMS  
FORD                0          3 /KING/JONES/FORD  
SMITH               0          4 /KING/JONES/FORD/SMITH  
BLAKE               0          2 /KING/BLAKE    
ALLEN               0          3 /KING/BLAKE/ALLEN  
WARD                0          3 /KING/BLAKE/WARD 
MARTIN              0          3 /KING/BLAKE/MARTIN     
TURNER              0          3 /KING/BLAKE/TURNER 
JAMES               0          3 /KING/BLAKE/JAMES  
CLARK               0          2 /KING/CLARK    
MILLER              0          3 /KING/CLARK/MILLER   

 

 

creat.txt

2015.04.08.수요일 PJY_DB.txt