SQL 문제 6-7일차

6일차

6 일차 SQL 과제 –

1.사원 테이블에서 급여(SAL) 값을 누계(SAL_CUMULATIVE) 값으로 사원번호(EMPNO) 오름차순 정렬 순서로 아래 예제처럼 출력하세요?
ex)

 

 

SELECT empno, ename, sal
,(sum(sal)OVER(ORDER BY empno)) AS SAL_CUMULATIVE
FROM emp
ORDER BY empno;

     

2.사원 테이블에서 직업이 ‘SALESMAN’ 사원 중에 급여(SAL) 낮은 순서대로 순위(RANK)를 아래 예제처럼 출력하세요?
ex)  급여가 같은 경우 데이터 정렬 순서 나오는 대로 순위 정함.
    

 

SELECT job, ename, sal, ROW_NUMBER() OVER (ORDER BY sal) AS rank
from emp
WHERE job = 'SALESMAN'
ORDER BY rank;

 

3.사원 테이블에서 직업이 ‘SALESMAN’ 사원 중에 급여(SAL) 낮은 순서대로 순위(RANK)를 아래 예제처럼 출력하세요?
ex) 급여가 같은 경우 순위는 같고 동률 순위 사람 수 만큼 다음 순위에 순위 차를 둠
   

 

SELECT job, ename, sal, rank() OVER (ORDER BY sal) AS rank
from emp
WHERE job = 'SALESMAN'
ORDER BY rank;

 

4.사원 테이블에서 직업이 ‘SALESMAN’ 사원 중에 급여(SAL) 낮은 순서대로 순위(RANK)를 아래 예제처럼 출력하세요?
ex) 급여가 같은 경우 순위는 같고 다음 순위는 위 순위 번호 다음값
 

 

SELECT job, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rank 
FROM emp   
WHERE job = 'SALESMAN'
ORDER BY rank;

 

5.EMP테이블을 백업 하기 위하여(복제하기 위하여)  한 sql문장으로
데이터를 EMP_BAK TABLE을 생성하여 넣는 문장을 작성하시오
(테이블 생성 및 INSERT가 한 문장으로 수행 되어야 함.)

CREATE TABLE EMP_BAK as SELECT * FROM emp;

 

6. EMP Table에 5000 건의 테스트 데이터를 생성하시오.

INSERT INTO emp (empno,	ename, job, mgr, hiredate, sal, comm, deptno)
    SELECT  	 LEVEL,	  
    dbms_random.string ('U', 4),
    CASE  
	      WHEN  LEVEL =  1		    THEN  'PRESIDENT'
	      WHEN  LEVEL <= 4   	    THEN  'MANAGER'	
	      WHEN  dbms_random.value < .5  THEN  'ANALYST'
	      WHEN  dbms_random.value < .5  THEN  'CLERK'
	      	    		      	    ELSE  'SALESMAN' END,	
    CASE
	    WHEN  LEVEL > 1
	    THEN  TRUNC (dbms_random.value (1, LEVEL))END,	      
      TRUNC ( SYSDATE - dbms_random.value (0, 3650)),	
      ROUND ( dbms_random.value (100, 5000) , -2),
      TRUNC (DBMS_RANDOM.VALUE (0, 10000), 0),
      TRUNC (DBMS_RANDOM.VALUE (0, 10000), 0)                 
FROM	 dual
CONNECT BY	 LEVEL	<= 5000;

쉬운건 이렇게도 가능

INSERT INTO EMP
SELECT LEVEL,
       NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM DUAL
CONNECT BY LEVEL <= 5000;

 

 

---

 

7일차

7일차 SQL에서만 6일차 5번 문제에서 만든 EMP이름 (각자이름) 테이블을 이용합니다.

1.사원(EMP이름)테이블에서 직업(JOB)이 ‘SALESMAN’ 인 사원 급여(SAL)에 400 더하는 수정(UPDATE) 구문을 구하세요?

 

UPDATE emp_bak SET sal=sal+400 WHERE job='SALESMAN';

 

2.사원(EMP이름)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원을 대상으로 고용일자(HIREDATE)를 1년 더하는 수정(UPDATE) 구문을 구하세요?

 

UPDATE emp_bak SET hiredate= hiredate+365 WHERE sal >(SELECT AVG(sal) FROM emp_bak);

 

3.사원(EMP이름)테이블에서 전체 사원을 대상으로 COMM 컬럼에 100 을 더하고 직업(JOB)이 ‘CLERK’ 인 사원은 현 급여에서 2배, ‘MANAGER’ 인 직업을 가진 사원은 현 급여에서 3배, 이외 직업을 가진 사원은 현 급여에서 4배를 더하는 수정(UPDATE) 구문을 구하세요?

 

UPDATE emp1 SET comm=comm+100
,sal = CASE job
WHEN 'CLERK' THEN sal*2
WHEN 'MANAGER' THEN sal*3
ELSE sal*4
END;

 

4.사원(EMP이름)테이블에서 이름(ENAME)이 ‘M’으로 시작하는 사원 
삭제(DELETE) 구문을 구하세요?

 

DELETE FROM emp_bak WHERE ename LIKE 'M%';

 

5.사원(EMP이름)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원 삭제(DELETE) 구문을 구하세요?

 

DELETE FROM emp_bak WHERE sal>(select AVG(sal) FROM emp_bak);