[SQL/실습] 데이터 검색 - 연습문제 풀어보기 2 (답안)

 

테이블 만들고 튜플 삽입하기

사원(emp) 테이블만들기

use example;

create table emp(
empno int primary key,
ename varchar(10),
job varchar(10),
mgr int,
hiredate datetime,
sal int,
comm int,
deptno varchar(2)
);

 

사원(emp) 테이블에 튜플 삽입

-- 컨트롤+ 쉬프트+ 엔터 > INSERT 한꺼번에 하기 
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7369','SMITH','CLERK','7902','1980-12-17','800',NULL,'20');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7499','ALLEN','SALESMAN','7698','1981-02-20','1600',300,'30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7521','WARD','SALESMAN','7698','1981-02-22','1250',500,'30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7566','JONES','MANAGER','7839','1981-04-02','2975',NULL,'20');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7654','MARTIN','SALESMAN','7698','1981-09-28','1250',1400,'30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7698','BLAKE','MANAGER','7839','1981-05-01','2850',NULL,'30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7782','CLARK','MANAGER','7839','1981-06-09','2450',NULL,'10');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7788','SCOTT','ANALYST','7566','1982-12-09','3000',NULL,'20');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7839','KING','PRESIDENT',null,'1981-11-17','5000',NULL,'10');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7876','ADAMS','CLERK','7788','1983-01-12','1100',NULL,'20');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7900','JAMES','CLERK','7698','1981-12-03','950',NULL,'30');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7902','FORD','ANALYST','7566','1981-12-03','3000',NULL,'20');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values('7934','MILLER','CLERK','7782','1982-01-23','1300',NULL,'10');

 

사원(emp) 테이블 조회해서 insert 여부 확인

select * from emp;

 

부서(dept) 테이블만들기

create table dept(
deptno varchar(2),
dname varchar(15),
loc varchar(15)
);

 

부서(dept) 테이블에 튜플 삽입

insert into dept(deptno,dname,loc) values('10','ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values('20','RESEARCH','DALLAS');
insert into dept(deptno,dname,loc) values('30','SALES','CHICAGO');
insert into dept(deptno,dname,loc) values('40','OPERATIONS','BOSTON');
insert into dept(deptno,dname,loc) values('50','DEVELOPER','KOREA');

 

부서(dept) 테이블 조회해서 insert 여부 확인

SELECT * FROM DEPT;

 

답안

 

-- 1. 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을 출력하세요

select empno, ename, sal
from emp
where deptno = '10'; 

-- 2. 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하세요.

select ename, hiredate, deptno
from emp
where empno='7369';

-- 3. 이름이 ALLEN인 사람의 모든 정보를 출력하세요.

select * 
from emp 
where ename = 'allen';

-- 4. 입사일이 83/01/12인 사람의 이름, 부서번호,월급을 출력하세요.

select ename, deptno, sal
from emp
where hiredate = '1983-01-12'
;

-- 5. 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하세요.

select *
from emp
where job!='manager';

--  6. 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든 정보를 출력하세요.

select *
from emp
where ename>'K%';

-- 7. 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하세요.

select ename, sal, deptno
from emp
-- where hiredate >= '1981-04-02' 
-- and hiredate <= '1982-12-09'
where hiredate between '1981-04-02' and '1981-12-09'
;

-- 8. 이름 중 S자가 들어가 있는 사람만의 모든 정보를 출력하세요.

select *
from emp
where ename like '%S%'
;

-- 9. 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를 출력하세요(단 이름은 전체 5자리)

select *
from emp
where ename like 'S___T'
;

-- 10. 첫 번째 문자는 관계없고 두 번째 문자가 A인 사람의 정보를 출력하세요.

select *
from emp
where ename LIKE '_A%'
;

 

 

-- 11. 급여가 많은 순으로 SORT(정렬)하세요.

select *
from emp
order by sal desc
;

-- 12. 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호, 이름, 부서번호를 출력하세요.

select empno, ename, deptno
from emp  
where ename like 'K%'
or deptno='30'
;

-- 13. 급여가 1500이상이고, 부서번호가 30번인 사원 중 직업이 MANAGER인 사람의 정보를 출력하세요.

select *
from emp
where sal >= '1500'
and deptno='30'
and job='manager'
;

-- 14. 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를 출력하세요.

select *
from emp
where not empno between 7654 and 7782
;

-- 15. 이름이 B와 J사이의 모든 사원의 정보를 출력하세요.

select *
from emp
where ename >= 'B%' and ename <='J%'
;

-- 16. 입사일이 81년 이외에 입사한 모든 정보를 출력하세요.

select *
from emp
where not hiredate between '1981-01-01' and '1981-12-31'
;

-- 17. 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하세요.

select *
from emp
where job in ('MANAGER','SALESMAN')
;

-- 18. 부서번호와 20,30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하세요.

select ename, empno, deptno
from emp
WHERE DEPTNO NOT IN('20','30')
;

-- 19. 입사일이 81년도인 사람의 모든 정보를 출력하세요.

select *
from emp
where hiredate between '1981-01-01' and '1981-12-31'
;

-- 20. 커미션이 NULL인 사람의 정보를 출력하세요

select *
from emp
where comm is null
;

-- 21. 커미션이 NULL이 아닌 사람의 모든 정보를 출력하세요.

select *
from emp
where comm is not null
;

-- 22. 부서가 30번 부서이고, 급여가 1500 이상인 사람의 이름, 부서, 월급을 출력하세요.

select ename, deptno, sal
from emp 
where deptno='30' and sal >=1500
;

-- 23. 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호, 이름, 부서번호를 출력하세요.

select empno, ename, deptno
from emp
where ename like 'K%' or deptno='30'
;

 

-- 서브쿼리
--  24. 'ALLEN'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하세요.

SELECT ENAME, DNAME, SAL, JOB 
FROM EMP E 
JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO 
WHERE JOB = (select JOB 
from emp  
where ename='ALLEN' 
) 
;

 

 

-- 25. JONES가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일, 급여를 출력하세요.

select empno, ename, hiredate, sal 
from emp   
where deptno=(select deptno 
from emp 
where ename='JONES') 
;

TIP) from emp e, dept d where e.deptno=d.deptno 이렇게도 쓸 수 있음.

 

 

-- 27. 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명, 지역, 급여를 급여가 많은 순으로 출력하세요.

select empno, ename, dname, loc, sal
from emp e 
join dept d
on e.deptno=d.deptno
where e.deptno='10'
order by sal desc;

 

 

--  28. 'MARTIN'이나 'SCOTT'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하세요.

select empno, ename, sal
from emp
where sal in (select sal
from emp
where ename in ('MARTIN','SCOTT')
);

-- 다중 결과가 출력되기 때문에 in을 쓰면 안됨
-- 단일인지 다중인지 잘 따져봐야한다 
-- 단일은 => <= ..등등 사용가능
-- 다중은 in any all 을 써야함

 

 

-- 29. 부서번호가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하세요.

select *
from emp
where deptno='30'
; -- 부서번호가 30번인 사원

select max(sal) from emp where deptno='30';
-- 부서번호가 30번인 사원 중 가장 높은 연봉의 사람 

select empno, ename, sal
from emp
where sal > (select max(sal)
from emp
where deptno='30')
; -- 결과값 도출

 

 

-- 30. 사원중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우, 평균 금액을 구하세요.

select max(sal+comm) , min(sal+comm) , avg(sal+comm)
from emp;

 

 

-- 31. 부서별로 급여합계를 구하세요.

select sum(sal) 
from emp 
group by deptno 
;

 

 

-- 32. 급여가 3000이상이면, 급여+급여의 15%의 격려금을, 급여가 2000이상이면, 급여+급여의 10%의 격려금을,급여가 1000이상이면, 급여+급여의 5%의 격려금을, 그렇지 않으면 급여를 구하여, 이름, 직업, 급여,격려금을 표시하시오.
-- select case 
when 조건 then 결과
when 조건 then 결과
else 그렇지않으면 when 조건에 해당안되는 경우 반환값
end as 속성명 

select ename, job, sal, case  
when sal >= 3000 then sal+sal*0.15 
when sal >= 2000 then sal+sal*0.1 
when sal >= 1000 then sal+sal*0.05 
else sal 
end as INCENTIVE 
from emp ; 

 

 

-- 33. 'MARTIN'과 같은 매니저와 일하는 이름, 직업, 급여, 부서명, 지역을 구하세요.
-- select mgr from emp where ename='martin'; -- 얘가 단일이라서 = 기호 쓸 수 있음

select ename, job, sal, dname, loc
from emp e 
join dept d
on e.deptno=d.deptno
where mgr = (select mgr from emp where ename='martin')
;

 

 

-- 34. 부서명이 'RESEARCH'인 사람의 이름, 직업, 급여,부서명을 표시하시오.

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

 

 

-- 35. 각 부서별 평균 급여를 구하고, 그 중에서 평균 급여가 가장 적은 부서 의 평균 급여보다 적게 받는 사원들의 부서명, 지역, 급여를 구하세요.
-- 인라인뷰
-- 함수안에 함수를 사용할 수 없다 ex) min(avg(sal)) 불가능
-- min 함수는 괄호안에 속성명이 들어가야함 

-- select 속성명 from 테이블명 

select dname, loc, sal
from emp e
join dept d
on e.deptno=d.deptno
where sal > (select min(avgsal)
from ( select avg(sal) avgsal
from emp 
group by deptno) as avg
)
;

select min(avgsal)
from ( select avg(sal) avgsal
from emp 
group by deptno) as avg; -- 인라인뷰는 별칭이 꼭 필요함 '테이블명' 

select avg(sal) avgsal
from emp
group by deptno;

 

 

-- 36. 'BLAKE'와 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데 'BLAKE'는 빼고 출력하라.

select deptno from emp where ename = 'blake'; 
select ename, hiredate from emp  
where deptno=(select deptno from emp where ename = 'blake') 
and ename != 'blake'; 

 

 

-- 37. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고있는 사원의 사원번호와 이름을 출력하라.

select * from emp where deptno in('20','30','20','30'); 
select deptno from emp where ename like '%T%'; 

select * from emp  
where deptno in (select deptno from emp where ename like '%T%'); 

-- 38. 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름, 급여를 출력하라.

select avg(sal) from emp;


select deptno from emp where sal > (select avg(sal) from emp) and ename like '%S%';
-- 결과 갯수가 다중으로 나왔기 때문에 다중으로 처리해줘야함

select empno, ename, sal from emp where deptno in(
select deptno from emp where sal > (select avg(sal) from emp) 
and ename like '%S%')
;

 

-- 39. 커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라.

select ename , sal, deptno 
from emp 
where deptno in (select deptno from emp where comm>0) 
and sal in ( select sal from emp where comm>0); 

 

 

-- 40. 직업명이 'PRESIDENT' 이면 'A', 직업이 'ANALYST' 이면 'B', 직업이 'MANAGER' 이면 'C', 직업이 'SALESMAN' 이면 'D', 직업이 'CLEARK' 이면 'E' 로 표시하시오.

select case  
when job= 'president' then 'A' 
when job= 'analyst' then 'B' 
when job= 'manager' then 'C' 
when job= 'salesman' then 'D' 
ELSE 'E' 
END AS JOBABB 
FROM EMP;

 

 

-- 41. 10번 부서중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명,입사일, 지역을 출력하라.

SELECT * FROM EMP WHERE DEPTNO = '10';
SELECT * FROM EMP WHERE DEPTNO= '30';

select empno, ename, dname, hiredate, loc
from emp e
join dept d 
on e.deptno=d.deptno 
where JOB IN (
SELECT job FROM EMP WHERE DEPTNO = '10'
)
AND JOB NOT IN (
SELECT job FROM EMP WHERE DEPTNO= '30'
);

 

 

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

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

 

 

-- 43. 급여가 30번 부서의 최저 급여보다 낮은 사원의 사원번호, 이름, 급여를 출력하라.

select empno, ename, sal  
from emp 
where sal < (select min(sal) from emp where deptno='30') 
; 

 

 

-- 44. 사원 중에서 입사일이 가장 빠른 사원의 사원번호, 이름, 입사일, 부서명을 출력하세요.

select empno, ename, hiredate, dname
from emp e
join dept d 
on e.deptno=d.deptno
where hiredate=(select min(hiredate) from emp);

 

 

-- 45. 평균 연봉보다 많이 받는 사원들의 사원번호, 이름, 연봉을 연봉이 높은 순으로 정렬하여 출력하세요.
-- (연봉은 sal*12+comm으로 계산)
-- IFNULL사용(null에 0이라고 임의로 채워주는것 // 빈칸을 빈칸이 아니게)

select ifnull(comm,0) -- comm이 null이면 0으로 채우라는 뜻
from emp;

select  avg(sal * 12 + ifnull(comm,0)) -- comm자리에 ifnull
from emp;

select empno, ename, sal * 12 + ifnull(comm,0) anuincom
from emp
where sal * 12 + ifnull(comm,0) >(select  avg(sal * 12 + ifnull(comm,0))
from emp)
order by anuincom desc;

 

 

-- 46. EMP와 DEPT TABLE을 JOIN하여 부서 번호, 부서명, 이름, 급여를 출력하라.

select empno, dname, ename, sal 
from emp e  
join dept d 
on d.deptno = e.deptno 
; 

 

 

-- 47. 이름이 'ALLEN'인 사원의 부서명을 출력하라.

select dname from emp e join dept d on d.deptno=e.deptno 
where ename='allen';

 

 

-- 48. DEPT Table 에는 존재하는 부서코드이지만 해당부서에 근무하는 사람이 존재하지 않는 경우의 결과를 출력하라.
-- exists 는 서브쿼리에 존재하는것을 다 나타내라 라는 의미

select * 
from dept 
where deptno not in (select deptno from emp);