[Oracle] 오라클 데이터베이스 연습문제

1. SQL 문제

1)2006년도에 고용된 모든 사람들의 이름 및 고용일을 조회한다

SELECT LAST_NAME, HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE LIKE '06%';

 

2)매니저가 없는 사람들의 이름 및 업무를 출력한다.

SELECT LAST_NAME, JOB_ID

FROM EMPLOYEES

WHERE MANAGER_ID IS NULL;

 

3)매니저가 있는 사람들의 이름 및 업무, 매니저 번호를 조회한다.

SELECT LAST_NAME, JOB_ID, MANAGER_ID

FROM EMPLOYEES

WHERE MANAGER_ID IS NOT NULL;

4)커미션을 받는 모든 사원들의 이름, 연봉 및 커미션을 출력한다.

연봉을 역순으로 정렬하고, 연봉은 ANNSAL로 출력한다.

SELECT LAST_NAME, SALARY*12 AS ANNSAL, COMMISION_PCT

FROM EMPLOYEES

WHERE COMMISION_PCT IS NOT NULL

ORDER BY SALARY*12 DESC;

 

5)이름의 네 번째 글자가 a인 사원의 이름을 조회한다.

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE '___a%';

 

6)이름에 a 및 글자 e가 있는 사원의 이름을 조회한다.

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE '%a%' AND LAST_NAME LIKE '%e%';

 

7)급여가 2500, 3500, 7000이 아니며 직업이 SA_REP나 ST_CLERK인 사원의 이름과 급여 직업을 출력한다.

SELECT LAST_NAME, SALARY, JOB_ID

FROM EMPLOYEES

WHERE SALARY NOT IN (2500,3500, 7000) AND

JOB_ID IN('SA_REP', 'ST_CLERK'); <<문자열에 ' '꼭 붙이기!!

 

8)30번 부서 내의 모든 직업들을 유일한 값으로 출력한다.

90번 부서 또한 포함하고, 직업을 오름차순으로 출력한다.

SELECT DISTINCT JOB_ID, DEPARTMENT_ID 

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (30,90)

ORDER BY JOB_ID;

 

9) 모든 사원들의 이름, 부서이름 , 부서번호를 출력한다.

SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_NAME, D.DEPARMENT_ID

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

 

10)커미션을 받는 모든 사람들의 이름, 부서명, 지역ID 및 도시명을 출력한다.

SELECT E.LAST_NAME, D.DEPARTMENT_NAME, L.LOCATION_ID, L.CITY

FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L

WHERE E.DAPRTMENT_ID = D.DEPARTMENT_ID

AND D.LOCATION_ID = L.LOCATION_ID

AND E.COMMISION_PCT IS NOT NULL;

 

11)자신의 매니저보다 먼저 고용된 사원들의 이름 및 고용일을 출력한다.

SELECT E.LAST_NAME, E.EMPLOYEE_ID, E.HIRE_DATE

FROM EMPLOYEES E, EMPLOYEES M

WHERE E.MANAGER_ID = M.MANAGER_ID

AND E.HIRE_DATE < M.HIRE_DATE

ORDER BY E.LAST_NAME;

 

12)회사 전체의 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력한다.

SELECT MAX(SALARY), MIN(SALARY), SUM(SALARY), AVG(SALARY)

FROM EMPLOYEES;

13)각 직업별, 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력한다.

단 최대 급여는 MAX, 최소 급여는 MIN, 그병 총 합은 SUM 및 평균 급여는 AVG로 출력하고,

직업을 오름차순으로 출력한다.

SELECT JOB_ID, MAX(SALARY) MAX, MIN(SALARY) MIN , SUM(SALARY) SUM , AVG(SALARY) AVG

FROM EMPLOYEES

GROUP BY JOB_ID  <<GROUP BY 목록의 열 중 그룹 함수에 없는 열은 모두 GROUP BY 절에 포함되어야 함

ORDER BY JOB_ID;

 

14)동일한 직업을 가진 사원들의 총 수를 출력한다.

SELECT JOB_ID, COUNT(EMPLOYEE_ID)

FROM EMPLOYEES

GROUP BY JOB_ID

ORDER BY JOB_ID;

 

15)매니저로 근무하는 사원들의 총 수를 출력한다.

SELECT COUNT(DISTINCT MANAGER_ID)

FROM EMPLOYEES;

 

16)사내의 최대 급여 및 최소 급여의 차이를 출력한다.

SELECT MAX(SALARY) - MIN(SALARY)

FROM EMPLOYEES;

 

17)매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력한다.

-매니저가 없는 사람들은 제외

-최소 급여가 5000 미만인 경우는 제외

-급여 기준 역순으로 조회

SELECT MANAGER_ID, MIN(SALARY)

FROM EMPLOYEES

WHERE MANAGER_ID IS NOT NULL

GROUP BY MANAGER_ID

HAVING MIN(SALARY) >= 5000 <<HAVING 절을 사용하여 그룹을 제한한다. 행이 그룹화 되고, 그룹 함수가 적용됨

ORDER BY MIN(SALARY)DESC;

 

18)부서명, 부서위치ID, 각 부서별 사원 총 수, 각 부서 별 평균 급여를 출력하되, 부서위치를 오름차순 출력한다.

SELECT D.DEPARTMENT_ID, D.LOCATION_ID, COUNT(E.EMPLOYEE_ID), AVG(E.SALARY) AVG_SALARY

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY D.DEPARTMENT_NAME, D.LOCATION_ID

ORDER BY D.LOCATION_ID;

 

19) Zlotkey와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 출력한다.

SELECT EMPLOYEE_ID, HIRE_DATE

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Zlotkey')

AND LAST_NAME != 'Zlotkey';

 

20)회사 전체 평균 급여보다 더 급여를 많이 받는 사원들의 사번 및 이름을 출력한다.

SELECT EMPLOYEE_ID, LAST_NAME

FROM EMPLOYEES

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

 

---

 

21) 부서 테이블과 사원 테이블에서 사원, 사원명, 급여, 부서명을 출력한다.

단 , 급여가 2000 이상인 사원에 대해 급여 기준으로 내림차순 정렬한다.

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, D.DEPARTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND SALARY >= 2000

ORDER BY SALARY DESC;

 

22)부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여 , 부서명을 출력한다.

단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순 정렬
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.JOB_ID, E.SALARY, D.DEAPRTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND JOB = 'MANAGER'

AND SALARY >= 2500

ORDER BY EMPLOYEE_ID;