210825_DDL의 실전문제

--DDL의 실전문제

--1.사원테이블과 같은 구조의 T_TDL1테이블을 생성하라

CREATE TABLE T_TBL1(

T_EMPNO NUMBER(4) NOT NULL,

T_ENAME VARCHAR2(20),

T_JOB VARCHAR2(10),

T_MGR NUMBER(4),

T_HIREDATE DATE,

T_SAL NUMBER(7),

T_COMM NUMBER(7),

T_DEPTNO NUMBER(5),

CONSTRAINT T_TBL1_PK PRIMARY KEY(T_EMPNO)

);

--2.T_TBL1의 테이블 구조를 살피시오?

DESC T_TBL1;

 

--3.T_TBL1테이블에 부서번호가 20인 데이터를 삽입하라(5개)?

SELECT * FROM EMP;

INSERT INTO T_TBL1

VALUES('7369','SMITH','CLERK',7902,TO_DATE('1980-12-17','YYYY-MM-DD'),

800,NULL,20);

INSERT INTO T_TBL1

VALUES('7566','JONES','MANAGER',7839,TO_DATE('1981-04-02','YYYY-MM-DD'),

297,NULL,20);

INSERT INTO T_TBL1

VALUES('7788','SCOTT','ANALYST',7566,TO_DATE('1987-07-13','YYYY-MM-DD'),

3000,NULL,20);

INSERT INTO T_TBL1

VALUES('7876','ADAMS','CLERK',7788,TO_DATE('1987-07-13','YYYY-MM-DD'),

1100,NULL,20);

INSERT INTO T_TBL1

VALUES('7902','FORD','ANALYST',7566,TO_DATE('1980-12-03','YYYY-MM-DD'),

3000,NULL,20);

--4.T_TBL1테이블을 검색하라?

SELECT * FROM T_TBL1;

 

--5.T_TBL1의 테이블에 성별(GENDER)열을 삽입하라

ALTER TABLE T_TBL1

ADD (T_GENDER CHAR(1));

 

--6.T_TBL1에 새로운열이 삽입되었는지 확인하라

SELECT * FROM T_TBL1;

 

--7.T_TBL1테이블에서 성별의 구조를 변경하고 확인하라

--CHAR(1)에서 VARCHAR2(10)로 변경하라?

ALTER TABLE T_TBL1

MODIFY(T_GENDER VARCHAR2(10));

DESC T_TBL1;

--8.T_TBL1테이블에서 성별인 열을 삭제하라 

ALTER TABLE T_TBL1

DROP (T_GENDER);

DESC T_TBL1;

 

--9.T_TBL1테이블의 모든 데이터를 삭제하라

SELECT * FROM T_TBL1;

TRUNCATE TABLE T_TBL1;

 

--10.T_TBL1테이블 제약조건을 검색하라?

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='T_TBL1';

 

--11.T_TBL1테이블의 PRIMARY KEY 를 비활성화 하라. 

ALTER TABLE T_TBL1

DISABLE CONSTRAINT T_TBL1_PK;

 

--12.T_TBL1의 테이블의 PRIMARY KEY 를 활성화하라

ALTER TABLE T_TBL1

ENABLE CONSTRAINT T_TBL1_PK;

 

--.13.T_TBL1테이블을 삭제하라.

DROP TABLE T_TBL1;

 

--14.사원테이블에서 20번 부서의 사원들로 이루어진 뷰를 생성하라?

CREATE OR REPLACE VIEW V_EMP20

AS

SELECT * FROM EMP WHERE DEPTNO = 20;

SELECT * FROM V_EMP20;

 

--15.사원번호,사원이름,부서이름 가지는 뷰를 생성하라

CREATE OR REPLACE VIEW V_EMP_DEPT

AS

SELECT EMPNO,ENAME,DNAME 

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO;

SELECT * FROM V_EMP_DEPT;

 

--16.부서별 사원중 가장 최근에 입사한 사원보다 먼저 입사한 사원의 정보를 검색하라

SELECT A.*

FROM EMP A,

(SELECT DEPTNO,MAX(HIREDATE) AS MAX_HIREDATE

FROM EMP

GROUP BY DEPTNO) B

WHERE A.DEPTNO=B.DEPTNO

AND A.HIREDATE < B.MAX_HIREDATE;

 

SELECT DEPTNO,MAX(HIREDATE) AS MAX_HIREDATE

FROM EMP

GROUP BY DEPTNO;

 

--17.가장 최근에 입사한 5명의 사원번호 사원이름 입사일을 검색하라?

SELECT EMPNO,ENAME,HIREDATE 

FROM (SELECT EMPNO,ENAME,HIREDATE FROM EMP

WHERE HIREDATE IS NOT NULL

ORDER BY HIREDATE DESC)

WHERE ROWNUM <= 5;

 

--SELECT EMPNO,ENAME,HIREDATE FROM EMP

--WHERE HIREDATE IS NOT NULL

--ORDER BY HIREDATE DESC;

 

--18.급여와 커미션을 합한 금액으로 상위 7명만의 사원번호,사원이름을 검색하라

SELECT EMPNO,ENAME,SALARY

FROM (SELECT EMPNO,ENAME,NVL2(COMM,SAL+COMM,SAL) SALARY FROM EMP 

ORDER BY 3 DESC)

WHERE ROWNUM<=7;

 

SELECT EMPNO,ENAME,NVL2(COMM,SAL+COMM,SAL) SALARY FROM EMP 

ORDER BY 3 DESC;

 

--19.부서별 평균급여가 가장 큰 부서2개의 부서이름을 검색하라

SELECT DNAME,KK

FROM (SELECT DNAME,AVG(SAL) KK

FROM EMP NATURAL JOIN DEPT

GROUP BY DNAME

ORDER BY 2 DESC)

WHERE ROWNUM <=2;

 

--20.사원 직무별 급여 표준편차가 큰 상위 2개의 사원직무를 검색하라

SELECT JOB,ROUND(DD)

FROM (SELECT JOB,STDDEV(SAL) DD

FROM EMP

GROUP BY JOB

ORDER BY 2 DESC)

WHERE ROWNUM <=2;