JOIN

Q. 직급이 대리인 사원의 사번, 사원명, 직급명, 급여조회

SELECT
    emp_id,
    emp_name,
    job_name,
    salary
FROM
         employee
    JOIN job USING ( job_code )
WHERE
    job_name = '대리';

Q. 부서가 인사관리부인 사원들의 사번, 이름, 보너스 조회

SELECT
    emp_id,
    emp_name,
    bonus,
    dept_title
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
WHERE
    dept_title = '인사관리부';

Q. DEPARTMENT와 LOCATION 테이블을 참고하여 전체 부서의 부서코드, 부서명, 지역코드, 지역명 조회

SELECT
    dept_id,
    dept_title,
    location_id,
    local_name
FROM
         department
    JOIN location ON ( location_id = local_code );

Q. 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명 조회

SELECT
    emp_id,
    emp_name,
    bonus,
    dept_title
FROM
         employee
    JOIN department ON ( dept_id = dept_code )
WHERE
    bonus IS NOT NULL;

Q.  부서가 총무부가 아닌 사원들의 사원명, 급여 조회

SELECT
    emp_name,
    salary
FROM
         employee
    JOIN department ON ( dept_id = dept_code )
WHERE
    dept_title != '총무부';

Q. 사번, 사원명, 부서명, 지역명, 국가명 조회

SELECT
    emp_id,
    emp_name,
    dept_title,
    local_name,
    national_name
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
    JOIN location ON ( location_id = local_code )
    JOIN national USING ( national_code );

Q. 사번, 사원명, 부서명, 직급명, 지역명, 국가명, 급여등급 조회

SELECT
    emp_id,
    emp_name,
    dept_title,
    job_name,
    local_name,
    national_name,
    sal_level
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
    JOIN job USING ( job_code )
    JOIN location ON ( location_id = local_code )
    JOIN national USING ( national_code )
    JOIN sal_grade ON ( salary BETWEEN min_sal AND max_sal );