봄이여.ora

1.EMP테이블과 같은 구조, 같은 데이터를 갖는 테이블 REEMP 테이블을 생성하고 ,테이블의 구조와 데이터를 확인하여라.

CREATE TABLE REEMP 

AS 

SELECT * FROM EMP;

DESC REEMP

2.DEPT 테이블과 같은 구조, 같은 데이터를 갖는 테이블 REDEPT테이블을 생성하고, 테이블의 구조와 데이터를 확인하여라.

CREATE TABLE REDEPT 

AS SELECT * FROM DEPT;

DESC REDEPT

3.REEMP 테이블을 토대로 EMPNO,ENAME,DEPTNO를 포함하는 뷰 REEMP_VIEW를 작성하여라.

CREATE OR REPLACE VIEW REEMP_VIEW AS SELECT EMPNO,ENAME,DEPTNO FROM REEMP;

1) 뷰 REEMP_VIEW의 구조를 확인하여라.

DESC REEMP_VIEW

2) 뷰 REEMP_VIEW의 데이터를 확인하여라.

SELECT * FROM REEMP_VIEW;

3) 뷰 REEMP_VIEW의 EMPNO의 데이터를 EMPNO+100으로 변경하여라.

UPDATE REEMP_VIEW SET EMPNO=EMPNO+100;

4) 뷰 EMPNO의값이 변경되었는지를 REEMP_VIEW에서 확인하여라.

100씩 추가되었다.

5) 뷰 EMPNO의 값이 변경되었는지를 REEMP테이블에서 확인하여라.

100씩 추가되었다.

6) 데이터사전을 이용해서 생성된뷰 REEMP_VIEW의 이름과 생성구문을 확인하여라.

SELECT * FROM USER_VIEWS; 

4.REEMP 테이블로부터 DEPTNO=30인 사원들로만 된 REEMP_VIEW30을 만들어라. 단 DEPTNO의 값은 변경될 수 없도록 하여라.

CREATE OR REPLACE VIEW REEMP_VIEW30 

AS 

SELECT * FROM REEMP 

WHERE DEPTNO = 30 

WITH CHECK OPTION;

1)뷰 REEMP_VIEW30의 구조를 확인하여라.

DESC REEMP_VIEW30;

2) 뷰REEMP_VIEW30의 데이터를 확인하여라.

SELECT * FROM REEMP_VIEW30;

3)뷰 REEMP_VIEW30 에서 DEPTNO가 30인것을 10으로 바꾸어라, 바꿀수없다면 그 이유에 대해 설명하여라.

UPDATE REEMP_VIEW30 

SET DEPTNO=10 WHERE DEPTNO =30;

뷰의 WITH CHECK OPTION의 조건에 위배되기 때문에 바꿀수없다.

4) 데이터 사전을 이용해서 생성된 뷰 REEMP_VIEW30의 이름과 생성 구문을 확인하여라.

SELECT * FROM USER_VIEWS; 

5.REEMP 테이블과 REDEPT테이블로부터 EMPNO,ENAME,DEPTNO,DNAME을 포함하는 뷰 E_D_VIEW를 작성하여라.

CREATE OR REPLACE VIEW E_D_VIEW 

AS 

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME 

FROM REEMP E, REDEPT D 

WHERE E.DEPTNO =D.DEPTNO;

1)뷰 E_D_VIEW의 구조를 확인하여라.

DESC E_D_VIEW 

2)뷰 E_D_VIEW의 모든 데이터를 확인하여라.

SELECT * FROM E_D_VIEW;

3)뷰 E_D_VIEW에 하나의 데이터(9000,'KIM',30,'MARKETING')를 추가하여라.만약 추가할 수 없다면 그 이유에 대해 설명하여라.

INSERT INTO E_D_VIEW(EMPNO,ENAME,DEPTNO,DNAME) VALUES(9000,'KIM',30,'MARKETING');

추가할수없다.

유니크한 키가없기때문에?

4)데이터 사전을 이용해서 생성된 뷰 E_D_VIEW의 이름과 생성구문을 확인하여라. 

SELECT * FROM USER_VIEWS; 

1.DEPT학번c 테이블을 다음과 같이 변경하여라.

1)DEPT학번c 테이블의 구조를 확인하여라.

DESC DEPT학번c

2)테이블 이름을 D학번 으로 변경하여라.

RENAME DEPT학번c TO D학번; 

3)D학번 테이블의 구조를 확인하여라.

DESC D학번

4)NAME 칼럼에 NOT NULL 제약조건을 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE D학번 MODIFY NAME VARCHAR2(10) CONSTRAINT D학번_NAME_nn NOT NULL; 

5)regionid 칼럼을 NUM(5)로 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE D학번 ADD regionid NUMBER(5);  

DESC D학번

6)D학번 테이블의 제약조건을 확인하여라.

SELECT TABLE_NAME, CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'D학번';

2.EMP학번c 테이블을 다음과 같이 변경하여라.

1) EMP학번c 테이블의 구조를 확인하여라.

DESC EMP학번c 

2) 테이블의 이름을 E학번 으로 변경하여라.

RENAME EMP학번c TO E학번; 

3) E학번 테이블의 구조를 확인하여라.

DESC E학번

4) managerid 칼럼을 NUM(7) NOT NULL 제약조건으로 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 ADD managerid NUMBER(7) CONSTRAINT E학번_managerid_nn NOT NULL;  

DESC E학번 

5) ID 칼럼의 길이를 NUM(10)으로 하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 MODIFY ID NUMBER(10);

DESC E학번 

6) LNAME 칼럼의 NOT NULL 제약조건을 삭제하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 DROP CONSTRAINT EMP학번c_LNAME_nn; 

7)E학번 테이블의 managerid 칼럼에 D학번 테이블의 NO 칼럼을 참조하는 FOREIGN KEY 제약조건을 추가하여라.

ALTER TABLE E학번 

ADD CONSTRAINT  E학번_managerid_fk FOREIGN KEY(managerid) REFERENCES D학번(NO);

8) E학번 테이블의 제약조건을 확인하여라.

SELECT TABLE_NAME, CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'E학번';

1.다음테이블을 생성하여라.

테이블이름 :EMP학번c(칼럼레벨),EMP학번t(테이블레벨)

테이블이름 :DEPT학번c(칼럼레벨),DEPT학번t(테이블레벨)

1. 10번 부서에 대해 급여의 평균 값,최대 값,최소 값,인원 수를 구하여 출력하여라.

SELECT AVG(sal),MAX(sal),MIN(sal),COUNT(empno) FROM emp WHERE deptno = 10;

2. 각 부서별 같은 직무를 갖는 사원의 인원수를 구하여 부서 번호, 직무,인원수를 출력하여라.

SELECT deptno,job,COUNT(empno) FROM emp GROUP BY deptno,job;

3. 사원들의 직무별 평균 급여와 최고급여,최저급여를 평균급여에 대해 오름차순으로 정렬하여라.

SELECT AVG(sal),MAX(sal),MIN(sal) FROM emp GROUP BY job ORDER BY AVG(sal) ASC;

4. EMP와 DEPT테이블을 조인하여 모든 사원에 대해 부서 번호, 부서이름, 사원이름, 급여를 출력하여라.

SELECT e.ename,e.sal,e.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ;

5. 이름이 'ALLEN'인 사원의 부서 이름을 출력하여라.

SELECT d.dname FROM  emp e,dept d WHERE e.deptno =d.deptno AND e.ename = (SELECT ename FROM emp WHERE ename = 'ALLEN');

6. 'ALLEN'과 직무가 같은 사원의 이름,부서 이름, 급여, 부서위치를 출력하여라.

SELECT e.ename, d.dname, e.sal,d.loc FROM emp e,dept d WHERE e.deptno =d.deptno AND e.job = (select job from emp where ename ='ALLEN');

7. 전체사원의 평균 급여보다 급여가 많은 사람의 사원 번호,이름, 부서이름,입사일,부서위치,급여를 출력하여라.

select e.empno,e.ename,d.dname,e.hiredate,d.loc,e.sal from emp e, dept d where e.deptno =d.deptno and e.sal > (select avg(sal) from emp);

8. 10번 부서 사원들 중에서 20번 부서의 사원과 같은 직무를 갖는 사원의 사원 번호 ,이름,부서 이름, 입사일, 부서 위치를 출력하여라.

select e.empno,d.dname,e.hiredate,d.loc from emp e,dept d where e.deptno =d.deptno and e.deptno=10 and e.job in (select job from emp where deptno =20);

9. 'ALLEN'의 급여보다 많고 'SCOTT'의 급여보다 적은 사원의 사원번호,이름,부서이름,부서위치,급여를 급여가 많은 순으로 출력하여라.

select e.empno,e.ename,d.dname,d.loc,e.sal from emp e, dept d where e.deptno = d.deptno and sal >(select sal from emp where  ename ='ALLEN') and sal <(select sal from emp where  ename ='SCOTT');

10. 30번 부서의 최고급여보다 급여가 많은 사원의 사원 번호 ,이름,급여를 출력하여라.

select empno,ename,sal from emp where sal > (select max(sal) from emp where deptno ='30');

11. 사원 이름의 두 번째 글자가 'A'인 사원들의 수를 출력하여라.

select count(*) from emp where ename like '_A%';

12. 사원들의 평균 급여,총 급여, 최고급여액,최저 급여액을 구하여라.

select avg(sal),sum(sal),max(sal),min(sal) from emp;

13. 평균급여가  1500이 넘는  직무와 평균 급여를 구하여라.

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

14.사원들의 이름,부서 이름, 급여를 출력하여라.

select e.ename,d.dname, e.sal from emp e, dept d where e.deptno =d.deptno;

15. 각 부서별 부서 번호(모든 부서에 대해서),부서 이름,부서 위치,사원의 수를 출력하여라.

select d.deptno,d.dname,d.loc,count(*) from emp e, dept d where e.deptno =d.deptno group by d.deptno,d.dname,d.loc

16.모든 사원들의 평균 급여보다 많이 받는 사원들의 사원 번호와 이름을 출력하여라.

select empno,ename from emp where sal > (select avg(sal) from emp);

17.'FORD'와 부서가 같은 사원들의 이름,부서 이름, 직무, 급여를 출력하여라.

select e.ename,d.dname,e.job,e.sal from emp e, dept d where e.deptno =d.deptno  and job = (select job from emp where ename ='FORD'); 

18.부서이름이 'SALES'인 사원들의 평균 급여보다 많고, 부서 이름이 'RESEARCH'인 사원들의 평균 급여보다 적은 사원들의 이름, 부서번호, 급여, 직무를 출력하여라.

select ename,sal,job from emp where sal > (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='SALES')

and  sal < (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='RESEARCH');

19.부서별 평균 급여가 1000보다 적은 부서 사원들의 부서 번호를 출력하여라.

select deptno from emp group by deptno having avg(sal) <1000;

20. 직무가 'ANALYST'인 사원보다 급여가 적으면서, 직무가 'ANALYST'가 아닌 사원들의 사원번호,이름, 직무, 급여를 출력하여라.

select deptno,ename,job,sal from emp where sal < (select sal from emp where job ='ANALYST')

and job != 'ANALYST';-- 모름 

1.모든사원의 사원번호, 이름, 급여, 부서 번호를 출력하여라.

SELECT ename, sal, deptno 

FROM emp;

2.모든 사원의 이름, 급여, 커미션, 총액(급여+커미션)을 구하여 총액이 많은 순서로 출력하여라(단,커미션이 null인 사원도 0으로해서 포함하여라.).

SELECT ename, sal, NVL(comm,0) AS "comm", sal+NVL(comm,0) AS "총액" 

FROM emp 

ORDER BY sal+NVL(comm,0) DESC;

3.10번부서의 모든 사원에게 급여의 13%를 보너스로 지불하기로 하였다. 10번부서 사원들의 이름,급여,보너스 금액,부서번호를 출력하여라.

SELECT ename, sal, sal*1.13, deptno 

FROM emp 

WHERE deptno =10;

4.급여가 $1,500부터$3,000 사이의 사원에 대해서만 급여의 15%를 회비로 지불하기로 하였다. 조건에 해당되는 사원의 이름,급여,회비(소수이하 2자리까지 반올림)를 출력하여라.

SELECT ename, sal, ROUND(sal*0.15,2) AS "회비" 

FROM emp 

WHERE sal >1500 

AND sal <3000;

5.모든 사원의 부서번호, 이름, 입사일, 현재일, 입사일부터 현재까지의 근무 일 수(소수점 이하 절삭),근무 년 수, 근무 월 수(30일 기준), 근무 주수를 출력하여라.

SELECT deptno, ename, hiredate, sysdate, TRUNC(sysdate-hiredate), 모르겟음 

6.모든 사원에 대해 입사일로부터 90일이 지난 후의 날짜를 계산해서 이름, 입사일, 90일 후의 날짜,급여를 출력하여라.

SELECT ename, hiredate, hiredate+90 AS "입사일+90일",sal 

FROM emp;

7.모든 사원에 대해 입사한 달의 근 무 일수를 계산하여 부서 번호, 이름, 근무 일 수를 출력하여라.

SELECT deptno,ename, LAST_DAY(hiredate)-hiredate AS "입사한달 근무일수" 

FROM emp;

8.모든 사원에 대해 입사한 날로부터 60일이 지난 후의 '월요일'이 몇 년,몇 월,몇 일인지를 구하여 이름, 입사일, 60일 후의 '월요일' 날짜를 출력하여라.

SELECT TO_CHAR(NEXT_DAY(hiredate+60,'월'),'yyyy-mm-dd') 

FROM emp;

9.이름의 글자수가 6자 이상인 사원의 이름을 앞에서 3자만 구하여 소문자로 이름만 출력하여라.

SELECT lower(SUBSTR(ename,1,3)) 

FROM emp 

WHERE LENGTH(ename) >= 6;

10.모든 사원에 대해 사원의 이름은 앞에서 세 글자만 대문자로, 업무는 소문자로 하여 이름, 직무, 부서 번호를 출력하여라.

SELECT UPPER(SUBSTR(ename,1,3))||lower(SUBSTR(ename,4)) AS "ename", job, deptno 

FROM emp;

11.모든 사원에 대해 사원의 이름과 부서 번호를 합성시켜 "연결예제"라는 HEADING으로 출력하여라.

SELECT ename||'의 부서번호는 '||deptno||' 이다.' AS "연결예제" 

FROM emp;

12.사원의 직무가 'SAL'로 시작하는 사원의 이름과 사원 이름의 길이를 출력하여라.

SELECT ename,LENGTH(ename) FROM emp 

WHERE job LIKE 'SAL%';

13.이름의 두 번째 글자가 'A'인 사원들의 이름과 직무를 출력하여라(단, 직무는 앞에서부터 세 글자로 줄여 표시하여라.).

SELECT ename, SUBSTR(job,1,3) 

FROM emp 

WHERE ename LIKE '_A%'; 

14.현재의 7일전과 7일후, 그리고 30일이 지난 후의 첫 번째 월요일을 출력하여라.

SELECT NEXT_DAY(sysdate-7,'월') AS "7일전",NEXT_DAY(sysdate+7,'월') AS "7일후",NEXT_DAY(sysdate+30,'월') AS "30일후" 

FROM emp;

15.직무가 'CLERK'인 사원들의 사원 번호, 이름, 급여를 표시하여라(단, 급여는 1000단위마다 콤마(,)를 찍고 앞에는 국가별 화폐단위를 붙여 표시하여라.).

SELECT empno, ename, TO_CHAR(sal,'L999,999') 

FROM emp 

WHERE job=UPPER('clerk');

16.사원들의 사원 번호와 급여, 커미션,연봉((comm+sal)*12)을 연봉이 많은 순서로 출력하여라(단,커미션이 null인 사원도 0 으로 계산하여라.).

SELECT empno, sal, NVL(comm,0) AS "comm", ((NVL(comm,0)+sal)*12) AS "annual income" 

FROM emp 

ORDER BY ((NVL(comm,0)+sal)*12) 

17.모든 사원에 대해 입사일로부터 6개월이 지난 후의 날짜를 계산해서 이름, 입사일, 6개월 후의 날짜를 출력하여라.

SELECT ename,hiredate, ADD_MONTHS(hiredate,6) AS "addmonths"

FROM emp; 

18.모든 사원에 대해 사원들의 이름, 급여, 커미션을 급여가 적은 것부터 출력하여라(단, 커미션이 없는 사원은 'No Commission'이라는 말이 출력되게하여라.).

SELECT ename,sal, DECODE(comm,'','No Commission',comm) 

FROM emp 

ORDER BY sal ASC;

19.'CLERK'은 20%, 'SALESMAN'은 15%,'ANALYST'는 10%, 'MANAGER'는 5%, 'PRESIDENT' 는 0%와 같이 업무에 따라 급여인상을 다르게 할 경우, 모든 사원들의 이름, 직무, 급여, 인상 후의 급여를 출력하여라.

SELECT ename, job, sal, DECODE(job, 'CLERK', sal*1.20, 'SALESMAN', sal*1.15, 'ANALYST', sal*1.05, 'PRESIDENT', sal*1.0, sal) 

FROM emp;

20. 모든 사원들의 입사한 년도와 입사한 달을 출력하여라.

SELECT TO_CHAR(hiredate,'yyyy')||'년도',TO_CHAR(hiredate,'mm')||'월' 

FROM emp;

21.사원번호가 짝수인 사원들의 모든 정보를 출력하여라.  

SELECT * 

FROM emp 

WHERE MOD(empno,2) = 0;

1.사원 번호가 7499인 사원 중 이름, 입사일, 부서번호를 출력하여라.

SELECT ename, hiredate, deptno 

FROM emp 

WHERE empno='7499';

2.이름이 ALLEN인 사원의 모든정보를 출력하여라.

SELECT * 

FROM emp 

WHERE ename='ALLEN';

3.이름이 K보다 큰 글자로 시작하는 사원의 모든 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE ename>'K';

4.입사일이 02-APR-81보다 늦고 09-DEC-82보다 빠른 사원의 이름,급여,부서번호를 출력하여라.

SELECT ename, sal, deptno 

FROM emp 

WHERE TO_CHAR(hiredate,'dd-mon-yy')>'02-APR-81' 

AND TO_CHAR(hiredate,'dd-mon-yy')<'09-DEC-82' ;

5.급여가 $1,600 보다 크고 $3,000보다 작은 사원의 이름,직무,급여를 출력하여라.

SELECT ename, job, sal 

FROM emp 

WHERE sal >1600 

AND sal <3000 ;

6.입사일이 81년 이외에 입사한 사원의 모든 정보를 입력하여라.

SELECT * 

FROM emp 

WHERE TO_CHAR(hiredate,'yyyy') !='1981';

7.직업이 MANAGER와 SALESMAN인 사원의 모든 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE job=upper('manager') 

OR job=upper('salesman');

8.부서 번호가 20번,30번을 제외한 모든 사원의 이름, 사원번호, 부서번호를 출력하여라.

SELECT ename, empno, deptno 

FROM emp 

WHERE deptno NOT IN (20,30)

9.이름이 S로 시작하는 사원의 사원번호,이름,입사일,부서번호를 출력하여라.

SELECT empno, ename, hiredate, deptno 

FROM emp 

WHERE ename LIKE 'S%';

10.입사일이 81년도인 사원의 모든정보를 출력하여라.

SELECT * 

FROM emp 

WHERE TO_CHAR(hiredate,'yyyy')='1981';

11.이름중 S자가 들어가 있는 사원의 모든정보를 출력하여라.

SELECT * 

FROM emp 

WHERE ename LIKE '%S%';

12.이름이 S자로 시작하고 마지막 글자가 T인사원의 모든정보를 출력하여라.(단, 이름은 전체 5자리이다.)

SELECT * 

FROM emp 

WHERE ename LIKE 'S%' 

AND ename like '%T';

13.처음의 글자는 관계없고 두 번째 글자가 A인 사원의 모든 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE ename LIKE '_A%';

14. 커미션이 NULL이 아닌 사원의 모든 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE comm IS NOT NULL

15.급여가 $1,500 이상이고, 부서번호가 30번인 사원중 직무가 MANAGER인 사원의 모든정보를 출력하여라.

SELECT * 

FROM emp 

WHERE sal >=1500 

AND deptno =30 

AND job=UPPER('manager');

16. 부서 번호가 30인 사원들의 모든 정보를 사원 번호 순으로 정렬하여라.

SELECT * 

FROM emp 

WHERE deptno =30 

ORDER BY empno;

17.사원의 모든정보를 부서 번호에 대해 내림차순으로 정렬하고  이름에 대해 오름차순으로, 급여에 대해 내림차순으로 정렬하여라.

SELECT * 

FROM emp 

ORDER BY deptno DESC, ename, sal DESC;

18.부서번호가 10번인 사원의 사원 번호,이름,급여를 출력하여라.

SELECT empno, ename, sal  

FROM emp 

WHERE deptno =10;

19.직무가 MANAGER가 아닌 사원의 모든 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE job !=UPPER('manager');

20. 1981년 4월 2일 이후에 입사한 사원의 정보를 출력하여라.

SELECT * 

FROM emp 

WHERE hiredate>'1981-04-02'

21.사원 번호가 7698보다 작거나 같은 사원들의 사원번호와 이름을 출력하여라.

SELECT empno,ename 

FROM emp 

WHERE empno <= '7698';

22. 사원 번호가 7654와 7782 사이 이외의 사원의 모든정보를 출력하여라.

SELECT * 

FROM emp 

WHERE empno NOT BETWEEN 7654 AND 7782; 

23. 부서가 30번이고 급여가 $1,500 이상인 사원의 이름,부서,급여를 출력하여라.

SELECT ename, dept, sal 

FROM emp

WHERE dept=30 

AND sal =1500;

24. 사원의 모든 정보를 부서 번호에 대해 오름차순으로 정렬한 후 급여가 많은 사원부터 차례로 출력하여라.

SELECT * 

FROM emp 

ORDER BY deptno, sal DESC;