[데이터베이스] 문제 모음. 01

0123

- 테이블 예시 -

 

 

1. Sales 부서에서 입사일의 평균은?

SELECT ROUND((AVG(TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')))),0 ) AS "입사년도", 
ROUND((AVG(TO_NUMBER(TO_CHAR(HIRE_DATE,'MM')))),0 ) AS "월", ROUND((AVG(TO_NUMBER(TO_CHAR(HIRE_DATE,'DD')))),0 ) AS "일"
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND DEPARTMENT_NAME = 'Sales';

---

2. ASIA에서 DEPARTMENT_NAME이 IT Support 인 사원의 이름을 출력 

SELECT DEPARTMENT_NAME,FIRST_NAME
FROM DEPARTMENTS D, EMPLOYEES E, LOCATIONS L, COUNTRIES C, REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND D.LOCATION_ID = L.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID
AND C.REGION_ID = R.REGION_ID
AND R.REGION_NAME = 'ASIA'
AND DEPARTMENT_NAME = 'IT Support';

---

3.  부서명이 Marketing 인 사원중 EUROPE에 속해있는 FIRST_NAME을 출력 

 

SELECT DEPARTMENT_NAME,FIRST_NAME
FROM DEPARTMENTS D, EMPLOYEES E, LOCATIONS L, COUNTRIES C, REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND D.LOCATION_ID = L.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID
AND C.REGION_ID = R.REGION_ID
AND R.REGION_NAME = 'Europe'
AND DEPARTMENT_NAME = 'Marketing';

---

4. SALARY가 가장 많은 사원의 FIRST_NAME 출력

SELECT FIRST_NAME 사원명
FROM EMPLOYEES
WHERE SALARY = (SELECT MAX(SALARY)
                FROM EMPLOYEES);

---

5. Sales부서에서 HIRE_DATE가 가장 빠른사원을 조회

SELECT E.FIRST_NAME,D.DEPARTMENT_ID, D.DEPARTMENT_NAME, HIRE_DATE 
FROM EMPLOYEES E, DEPARTMENTS D 
WHERE D.DEPARTMENT_NAME='SALES' 
AND HIRE_DATE =(SELECT MIN(HIRE_DATE) AS HIRE_DATE 
		FROM EMPLOYEES E, DEPARTMENTS D 
                WHERE D.DEPARTMENT_NAME='SALES');

---

6.  자신의 부서 평균 SALARY 보다 SALARY가 높은 사원의
     DEPARTMENT_ID, FIRST_NAME, SALARY 조회

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES E, (SELECT DEPARTMENT_ID DEP, AVG(SALARY) SAL
                FROM EMPLOYEES
                GROUP BY DEPARTMENT_ID) A
WHERE E.DEPARTMENT_ID = A.DEP
AND SALARY > A.SAL
ORDER BY DEPARTMENT_ID ASC, SALARY DESC;

---

7. 부서별 최고 SALARY를 가진 사원의 FIRST_NAME SALARY를 높은순으로 조회

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

---

8. 부서별 SALARY 상위 5명의 DEPARTMENT_ID, FIRST_NAME, SALARY를 조회

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY
FROM (SELECT DEPARTMENT_ID, FIRST_NAME, SALARY, DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc) DEP_RANK
      FROM EMPLOYEES)
WHERE DEP_RANK <= 5
ORDER BY DEPARTMENT_ID ASC;

---

마무리

데이터베이스에서 데이터를 조회하는 방법입니다. jdbc를 통해 자바와 연동하거나

mybatis를 이용할 때 자주 사용할 것 같습니다.