자바,아이폰 공부하자~!!

-     4 일차 SQL 과제 –

1.    사원 테이블에서 각 사원에 급여(SAL)가 높은 순서대로 상위 5명을 아래 예제처럼 출력하세요
ex)

SELECT  e.empno

        , e.ename

        , e.job

        , e.mgr

        , e.hiredate

        , e.sal

        , e.comm

        , e.deptno 

FROM emp e RIGHT JOIN (

                       SELECT empno,RANK() OVER (ORDER BY sal asc)  aa

                        FROM emp

                        ) i

ON e.empno = i.empno     

WHERE aa<=5;

2. 사원 테이블에서 각 사원에 급여(SAL)가 높은 순서대로 순위를 부여 했을 때 6등~10등인 사람을 순위대로 아래 예제처럼 출력하세요
    ex)

SELECT *

FROM (SELECT empno

             , ename

             , job

             , mgr

             , hiredate

             , sal

             , comm

             , deptno

             , RANK() OVER(ORDER BY sal asc) RN

      FROM emp

      )

WHERE RN BETWEEN 6 AND 10

3. SALGRADE 테이블 데이터 세로 정보를 가로로 아래 예제처럼 출력하세요

ex)

è 

SELECT *

FROM (SELECT (CASE grade

               WHEN 1 THEN '700~1200'

               END) AS GRADE_1

               ,(CASE grade

               WHEN 1 THEN '1201~1400'

               END) AS GRADE_2

               ,(CASE grade

               WHEN 1 THEN '1401~2000'

               END) AS GRADE_3

               ,(CASE grade

               WHEN 1 THEN '2001~3000'

               END) AS GRADE_4

               ,(CASE grade

               WHEN 1 THEN '3001~9999'

               END) AS GRADE_5

        FROM salgrade

        )

WHERE GRADE_1 IS NOT NULL  

또는

SELECT MAX(CASE grade

               WHEN 1 THEN '700~1200'

               END) AS GRADE_1

               ,MAX(CASE grade

               WHEN 2 THEN '1201~1400'

               END) AS GRADE_2

               ,MAX(CASE grade

               WHEN 3 THEN '1401~2000'

               END) AS GRADE_3

               ,MAX(CASE grade

               WHEN 4 THEN '2001~3000'

               END) AS GRADE_4

               ,MAX(CASE grade

               WHEN 5 THEN '3001~9999'

               END) AS GRADE_5

        FROM salgrade

이게 답이다..밑에꺼..

SELECT MAX( DECODE(grade,1, concat(concat(losal, '~'), hisal))) AS GRADE_1

              ,   MAX( DECODE(grade,2, concat(concat(losal, '~'), hisal))) AS GRADE_2

              ,    MAX( DECODE(grade,3, concat(concat(losal, '~'), hisal))) AS GRADE_3

              ,     MAX( DECODE(grade,4, concat(concat(losal, '~'), hisal))) AS GRADE_4

              ,      MAX( DECODE(grade,5, concat(concat(losal, '~'), hisal))) AS GRADE_5

FROM salgrade

-     5 일차 SQL 과제 –

EMP 테이블의 복사본을 만든 후 아래 과제를 수행하세요.

예.

CREATE TABLE emp_bak

AS

SELECT *

FROM emp;

1.    사원(EMP이름)테이블에서 직업(JOB)이 ‘SALESMAN’ 인 사원 급여(SAL)에 400 더하는 수정(UPDATE) 구문을 구하세요?

UPDATE emp1 SET sal=sal+400 WHERE job='SALESMAN';

2.    사원(EMP이름)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원을 대상으로 고용일자(HIREDATE)를 1년 더하는 수정(UPDATE) 구문을 구하세요?

UPDATE emp1  SET hiredate= hiredate+365

                 WHERE sal >(SELECT AVG(sal) FROM emp1);

UPDATE emp1 SEt hiredate = ADD_MONTHS(hiredate,12)
WHERE sal> (SELECT AVG(sal) FROM emp1)
   
   

3.    사원(EMP이름)테이블에서 전체 사원을 대상으로 COMM 컬럼에 100 을 더하고 직업(JOB)이 ‘CLERK’ 인 사원은 현 급여에서 2배, ‘MANAGER’ 인 직업을 가진 사원은 현 급여에서 3배, 이외 직업을 가진 사원은 현 급여에서 4배를 더하는 수정(UPDATE) 구문을 구하세요?

UPDATE emp1 SET comm= comm+100   

UPDATE emp1 SET sal=sal*2 WHERE job LIKE 'CLERK';
UPDATE emp1 SEt sal=sal*3 WHERE job LIKE 'MANAGER';
UPDATE emp1 SET sal=sal*4 WHERE job! = 'CLERK'  OR job != 'MANAGER'
       
   
   
UPDATE emp1 SET comm=comm+100
                                    ,sal = CASE job
                                                          WHEN 'CLERK' THEN sal*2
                                                          WHEN 'MANAGER' THEN sal*3
                                                          ELSE sal*4
                                                          END;

4.    사원(EMP이름)테이블에서 이름(ENAME)이 ‘M’으로 시작하는 사원

삭제(DELETE) 구문을 구하세요?

DELETE FROM emp1 WHERE ename LIKE 'M%';

5.     사원(EMP이름)테이블에서 급여(SAL)가 사원 평균급여 보다 높은 사원 삭제(DELETE) 구문을 구하세요?

DELETE FROM emp1 WHERE sal>(select AVG(sal) FROM emp1)

더보기

더보기

더보기

더보기

더보기

INSERT INTO departments(department_id,department_name) VALUES(departments_seq.NEXTVAL,'GH');
departments_seq.NEXTVAL 이건 시퀀스로 머 만들어 졌으며
저렇게 써주면 부서 번호 10씩 증가..머 그렇게 만들어졌기 때문에..ㅎㅎ

더보기

더보기

더보기

상관하의질의)

더보기

더보기

더보기

문제2
씨애틀이 있는 주문을 3번이상 한 사람

더보기

분석 함수

SELECT department_id
      , last_name, salary
      , commission_pct,
        RANK() OVER (   ORDER BY salary DESC, commission_pct) "Rank"
        FROM employees
        WHERE department_id = 80;


RANK() OVER(ORDER BY 차순, 기준)

CASE) IF랑 같은거

더보기

더보기

-     3 일차 SQL 과제 –

-      

1.     사원 테이블에서 각 사원에 급여(SAL) 등급을 아래 예제처럼 출력하세요
(급여순위점수(SALGRADE) 테이블 조인)
ex) 정렬은 등급(GRADE) 오름차순

SELECT empno, ename, sal, grade

FROM emp e JOIN salgrade s

ON s.losal <= e.sal AND s.hisal >= e.sal

또는

SELECT empno, ename, sal, grade

FROM emp e JOIN salgrade s

ON e.sal BETWEEN s.losal AND s.hisal

2.    사원 테이블에서 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요
ex) 정렬은 급여(SAL) 내림차순

SELECT empno, ename, job, sal

FROM emp

WHERE sal>(

           SELECT ROUND(AVG(sal),1)

           FROM emp

           )

ORDER BY sal DESC;

3.    사원 테이블에서 부서별 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요

ex) 정렬은 급여(SAL) 내림차순

SELECT dname, empno, ename, job, sal

FROM emp e JOIN dept d

ON e.deptno = d.deptno

WHERE sal > ( SELECT ROUND(AVG(sal),1)

               FROM emp

              ) 

ORDER BY sal DESC;

4.    사원 테이블에서 각 사원에 급여(SAL) 순위 점수 별로 인원수를 아래 예제처럼 출력하세요
(급여순위점수(SALGRADE) 테이블 조인)

ex)

SELECT s.grade, s.losal, s.hisal, f.CNT

FROM salgrade s JOIN (SELECT  grade,COUNT(*) AS CNT

                FROM emp e JOIN salgrade s

                ON e.sal BETWEEN s.losal AND s.hisal

                 GROUP BY grade

                ) f

ON  s.grade= f.grade

5.    부서명이 'RESEARCH' 이거나 부서위치가 'NEW YORK' 사원 정보를 아래 예제처럼 출력하세요

ex) 정렬은 부서명(DNAME) 오름차순

SELECT dname, loc, empno, ename

-     2 일차 SQL 과제 –

-      

1.     사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요
(사원, 부서 테이블 조인 시 부서가 없는 사원은 출력 안함)
ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순

SELECT dname,empno, ename

FROM emp e JOIN dept d

ON e.deptno = e.deptno

ORDER BY dname ASC;

2.    사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요
--(사원, 부서 테이블 조인 시 부서가 없는 사원도 출력)

사원이 없는 부서도 출력
ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순

3.    부서 위치가 'DALLAS', 'CHICAGO' 곳에 근무하는 사원 정보 아래
예제처럼 출력하세요

ex) 정렬은 부서위치(LOC) 내림차순, 사원명(ENAME) 오름차순

SELECT loc,LOWER(loc), empno,ename

FROM emp e JOIN dept d

ON e.deptno = d.deptno

WHERE LOC IN ('DALLAS','CHICAGO')

ORDER BY loc DESC, ename ASC;

대소문자를 모를 때

WHERE loc IN (‘dallas’,’chicago’)

요것도 가능

SELECT loc, LOWER(loc), empno,ename

FROM emp e JOIN dept d

ON e.deptno = d.deptno

--WHERE LOC IN ('DALLAS','CHICAGO')

--WHERE LOWER(loc) IN (‘dallas’,’chicago’)

WHERE INITCAP(loc) IN('Dallas','Chicago')

ORDER BY loc DESC, ename ASC;

4.    부서별 최고 급여(SAL) 금액을 아래 예제처럼 출력하세요

ex) 부서 없는 사원은 제외

SELECT d.deptno

             ,MAX(sal)

FROM emp e JOIN dept d

ON e.deptno = d.deptno

GROUP BY d.deptno;

SELECT deptno,MAX(sal)

FROM emp

WHERE deptno IS NOT NULL

GROUP BY deptno

ORDER BY deptno ASC

5.    부서별 최고 급여(SAL) 금액을 받는 사원 정보를 아래 예제처럼 출력하세요

ex) 부서 없는 사원은 제외

SELECT d.deptno, empno, ename,sal

FROM emp e JOIN (SELECT deptno,MAX(sal) AS MAXSAL

                               FROM emp

                               GROUP BY deptno

                            ) d

ON e.deptno = d.deptno

WHERE sal = MAXSAL

ORDER BY d.deptno;

또는


SELECT deptno
            , sal
            , empno
            , ename
            , job   
FROM emp
WHERE (deptno, sal ) IN (     --여기에서는 값이 2개가 리턴되므로 =연산자는 쓰지 못한다. 그러므로 IN이라는 연산자를 써줘야 한다.                               
                               SELECT e.deptno,MAX( sal)
                               FROM emp e JOIN dept d
                               ON e.deptno = d.deptno
                               GROUP BY e.deptno
                               --ORDER BY e.deptno ASC;  (정렬은 마지막에 다 끝나고 정렬해야 한다. )
                               )
ORDER BY deptno ASC;

오늘의 학습 목표

1)OUTER JOIN
   INNER JOIN 은 일반 조인이다.
2)SLEF JOIN

문제 1)
영업 직무를 가진 사원들의 목록을 얻어보자.

결과> 사원번호, 사원이름, 직무

더보기

문제 2)
영업 부서에 소속한 사원들의 목록을 얻어보자.
결과> 사원번호, 사원이름, 부서이름

더보기

문제 3)

사원번호 178번 사원 정보를 아래와 같이얻어보자.
결과> 사원번호, 사원이름, 직무이름, 부서이름

더보기

더보기

더보기

더보기

더보기

더보기

더보기

문제 3)
사원번호 148번이 소속한 부서의 이름을 얻어보자.

한번
SELECT department_name
FROM departments
WHERE department_id = (
                                          SELECT department_id
                                          FROM employees 
                                          WHERE employee_id =148
                                          );
 두번쨰                                        
SELECT department_name
FROM employees , departments
WHERE employee_id = 148
AND employees.department_id = departments.department_id;

1일차_과제.doc

-      

다음과 같은 ERD 구조에 테이블이 3개 있습니다. 앞으로는 이 데이터를 바탕으로 주어진 요구 사항을 만족하는 SQL을 작성하세요.

위 ERD의 내용은 사원(EMP), 부서(DEPT), 급여등급(SALGRADE)에 대한 테이블입니다. 간단히 설명하자면 부서라는 테이블이 있고, 사원이라는 테이블이 있습니다. 사원은 특정 부서에 속해 있습니다.

사원이 받는 급여는 급여 등급 테이블의 구간(최소금액~최대금액)중에 하나 안에 속합니다. E

1.     사원 테이블에서 사원번호가 7369, 7698 번인 사원번호와 이름을

출력하세요?

SELECT ENAME

              ,EMPNO

FROM emp

WHERE empno IN(7369,7698);

2.    사원 테이블에서 사원번호가 7369, 7698 번인 아닌 사원번호와 이름을 출력하세요?

ex)

SELECT ENAME

              ,EMPNO

FROM emp

WHERE empno NOT IN(7369,7698);

 (WHERE  empno !=7369 AND empno !=7698)
3.    사원 테이블에서 급여(SAL)가 3000에서 5000사이인 사원 정보를 다 출력하세요?

ex)

SELECT *

FROM emp

WHERE sal BETWEEN 3000 AND 5000;

4.사원 테이블에서 고용일자(HIREDATE)가 1981년 12월 1일 이후 고용된 사원 정보를 다 출력하세요?

ex)

SELECT hiredate

              ,TO_CHAR(hiredate,'YYYY-MM-DD')
       FROM emp

--WHERE hiredate >= '81/12/01'

--WHERE TO_CHAR(hiredate,'YYYY-MM-DD')>= '1981-12-01'
      EXTRACT(
      ORDER BY hiredate ASC; 

5.사원 테이블에서 직업(JOB)이 SALESMAN 중에서 사원번호의 최대값을 출력하세요?

ex)

SELECT MAX(empno)

FROM emp

WHERE job= 'SALESMAN';

6. 사원 테이블에서 직업이 SALESMAN 중에서 급여를 제일 많이 받는 사람의 이름 출력


SELECT empno, ename, sal
FROM emp e JOIN (
                           SELECT job,MAX(sal) as MAXSAL
                            FROM emp
                            WHERE job='SALESMAN'
                             GROUP BY job
                              ) j
ON e.job = j.job
WHERE sal = MAXSAL;