오라클 SQL 9강

--9강
--SUB QUERY?
--옥성우 학생보다 신장이 큰 학생들의 학번,이름,신장을 구하시오
SELECT STU_HEIGHT
FROM STUDENT
WHERE STU_NAME='옥성우';

SELECT STU_NO,STU_NAME,STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT>172;

SELECT STU_NO,STU_NAME,STU_HEIGHT
FROM STUDENT
WHERE STU_HEIGHT>=
(SELECT STU_HEIGHT
FROM STUDENT
WHERE STU_NAME='옥성우')
AND STU_NAME <> '옥성우';

--학생테이블에서 박희철 학생과 같은 몸무게를 가진 학생의 정보를 구하라
SELECT STU_WEIGHT
FROM STUDENT
WHERE STU_NAME='박희철';
SELECT * FROM STUDENT
WHERE STU_WEIGHT=63
AND STU_NAME<>'박희철';

SELECT * FROM STUDENT
WHERE STU_WEIGHT=(SELECT STU_WEIGHT FROM STUDENT
WHERE STU_NAME='박희철')
AND STU_NAME<>'박희철';

--학생테이블에서 컴퓨터정보학과와 같은 반인 다른학과의 학생정보를 IN을 이용하여 쿼리문으로 구하시오
SELECT * FROM STUDENT
WHERE STU_CLASS IN(SELECT STU_CLASS FROM STUDENT
WHERE STU_DEPT='컴퓨터정보')
AND STU_DEPT<>'컴퓨터정보';

--신장이 전체학생들의 평균신장보다 큰 학생의 정보를 구하라
SELECT * FROM STUDENT
WHERE STU_HEIGHT>
(SELECT AVG(STU_HEIGHT)
FROM STUDENT);

--신장이 모든 학과들의 평균신장보다 큰 학생의 정보를 구하라
SELECT * FROM STUDENT
WHERE STU_HEIGHT>ALL
(SELECT AVG(STU_HEIGHT) FROM STUDENT
GROUP BY STU_DEPT);

SELECT * FROM STUDENT
WHERE STU_HEIGHT>
(SELECT MAX(AVG(STU_HEIGHT)) FROM STUDENT
GROUP BY STU_DEPT);

--컴퓨터정보학과의 최소신장과 비교하여 최소신장이 더 큰 학과의 학과명과 최소신장을 구하라
SELECT STU_DEPT , MIN(STU_HEIGHT) FROM STUDENT
GROUP BY STU_DEPT HAVING MIN(STU_HEIGHT)>
(SELECT MIN(STU_HEIGHT) FROM STUDENT
WHERE STU_DEPT='컴퓨터정보');

SELECT STU_DEPT,MIN(STU_HEIGHT) FROM STUDENT
GROUP BY STU_DEPT HAVING MIN(STU_HEIGHT)>
(SELECT MIN(STU_HEIGHT) FROM STUDENT
WHERE STU_DEPT='컴퓨터정보');

SELECT MIN(STU_HEIGHT) FROM STUDENT
WHERE STU_DEPT='컴퓨터정보';

--복수열부질의를 이용하여 테이블을 만드는 경우?
CREATE TABLE TEST(EMPNO,ENAME,SAL,COMM,DEPTNO)
AS
SELECT EMPNO,ENAME,SAL,COMM,DEPTNO
FROM EMP
WHERE DEPTNO=1;
SELECT * FROM TEST;

INSERT INTO TABLE TEST VALUES(11,'APPLE',1000,NULL,30);
INSERT INTO TABLE TEST VALUES(12,'BNANA',2000,100,30);


--FROM절의 부질의
SELECT STU_DEPT,ROUND(AVG(STU_HEIGHT),2) AS AVG_HEIGHT
FROM STUDENT
GROUP BY STU_DEPT;

SELECT STU_NO,STU_NAME,A.STU_DEPT,STU_HEIGHT,AVG_HEIGHT FROM STUDENT A,
(SELECT STU_DEPT,ROUND(AVG(STU_HEIGHT),2) AS AVG_HEIGHT
FROM STUDENT GROUP BY STU_DEPT) B

WHERE A.STU_DEPT=B.STU_DEPT AND STU_HEIGHT>AVG_HEIGHT;

 

--9-3

SELECT * FROM EMP

WHERE SAL>(SELECT SAL FROM EMP

WHERE ENAME='JONES');

 

--9-4

SELECT * FROM EMP

WHERE HIREDATE>(SELECT HIREDATE FROM EMP

WHERE ENAME='SCOTT');

 

--9-5

SELECT EMPNO,ENAME,SAL,A.DEPTNO,DNAME,LOC

FROM EMP A,DEPT B

WHERE A.DEPTNO=B.DEPTNO

AND A.DEPTNO=20

AND SAL>(SELECT AVG(SAL) FROM EMP);

 

--9-7

SELECT * FROM EMP

WHERE SAL IN(SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

--ANY(적은),SOME

--9-9

SELECT * FROM EMP

WHERE SAL=ANY(SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

SELECT * FROM EMP

WHERE SAL=SOME(SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

--9-11 30번부서 사원들의 최대급여보다 적은 급여를 받는 사원정보 출력

SELECT * FROM EMP

WHERE SAL<ANY(SELECT SAL FROM EMP

WHERE DEPTNO=30)

ORDER BY SAL,EMPNO;

 

--30번부서의 사원들의 최소급여보다 많은 급여를 받는 사원정보 출력

SELECT * FROM EMP

WHERE SAL > ANY(SELECT SAL FROM EMP

WHERE DEPTNO=30)

ORDER BY SAL,EMPNO;

 

--9-14 부서번호가 30번인 사원들의 최소급여보다 더 적은 급여를 받는 사원 출력하라

SELECT * FROM EMP

WHERE SAL < ALL (SELECT SAL FROM EMP

WHERE DEPTNO=30);

 

--9-15 부서번호가 30번인 사원들의 최대급여보다 더 많은 급여를 받는 사원 출력하라

SELECT * FROM EMP

WHERE SAL > ALL(SELECT SAL FROM EMP

WHERE DEPTNO=30);

 

--EXISTS(결과값이 존재하는 경우)

--9-16

SELECT * FROM EMP

WHERE EXISTS(SELECT DNAME FROM DEPT

WHERE DEPTNO=30);

 

--다중열 서브쿼리

--9-18

SELECT * FROM EMP

WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

--SELECT절에 동일한 SELECT문을 사용하는경우

--9-21

SELECT EMPNO,ENAME,JOB,SAL,(SELECT GRADE FROM SALGRADE

WHERE A.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,DEPTNO,

(SELECT DNAME FROM DEPT

WHERE A.DEPTNO=DEPT.DEPTNO)AS DNAME

FROM EMP A