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

---

2015년 Naver Blog 기록

---

서브쿼리

 

문제 1) JOB이 MANAGER인 직원들의 평균 급여보다 높은 직원들을 출력(EMPNO,ENAME,SAL)   SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL > ( SELECT AVG(SAL)  FROM EMP  WHERE JOB='MANAGER');

 

 

문제 2) 'SMITH'와 같은 JOB을 가지는 사원의 이름,급여,JOB을 나타내기(급여가 적은 사원부터)   SELECT ENAME,SAL,JOB FROM EMP WHERE JOB = ( SELECT JOB  FROM EMP  WHERE ENAME='SMITH') ORDER BY SAL;   TIP - 나중에 들어가는 쿼리가 서브쿼리가 된당

 

 

문제 3) 부서의 위치가 'DALLAS'인 부서의 평균 급여보다 높은 직원 출력     (EMPNO,ENAME,DNAME,CITY)   SELECT E.EMPNO,E.ENAME,D.DNAME,L.CITY FROM EMP E, DEPT D, LOCATIONS L WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE AND    E.SAL > ( SELECT AVG(E.SAL)    FROM EMP E, DEPT D, LOCATIONS L    WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE AND  L.CITY = 'DALLAS');   검산 SELECT AVG(E.SAL) FROM EMP E, DEPT D, LOCATIONS L WHERE E.DEPTNO = D.DEPTNO AND D.LOC_CODE = L.LOC_CODE AND L.CITY='DALLAS';

---

1. Employees 테이블에서 입사일자(hire_date)에 따라 2005년 입사한 직원들 가운데 first_name이 'Lisa'인 직원보다 빨리 입사한 직원의 사번(employee_id), 이름(first_name), 성(last_name), 입사일자(hire_date)를 조회하는 SQL 문장을 작성하시오. 
단, <실행 결과>처럼 first_name과 하나의 공백(space bar), last_name 은 name 이라는 컬럼명으로 출력되도록 합니다. 

 

 

 

SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME NAME, HIRE_DATE FROM EMPLOYEES WHERE SUBSTR(HIRE_DATE,1,2) = 05 AND    HIRE_DATE < ( SELECT HIRE_DATE   FROM EMPLOYEES   WHERE FIRST_NAME = 'Lisa') ORDER BY HIRE_DATE;

 

 

 

 

 

2.Sales’부서에 속한 직원의 이름(first_name), 급여(salary), 부서이름(department_name)을 조회하시오. 단, 급여는 100번 부서의 평균보다 적게 받는 직원 정보만 출력되어야 합니다.

 

 

 

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND  E.SALARY > ( SELECT AVG(E.SALARY)  FROM EMPLOYEES E, DEPARTMENTS D  WHERE D.DEPARTMENT_ID = 100);

 

 

 

 

3. De Haan 사원의 관리자 사원번호, 이름(last_name), 입사일 및 급여를 표시하는 SQL문을 작성하십시오.alias를 포함하여 실행결과와 동일하게 출력되어야 합니다.

 

 

 

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = ( SELECT MANAGER_ID FROM EMPLOYEES WHERE LAST_NAME='De Haan');

 

---

1. 부서 이름(department_name) 별 직원들의 평균연봉(salary) 을 조회하시오.

단,'30번’ 부서의 직원 평균 연봉보다 평균 연봉이 이하인 부서 정보만 출력되어야 합니다.

 

 

 

 

2. 업무명(job_title)이 ‘Sales Representative’인 직원 중에서 

연봉(salary)이 9,000 이상, 10,000 이하인 직원들의 

이름(first_name), 성(last_name)과 연봉(salary)을 출력하시오

 

SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES  WHERE SALARY BETWEEN 9000 AND 10000   AND JOB_ID =  (SELECT JOB_ID  FROM JOBS  WHERE  JOB_TITLE = 'Sales Representative');

 

 

3.각 부서(department_id)별로 최고 연봉(salary)를 받는 사원의 

사번(employee_id), 성(last_name)과 연봉(salary)을 조회하시오. 

단 조회결과는 연봉의 내림차순으로 정렬되어 나타나야 합니다.

SELECT DEPARTMENT_ID, EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES WHERE (DEPARTMENT_ID, SALARY) IN (SELECT DEPARTMENT_ID, MAX(SALARY)     FROM EMPLOYEES     GROUP BY DEPARTMENT_ID)     ORDER BY SALARY DESC;