오라클 DB 문제 풀이

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

-- EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서 번호, 인원수, 급여의 합을 출력하라
SELECT DEPARTMENT_ID, COUNT(*), SUM(SALARY) 
FROM HR_EMPLOYEES 
GROUP BY DEPARTMENT_ID 
HAVING COUNT(*)>4

-- EMP테이블에서 가장 많은 사원이 속해 있는 부서번호와 사원수를 출력하라
SELECT DEPARTMENT_ID, COUNT(DEPARTMENT_ID) CNT 
FROM HR_EMPLOYEES 
GROUP BY DEPARTMENT_ID 
ORDER BY CNT DESC 
LIMIT 1

-- EMP테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하라.
SELECT MANAGER_ID 
FROM HR_EMPLOYEES 
WHERE EMPLOYEE_ID = (SELECT EMP.EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID, COUNT(*) CNT FROM HR_EMPLOYEES GROUP BY MANAGER_ID) EMP ORDER BY CNT DESC LIMIT 1)


-- EMP테이블에서 사원번호가 133인 사원의 직업과 같고 사원번호가 134인 사원의 급여보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하라
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY 
FROM HR_EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID FROM HR_EMPLOYEES WHERE EMPLOYEE_ID=133) AND SALARY > (SELECT SALARY FROM HR_EMPLOYEES WHERE EMPLOYEE_ID=134) 

-- 직업별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하라
-- 조건 1. 직업별로 내림차순 정렬
SELECT * 
FROM ( SELECT EMP.EMPLOYEE_ID , EMP.FIRST_NAME, EMP.JOB_ID JOB_ID, DEP.DEPARTMENT_NAME 
	FROM HR_EMPLOYEES EMP INNER JOIN HR_DEPARTMENTS DEP
	ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
	GROUP BY JOB_ID
	ORDER BY SALARY ASC
	) TEMP
ORDER BY TEMP.JOB_ID

-- 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하라
-- 조건1.한달 근무일수는 20일, 하루 근무시간을 8시간이다
-- 조건2.시급은 소수 두 번째 자리에서 반올림한다
-- 조건3.부서별로 오름차순 정렬
-- 조건4.시급이 많은 순으로 출력
SELECT DEPARTMENT_ID, FIRST_NAME, ROUND(SALARY/160,2) 시급 
FROM HR_EMPLOYEES 
ORDER BY DEPARTMENT_ID, 시급 DESC

-- 사원 별 커미션이 0 또는 NULL이고 부서위치가 ‘E’또는 'D'로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하라.
-- 조건1. 보너스가 NULL이면 0으로 출력
SELECT EMP.EMPLOYEE_ID, EMP.FIRST_NAME, IF(EMP.COMMISSION_PCT IS NULL,0,0) COM, EMP.DEPARTMENT_ID, DEP.DEPARTMENT_NAME , LOC.CITY
FROM HR_EMPLOYEES EMP INNER JOIN HR_DEPARTMENTS DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
INNER JOIN HR_LOCATIONS LOC
ON DEP.LOCATION_ID = LOC.LOCATION_ID
WHERE (EMP.COMMISSION_PCT = 0 OR EMP.COMMISSION_PCT IS NULL) AND (LOC.CITY LIKE '%E' OR LOC.CITY LIKE '%D')

-- 부서별 평균 급여가 7000이상이면 초과, 그렇지 않으면 미만을 출력하라
SELECT DEPARTMENT_ID, IF(AVG(SALARY)>7000,'초과','미만') 비고
FROM HR_EMPLOYEES
GROUP BY DEPARTMENT_ID

-- 부서별 입사일이 가장 오래된 사원을 한명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하라
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE
FROM HR_EMPLOYEES 
GROUP BY DEPARTMENT_ID
ORDER BY HIRE_DATE ASC

-- 1999년 2월 7일 이후 입사사중 커미션이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 커미션을 출력하라.
SELECT FIRST_NAME, 
CASE 
	WHEN COMMISSION_PCT=0 THEN 500
	WHEN COMMISSION_PCT IS NULL THEN 500
	ELSE COMMISSION_PCT
END COM
FROM HR_EMPLOYEES
WHERE HIRE_DATE>'1999-02-07'

-- 1999년 1월 1일 ~ 2000년 1월 1일 입사자 중 부서명이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하라
-- 조건1. 입사일 오름차순 출력
SELECT EMP.DEPARTMENT_ID, EMP.FIRST_NAME, EMP.JOB_ID, EMP.HIRE_DATE, DEP.DEPARTMENT_NAME
FROM HR_EMPLOYEES EMP INNER JOIN HR_DEPARTMENTS DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
WHERE EMP.HIRE_DATE>'1990-01-01' AND EMP.HIRE_DATE<'2005-01-01' AND DEP.DEPARTMENT_NAME LIKE '%SALES%'
ORDER BY HIRE_DATE

-- 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하라.
-- 조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
SELECT SYSDATE() 현재시간, ADDTIME(SYSDATE(),'0 1:0:0') 한시간뒤

-- 각 부서별 사원수를 출력하라
-- 조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
-- 조건2. 부서별 사원수가 0인 경우 '없음' 출력
-- 조건3. 부서번호 오름차순 정렬
SELECT EMP.DEPARTMENT_ID, DEP.DEPARTMENT_NAME, IF(COUNT(*)=0,'없음',COUNT(*)) 사원수
FROM HR_EMPLOYEES EMP INNER JOIN HR_DEPARTMENTS DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
GROUP BY EMP.DEPARTMENT_ID
ORDER BY EMP.DEPARTMENT_ID ASC

-- 본인의 매니저 보다 많은 급여를 받는 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하라
SELECT EMP1.EMPLOYEE_ID 사원, EMP1.FIRST_NAME 이름, EMP2.EMPLOYEE_ID 매니저, EMP2.FIRST_NAME 이름
FROM HR_EMPLOYEES EMP1, HR_EMPLOYEES EMP2
WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID AND EMP1.SALARY> EMP2.SALARY

-- 사원의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때, 모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하라.
SELECT EMP1.FIRST_NAME, 
	IF(EMP1.COMMISSION_PCT IS NULL,0,EMP1.COMMISSION_PCT) COMM1, 
	EMP2.COMM2, 
	IF(EMP1.COMMISSION_PCT IS NULL,0+EMP2.COMM2,EMP1.COMMISSION_PCT+EMP2.COMM2)
FROM HR_EMPLOYEES EMP1,
	(SELECT COMMISSION_PCT COMM2
	FROM HR_EMPLOYEES
	WHERE FIRST_NAME LIKE 'A%LL%_') EMP2

-- 부서별로 1998년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
-- 조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
-- 조건2. 부서번호 오름차순 정렬
-- 조건3. 입사일 오름차순 정렬
SELECT EMP.DEPARTMENT_ID, DEP.DEPARTMENT_NAME, EMP.EMPLOYEE_ID, EMP.FIRST_NAME, EMP.HIRE_DATE
FROM HR_EMPLOYEES EMP INNER JOIN HR_DEPARTMENTS DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
WHERE EMP.HIRE_DATE >'1998-05-31'
ORDER BY EMP.DEPARTMENT_ID ASC, EMP.HIRE_DATE ASC

-- 입사일로부터 지금까지 근무년수가 20년 이상인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하라.
-- 조건1. 근무년수는 월을 기준으로 버림
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, FLOOR(DATEDIFF(SYSDATE(),HIRE_DATE)/365)
FROM HR_EMPLOYEES
WHERE DATEDIFF(SYSDATE(),HIRE_DATE)/365>=20