SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
l.local_name AS '지역명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
ORDER BY emp_name
;
-- 부서가 있는 사원만
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
l.local_name AS '지역명',
n.national_name AS '국가명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
INNER JOIN national n ON l.national_code = n.national_code
;
-- 부서가 없는 사원도
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
l.local_name AS '지역명',
n.national_name AS '국가명'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
LEFT OUTER JOIN national n ON l.national_code = n.national_code
;
부서가 존재하는 사원만부서가 존재하지 않는 사원
3. 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명, 급여 등급 조회하시오.
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
l.local_name AS '지역명',
n.national_name AS '국가명',
g.sal_level AS '급여 등급'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
LEFT OUTER JOIN national n ON l.national_code = n.national_code
INNER JOIN sal_grade g ON e.salary BETWEEN g.min_sal AND g.max_sal
;
SELECT e.emp_name AS '직원명',
j.job_name AS '직급명',
d.dept_title AS '부서명',
l.local_name AS '근무 지역'
FROM employee e
LEFT OUTER JOIN job j ON e.job_code = j.job_code
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
;
5. 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 직원명, 직급명, 부서명, 근무지역, 급여를 조회하시오.
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
j.job_name AS '직급명',
d.dept_title AS '부서명',
l.local_name AS '근무 지역',
e.salary AS '급여'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
WHERE l.local_name LIKE 'ASIA%' AND
j.job_name = '대리'
;
6. 보너스를 받는 직원들의 직원명, 보너스, 연봉, 부서명, 근무지역을 조회하시오. (단, 부서 코드가 없는 사원도 출력될 수 있게 OUTER JOIN 사용)
SELECT e.emp_name AS '직원명',
e.bonus AS '보너스',
e.salary * 12 AS '연봉',
d.dept_title AS '부서명',
l.local_name AS '근무 지역'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
WHERE bonus IS NOT NULL
;
7. 한국과 일본에서 근무하는 직원들의 직원명, 부서명, 근무지역, 근무 국가를 조회하시오.
SELECT e.emp_name AS '직원명',
d.dept_title AS '부서명',
l.local_name AS '근무 지역',
n.national_name AS '근무 국가'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
INNER JOIN national n ON l.national_code = n.national_code
WHERE n.national_name IN ('한국', '일본')
;
다른 JOIN은 같은 지를 비교하지만 NON EQUAL JOIN은 조인 조건에 등호(=)를 사용하지 않는 조인
-- NON EQUAL JOIN 실습
-- 조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.
-- employee 테이블과 sal_grade 테이블을 비등가 조인하여 직원명, 급여, 급여 등급 조회
-- INNER JOIN이기 때문에 조인 조건을 만족하는 경우가 아니면 출력이 안됨
SELECT e.emp_name,
e.salary,
s.sal_level
FROM employee e
INNER JOIN sal_grade s ON e.salary >= s.min_sal AND e.salary <= s.max_sal
# INNER JOIN sal_grade s ON e.salary between s.min_sal AND s.mas_sal과 결과 동일
;
SELECT u.NAME AS '이름',
u.addr AS '주소',
CONCAT(u.mobile1, u.mobile2) AS '연락처',
b.prodName AS '주문 상품'
FROM usertbl u
INNER JOIN buytbl b ON u.userID = b.userID
WHERE u.userID = 'JYP';
Q2. employee 테이블과 department 테이블을 조인하여 보너스를 받는 사원들의 사번, 직원명, 보너스, 부서명을 조회하시오.
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
e.bonus AS '보너스',
d.dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE e.bonus IS NOT NULL;
Q3. employee 테이블과 department 테이블을 조인하여 인사관리부가 아닌 사원들의 직원명, 부서명, 급여를 조회하시오.
SELECT e.emp_name AS '직원명',
d.dept_title AS '부서명',
e.salary AS '급여'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE d.dept_title != '인사관리부';
# ['employee' JOIN 'department'] JOIN 'job'
# 순서가 잘못되면 JOIN이 안될 수도 있음
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code;
Q5. 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 직원명, 주민번호, 부서명, 직급명을 조회하시요.
SELECT e.emp_name AS '직원명',
e.emp_no AS '주민번호',
d.dept_title AS '부서명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_no LIKE '7%'
AND SUBSTRING(e.emp_no, 8, 1) = 2
AND e.emp_name LIKE '전%'
;
Q6. 각 부서별 평균 급여를 조회하여 부서명, 평균 급여를 조회 단 부서 배치가 안된 사원들의 평균도 같이 나오게끔 조회하시오.
SELECT IFNULL(d.dept_title, '부서없음')AS '부서명',
FLOOR(AVG(e.salary)) AS '평균 급여'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_ID
GROUP BY d.dept_title
ORDER BY d.dept_title
;
SELECT d.dept_title AS '부서명',
SUM(e.salary) AS '급여의 합'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_ID
GROUP BY d.dept_title
HAVING SUM(e.salary) >= 10000000
ORDER BY SUM(e.salary) DESC
;
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_name LIKE '%형%';
SELECT e.emp_name AS '직원명',
j.job_name AS '직급명',
e.dept_code AS '부서코드',
d.dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE d.dept_title LIKE '해외영업%'
ORDER BY d.dept_title
;
Q10. 각 부서별 최소 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 조회하시오.
SELECT emp_id AS '사번',
emp_name AS '사원명',
IFNULL(dept_code, '부서없음') AS '부서코드',
salary AS '급여'
FROM employee
WHERE (salary, IFNULL(dept_code, '부서없음')) IN (
SELECT MIN(salary), IFNULL(dept_code, '부서없음')
FROM employee
GROUP BY dept_code
)
ORDER BY dept_code
;
Q11. 각 직급별로 최소 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회하시오.
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급코드',
salary AS '급여'
FROM employee
WHERE (salary, job_code) IN (
SELECT MIN(salary), job_code
FROM employee
GROUP BY job_code
)
ORDER BY job_code
;
SELECT userID AS "아이디",
NAME AS "이름",
mDate AS "가입일"
FROM usertbl
ORDER BY NAME DESC;
# ORDER BY가 가장 마지막에 수행되기 때문에 별칭이나 별칭의 순서로도 기입 가능
# ORDER BY `이름` DES과 동일
# ORDER BY 2 DESC과 동일
# 하지만 WHERE절은 SELECT절 보다 먼저 실행되기 때문에 별칭이나 별칭의 순서 기입 불가능
Q6. employee 테이블에서 부서별 사원의 수, 보너스를 받는 사원의 수, 급여의 합, 평균 급여, 최고 급여, 최저 급여를 조회(부서별 내림차순 정렬)
-- employee 테이블에서 부서별 사원의 수, 보너스를 받는 사원의 수, 급여의 합, 평균 급여
-- 최고 급여, 최저 급여를 조회, 부서별로 내림차순 정렬
SELECT dept_code,
COUNT(*) AS "사원의 수",
COUNT(bonus) AS "보너스를 받는 사원의 수",
SUM(salary) AS "급여의 합",
AVG(salary) AS "평균 급여",
MAX(salary) AS "최고 급여",
MIN(salary) AS "최저 급여"
FROM employee
GROUP BY dept_code
ORDER BY dept_code DESC
;
Q7. EMPLOYEE 테이블에서 부서별로 급여가 300만원 이상인 직원의 평균 급여를 조회
