오라클 Day 5

Hyunsu·2023년 4월 19일

oracle

Today I Learned

목록 보기

28/37

📝 목차

Chapter 09 서브쿼리

Chapter 09 서브쿼리

📌 문제 1

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND JOB = (SELECT JOB
		   FROM EMP
		   WHERE ENAME = 'ALLEN');

📌 문제 2

SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, 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
AND SAL > (SELECT AVG(SAL)
		   FROM EMP)
ORDER BY E.SAL DESC, EMPNO;

📌 문제 3

SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME, D.LOC 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 10
AND E.JOB NOT IN (SELECT DISTINCT JOB
				  FROM EMP
				  WHERE DEPTNO = 30);

📌 문제 4

-- 다중행 함수를 사용하지 않는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL > (SELECT MAX(SAL)
		   FROM EMP
		   WHERE JOB = 'SALESMAN')
ORDER BY EMPNO;

-- 다중행 함수를 사용한 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL > ALL (SELECT DISTINCT SAL
			   FROM EMP
			   WHERE JOB = 'SALESMAN')
ORDER BY EMPNO;