[Database] 9장 SQL 연습문제2

 

제공되는 데이터

 

CREATE TABLE EMP
(EMPNO      CHAR(4) NOT NULL ENABLE, 
	ENAME      VARCHAR2(20) NOT NULL ENABLE, 
	JOB        VARCHAR2(30) NOT NULL ENABLE, 
	MGR        VARCHAR2(4), 
	HIREDATE   DATE NOT NULL ENABLE, 
	SAL        NUMBER(10,0) NOT NULL ENABLE, 
	COMM       NUMBER, 
	DEPTNO     VARCHAR2(2) NOT NULL ENABLE,
CONSTRAINT XPK_EMP PRIMARY KEY (EMPNO) );

CREATE TABLE DEPT
(DEPTNO     CHAR(2) NOT NULL ENABLE,
	DNAME      VARCHAR2(20) NOT NULL ENABLE, 
	LOC        VARCHAR2(30) NOT NULL ENABLE,
CONSTRAINT XPK_DEPT PRIMARY KEY (DEPTNO) );

-- 사원관리
SELECT * FROM EMP;
SELECT * FROM DEPT;

INSERT INTO EMP(EMPNO,  ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7369', '장길동', '프로그래머', '7902', '2013/12/17', 600, 200 , '50');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7499', '고영우', '시장조사', '7698', '2021/02/20', 550, 300 , '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7521', '구기현', '영업사원', '7698', '2020/02/22', 250, 400 , '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7566', '김동혁', '관리자', '7839', '2012/04/02', 375,NULL, '40');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7654', '김민욱', '영업사원', '7698', '2018/09/28', 350, 700 , '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7698', '김민정', '관리자', '7839', '2014/05/01', 450,NULL, '40');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7782', '김지완', '관리자', '7839', '2022/02/20', 470, 600, '40');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7788', '박승우', '분석가', '7839', '2021/03/22', 300,NULL , '50');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7839', '박윤수', '대표이사', NULL, '2010/01/05', 900, 100, '40');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7844', '박정수', '영업사원', '7698', '2016/09/28',340,NULL, '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7876', '배문기', '회계업무', '7788', '2014/05/01', 420,NULL, '10');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7900', '변정민', '시장분석', '7698', '2012/04/02', 750,NULL, '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7902', '신국현', '분석가', '7566', '2019/09/28', 300,NULL, '50');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7934', '신현근', '프로그래머', '7782', '2018/01/23', 290,400, '50');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7999', '오대환', '회계업무', '7782',SYSDATE, 210,NULL, '10');

INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('10', '회계부', '서울');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('20', '조사부', '세종');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('30', '영업부', '서울');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('40', '관리부', '대전');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('50', '개발부', '대전');

 

 

 

문제

 

1. 덧셈연산자를 이용하여 모든 사원에 대해서 연봉에 대해 300만원의 급여 인상을 계산한 후 사원의 사번, 이름, 급여, 인상된 연봉을 출력하시오.

 

2. 사원번호 7999 사람의 업무(JOB)를 '프로그래머'로 수정하시오.

 

3. emp 테이블 job 속성의 데이터타입을 varchar2(20)으로 수정하시오.

 

4. emp 테이블에 부서번호와 dept 테이블의 외래키를 설정하시오.

 

5. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오.

연간 총수입은 월급에 12를 곱한 후 100만원의 상여금을 더해서 계산하시오.

 

6. 급여가 320만원이 넘는 사원의 이름과 급여를 조회하는데 급여가 많은 것부터 작은 순으로 출력하시오.

 

7. 사원번호가 7788인 사원의 이름과 부서번호, 부서명을 출력하시오.

 

8. 급여가 300에서 400 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.

9. 2020년 2월 20일부터 2022년 12월 31일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.

10. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 출력하시오.

11. 사원의 급여가 300에서 450 사이에 포함되고 부서번호가 20 또는 30인 사원의 이름, 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.

12. 2021년도에 입사한 사원의 이름과 입사일을 출력하시오.
like 연산자와 와일드카드 사용

13. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.

14. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 커미션을 기준으로 내림자순 정렬하여 표시하시오.

15. 이름의 세번째 문자가 "우"인 사원의 이름을 표시하시오.

16. 이름에 "민"와 "김을 모두 포함하고 있는 사원의 이름을 표시하시오.

17. 담당업무가 점원, 또는 영업사원이면서 급여가 550, 350 또는 210이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.

 

 

1번 문제

 

-- 1. 덧셈연산자를 이용하여 모든 사원에 대해서 연봉에 대해 300만원의 급여 인상을 계산한 후 사원의 사번, 이름, 급여, 인상된 연봉을 출력하시오.
SELECT empno 사번, ename 이름, sal 급여, (sal*12)+300 인상된연봉 
    FROM EMP;

 

결과 화면1

 

 

2번 문제

 

-- 2. 사원번호 7999 사람의 업무(JOB)를 '프로그래머'로 수정하시오.
select * from emp;
update emp set job = '프로그래머' where empno=7999;

 

결과 화면2

 

 

 

3번 문제

 

-- 3. emp 테이블 job 속성의 데이터타입을 varchar2(20)으로 수정하시오.
alter table emp modify (job varchar2(20));

 

결과 화면3

 

 

 

4번 문제

 

-- 4. emp 테이블에 부서번호와 dept 테이블의 외래키를 설정하시오.
alter table emp add ( constraint fk_emp foreign key(deptno) references dept(deptno) );
-- emp의 deptno의 타입과 dept의 deptno의 타입이 달라서 오류가 발생하므로 변경 가능한 emp의 deptno의 타입을 변경 후 설정하면 오류 없이 작동된다.
alter table emp modify (deptno char(20));

 

결과 화면4

 

 

 

5번 문제

 

-- 5. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오.
-- 연간 총수입은 월급에 12를 곱한 후 100만원의 상여금을 더해서 계산하시오.
select ename 사원의이름, sal 급여, sal*12+100 연간총수입 from emp
    order by 3 desc;

 

결과 화면5

 

 

 

6번 문제

 

-- 6. 급여가 320만원이 넘는 사원의 이름과 급여를 조회하는데 급여가 많은 것부터 작은 순으로 출력하시오.
select ename 사원의이름, sal 급여 from emp
    where sal > 320
    order by sal desc;

 

결과 화면6

 

 

 

7번 문제

 

-- 7. 사원번호가 7788인 사원의 이름과 부서번호, 부서명을 출력하시오.
select * from emp;
select * from dept;
select a.empno 사원번호, a.ename 사원의이름, b.deptno 부서번호, b.dname 부서명 from emp a, dept b
    where a.deptno = b.deptno
    and a.empno = 7788;

 

결과 화면7

 

 

 

8번 문제

 

-- 8. 급여가 300에서 400 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.
select ename 사원명, sal 급여 from emp
    where sal not between 300 and 400;

 

결과 화면8

 

 

 

9번 문제

 

-- 9. 2020년 2월 20일부터 2022년 12월 31일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
select ename 사원명, job 담당업무, hiredate 입사일 from emp
    where hiredate between '20/02/20' and '22/12/31';

 

결과 화면9

 

 

 

10번 문제

 

-- 10. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 출력하시오.
select * from emp;
select ename 사원명, deptno 부서번호 from emp
    where deptno in(20, 30)
    order by ename desc;

 

결과 화면10

 

 

 

11번 문제

 

-- 11. 사원의 급여가 300에서 450 사이에 포함되고 부서번호가 20 또는 30인 사원의 이름, 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.
select ename 사원명, sal 급여, deptno 부서번호 from emp
    where sal between 300 and 450
    and deptno in(20, 30)
    order by ename asc;

 

결과 화면11

 

 

 

 

12번 문제

 

-- 12. 2021년도에 입사한 사원의 이름과 입사일을 출력하시오.
-- like 연산자와 와일드카드 사용
select ename 사원명, hiredate 입사일 from emp
    where hiredate like '21%';
--    where substr(hiredate, 1, 2) = '21';

 

결과 화면12

 

 

 

13번 문제

 

-- 13. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.
select ename 사원명, job 담당업무 from emp
    where mgr is null;

 

결과 화면13

 

 

 

14번 문제

 

-- 14. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 커미션을 기준으로 내림자순 정렬하여 표시하시오.
select ename 사원명, sal 급여, comm 커미션 from emp
    where comm is not null
    order by comm desc;

 

결과 화면14

 

 

 

15번 문제

 

-- 15. 이름의 세번째 문자가 "우"인 사원의 이름을 표시하시오.
select ename 사원명 from emp
    where substr(ename, 3, 1) = '우';

 

결과 화면15

 

 

 

16번 문제

 

-- 16. 이름에 "민"와 "김을 모두 포함하고 있는 사원의 이름을 표시하시오.
select ename 사원명 from emp
    where ename like '%민%' and ename like '%김%';

 

결과 화면16

 

 

 

17번 문제

 

-- 17. 담당업무가 점원, 또는 영업사원이면서 급여가 550, 350 또는 210이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.
select ename 사원명, job 담당업무, sal 급여 from emp
    where job in ('점원', '영업사원') and sal not in (550, 350, 210);

 

결과 화면17