SQL 실습 (2)

* 실습은 앞의 SQL(2) 게시물에서 이용한 데이터베이스를 이용해 진행했다.

# 1번. 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명을 조회하시오.(1행)
SELECT 
       a.emp_id
     , a.emp_name
     , b.dept_title
  FROM employee a
  LEFT JOIN department b ON a.dept_code = b.dept_id
 WHERE a.emp_name LIKE '%형%';
  
# 2번. 해외영업팀에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하시오.(9행)
SELECT  
	   a.emp_name
	 , c.job_name 
	 , b.dept_id
	 , b.dept_title 
  FROM employee a
  JOIN department b ON a.dept_code = b.dept_id
  JOIN job c ON a.job_code= c.job_code
 WHERE b.dept_title LIKE "해외영업%";

#3번. 보너스포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.(8행)(INNER JOIN 결과)
SELECT 
	   a.emp_name
	 , a.bonus
	 , b.dept_title
	 , c.local_name
  FROM employee a
  JOIN department b ON a.dept_code=b.dept_id
  JOIN location c ON b.location_id=c.local_code
 WHERE a.bonus IS NOT NULL;

# 4번. 부서코드가 D2인 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오.(3행)
SELECT 
	   a.emp_name
	 , d.job_name
	 , b.dept_title
	 , c.local_name
  FROM employee a
  JOIN department b ON a.dept_code=b.dept_id
  JOIN location c ON b.location_id=c.local_code
  JOIN job d ON a.job_code = d.job_code
 WHERE a.dept_code = "D2";

# 5번. 급여 테이블의 등급별 최소급여(MIN_SAL)보다 많이 받는 직원들의 사원명, 직급명, 급여, 연봉을 조회하시오. 연봉에 보너스포인트를 적용하시오.(20행)
SELECT 
	   a.emp_name
	 , b.job_name
	 , a.salary
	 , a.salary * 12 * (1+IFNULL(a.bonus, 0))   # oracle에서의 NVL()이 mariaDB에선 IFNULL()이다.
  FROM employee a
  JOIN job b ON a.job_code = b.job_code
  JOIN sal_grade c ON a.sal_level=c.sal_level
 WHERE a.salary > c.min_sal;

# 6번. 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.(15행)
SELECT 
	   a.emp_name
 	 , b.dept_title
 	 , c.local_name
     , d.national_name
  FROM employee a
  JOIN department b ON a.dept_code=b.dept_id
  JOIN location c ON b.location_id=c.local_code
  JOIN national d ON c.national_code=d.national_code
 WHERE d.national_name="한국" 
  OR d.national_name="일본";

# 7번. 보너스포인트가 없는 직원들 중에서 직급코드가 J4와 J7인 직원들의 사원명, 직급명, 급여를 조회하시오. 단, join과 IN 사용할 것(8행)
SELECT 
	   a.emp_name
 	 , b.job_name
     , a.salary
  FROM employee a
  JOIN job b ON a.job_code=b.job_code
 WHERE a.bonus IS NULL 
  AND a.job_code IN ("J4", "J7");

# 8번. 직급이 대리이면서 아시아 지역(ASIA1, ASIA2, ASIA3 모두 해당)에 근무하는 직원 조회(2행)
-- 사번(EMPLOYEE.EMP_ID), 이름(EMPLOYEE.EMP_NAME), 직급명(JOB.JOB_NAME), 부서명(DEPARTMENT.DEPT_TITLE),
-- 근무지역명(LOCATION.LOCAL_NAME), 급여(EMPLOYEE.SALARY)를 조회하시오
SELECT 
	   a.emp_id
	 , a.emp_name
	 , b.job_name
	 , c.dept_title
	 , d.local_name
	 , a.salary
  FROM employee a
  JOIN job b ON a.job_code=b.job_code
  JOIN department c ON a.dept_code = c.dept_id
  JOIN location d ON c.location_id=d.local_code
 WHERE b.job_name="대리" 
  AND d.local_name LIKE "asia%";