Tag Archives: 서브쿼리

오라클 공부중-_-;

아 정신없다. 한밤중에 포스팅 하는건 무리다. 일단 공부한거 올려만 놓겠다.

[CODE]SELECT A.EMP_ID, A.EMP_NAME,A.DEPT_CODE, B.DEPT_NAME
FROM TEMP A, TDEPT B
WHERE B.DEPT_CODE = A.DEPT_CODE

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY + B.COMM
FROM TEMP A, TCOM B
WHERE A.EMP_ID = B.EMP_ID
  AND B.WORK_YEAR = ‘2001’

SELECT A.EMP_ID, A.EMP_NAME, A.LEV, A.SALARY
FROM TEMP A, EMP_LEVEL B
WHERE A.SALARY BETWEEN B.FROM_SAL AND B.TO_SAL
  AND B.LEV = ‘과장’
 
SELECT A.EMP_ID, B.EMP_ID
FROM TEMP A, TCOM B
WHERE B.WORK_YEAR(+) = ‘2001’
AND B.EMP_ID(+) = A.EMP_ID

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY, B.FROM_SAL, B.TO_SAL
FROM TEMP A, EMP_LEVEL B
WHERE A.LEV = B.LEV(+)

SELECT A.DEPT_CODE “부서코드”,
A.DEPT_NAME “부서명”,
B.DEPT_CODE “상위부서코드”,
B.DEPT_NAME “상위부서명”
FROM TDEPT A, TDEPT B
WHERE B.DEPT_CODE = A.PARENT_DEPT

SELECT A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE, COUNT(A.EMP_ID) “COUNT”
FROM TEMP A, TEMP B
WHERE A.BIRTH_DATE > B.BIRTH_DATE(+)
GROUP BY A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE
ORDER BY COUNT DESC

SELECT AVG(SALARY) FROM TEMP

SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE SALARY > (SELECT AVG(SALARY) FROM TEMP)

SELECT DEPT_CODE, COUNT(*)
FROM TEMP
WHERE EMP_ID IN (SELECT EMP_ID FROM TCOM)
GROUP BY DEPT_CODE

SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE (DEPT_CODE, EMP_ID) IN (SELECT DEPT_CODE, BOSS_ID FROM TDEPT);

SELECT DEPT_CODE, MAX(SALARY) “MAX_SALARY”
FROM TEMP
GROUP BY DEPT_CODE

SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE (DEPT_CODE, SALARY) IN
  (SELECT DEPT_CODE, MAX(SALARY)
  FROM TEMP
  GROUP BY DEPT_CODE)[/CODE]