[Database] 조인(JOIN)과 UNION 연산자/서브쿼리 실습문제

실습 문제

Q1. usertbl 테이블과 buytbl 테이블을 조인하여 JPY라는 아이디를 가진 회원의 이름, 주소, 연락처, 주문 상품 이름을 조회하시오.

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 != '인사관리부';

 

Q4. employee 테이블과 department 테이블, job 테이블을 조인하여 사번, 직원명, 부서명, 직급명 조회하시오.

# ['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
;

 

Q7. 각 부서별 총 급여의 합이 1000만원 이상인 부서명, 급여의 합을 조회하시오.

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
;

 

Q8. 이름에 '형'자가 들어있는 직원들의 사번, 직원명, 직급명을 조회하시오.

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 '%형%';

 

Q9.  해외영업팀에 근무하는 직원들의 직원명, 직급명, 부서 코드, 부서명을 조회하시오.

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
;