1월 11일 31일차

#1

1.

50, 80, 100 번 부서에 속해있는 직원들을 대상으로 1995년 이전에 입사했으며, 급여가 5000 이상인 직원들의 이름, 부서id, 입사일, 급여를 조회하시오.

단, 출력결과는 입사일이 빠른 직원이 먼저 출력되어야 한다.

SQL> SELECT FIRST_NAME, DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') "HIRE_DATE", SALARY

  2  FROM EMPLOYEES

  3  WHERE DEPARTMENT_ID IN(50, 80, 100) AND SALARY >= 5000 AND EXTRACT(YEAR FROM HIRE_DATE) <= 1995;

SELECT FIRST_NAME, DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'), HIRE_DATE, SALARY 

FROM EMPLOYEES

WHERE SUBSTR(TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'), 1, 4) <= 1995 AND SALARY > 5000 AND DEPARTMENT_ID IN(50, 80, 100) 

ORDER BY 1;

2.

입사 년도 별로 입사자수를 조회하되, 입사자 수가 가장 많은 년도부터 출력되록 하시오.

COUNT(*) + GROUP BY

SQL> SELECT TO_CHAR(HIRE_DATE, 'YY')"입사년도", COUNT(JOB_ID)"직원수"

  2  FROM EMPLOYEES

  3  GROUP BY TO_CHAR(HIRE_DATE, 'YY')

  4  ORDER BY COUNT(JOB_ID) DESC;

3. 부서이름 별 직원수를 조회하되 

부서배치가 되지 않은 직원이 있다면 부서명이 미배치 직원에 포함되어 출력되도록 하시오.

SELECT NVL(D.DEPARTMENT_NAME, '<미배치 직원>'> 부서명,

COUNT(*) 직원수

FROM DEPARTMENTS D, EMPLOYEES E

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)

GROUP BY D.DEPARTMENT_NAME;

4.

자신이 속한 부서의 평균급여보다 급여를 많이 받는 직원의 이름과 급여, 부서id 를 조회하시오.

SELECT FIRST_NAME "이름", SALARY "급여", DEPARTMENT_ID "부서ID"

FROM EMPLOYEES E

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES WHERE E.DEPARTMENT_ID = H.DEPARTMENT_ID);

#2

Join 함수

INNER JOIN (FALSE, NULL은 가져오지 않음)

- 각 테이블의 조인 컬럼(공통 컬럼)을 비교하여 조인조건만 만족하는 레코드만 선택하는 조인

- 테이블 X, S에서 M1 = S1 조건을 만족하는 레코드만 가져오는 JOIN문을 작성해보자.

-- Ansi JIN

SELECT * FROM M INNER JOIN S ON M1 = S1

칼럼이 다를 경우 ON

칼럼이 같을 경우 USING

-- SQL SERVER JOIN - 

SELECT * FROM M, S WHERE M1 = S1

#3

Q1. INNER JOIN을 이용해서 사원의 이름과 그 사원이 속해있는 부서이름을 출력해보자.

<ANSI JOIN>

SQL> SELECT ENAME, DNAME

  2  FROM EMP JOIN DEPT USING(DEPTNO);

<ORACLE JOIN>

SQL> SELECT ENAME, DNAME

  2  FROM EMP, DEPT

  3  WHERE EMP.DEPTNO = DEPT.DEPTNO;

SQL> SELECT ENAME, DNAME

  2  FROM EMP E, DEPT D

  3  WHERE E.DEPTNO = D.DEPTNO;

Q2. 사원의 이름과 그 사원이 속해있는 부서이름과 부서번호를 출력하라.

<ANSI JOIN>

SQL> SELECT ENAME, DNAME, DEPTNO

  2  FROM EMP JOIN DEPT USING(DEPTNO);

<ORACLE JOIN>

SQL> SELECT ENAME, DNAME, DEPT.DEPTNO

  2  FROM EMP, DEPT

  3  WHERE EMP.DEPTNO = DEPT.DEPTNO;

#4

CROSS JOIN 

각 테이블의 모든 로우에 대해서 가능한 모든 조합을 가지는 쿼리 결과를 만들어내는 조인

-- ANSI JOIN --

SELECT *

FROM M CROSS JOIN S

SQL SERVER JOIN -

SELECT *

FROM M, S

 

ⓐ LEFT OUTER JOIN 

M 테이블을 주 테이블로 놓고, S테이블을 종 테이블로 하여 조인을 걸어서 M1 = S1 조건을 만족하는 레코드를 가져오는 LEFT OUTER JOIN을 작성

SELECT *

FROM M LEFT OUTER JOIN S 

ON M1 = S1

--SQL SERVER JOIN

SELECT *

FROM M, S

WHERE M1 = S1(+);

ⓑ RIGHT OUTER JOIN

종 테이블 에 맞지 않는 건 NULL로 RETURN

ⓒ FULL OUTER JOIN

-- ANSI JOIN 

SELECT *

FROM M FULL OUTER JOIN S

ON M1 = S1;

한번은 M1을 주를 주고 한 번은 S1을 주를 줌.

ⓒ 세 개 이상의 테이블에서 조인 걸기

-- ANSI JOIN 

SELECT *

FROM M INNER JOIN S

ON M1 = S1

INNER JOIN X

ON S1 = X1;

-- SQL SERVER JOIN

SELECT *

FROM M, S, X

WHERE M1 = S1 AND S1 = X1;

ⓓ SELF JOIN

하나의 테이블 내에서 서로 다른 컬럼 간에 참조 관계가 있을 때 걸리는 JOIN

SELECT 사원.EMPNO, 사원.ENAME, 관리자.EMPNO, 관리자.ENAME

FROM EMP 사원, EMP 관리자

WHERE 사원.MGR = 관리자.EMPNO(+);

Q1. 각 사원의 이름과 월급, 그리고 그 사원의 급여 등급을 출력하자

SELECT E.ENAME, E.SAL, S.GRADE

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

Q2. 각 사원의 이름, 월급, 급여등급, 그가 속한 부서 이름을 출력하라.

SELECT E.ENAME, E.SAL, D.DNAME, S.GRADE

FROM EMP E, DEPT D, SALGRADE S

WHERE E.DEPTNO = D.DEPTNO

AND E.SAL BETWEEN S.LOSAL AND S.HISAL