DataBase 연습문제

-- 실습.1 
-- 연봉이 120000 이상되는 사원들의 이름 및 연봉을 출력
SELECT FIRST_NAME, SALARY*12
FROM EMPLOYEES
WHERE SALARY*12 >= 120000;

-- 실습.2
-- 사원번호가 176 인 사원의 이름과 부서 번호를 출력
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 176;

-- 실습.3
-- 연봉이 150,000 에서 200,000의 범위 이외인 사원들의 이름 및
-- 연봉을 출력하시오 (연봉 별칭 AnnSal)
SELECT FIRST_NAME, SALARY*12 AS AnnSal
FROM EMPLOYEES
WHERE SALARY*12 NOT BETWEEN 150000 AND 200000;

-- 실습.4
-- 2023/01/01 일부터 2005/05/30일 사이에 고용된 사원들의
-- 이름, 사번, 고용일자를 출력 (고용일자 역순)
SELECT FIRST_NAME, EMPLOYEE_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '2005/05/30' AND '2023/01/01'
ORDER BY HIRE_DATE DESC;

-- 실습.5
-- 20번 및 50번 부서에서 근무하는 모든 사원들의 이름 및
-- 부서번호를 알파벳 순으로 출력
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
  OR DEPARTMENT_ID = 50
ORDER BY FIRST_NAME ASC;

-- 실습.6  
-- 20번 및 50번 부서에 근무하며, 연봉이 200,000 ~ 250,000
-- 사이인 사원들의 이름 및 연봉을 출력
SELECT FIRST_NAME, SALARY*12, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY*12 BETWEEN 200000 AND 250000
AND (DEPARTMENT_ID = 20
OR DEPARTMENT_ID = 50);

-- 실습.7
-- 2006년도에 고용된 모든 사람들의 이름 및 고용일 조회
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '2006/01/01' AND '2007/01/01';

-- 실습.8
-- 매니저가 없는 사람들의 이름 및 업무를 출력
SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

-- 실습.9
-- 매니저가 있는 사람들의 이름 및 업무, 매니저번호 조회
SELECT FIRST_NAME, JOB_ID, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL;

-- 실습.10
-- 커미션을 받는 모든 사원들의 이름, 연봉 및 커미션 출력
-- 연봉 역순 정렬, 별칭 AnnSal
SELECT FIRST_NAME, SALARY*12 AS AnnSal, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY*12 DESC;

-- 실습. 11
-- 이름의 네번째 글자가 a인 사람의 이름을 조회
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '___a';

-- 실습.12
-- 이름에 a 및 e글자가 있는 사원의 이름 조회
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%a%'
AND FIRST_NAME LIKE '%e%';

-- 실습.13
-- 급여가 2500,3500,7000이 아니며 직업이 SA_REP나
-- ST_CLERK인 사원의 이름과 급여 직업 출력
SELECT FIRST_NAME, SALARY, JOB_ID
FROM EMPLOYEES
WHERE SALARY NOT IN (2500, 3500, 7000)
AND JOB_ID IN ('SA_REP', 'ST_CLERK');

-- 실습.14
-- 30번 부서내의 모든 직업들을 유일한 값으로 출력
-- 90번 부서 또한 포함, 직업을 오름차순 출력
SELECT DISTINCT JOB_ID , DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
OR DEPARTMENT_ID = 90
ORDER BY JOB_ID;

-- 실습. 15
-- 회사 전체의 최대 급여, 최소 급여, 급여 총 합 및 평균 급여 출력
SELECT MAX(SALARY), MIN(SALARY), SUM(SALARY), ROUND(AVG(SALARY))
FROM EMPLOYEES;

-- 실습. 16
-- 동일한 직업을 가진 사원들의 총 수를 출력
SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY JOB_ID;

-- 실습. 17
-- 각 직업별 최대급여, 최소급여, 급여 총 합 및 평균 급여 출력
-- 단 최대 급여는 MAX, 최소 급여는 MIN, 총 합은 SUM, 평균은 AVG로 출력
-- 직업을 오름차순으로 정렬
SELECT JOB_ID, MAX(SALARY) AS MAX, MIN(SALARY) AS MIN, SUM(SALARY) AS SUM,
       ROUND(AVG(SALARY)) AS AVG
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID ASC;

-- 실습. 18  
-- 매니저로 근무하는 사원들의 총 수를 출력
SELECT COUNT(*)
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
GROUP BY EMPLOYEE_ID;

-- 실습. 19
-- 사내의 최대 급여 및 최소 급여의 차이 출력
SELECT (MAX(SALARY) - MIN(SALARY))
FROM EMPLOYEES;

-- 실습. 20
-- 모든 사원들의 이름, 부서이름, 및 부서 번호 출력
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, D.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 실습. 21
-- 매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의
-- 급여 출력
-- 매니저 없는 사람 제외/ 최소급여 5000미만 제외/ 급여기준 역순 
SELECT MANAGER_ID, MIN(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
HAVING MIN(SALARY) < 5000
ORDER BY MIN(SALARY) DESC;

-- 실습. 22
-- 커미션을 받는 모든 사람들의
-- 이름, 부서 명, 지역ID 및 도시 명 출력
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, D.LOCATION_ID, L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
JOIN LOCATIONS L ON(D.LOCATION_ID = L.LOCATION_ID)
WHERE E.COMMISSION_PCT IS NOT NULL;

* SELF JOIN

셀프 조인문제, 같은 테이블끼리도 JOIN 가능

-- 실습. 23
-- 자신의 매니저보다 먼저 고용된 사원들의 이름 및 고용일 출력
SELECT E1.LAST_NAME, E1.HIRE_DATE
  FROM EMPLOYEES E1, EMPLOYEES E2
 WHERE E1.MANAGER_ID = E2.EMPLOYEE_ID
   AND E1.HIRE_DATE < E2.HIRE_DATE;

-- 실습. 24
-- 부서 명, 부서위치ID, 각 부서 별 사원의 총 수, 각 부서 별 평균 급여 출력
-- 부서 위치 오름차순
SELECT D.DEPARTMENT_NAME, D.LOCATION_ID, COUNT(E.DEPARTMENT_ID), ROUND(AVG(SALARY))
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
ORDER BY D.LOCATION_ID ASC;

-- 실습. 25
-- Zlotkey 와 동일한 부서에 근무하는 다른 모든 사원들의 사번 및 고용날짜 출력
SELECT D.DEPARTMENT_ID, E.HIRE_DATE
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                         FROM EMPLOYEES
                         WHERE LAST_NAME = 'Zlotkey');

-- 실습. 26
-- 회사 전체 평균급여보다 급여를 많이 받는 사원들의 사번 및 이름 출력
SELECT DEPARTMENT_ID, FIRST_NAME
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEES);

-- 실습. 27
-- 이름에 u가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 이름 출력
SELECT DEPARTMENT_ID, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID
                       FROM EMPLOYEES
                       WHERE FIRST_NAME LIKE'%u%');

-- 실습. 28
-- 시애들에 근무하는 사람 중 커미션을 받지 않는 모든 사람들의
-- 이름, 부서 명, 지역ID를 출력
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.LOCATION_ID
FROM DEPARTMENTS D, EMPLOYEES E ,LOCATIONS L
WHERE CITY = 'Seattle'
AND COMMISSION_PCT IS NULL;

-- 실습. 29
-- 이름이 DAVIES 인 사람보다 후에 고용된 사원들의 이름 및 고용일자 출력
-- 단. 고용일자 역순
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (SELECT HIRE_DATE
                   FROM EMPLOYEES
                   WHERE LAST_NAME = 'Davies')
ORDER BY HIRE_DATE DESC;

-- 실습. 30
-- KING을 매니저로 두고 있는 모든 사원들의 이름 및 급여 출력
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = 100;

-- 실습. 31
-- 회사 전체 평균급여보다 더 많이 받는 사원들 중 이름에 U가 있는 사원들이
-- 근무하는 부서에서 근무하는 사원들의 사번, 이름 및 급여 출력
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEES)
AND DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID
                     FROM EMPLOYEES
                     WHERE FIRST_NAME LIKE '%u%');