1. WHERE절
--문제1 ) EMP 테이블에서 JOB이 Manager,Clerk,Analyst가 아닌 사원의 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하여라
SELECT empno, ename, job, sal, deptno FROM emp WHERE job NOT IN('MANAGER', 'CLERK', 'ANALYST');
--문제 2) 모든 사원의 이름 및 급여, 급여에 300을 더한 값을 출력하시오.
SELECT ename, sal, (sal +300) FROM emp;
--문제 3) emp 테이블에서 이름, 급여, 보너스, 연봉을 출력하시오(출력값 변환).
select ename, sal, comm, nvl(sal12 + comm, sal12) "연봉" from emp;
--문제 4) emp 테이블에서 ENAME을 NAME 로, SAL을 SALARY로 출력하시오.
select ename "NAME", sal "SALARY" from emp;
--문제 5) EMP 테이블에서 ENAME를 '성 명' 으로 sal 12 를 '급 여'로 출력하시오.
SELECT ename "성 명", sal12 "급 여" FROM emp;
--문제 6) EMP 테이블에서 이름과 업무를 "KING is a PRESIDENT" 형식으로 출력하시오( ||' '|| : 사용방법).
SELECT ename||' is a '|| job FROM emp;
--문제 7) EMP 테이블에서 이름과 연봉을 "KING : 1 Year salary = 60000" 형식으로 출력하시오( ||' '|| : 사용방법).
SELECT ename||' : 1 Year salary = '||( (sal*12)+nvl(comm,0) ) "이름 및 연봉" FROM emp;
--문제 8) EMP테이블에서 사원이름순으로 사원번호, 이름, 업무, 부서번호를 조회하시오(ORDER BY : 정렬 출력, 기본 오름차순).
SELECT empno, ename, job, deptno FROM emp ORDER BY ename;
--문제 9) EMP테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자, 부서번호를 출력하라(ORDER BY : 정렬 출력 + DESC; 내림차순 정렬).
SELECT empno, ename, job, sal, hiredate, deptno FROM emp ORDER BY hiredate DESC;
--문제 10) EMP 테이블에서 SMITH 사원의 모든 정보를 조회하시오(조건식 : WHERE 절 사용방법).
SELECT * FROM emp WHERE ename = 'SMITH';
--문제 11) EMP 테이블에서 급여가 3000이상인 사원의 사원번호, 이름, 담당업무, 급여를 출력하라(조건식 : WHERE 절 사용방법).
SELECT empno, ename, job, sal FROM emp WHERE sal >= 3000;
--문제 12) EMP 테이블에서 담당업무가 Manager인 사원의 정보를 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라(조건식 : WHERE 절 사용방법).
SELECT empno, ename, job, sal, deptno FROM emp WHERE job = 'MANAGER';
--문제 13) EMP 테이블에서 1982년 1월 1일 이후에 입사한 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라(조건식 : WHERE 절 사용방법).
SELECT empno, ename, sal, hiredate, deptno FROM emp
WHERE to_char(hiredate,'yyyy-mm-dd') > '1982-01-01';
--문제 14) EMP 테이블에서 급여가 1300에서 1500사이의 사원의 성명, 담당업무, 급여, 부서번호를 출력하라.
SELECT ename, job, sal, deptno FROM emp WHERE sal BETWEEN 1300 AND 1500;
--문제 15) EMP 테이블에서 사원번호가 7902, 7788, 7566 인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자를 출력하여라.
SELECT empno, ename, job, sal, hiredate FROM emp WHERE empno IN('7902', '7788', '7566');
--문제 16) EMP 테이블에서 입사일자가 1982년도에 입사한 사원의 사번, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE hiredate LIKE '%82%';
SELECT empno, ename, job, sal, hiredate, deptno FROM emp
WHERE to_char(hiredate,'yyyy-mm-dd') between '1982-01-01' and '1982-12-31';
--문제 17) EMP 테이블에서 보너스가 NULL인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE comm IS NULL;
--문제 18) EMP 테이블에서 입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름,업무,입사일을 출력하는 SELECT 문장을 작성하시오. 단 입사일 순으로 출력하시오.
SELECT ename, job, hiredate FROM emp
WHERE to_char(hiredate,'yy/mm/dd')
BETWEEN '81/02/20' AND '81/05/01'
ORDER BY hiredate;
--문제 19) EMP 테이블에서 부서번호가 10,20인 사원의 모든 정보를 출력하는 SELECT 문장을 작성하시오. 단 이름순으로 정렬하시오.
SELECT * FROM emp WHERE deptno IN(10, 20) ORDER BY ename;
--문제 20) EMP테이블에서 사원의 입사일이 1981년도 2월인 사원들의 모든 정보를 조회하시오.
SELECT * FROM emp WHERE hiredate LIKE '%81/02%';
SELECT * FROM emp WHERE to_char(hiredate,'yy/mm/dd')
BETWEEN '81/02/01' and '81/02/28';
--문제 23). EMP 테이블에서 급여가 1100이상이고 JOB이 Manager인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE sal >=1100 AND job = 'MANAGER';
--문제 24). 업무가 PRESIDENT이고 급여가 1500이상이거나,업무가 SALESMAN인 사원의 사원번호, 이름, 업무, 급여를 출력하여라.
SELECT empno, ename, job, sal FROM emp WHERE ( job = 'PRESIDENT' AND sal >= 1500 ) OR job='SALESMAN';
--문제 25) EMP 테이블에서 급여가 1500이상이고, 부서번호가 10 또는30인 사원의 이름과 급여를 출력하는 SELECT 문장을 작성하여라. 단 HEADING을 Employee과 Monthly Salary로 출력하여라.
SELECT ename "Employee", sal "Monthly Salary" FROM emp WHERE sal >=1500 AND deptno IN(10, 30);
--문제 26) SCOTT이 소유하고 있는 EMP TABLE에서 사원번호(empno), 이름(ename), 급여(sal), 담당업무(job)의 값을 출력하시오.
SELECT empno, ename, sal, job FROM EMP where ename = 'SCOTT';
--문제 27) EMP 테이블에서 ename이 'SMITH' 사원의 모든 정보를 조회하시오(조건식 : WHERE 절 사용방법).
SELECT * FROM WHERE ename = 'SMITH'
--문제 28) EMP 테이블에서 급여가 3000이상인 사원의 사원번호, 이름, 담당업무, 급여를 출력하라(조건식 : WHERE 절 사용방법).
SELECT empno, ename, job, sal WHERE SAL >= 3000;
--문제 29) EMP 테이블에서 관리자사원번호(mgr)가 7839인 사원의 사원번호, 이름, 담당업무, 부서번호를 출력하시오.
select empno 사원번호,
ename 이름,
job 담당업무,
deptno 부서번호
from emp
where mgr = 7839;
--문제 30) EMP 테이블에서 연봉인 가장 높은 순서대로 사원의 사원번호, 이름, 담당업무, 부서번호, 연봉을 출력하시오.
select empno 사원번호,
ename 이름,
job 담당업무,
deptno 부서번호,
((sal*12)+nvl(comm,0)) 연봉
from emp
order by 연봉;
2. JOIN
--1) EMP 테이블에서 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의
--사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
SELECT empno, ename, job, hiredate, sal
FROM emp
WHERE job = ( SELECT job FROM emp WHERE empno='7521')
AND sal > (SELECT sal FROM emp WHERE empno=7934);
--2) EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
SELECT empno, job, sal, deptno
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);
--3) 사원들의 부서코드가 부서테이블의 부서코드와 일치하지 않는 부서코드를 갖는 사원의 모든 정보를 조회하시오.
SELECT
FROM emp
WHERE NOT EXISTS ( SELECT FROM emp e, dept d WHERE e.deptno = d.deptno );
--4)EMP 테이블에서 Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하시오.
SELECT deptno "부서명", ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'BLAKE');
--5)EMP 테이블에서 평균 급여 이상을 받는 모든 종업원에 대해서 종업원 번호와 이름을 출력하는 SELECT문을 작성하시오.
--단 급여가 많은 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE sal >= (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
--6) EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
--단 사원번호 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%')
ORDER BY empno;
--7) EMP 테이블에서 부서 위치가 Dallas인 모든 사원에 대해 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
SELECT e.ename, e.job, e.sal
FROM emp e, (SELECT deptno FROM dept WHERE loc='DALLAS') d
WHERE (e.deptno = d.deptno);
--8) EMP 테이블에서 King에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하시오.
SELECT ename, sal, mgr
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
--9) EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
--단 업무별, 월급이 많은 순으로 출력하여라.
SELECT ename, job, deptno, sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='JONES') OR sal > ( SELECT sal FROM emp WHERE ename='FORD')
ORDER BY job, sal ASC;
--10) EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
SELECT e.ename, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = (SELECT loc FROM dept WHERE loc = 'CHICAGO');
SELECT e.ename, e.job, e.deptno
FROM emp e, (SELECT deptno FROM dept WHERE loc = 'CHICAGO') d
WHERE e.deptno = d.deptno;
--11) EMP 테이블에서 FORD와 업무 및 월급이 같은 사원의 모든 정보를 출력하는 SELECT문을 작성하시오.(결과에서 FORD는 제외)
SELECT *
FROM emp
WHERE ename NOT IN(SELECT ename FROM emp WHERE ename='FORD')
AND job = (SELECT job FROM emp WHERE ename='FORD')
AND sal = (SELECT sal FROM emp WHERE ename='FORD');
--12) EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
SELECT e.ename, e.job, e.deptno
FROM emp e, (SELECT deptno FROM dept WHERE loc = 'CHICAGO') d
WHERE e.deptno = d.deptno;
SELECT ename, job
FROM emp
WHERE job in (SELECT job
FROM emp
WHERE deptno = ( SELECT deptno FROM dept WHERE loc = 'CHICAGO') );
--13) EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
SELECT empno, job, sal, deptno
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);
--14) EMP 테이블에서 업무별 평균급여 중 가장 낮은 평균 급여를 갖는 업무명과 그 평균 급여를 출력하여라.
SELECT job, AVG(SAL)
FROM emp
GROUP BY job
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL)) FROM emp GROUP BY job );
--15) EMP 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라.
SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY job )
ORDER BY JOB;
--16) emp와 dept 테이블에서 업무가 manager인 사원의 이름, 업무, 부서명, 근무지를 출력하여라. (Inline View 를 사용하자)
SELECT e.ename, e.job, d.dname, d.loc
FROM (SELECT * FROM emp WHERE JOB='MANAGER') e, dept d
WHERE e.deptno = d.deptno;
--17) EMP 테이블에서 30번 부서원 중 최저급여를 받는 사원을 제외한 나머지 사원들의 모든 정보를 출력하는 SELECT문을 작성하시오.
SELECT *
FROM emp
WHERE NOT((deptno, sal) IN(SELECT deptno, MIN(sal) FROM emp GROUP BY deptno )) AND deptno = 30;
--18) EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를 출력하는 SELECT문을 작성하시오.(말단사원: 다른 사원을 관리하지 않는 사원
SELECT empno, ename, job, deptno
FROM emp
WHERE empno NOT IN(SELECT DISTINCT(nvl(mgr,0)) FROM emp)
--19) EMP 테이블에서 사원번호, 이름, 업무, 급여, 급여의 등급을 출력하되 3등급 이상인 사원의 정보만을 출력하세요.
(emp와 salgrade 테이블을 이용);
SELECT e.empno, e.ename, e.job, e.sal, s.grade
FROM emp e, salgrade s
WHERE ( s.losal <= e.sal ) AND ( e.sal <= s.hisal)
and grade >= 3;
--20) 부서번호, 부서에 속한 직원수, 부서명, 도시명을 출력하세요.직원수가 5명이상인 부서만 출력하세요.
select e.deptno, e.empcnt, d.dname, d.loc
from (select deptno,count() empcnt
from emp
group by deptno
having count() >= 5 ) e,
dept d
where e.deptno = d.deptno;
--21) Employees 테이블에서 업무(Job_id)별로 직원수가 많은 상위 5개 업무의
--업무코드, 직원수, 업무명, 최소급여, 최대급여 를 직원수가 많은 순서대로 출력하세요
select e.job_id, e.empcnt, j.job_title, j.min_salary, j.max_salary
from (select job_id,count() empcnt
from employees
group by job_id
order by count() desc ) e,
jobs j
where rownum <= 5
and e.job_id = j.job_id
3. Join_Subquery
--Join 연습
--1) EMP 테이블에서 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의
--사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
SELECT empno, ename, job, hiredate, sal
FROM emp
WHERE job = ( SELECT job FROM emp WHERE empno='7521')
AND sal > (SELECT sal FROM emp WHERE empno=7934);
--2) EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
SELECT empno, job, sal, deptno
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);
--3) 사원들의 부서코드가 부서테이블의 부서코드와 일치하지 않는 부서코드를 갖는 사원의 모든 정보를 조회하시오.
SELECT
FROM emp
WHERE NOT EXISTS ( SELECT FROM emp e, dept d WHERE e.deptno = d.deptno );
--4)EMP 테이블에서 Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하시오.
SELECT deptno "부서명", ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'BLAKE');
--5)EMP 테이블에서 평균 급여 이상을 받는 모든 종업원에 대해서 종업원 번호와 이름을 출력하는 SELECT문을 작성하시오.
--단 급여가 많은 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE sal >= (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
--6) EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
--단 사원번호 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%')
ORDER BY empno;
--7) EMP 테이블에서 부서 위치가 Dallas인 모든 사원에 대해 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
SELECT e.ename, e.job, e.sal
FROM emp e, (SELECT deptno FROM dept WHERE loc='DALLAS') d
WHERE e.deptno = d.deptno;
--8) EMP 테이블에서 King에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하시오.
SELECT ename, sal, mgr
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
--9) EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
--단 업무별, 월급이 많은 순으로 출력하여라.
SELECT ename, job, deptno, sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='JONES')
OR sal > ( SELECT sal FROM emp WHERE ename='FORD')
ORDER BY job, sal desc;
--10) EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
--Nested Query
SELECT e.ename, e.job, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc = (SELECT loc FROM dept WHERE loc = 'CHICAGO');
SELECT ename, job
FROM emp
WHERE job in (SELECT job
FROM emp
WHERE deptno = ( SELECT deptno FROM dept WHERE loc = 'CHICAGO') );
--Inline View
SELECT e.ename, e.job, e.deptno
FROM emp e, (SELECT deptno FROM dept WHERE loc = 'CHICAGO') d
WHERE e.deptno = d.deptno;
--11) EMP 테이블에서 FORD와 업무 및 월급이 같은 사원의 모든 정보를 출력하는 SELECT문을 작성하시오.
--(결과에서 FORD는 제외)
SELECT *
FROM emp
WHERE ename NOT IN(SELECT ename FROM emp WHERE ename='FORD')
AND job = (SELECT job FROM emp WHERE ename='FORD')
AND sal = (SELECT sal FROM emp WHERE ename='FORD');
--12) EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
SELECT ename, job
FROM emp
WHERE job in (SELECT job
FROM emp
WHERE deptno = ( SELECT deptno FROM dept WHERE loc = 'CHICAGO') );
--13) EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
SELECT empno, job, sal, deptno
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);
--문제 14) EMP 테이블에서 업무별 평균급여 중 가장 낮은 평균 급여를 갖는 업무명과 그 평균 급여를 출력하여라.
SELECT job, AVG(SAL)
FROM emp
GROUP BY job
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL)) FROM emp GROUP BY job );
--문제14) EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를 출력하는 SELECT문을 작성하시오.
--(말단사원: 다른 사원을 관리하지 않는 사원)
SELECT empno, ename, job, deptno
FROM emp
WHERE empno NOT IN(SELECT DISTINCT(nvl(mgr,0)) FROM emp)
4. Function
--1. emp 테이블에 존재하는 모든 사원들의 인원수, 급여의 평균, 최고액, 최저액을 구하시오.
--급여평균은 반올림하세요.
select count(empno), round(avg(sal)), max(sal), min(sal) from emp;
--2. emp 테이블에서 업무가 SALESMAN인 사원들의 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
select count(empno), round(avg(sal)), max(sal), min(sal)
from emp
where job = 'SALESMAN';
--3. emp 테이블에서 사원들이 속해 있는 부서의 갯수와 업무의 갯수를 출력하여라.
--단 중복된 데이터는 제거해야 합니다.
select count(distinct deptno), count(distinct job) from emp;
--4. 각 부서별로 그룹지어 "부서번호", "인원수", "평균급여", "최저급여", "최고급여", "급여합" 을 구하여
--급여의 합이 많은 순으로 출력하세요.
SELECT deptno "부서번호", COUNT(deptno) "인원수",
AVG(sal) "평균급여", MIN(sal) "최저급여",
MAX(sal) "최대급여", SUM(SAL) "급여합"
FROM emp GROUP BY deptno ORDER BY SUM(sal) DESC;
--4-1. 각 부서별로 그룹지어 "부서번호","부서명", "인원수", "평균급여", "최저급여", "최고급여", "급여합" 을 구하여
--급여의 합이 많은 순으로 출력하세요.
SELECT e.deptno "부서번호", dname "부서명", COUNT(e.deptno) "인원수",
round(AVG(sal)) "평균급여", MIN(sal) "최저급여",
MAX(sal) "최대급여", SUM(SAL) "급여합"
FROM emp e, dept d
where e.deptno = d.deptno
GROUP BY e.deptno,dname ORDER BY SUM(sal) DESC;
--5. 각 업무별로 그룹지어 "업무", "인원수", "평균급여", "최저급여", "최고급여", "급여합" 을 구하여
--급여의 합이 많은 순으로 출력하세요.
SELECT job "업무", COUNT(deptno) "인원수",
round(AVG(sal)) "평균급여", MIN(sal) "최저급여",
MAX(sal) "최대급여", SUM(SAL) "급여합"
FROM emp GROUP BY job ORDER BY SUM(sal) DESC;
-- 6. EMP 테이블에 등록되어 있는 인원수, 보너스가 NULL이 아닌 인원수, 보너스의 평균, 등록되어
--있는 부서의 수를 구하여 출력하세요.
SELECT COUNT(*), COUNT(distinct comm), AVG(comm), COUNT(DISTINCT(deptno)) FROM emp;
--7. emp 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하여라.
SELECT deptno "부서번호", COUNT(deptno) "인원수", SUM(SAL) "급여합"
FROM emp GROUP BY deptno
having count(deptno) > 4
ORDER BY SUM(sal) DESC;
--8) EMP 테이블에서 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의
--사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
SELECT empno, ename, job, hiredate, sal
FROM emp
WHERE job = ( SELECT job FROM emp WHERE empno='7521')
AND sal > (SELECT sal FROM emp WHERE empno=7934);
--9) EMP 테이블에서 평균급여보다 더 많은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
SELECT empno, job, sal, deptno
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
--10)EMP 테이블에서 Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하시오.
SELECT deptno "부서명", ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'BLAKE');
--11)EMP 테이블에서 평균 급여 이하를 받는 모든 종업원에 대해서 종업원 번호와 이름을 출력하는 SELECT문을 작성하시오.
--단 급여가 많은 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE sal <= (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
--12) EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
--단 사원번호 순으로 출력하여라.
SELECT empno, ename, sal
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%')
ORDER BY empno;
--13) EMP 테이블에서 부서 위치가 Dallas인 모든 사원에 대해 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
SELECT e.ename, e.job, e.sal
FROM emp e, (SELECT deptno FROM dept WHERE loc='DALLAS') d
WHERE e.deptno = d.deptno;
--14) EMP 테이블에서 King에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하시오.
--이름이 King인 직원의 사번을 먼저 알아내는 쿼리를 subquery로 하면 됩니다.
SELECT ename, sal, mgr
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
--15) EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
--단 업무별, 월급이 많은 순으로 출력하여라.
SELECT ename, job, deptno, sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='JONES') OR sal > ( SELECT sal FROM emp WHERE ename='FORD')
ORDER BY job, sal ASC;
--16) EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
--Nested Query
SELECT e.ename, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = (SELECT loc FROM dept WHERE loc = 'CHICAGO');
--17) EMP 테이블에서 FORD와 업무 및 월급이 같은 사원의 모든 정보를 출력하는 SELECT문을 작성하시오.
--(결과에서 FORD는 제외)
SELECT *
FROM emp
WHERE ename NOT IN(SELECT ename FROM emp WHERE ename='FORD')
AND job = (SELECT job FROM emp WHERE ename='FORD')
AND sal = (SELECT sal FROM emp WHERE ename='FORD');
5. function
--문제1) EMP 테이블에서 scott의 사원번호,성명,담당업무(소문자로),부서번호를 출력하여라.
select empno,ename,lower(job),deptno
from emp
where lower(ename)='scott';
--문제2) DEPT 테이블에서 모든 부서의 부서명,위치를 첫 글자들만 대문자로 변환하여 출력하여라.
select initcap(dname),initcap(loc)
from dept;
--문제3) EMP 테이블에서 이름의 첫글자가 ‘K’ 보다 크고 ‘Y’보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라. 단 이름순으로 정렬하여라.
select empno,ename,job,sal,deptno
from emp
where substr(ename,1,1)>'K' and substr(ename,1,1)<'Y'
order by ename;
--문제4) EMP 테이블에서 부서가 20번인 사원의 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.
select empno,ename,length(ename),sal,length(sal)
from emp
where deptno=20;
--문제5) EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.
select ename,sal,mod(sal,30)
from emp;
--날짜 기본 연산
--문제6) EMP 테이블에서 현재까지의 근무일수가 몇 주 몇 일인가를 출력하여라. 단 근무 일수가 많은 사람순으로 출력하여라.
select ename,hiredate,
trunc(sysdate-hiredate) "총근무일수",
trunc(trunc(sysdate-hiredate)/7) "총근무주수",
trunc(mod((sysdate-hiredate),7)) "나머지일수"
from emp
order by 3 desc;
--문제7) EMP 테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력하여라.
select ename,hiredate,trunc(months_between(sysdate,hiredate))
from emp
where deptno=10;
--문제8) EMP 테이블에서 10번 부서원의 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라.
select ename,hiredate,add_months(hiredate,5)
from emp
where deptno=10;
--문제9) EMP 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.
select ename,hiredate,next_day(hiredate,6)
from emp
where deptno=10;
--문제10) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단 토요일과 일요일도 근무 일수에 포함한다.
select ename,hiredate,
last_day(hiredate),
last_day(hiredate)-hiredate
from emp;
--문제 11) emp 테이블에서 현재까지의 근무일수가 몇 주 몇 일 인가를 출력하여라.
--단, 근무 일수가 많은 사람순으로 출력하여라.
select TRUNC(sysdate-hiredate,0) a,
TRUNC(MOD( (sysdate-hiredate), 7)) b,
(TRUNC(sysdate-hiredate,0) - TRUNC(MOD( (sysdate-hiredate), 7))) c,
trunc( (TRUNC(sysdate-hiredate,0) - TRUNC(MOD( (sysdate-hiredate), 7))) / 7 ) d,
TRUNC(MOD( (sysdate-hiredate),7)) "일"
from emp;
SELECT ename, sysdate,
TRUNC(sysdate-hiredate,0) "총 근무일수",
TRUNC(( TRUNC(sysdate-hiredate,0) - TRUNC(MOD( (sysdate-hiredate), 7)) ) / 7 , 0 ) "주",
TRUNC(MOD( (sysdate-hiredate),7)) "일"
FROM emp
ORDER BY (sysdate-hiredate) DESC;
--문제 12) 부서번호가 10번이면 '10번 부서', 20번이면 '20번 부서', 30번이면 '30번 부서', 기타이면 '기타부서' 로 출력하세요.
SELECT ename, deptno,
DECODE(deptno, 10, '10번 부서', 20, '20번 부서', 30, '30번 부서', '기타부서') "부서",
CASE WHEN deptno = 10 THEN '10번 부서'
WHEN deptno = 20 THEN '20번 부서'
WHEN deptno = 30 THEN '30번 부서'
ELSE '기타부서' end "case문"
FROM emp;
--문제 13) 부서 번호가 20인 부서의 시간당 임금을 계산하여 출력하라.
--단, 1달의 근무 일수는 12일이고, 1일 근무시간은 5시간이다.
--출력양식은 이름, 급여, 시간당 임금(소수 이하 1번째 자리에서 반올림)을 출력하라.
select ROUND(sal/12/5,1)
FROM emp;
SELECT ename,
TO_CHAR(sal, '$9,999'),
TO_CHAR(ROUND(sal/12/5,1), '$9,999.9') "시간당 임금"
FROM emp
WHERE deptno = 20;
--문제 14) 급여가 $1,500부터 $3,000 사이의 사람은 급여의 15%를 회비로 지불하기로 하였다.
--이를 이름, 급여, 회비(소수점 2째 자리에서 반올림)를 출력하라.
SELECT ename, sal,
ROUND((sal * 0.15),2) "회비"
FROM emp
WHERE sal between 1500 and 3000;
--문제 15) 모든 사원의 실수령액을 계산하여 출력하라. 단 급여가 많은 순으로 이름, 급여, 실수령액을 출력하라(실수령액은 급여에 대해 10%의 세금을 뺀 금액).
SELECT ename, sal,
(sal 0.1) "세금",
(sal - (sal0.1)) "실수령액"
FROM emp;
--문제 16) 성명, 입사일자, 입사일로부터 90일이 지난 후의 날짜, 급여를 출력하라.
SELECT ename, hiredate,
(hiredate + 90) "90일 후", sal
FROM emp;
--문제 17) 모든 사원의 60일이 지난 후의 'MONDAY'는 몇 년, 몇 월, 며칠인가를 구하여 이름, 입사일, 'MONDAY'를 출력하라.
SELECT ename, hiredate,
NEXT_DAY(hiredate + 60, '월') "월요일"
FROM emp;
--문제 18) 입사일자를 '1996년 5월 14일'의 형태로 이름, 입사일을 출력하라.
SELECT ename,
TO_CHAR(hiredate, 'yyyy')||'년 '||
TO_CHAR(hiredate, 'MM')||'월 '||
TO_CHAR(hiredate,'DD')||'일' "입사일자"
FROM emp;
참고 SQL 데이터
CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-2-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-4-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-9-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-3-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-6-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('9-12-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, null);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-9-1981', 'DD-MM-YYYY'), 1500, NULL, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-1-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-12-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-1-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
COMMIT;