SELECT DEPARTMENT_ID FROM EMPLOYEES e ;
SELECT * FROM DEPARTMENTS d ;
inner join 사원들의 부서원까지만..출력
SELECT e.EMPLOYEE_ID , e.DEPARTMENT_ID
, d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
INNER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.EMPLOYEE_ID = 178
;
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IS NULL ;
left outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME
, d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d -- DEPARTMENTS 를 기준으로 가져옴
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;
right outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME
, d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
RIGHT OUTER JOIN DEPARTMENTS d -- DEPARTMENTS 를 기준으로 가져옴
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;
SELECT *
FROM JOB_HISTORY jh
ORDER BY EMPLOYEE_ID
;
우리 회사에 있는 사원중에서 사원 이름, 사원 번호, 부서 이동 정보(Start_date, end_date, job_id)를 출력
SELECT e.EMPLOYEE_ID , e.FIRST_NAME, e.HIRE_DATE
, jh.START_DATE , jh.END_DATE , jh.JOB_ID
FROM EMPLOYEES e
LEFT OUTER JOIN JOB_HISTORY jh
ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID
;문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT e.FIRST_NAME , d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라
SELECT e.FIRST_NAME , e.JOB_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.DEPARTMENT_ID = 30
;문제3) 커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라
SELECT e.FIRST_NAME , e.JOB_ID , e.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.COMMISSION_PCT IS NOT NULL
;문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업,부서번호,부서명을 출력하라
SELECT d.LOCATION_ID , e.FIRST_NAME , e.JOB_ID , d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM EMPLOYEES e
FULL OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE d.LOCATION_ID = 2500
;문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT e.FIRST_NAME , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.FIRST_NAME LIKE '%A%'
;문제6) 사원이름과 그 사원의 관리자 이름을 출력하라 ???????
SELECT e.EMPLOYEE_ID, e.FIRST_NAME ,e2.FIRST_NAME
FROM EMPLOYEES e
INNER JOIN EMPLOYEES e2
ON e.EMPLOYEE_ID = e2.MANAGER_ID
;문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
SELECT e.FIRST_NAME , d.DEPARTMENT_NAME, e.JOB_ID , e.DEPARTMENT_ID , e.SALARY
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.SALARY >= 3000
ORDER BY SALARY
;문제8) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
SELECT e.FIRST_NAME , d.DEPARTMENT_NAME, SALARY
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.SALARY BETWEEN 3000 AND 5000
ORDER BY SALARY
;문제9) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라
SELECT e.FIRST_NAME , e.SALARY , d.LOCATION_ID , l.CITY
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS l
ON d.LOCATION_ID = l.LOCATION_ID
WHERE SALARY <= 3000
;문제10) Steven King의 부서명을 출력하라.
SELECT d.DEPARTMENT_NAME , e.FIRST_NAME , e.LAST_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
WHERE e.FIRST_NAME LIKE 'Steven'
AND e.LAST_NAME LIKE 'King'
;문제11) IT부서에서 근무하고 있는 사람들을 출력하라.
SELECT e.FIRST_NAME , e.LAST_NAME , d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE d.DEPARTMENT_NAME = 'IT'
;문제12) EMPLOYEES 테이블에서 사원번호,이름,업무, EMPLOYEES 테이블의 부서번호, DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID , e.DEPARTMENT_ID
, d.DEPARTMENT_ID , d.DEPARTMENT_NAME, d.LOCATION_ID
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;문제13) EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고 SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , e.SALARY , d.DEPARTMENT_ID , d.LOCATION_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE JOB_ID = 'SA_MAN'
;문제14) EMPLOYEES 테이블과 DEPARTMENTS 테이블에서 DEPARTMENTS 테이블 기준으로 사원번호,이름,업무, 부서번호,부서명,근무지를 EMPLOYEES 테이블의 부서번호 출력하여라
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID , d.DEPARTMENT_ID , d.DEPARTMENT_NAME
, d.LOCATION_ID , e.DEPARTMENT_ID
FROM EMPLOYEES e
RIGHT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;연봉 평균이하인사람 구하기 를 한번에 만들기
--6462
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY <6462
;
위 두개를 합친 모양
SELECT *
FROM EMPLOYEES e
WHERE SALARY < (SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e)
;
SELECT *
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US'
;
SELECT LOCATION_ID
FROM DEPARTMENTS d
WHERE LOCATION_ID IN (1400, 1500, 1600, 1700)
;
SELECT *
FROM DEPARTMENTS d
WHERE LOCATION_ID IN (SELECT LOCATION_ID
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US')
;
월급이 가장 적은 사원
SELECT MIN(SALARY)
FROM EMPLOYEES e
;
SELECT FIRST_NAME , SALARY
FROM EMPLOYEES e
WHERE e.SALARY IN (SELECT MIN(SALARY)
FROM EMPLOYEES e)
;
월급이 가장 많은 사원
SELECT MAX(SALARY)
FROM EMPLOYEES e
;
SELECT FIRST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY IN (SELECT MAX(SALARY)
FROM EMPLOYEES e)
;
문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.
SELECT SALARY
FROM EMPLOYEES e
;
SELECT EMPLOYEE_ID ,FIRST_NAME , LAST_NAME , JOB_ID , SALARY
FROM EMPLOYEES e
WHERE e.SALARY > (SELECT SALARY
FROM EMPLOYEES e
WHERE LAST_NAME = 'Kochhar')
;문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES e)
ORDER BY SALARY
;문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라
SELECT MIN(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 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)
;문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라.
단 업무별로 정렬하여라.
--업무별 급여의 최소값.
SELECT JOB_ID , MIN(SALARY)
FROM EMPLOYEES e
GROUP BY JOB_ID
;
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID , e.SALARY , e.DEPARTMENT_ID
FROM EMPLOYEES e
WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID , MIN(SALARY)
FROM EMPLOYEES e
GROUP BY JOB_ID)
ORDER BY JOB_ID
;문제5) EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 사원번호,이름,직업,급여를 출력하라.
SELECT JOB_ID FROM EMPLOYEES e WHERE EMPLOYEE_ID = 123;
SELECT SALARY FROM EMPLOYEES e WHERE EMPLOYEE_ID = 192;
SELECT EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY
FROM EMPLOYEES e
WHERE JOB_ID = (SELECT JOB_ID FROM EMPLOYEES e WHERE EMPLOYEE_ID = 123)
AND SALARY > (SELECT SALARY FROM EMPLOYEES e WHERE EMPLOYEE_ID = 192)
;문제6) EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라.
-- 단 50번은 제외
SELECT MIN(SALARY) FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50;
SELECT *
FROM EMPLOYEES e
WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50)
AND DEPARTMENT_ID <> 50
;/*SELECT e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID , jh.START_DATE , e.SALARY , e.DEPARTMENT_ID
FROM EMPLOYEES e
LEFT OUTER JOIN JOB_HISTORY jh
ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID
WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50)
ORDER BY SALARY
;
*/