[MySQL로 배우는 데이터베이스 개론과 실습] Chapter 3. 연습문제 풀이 - 8

8. [사원 데이터베이스] 다음은 demo_scott.sql 스크립트에 저장된 사원 데이터베이스이다. 다음 질문에 대해 SQL 문을 작성하시오. 이 때 Dept는 부서 테이블로 deptno(부서번호), dname(부서이름), loc(위치)로 구성 되어 있고, Emp는 사원 테이블로 empno(사원번호), ename(사원이름), job(업무), mgr(팀장번호), hiredate(고용날짜), sal(급여), comm(커미션금액), dptno(부서번호)로 구성되어 있다. 밑줄 친 속성 은 기본키고 Emp의 deptno는 Dept의 deptno를 참조하는 외래키이다.

Dept(deptno INTEGER(2), dname VARCHAR(14), loc VARCHAR(13))

Emp(empno INTEGER(4), ename VARCHAR(10), job VARCHAR(9), mgr INTEGER(4), hiredate DATE, 
	sal INTEGER(7,2), comm INTEGER(7,2), deptno INTEGER(2))

(1) 사원의 이름과 직위를 출력하시오. 단, 사원의 이름은 ‘사원이름’, 직위는 ‘사원직위’ 머리글이
나오도록 출력한다.

code)

SELECT ename AS "사원이름", dname AS "사원직위"
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno;

result)

(2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력하시오.

code)

SELECT ename AS "사원 이름", sal AS "급여"
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno AND
	  Dept.deptno = 30;

result)

(3) 사원 번호와 이름, 현재 급여와 10% 인상된 급여(열 이름은 ‘인상된 급여’)를 출력하시오. 단,
사원 번호순으로 출력한다. 증가된 급여분에 대한 열 이름은 ‘증가액’으로 한다.

code)

SELECT empno, ename, sal, sal*1.1 AS "인상된 급여", sal*0.1 AS "증가액"
FROM Emp
ORDER BY empno;

result)

(4) ‘S’로 시작하는 모든 사원과 부서번호를 출력하시오.

code)

SELECT Emp.ename, Dept.deptno
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno AND
	  Emp.ename LIKE "S%";

result)

(5) 모든 사원의 최대 및 최소 급여, 합계 및 평균 급여를 출력하시오. 열 이름은 각각 MAX, MIN,
SUM, AVG로 한다. 단, 소수점 이하는 반올림하여 정수로 출력한다.

code)

SELECT MAX(sal) AS "MAX", MIN(sal) AS "MIN", SUM(sal) AS "SUM", ROUND(AVG(sal)) AS "AVG"
FROM Emp;

result)

(6) 업무이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오. 열 이름은 각각 ‘업무’와
‘업무별 사원수’로 한다.

code)

SELECT job AS "업무", COUNT(empno) AS "업무별 사원수"
FROM Emp
GROUP BY job;

result)

(7) 사원의 최대 급여와 최소 급여의 차액을 출력하시오.

code)

SELECT MAX(sal) - MIN(sal) AS "차액"
FROM Emp;

result)

(8) 30번 부서의 구성원 수와 사원들 급여의 합계와 평균을 출력하시오.

code)

SELECT COUNT(empno) AS "구성원 수", SUM(sal) AS "급여 합계", ROUND(AVG(sal)) AS "급여 평균"
FROM Emp, Dept
WHERE Dept.deptno = 30;

result)

(9) 평균급여가 가장 높은 부서의 번호를 출력하시오.

code)

SELECT deptno AS "부서 번호" , ROUND(AVG(sal)) AS "평균 급여"
FROM Emp
GROUP BY deptno
ORDER BY "평균 급여" DESC
LIMIT 1;

result)

(10) 세일즈맨을 제외하고, 각 업무별 사원들의 총 급여가 3000 이상인 각 업무에 대해 서, 업무명
과 각 업무별 평균 급여를 출력하되, 평균급여의 내림차순으로 출력하시오.

code)

SELECT job AS "업무명", ROUND(AVG(sal)) AS "평균 급여"
FROM Emp
WHERE job != "SALESMAN"
GROUP BY deptno
HAVING SUM(sal) >= 3000
ORDER BY "평균 급여" DESC;

result)

(11) 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오.

code)

SELECT COUNT(empno) AS "팀장이 있는 사원의 수"
FROM Emp
WHERE NOT mgr IS NULL;

result)

(12) Emp 테이블에서 이름, 급여, 커미션 금액, 총액(sal + comm)을 구하여 총액이 많 은 순서대로
출력하시오. 단, 커미션이 NULL인 사람은 제외한다.

code)

SELECT ename, sal, comm, sal+comm AS "총액"
FROM Emp
WHERE NOT comm IS NULL
ORDER BY "총액" DESC;

result)

(13) 각 부서별로 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수 를 출력하
시오.

code)

SELECT deptno AS "부서번호", job AS "업무명", COUNT(*) AS "인원수"
FROM Emp
GROUP BY deptno, job;

result)

(14) 사원이 한 명도 없는 부서의 이름을 출력하시오.

code)

SELECT DISTINCT dname
FROM Dept, Emp
WHERE Dept.deptno NOT IN (Emp.deptno);

result)

(15) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하시오.

code)

SELECT job, COUNT(empno) AS "인원수"
FROM Emp
GROUP BY job
HAVING COUNT(empno) >= 4;

result)

(16) 사원번호가 7400 이상 7600 이하인 사원의 이름을 출력하시오.

code)

SELECT ename
FROM Emp
WHERE empno BETWEEN 7400 AND 7600;

result)

(17) 사원의 이름과 사원의 부서를 출력하시오.

code)

- solution 1

SELECT ename AS "사원명", dname AS "부서명"
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno;

- solution 2

SELECT ename AS "사원명" ,(SELECT dname
			  FROM Dept
			  WHERE Dept.deptno=Emp.deptno) AS "부서명"
FROM Emp;

result)

(18) 사원의 이름과 팀장의 이름을 출력하시오.

code)

SELECT e1.ename AS "사원 이름", e2.ename "팀장 이름"
FROM Emp e1, Emp e2
WHERE e1.mgr = e2.mgr;

result)

중략

(19) 사원 SCOTT 보다 급여를 많이 받는 사람의 이름을 출력하시오.

code)

SELECT ename AS "SCOTT보다 급여를 많이 받는 사람"
FROM Emp
WHERE sal > (SELECT sal
			 FROM Emp
             WHERE ename = "SCOTT");

result)

(20) 사원 SCOTT가 일하는 부서번호 혹은 DALLAS에 있는 부서번호를 출력하시오.

code)

SELECT DISTINCT Emp.deptno 
FROM Emp, Dept
WHERE ename = "SCOTT" OR
	  Emp.deptno = Dept.deptno AND
      Dept.loc = "DALLAS";

result)