[Oracle] 과제 문제 모음 -2-

 

 

---

2015년 Naver Blog 기록

---

Join 기본 예제

 

1. SALESMAN의사원번호, 이름, 급여, 부서명, 근무지코드를조회

SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, L.LOC_CODE
FROM EMP E, DEPT D, LOCATIONS L
WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE AND D.DNAME='SALES';

 

2.사원이름,부서명, 급여, 근무도시명을조회

SELECT E.ENAME, D.DNAME, E.SAL, L.CITY
FROM EMP E, DEPT D, LOCATIONS L
WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE;

 

3.DALLAS에 근무하는 사원 중 급여 1500 이상인 사원의 [이름,급여,업무,입사일,보너스]를 조회

SELECT E.ENAME, E.SAL, E.JOB, E.HIREDATE, NVL(E.COMM,0)
FROM EMP E, DEPT D, LOCATIONS L
WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE AND L.CITY='DALLAS' AND E.SAL>=1500;

 

4. EMP 테이블의 [사원번호, 이름, 업무, EMP 테이블의부서번호] DEPT 테이블의 [부서번호, 부서명]을 조회하되, 직원이 없는 부서까지 모두 나타내시오.

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;

- 직원이 없는 부서를 나타낼 땐(EMP) 부서가 없는 직원을 나타낼 땐(DEPT)
  정보가 없는 쪽에 (+) 기호를 붙이는 걸 잊지말 것!

 

5.DEPT 테이블, LOCATIONS 테이블을이용하여부서번호, 부서명, 해당부서의city 정보를조회
(근무 도시가 지정되지않은 부서번호, 부서명도표시) 

SELECT D.DEPTNO, D.DNAME, L.CITY
FROM DEPT D, LOCATIONS L
WHERE D.LOC_CODE = L.LOC_CODE(+);

---

문제 1) 부서별 사원의 수를 출력하시오.  

SELECT D.DNAME, COUNT(E.EMPNO)
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;

 

문제 2)CITY별 인원수를 구하시오.

SELECT L.CITY 지역, COUNT(E.EMPNO) 지역별인원수
FROM EMP E, DEPT D, LOCATIONS L
WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE
GROUP BY L.CITY;

 

문제 3)MANAGER 이름별로 관리하고 있는 직원이 몇명인지 출력하시오. 
(단, 관리자가 없는 경우 '관리자없음' 으로 출력하게 하시오.)

SELECT NVL(M.ENAME,'관리자없음') 관리자, COUNT(E.EMPNO) 관리직원수
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+)
GROUP BY M.ENAME;

 

워밍업 문제 1) 부서별 근무하는 인원 수를 나타내도록 하세요.단, 사원이 없는 부서명도 같이 나타내도록 합니다.

SELECT D.DEPARTMENT_NAME, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
ORDER BY D.DEPARTMENT_NAME;

 

워밍업 문제 2) 각 직책 별(job_title)로 급여의 총합을 구하되 직책이 Representative 인 사람은 제외하십시오. 단, 급여 총합이 30000 초과인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하십시오. 출력 결과의 컬럼명은 아래 결과와 동일하게 주십시오.

SELECT J.JOB_TITLE JOB, SUM(E.SALARY) 급여
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID AND J.JOB_TITLE NOT LIKE '%Representative%'
GROUP BY J.JOB_TITLE
HAVING SUM(E.SALARY) > 30000
ORDER BY 급여;

 

문제 1) FIRST_NAME의 두 번째 문자열에 "t"(소문자)가 포함된 사원의 사원번호, 이름(FIRST_NAME),관리자 이름을 출력하고 관리자가 없는 경우에는 "관리자 없음"이라고 출력되도록 작성하세요.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, NVL(M.FIRST_NAME,'관리자 없음')
FROM EMPLOYEES E, EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID(+) AND E.FIRST_NAME LIKE '_t%';

 

문제 2) 자신의 메니저 보다 연봉을 높게 받는 직원의 FIRST_NAME과 SALARY를 출력하세요

SELECT E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E, EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY>M.SALARY;

 

문제 3) 각 부서별로 평균연봉을 연봉이 높은 순서대로 출력하세요.단, 연봉은 소수이하 자리수는 반올림하여 나타내며 연봉이 5000이상인 데이터만 조회합니다.

SELECT D.DEPARTMENT_NAME DEPARTMENT_NAME, ROUND(AVG(E.SALARY),0) 평균연봉
FROM DEPARTMENTS D, EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
GROUP BY D.DEPARTMENT_NAME
HAVING ROUND(AVG(E.SALARY),0)>=5000
ORDER BY 평균연봉 DESC;

 

문제 4) 각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 업무명(job_title), 부서명(department_name)를 조회하시오. 단 도시명(city)이 ‘Seattle’인 지역(location)의 부서(department)에 근무하는 직원만 출력하시오.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.JOB_ID = J.JOB_ID AND D.LOCATION_ID = L.LOCATION_ID
AND L.CITY='Seattle';

 

문제 5) IT 부서에서 일하는 직원의 first_name, last_name, salary 를 출력하시요. 출력결과는 salary 가 낮은 사람부터 출력하시요. 전체 결과는 아래 수행결과처럼 하나의 문자열로 결합되어서 나와야 하며 전체 결과 하나의 컬럼으로 출력되도록 작성하세요.

SELECT E.FIRST_NAME|| ' ' || E.LAST_NAME || ' 의 연봉은 ' || E.SALARY || ' 입니다.' 결과
FROM EMPLOYEES E, DEPARTMENTS D
WHERE D.DEPARTMENT_NAME='IT' AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY SALARY;

---

1. 직원중 현재시간 기준으로 근무 개월수가 10년(12*10 개월) 보다 많은(초과) 사람의 first_name, salary, hire_date, department_name 을 출력하시요.

SELECT E.FIRST_NAME, E.SALARY, E.HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND MONTHS_BETWEEN(SYSDATE,E.HIRE_DATE) > 120;

 

2. 각 부서 이름 별로 2005년 이전에 입사한 직원들의 인원수를 조회하시오.

SELECT D.DEPARTMENT_NAME,COUNT(E.EMPLOYEE_ID)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND TO_CHAR(HIRE_DATE,'YYYY')<2005
GROUP BY D.DEPARTMENT_NAME;

 

3. 사원수가 3명 이상의 사원을 포함하고 있는 부서의 부서번호(department_id), 부서이름(department_name), 사원 수, 최고급여, 최저급여, 평균급여, 급여총액을 조회하여 출력하십시오. 출력 결과는 부서에 속한 사원의 수가 많은 순서로 출력하고, 컬럼명은 아래 결과와 동일하게 출력하십시오. (평균급여 계산시 소수점 이하는 버리시오)

 

SELECT D.DEPARTMENT_ID 부서번호, D.DEPARTMENT_NAME 부서명, 
COUNT(E.EMPLOYEE_ID) 사원수, MAX(E.SALARY) 최고급여, MIN(E.SALARY) 최소급여,
ROUND(AVG(E.SALARY),0) 평균급여, SUM(E.SALARY) 급여총액
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME
HAVING COUNT(E.EMPLOYEE_ID)>=3;

 

4. SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력

SELECT S.ENAME, E.ENAME
FROM EMP S, EMP E
WHERE S.DEPTNO = E.DEPTNO AND S.ENAME='SCOTT' AND E.ENAME <> 'SCOTT';