Oracle Join & 함수 문제

1. 모든 사원에 대해 사번, 이름, 급여, 보너스(comm), 보너스금액을 출력하는데  각각 열이름을 한글로 출력하게 하는 SQL문을 작성하시오. 단 보너스금액은 실급여(급여와 보너스의 합)에 10%를 더해서 출력합니다.

sql > SELECT empno AS "사번", ename "이름", sal "급여",

        comm "보너스", (sal + nvl(comm,0)) * 1.1 AS "보너스금액"

        FROM EMP; 

2. comm을 받는 모든 사원의 이름, 부서 이름 및 위치를 표시하는 질의를 작성하시오.

sql > SELECT e.ename, d.dname, d.loc

        FROM emp e, DEPT d

        WHERE e.DEPTNO = d.DEPTNO AND e.comm IS NOT NULL;

3. 업무별, 부서별로 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하시오.

sql > SELECT deptno, job, COUNT(*), AVG(sal), SUM(sal)

        FROM emp

        GROUP BY job, deptno;

4. 각 부서별 평균 월급, 전체 월급, 최고 월급, 최저 월급을 구하여 평균 월급이 많은 순으로 출력하시오.

sql > SELECT AVG(sal), SUM(sal), MAX(sal), MIN(sal)

        FROM emp

        GROUP BY deptno

        ORDER by AVG(sal) DESC;

5.  job이 CLERK이면서 급여가 $1100 이상인 사원의 사번, 이름, 직위, 급여를 출력하시오.

sql > SELECT empno, ename, job, sal

        FROM EMP

        WHERE job = UPPER('clerk') AND sal >= 1100;

6. 1987년 이후에 입사한 사원의 사번, 이름, 입사일을 출력하시오.

sql > select empno, ename, hiredate

        FROM EMP

        WHERE TO_CHAR(hiredate, 'YYYY') >= 1987;

7. 이름이 SCOTT 또는 MILLER인 사원의 사번, 이름, 관리자 사원번호, 부서번호, 부서이름, 부서위치를 출력하시오.

sql > SELECT e.empno, e.ename, e.mgr, e.DEPTNO, d.dname, d.loc

        FROM emp e, DEPT d

        WHERE e.DEPTNO = d.DEPTNO AND e.ENAME IN ('SCOTT', 'MILLER');

8. 10번 부서의 모든 사람들에게 급여의 13%를, 20번 부서의 모든 사람들에게 급여의 15%를, 30번 부서의 모든 사람들에게 급여의 18%를 보너스로 지불하기로 하였다. 이름, 급여, 보너스 금액, 부서번호, 부서이름을 출력하라.

sql > select e.ename, e.sal,

         CASE

                WHEN e.deptno = 10 THEN sal * 0.13

                WHEN e.DEPTNO = 20 THEN sal * 0.15

                WHEN e.DEPTNO = 30 THEN sal * 0.18

                END AS "보너스금액",

        e.deptno, d.dname

        FROM emp e, DEPT d

        WHERE e.DEPTNO = d.DEPTNO;

9. 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 이름만을 출력하라.

select LOWER(SUBSTR(ename, 1,3))

FROM EMP

WHERE LENGTH(ename) >= 6;

10. 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수, 평균봉급을 출력하시오.

SELECT job, COUNT(*), AVG(sal)

FROM EMP

GROUP BY job

HAVING COUNT(job) >= 4;

11. emp table에서 최고급여와 최소 급여의 차이는 얼마인가?

sql > SELECT MAX(sal) - MIN(sal)

        FROM EMP;

12. author 테이블에서 캘리포니아주(CA)에 사는 사람들을 출력하시오.

sql > SELECT *

        FROM AUTHORS

        WHERE state = 'CA';

13. publishers 테이블에서 나라별로 출판사의 갯수를 출력하시오.

sql > SELECT country, COUNT(*)

        FROM PUBLISHERS

        GROUP BY country;

14. titles 테이블에서 무가지는 어떤 책인지, 책이름, 책 가격, 책의 출판일자를 출력하시오.

sql > SELECT title, price, pubdate

        FROM TITLES

        WHERE price IS NULL;

15. titles 테이블에서 1991년 10월에 출판된 책의 정보를 출력하시오.

sql > SELECT *

        FROM TITLES

        WHERE TO_CHAR(pubdate, 'YYYY-MM') = '1991-10';

        --WHERE pubdate LIKE '%-OCT-91';

16. titles 테이블에서 'Life Without Fear' 책은 출판된지 몇 년 됐는지 출력하시오.

sql > SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, pubdate) / 12)

        FROM TITLES

        WHERE title = 'Life Without Fear';

17. employee 테이블에서 1991년과 1992년에 입사한 사람의 전체이름을 출력하시오.

sql > SELECT fname || ' ' || lname

        FROM EMPLOYEE

        WHERE TO_CHAR(hire_date, 'YYYY') IN (1991, 1992);

18. employee 테이블과 jobs 테이블을 이용해서 사원 아이디, 사원이름(fname), 하는 일(job_desc)을 출력하시오.

sql > SELECT e.emp_id, e.fname, j.job_desc

        FROM EMPLOYEE e, jobs j

        WHERE e.job_id = j.job_id;

19. titles 테이블에서 가장 비싼 책과 가장 싼 책의 가격차는 얼마인가?

sql > select MAX(price) - MIN(price)

         FROM TITLES;

20. sales 테이블에서 1994년에 팔린 책의 정보를 출력하시오.

sql > SELECT *

        FROM SALES

        WHERE TO_CHAR(ord_date, 'YYYY') = 1994;