ORACLE DB 연습문제 모음
예제
--1> 부서테이블의 모든 데이터를 출력하라.
SELECT *
FROM EMP
--2> EMP테이블에서 각 사원의 직업, 사원번호, 이름, 입사일을 출력하라.
SELECT JOB, EMPNO, ENAME, HIREDATE
FROM EMP
--3> EMP테이블에서 직업을 출력하되, 각 항목(ROW)가 중복되지 않게 출력하라.
SELECT DISTINCT JOB
FROM EMP
--4> 급여가 2850 이상인 사원의 이름 및 급여를 표시하는 출력하라.
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= 2850
--5> 사원번호가 7566인 사원의 이름 및 부서번호를 표시하는 출력하라.
SELECT ENAME, DEPTNO
FROM EMP
WHERE EMPNO=7566
--6> 급여가 1500이상 ~ 2850이하의 범위에 속하지 않는 모든 사원의 이름 및 급여를 출력하라.
SELECT ENAME, SAL
FROM EMP
WHERE SAL NOT BETWEEN 1500 AND 2850
--7> 1981년 2월 20일 ~ 1981년 5월 1일에 입사한 사원의 이름,직업 및 입사일을 출력하라. 입사일을 기준으로 해서 오름차순으로 정렬하라.
SELECT ENAME, JOB, HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN '1981-02-20' AND '1981-05-01'
ORDER BY HIREDATE
--8> 10번 및 30번 부서에 속하는 모든 사원의 이름과 부서 번호를 출력하되, 이름을 알파벳순으로 정렬하여 출력하라.
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO IN(10,30)
ORDER BY ENAME
--9> 10번 및 30번 부서에 속하는 모든 사원 중 급여가 1500을 넘는 사원의 이름 및 급여를 출력하라.
--(단 컬럼명을 각각 employee 및 Monthly Salary로 지정하시오)
SELECT ENAME AS 'Employee', SAL AS ' Monthly Salary'
FROM EMP
WHERE DEPTNO IN(10,30) AND SAL>1500
--10> 관리자가 없는 모든 사원의 이름 및 직위를 출력하라.
SELECT ENAME, JOB
FROM EMP
WHERE MGR IS NULL
--11> 커미션을 받는 모든 사원의 이름, 급여 및 커미션을 출력하되, 급여를 기준으로
-- 내림차순으로 정렬하여 출력하라.
SELECT ENAME, SAL, COMM
FROM EMP
WHERE COMM IS NOT NULL
ORDER BY SAL DESC
--12> 이름의 세 번째 문자가 A인 모든 사원의 이름을 출력하라.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__A%'
--13> 이름에 L이 두 번 들어가며 부서 30에 속해있는 사원의 이름을 출력하라.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%L%L%' AND DEPTNO=30
--14> 직업이 Clerk 또는 Analyst 이면서 급여가 1000,3000,5000 이 아닌
-- 모든 사원의 이름, 직업 및 급여를 출력하라.
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB IN('CLERK','ANALYST') AND SAL not IN(1000,3000,5000)
--15> 사원번호, 이름, 급여 그리고 15%인상된 급여를 정수로 표시하되 컬럼명을
-- New Salary로 지정하여 출력하라.
SELECT EMPNO, ENAME, SAL,
ROUND(SAL+(SAL*0.15),0) AS "New Salary"
FROM EMP
--16> 15번 문제와 동일한 데이타에서 급여 인상분(새 급여에서 이전 급여를 뺀 값)을 추가해서
-- 출력하라.(컬럼명은 Increase로 하라).
SELECT EMPNO, ENAME, SAL, ROUND(SAL+(SAL*0.15) ) AS "New Salary",
(ROUND(SAL+(SAL*0.15)))-SAL AS "Increase"
FROM EMP
--18> 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를
-- 표시하는 쿼리를 작성하고 컬럼 별칭은 적당히 넣어서 출력하라.
SELECT UPPER(SUBSTR(ENAME,1,1)) || LOWER(SUBSTR(ENAME,2,12)) AS "NAME",
LENGTH(ENAME) AS "LENENAME"
FROM EMP
--19> 사원의 이름과 커미션을 출력하되, 커미션이 책정되지 않은
-- 사원의 커미션은 'no commission'으로 출력하라.
SELECT ENAME, NVL(TO_CHAR(COMM),'no commission')
FROM EMP
--20> 모든 사원의 이름,부서번호,부서이름을 표시하는 질의를 작성하라.(DECODE)
SELECT empno, deptno , DECODE(deptno,10,'ACCOUNTING' ,
20,'RESEARCH',
30,'SALES',
40,'OPERATIONS') AS "dname"
FROM EMP;
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
--21> 30번 부서에 속한 사원의 이름과 부서번호 그리고 부서이름을 출력하라.
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=30
--22> 30번 부서에 속한 사원들의 모든 직업과 부서위치를 출력하라.
--(단, 직업 목록이 중복되지 않게 하라.)
SELECT DISTINCT E.JOB, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=30
--23> 커미션이 책정되어 있는 모든 사원의 이름, 부서이름 및 위치를 출력하라.
SELECT E.ENAME, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND COMM IS NOT NULL
--24> 이름에 A가 들어가는 모든 사원의 이름과 부서 이름을 출력하라.
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.ENAME LIKE '%A%'
--25> Dallas에서 근무하는 모든 사원의 이름, 직업, 부서번호 및 부서이름을 출력하라.
SELECT E.ENAME, E.JOB, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.LOC='Dallas'
--26> 사원이름 및 사원번호, 해당 관리자이름 및 관리자 번호를 출력하되,
--각 컬럼명을 employee,emp#,manager,mgr#으로 표시하여 출력하라.
SELECT E.ENAME AS "EMPPLOYEE", E.EMPNO AS "EMP#",
M.ENAME AS "MANAGER", M.EMPNO AS "MGR#"
FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO
--27> 모든 사원의 이름,직업,부서이름,급여 및 등급을 출력하라.
SELECT E.ENAME, E.JOB, D.DNAME, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
--28> Smith보다 늦게 입사한 사원의 이름 및 입사일을 출력하라.
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='SMITH')
--29> 자신의 관리자보다 먼저 입사한 모든 사원의 이름, 입사일,
--관리자의 이름, 관리자의 입사일을 출력하되 각각 컬럼명을
Employee,EmpHiredate, Manager,MgrHiredate로 표시하여 출력하라.
SELECT E.ENAME AS "Employee", E.HIREDATE AS "EmpHIredate",
M.ENAME AS "Manager",
M.HIREDATE AS "MgrHiredate"
FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO AND E.HIREDATE<M.HIREDATE
--30> 모든 사원의 급여 최고액,최저액,총액 및 평균액을 출력하되
--각 컬럼명을 Maximum, Minimum, Sum, Average로 지정하여 출력하라.
SELECT MAX(SAL) AS "Maximum", MIN(SAL) AS "Minimum",
SUM(SAL) AS "Sum", AVG(SAL) AS "Average"
FROM EMP
--31> 각 직업별로 급여 최저액.최고액,총액 및 평균액을 출력하라.
SELECT JOB, MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY JOB
--32> 직업이 동일한 사람 수를 직업과 같이 출력하라.
SELECT JOB, COUNT(JOB)
FROM EMP
GROUP BY JOB
--33> 관리자의 수를 출력하되, 관리자 번호가 중복되지 않게하라. 그리고,
--컬럼명을 Number of Manager로 지정하여 출력하라.
SELECT COUNT(DISTINCT(MGR)) AS "Number of Manager"
FROM EMP
--34> 최고 급여와 최저 급여의 차액을 출력하라.
SELECT MAX(SAL)-MIN(SAL) FROM EMP
--35> 관리자 번호 및 해당 관리자에 속한 사원들의 최저 급여를 출력하라.
--단, 관리자가 없는 사원 및 최저 급여가 1000 미만인 그룹은 제외시키고 급여를 기준으로
--출력 결과를 내림차순으로 정렬하라.
SELECT MGR, MIN(SAL)
FROM EMP
WHERE MGR IS NOT NULL
GROUP BY MGR
HAVING MIN(SAL)>=1000
ORDER BY MIN(SAL)
--36> 부서별로 부서이름, 부서위치, 사원 수 및 평균 급여를 출력하라.
--그리고 각각의 컬럼명을 부서명,위치,사원의 수,평균급여로 표시하라.
--SELECT E.DEPTNO AS "부서명", D.DNAME AS "부서이름", D.LOC AS "위치",
COUNT(E.EMPNO) AS "사원의 수", AVG(E.SAL) AS "평균급여"
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY E.DEPTNO, D.DNAME, D.LOC
--37> Smith와 동일한 부서에 속한 모든 사원의 이름 및 입사일을 출력하라.
--단, Smith는 제외하고 출력하시오
SELECT ENAME, TO_CHAR(HIREDATE,'YYYY-MM-DD')
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE ENAME='SMITH')
AND ENAME!='SMITH'
--38> 자신의 급여가 평균 급여보다 많은 모든 사원의 사원 번호, 이름, 급여를
--표시하는 질의를 작성하고 급여를 기준으로 결과를 내림차순으로 정렬하라.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>(SELECT AVG(SAL)
FROM EMP)
ORDER BY SAL DESC
--39> 이름에 T가 들어가는 사원의 속한 부서에서 근무하는 모든 사원의
--사원번호 및 이름을 출력하라.
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%T%')
--40> 부서위치가 Dallas인 모든 사원의 이름,부서번호 및 직위를 출력하라.
SELECT E.ENAME, E.DEPTNO, E.JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND D.LOC='DALLAS'
--41> KING에게 보고하는 모든 사원의 이름과 급여를 출력하라.
SELECT ENAME, SAL
FROM EMP
WHERE MGR=(SELECT EMPNO
FROM EMP
WHERE ENAME='KING')
--42> Sales 부서의 모든 사원에 대한 부서번호, 이름 및 직위를 출력하라.
SELECT E.DEPTNO, E.ENAME, E.JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND D.DNAME='SALES'
SELECT E.DEPTNO, E.ENAME, E.JOB
FROM EMP E inner join DEPT D on E.DEPTNO=D.DEPTNO
where D.DNAME='SALES'
--43> 자신의 급여가 평균 급여보다 많고 이름에 T가 들어가는 사원과
--동일한 부서에 근무하는 모든 사원의 사원 번호, 이름 및 급여를 출력하라.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP)
AND DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%T%')
--44> 커미션을 받는 사원과 급여가 일치하는 사원의 이름,부서번호,급여를 출력하라.
SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL IN(SELECT SAL
FROM EMP
WHERE COMM IS NOT NULL)
--45> Dallas에서 근무하는 사원과 직업이 일치하는 사원의 이름,부서이름, 및 급여를 출력하시오
SELECT E.ENAME, D.DNAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.JOB IN(SELECT E.JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND D.LOC='DALLAS')
--46> Scott과 동일한 급여 및 커미션을 받는 모든 사원의 이름, 입사일 및 급여를 출력하시오
SELECT ENAME, HIREDATE, SAL
FROM EMP
WHERE SAL=(SELECT SAL
FROM EMP
WHERE ENAME='SCOTT')
AND NVL(COMM,0)=(SELECT NVL(COMM,0)
FROM EMP
WHERE ENAME='SCOTT')
--47> 직업이 Clerk 인 사원들보다 더 많은 급여를 받는 사원의 사원번호, 이름, 급여를 출력하되,
--결과를 급여가 높은 순으로 정렬하라.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>ALL(SELECT SAL
FROM EMP
WHERE JOB='CLERK') --결국 최대값과 비교 any 최소값과 비교
ORDER BY SAL DESC
--48> 이름에 A가 들어가는 사원과 같은 직업을 가진 사원의 이름과 월급, 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE JOB IN(SELECT JOB
FROM EMP
WHERE ENAME LIKE '%A%')
--49> New York 에서 근무하는 사원과 급여 및 커미션이 같은 사원의 사원이름과 부서명을 출력하라.
select * from emp join dept
on emp.deptno = dept.deptno
and dept.loc='NEW YORK';
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE
e.deptno= d.deptno
AND E.SAL IN(
SELECT E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND LOC='NEW YORK'
)
AND NVL(COMM,0) IN(
SELECT NVL(COMM,0)
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND LOC='NEW YORK');
--50> Dallas에서 근무하는 사원과 직업 및 관리자가 같은 사원의 사원번호,사원이름,
--직업,월급,부서명,커미션을 출력하되 커미션이 책정되지 않은 사원은 NoCommission으로 표시하고,
--커미션의 컬럼명은 Comm으로 나오게 출력하시오. (단, 최고월급부터 출력되게 하시오)
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DNAME,
NVL((TO_CHAR(E.COMM)),'NoCommision') AS "COMM"
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND JOB IN(SELECT JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND LOC='DALLAS')
AND MGR IN(SELECT MGR
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND LOC='DALLAS')