[DBMS] 시험 및 예제 문제

김윤섭·2023년 2월 10일

DBMS

목록 보기

6/8

기본함수

EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무(대문자로),부서번호를 출력하라.

SELECT 	EMPLOYEE_ID , FIRST_NAME , LAST_NAME  
	, 	UPPER(JOB_ID) , DEPARTMENT_ID
FROM 	EMPLOYEES e
WHERE 	LOWER (LAST_NAME) = 'king'
;

SALARY+SALARYnvl(COMMISSION_PCT ,0) 이
10000이상이면, 'good' ,
5000 이상이면 'average',
1이상 5000미만이면 'bad',
0이면 no good 로 평가하고
EMPLOYEE_ID ,FIRST_NAME , SALARY ,COMMISSION_PCT, SALARY+SALARYnvl(COMMISSION_PCT ,0) 평가를 출력해라.

SELECT 	EMPLOYEE_ID ,	FIRST_NAME 
	, 	SALARY ,	COMMISSION_PCT AS cp
	, SALARY+SALARY*nvl(COMMISSION_PCT ,0) AS sal
	, SALARY*nvl(COMMISSION_PCT ,0) AS nvl
	,	CASE WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 10000 THEN 'good'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 5000 	
             AND SALARY+SALARY*nvl(COMMISSION_PCT ,0) < 10000 THEN 'average'
		 	 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) < 5000 	THEN 'bad'
		 	 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) = 0 		THEN 'no good' 
		 	 END AS grade
FROM 	EMPLOYEES e
ORDER BY	SALARY+SALARY*nvl(COMMISSION_PCT ,0)
;

group by, having

EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하라.

단, 판매원(SA_)은 제외하고 월 급여 합계로 정렬(내림차순)하라.

SELECT 	JOB_ID 	, 	SUM(SALARY)
FROM 	EMPLOYEES e 
WHERE 	JOB_ID NOT LIKE 'SA_%'
GROUP BY JOB_ID 
HAVING SUM(SALARY) > 10000
ORDER BY SUM(SALARY) DESC  
;

EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라

SELECT 	DEPARTMENT_ID , COUNT(*)
	,   SUM(SALARY)
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
HAVING 	COUNT(*) > 4
;

EMPLOYEES 테이블에 등록되어 있는 인원수, 커미션이 NULL이 아닌 인원수, 연봉평균, 등록되어 있는 부서의 수를 구하여 출력하라.

SELECT 	count(*)
	, 	count(COMMISSION_PCT) -- null값 자동제거
	, 	AVG(SALARY)
	,	COUNT(DISTINCT DEPARTMENT_ID) 
FROM 	EMPLOYEES e 
;

EMPLOYEES 테이블에서 job_id가 'SA'로 시작하는 사람에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

SELECT 	AVG(SALARY)	, MAX(SALARY)
	, 	MIN(SALARY) , SUM(SALARY)  
FROM 	EMPLOYEES e 
WHERE	JOB_ID LIKE UPPER('sa%')

join

지역번호 2500 에서 근무하는 사원의 이름, 직업,부서번호,부서명을 출력하라

SELECT  e.FIRST_NAME 	, j.JOB_TITLE 
	,	e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
	, 	d.LOCATION_ID 
FROM 	EMPLOYEES e
	LEFT OUTER JOIN DEPARTMENTS d  
	ON d.DEPARTMENT_ID = e.DEPARTMENT_ID 
	LEFT OUTER JOIN JOBS j 
	ON j.JOB_ID = e.JOB_ID 
WHERE d.LOCATION_ID = 2500
;

이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라

SELECT  e.FIRST_NAME , j.JOB_TITLE 
FROM 	EMPLOYEES e
	LEFT OUTER JOIN JOBS j 
	ON j.JOB_ID = e.JOB_ID 
WHERE e.FIRST_NAME LIKE upper('%a%')

사원이름과 그 사원의 관리자 이름을 출력하라

SELECT  e.FIRST_NAME , e2.FIRST_NAME 
	,	e.MANAGER_ID , e2.EMPLOYEE_ID 
FROM 	EMPLOYEES e
	LEFT OUTER JOIN EMPLOYEES e2
	ON e.MANAGER_ID = e2.EMPLOYEE_ID 
;

subQuery

EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라

SELECT 	DEPARTMENT_ID , min(salary)
FROM 	EMPLOYEES e 
group BY DEPARTMENT_ID 
HAVING min(SALARY)>	(SELECT min(salary)
					FROM	EMPLOYEES e 
					WHERE 	DEPARTMENT_ID = 100)

업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라. 단, 업무별로 정렬하여라.

SELECT  e.EMPLOYEE_ID 	,	e.FIRST_NAME 
	,	e.JOB_ID 		,	e.SALARY 
FROM 	EMPLOYEES e
WHERE 	(JOB_ID, SALARY) IN (	SELECT 	JOB_ID 	,  MIN(SALARY) 
								FROM 	EMPLOYEES e 
								GROUP BY JOB_ID )
ORDER BY JOB_ID
;

EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 사원번호,이름,직업,급여를 출력하라.

SELECT	EMPLOYEE_ID ,	FIRST_NAME 
	,	JOB_ID 		,	SALARY
FROM	EMPLOYEES e
WHERE 	e.JOB_ID = (SELECT JOB_ID 
					FROM EMPLOYEES e2 
					WHERE EMPLOYEE_ID = 123)
AND 	e.SALARY > (SELECT SALARY  
					FROM EMPLOYEES e3 
					WHERE EMPLOYEE_ID = 192)

1. EMPLOYEES 테이블에서 job_id가 'SA"로 시작하는 사람들의 급여의 평균, 최고액, 최저액, 합계를 출력하여라

2. EMPLOYEES 테이블에 등록되어있는 인원수, 커미션이 NULL이 아닌 인원수, 연봉 편균, 등록되어있는 부서의 수를 구하여 출력하라.

3. EMPLOYEES 테이블에서 부서별 인원수,평균급여, 최저급여, 최고급여, 급여의 총합을 출력하라

4. EMPLOYEES 테이블에서 각 부서별 인원수, 급여의 평균, 최저급여, 최고급여, 급여의 합을 급여의 합이 많은 사람 순으로 정렬해서 출력해라

5. EMPLOYEES 테이블에서 부서별, 업무별로 그룹하여 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.