한화시스템 BEYOND SW Camp_0627

오전에 알바하느라, 늦참했어여. 히잉ㅠ 다음주부터는 빠질 일.. 휴가 때말고는 없다!! 참고로 난 test_db가 beyond에 없어서. 따로 만들어줘야 함.. 아직 안 만들어서, test_db에 있는 Tab들을 불러오는 거 못 함ㅎ.. 그래서 캡처본이 없어유

왜 벌써 8번이야..

8. 서브 쿼리

• test_db 내부의 테이블을 활용하여, 한 번이라도 구매한 적이 있는 회원의 아이디, 이름 , 주소 조회

• 1. 계정별 구매 내역을 조회

SELECT *
FROM buytbl
WHERE userid = 'YJS';

• 2. EXISTS 연산자는 서브 쿼리의 결과가 한 건이라도 존재하면 참이 된다.

SELECT u.'userid',
		u.'name',
		u.'addr'
FROM usertbl u
WHERE EXISTS (
	SELECT * -- 행과 열의 개수는 상관x
	FROM buytbl b 
	WHERE b.userid = u.userid;
);

cf) JOIN 활용해서 작성해보기.

SELECT DISTINCT u.userid,
			u.name,
			u.addr
FROM usertbl u
INNER JOIN buytbl b ON u.userid =b.userid;

• 3. 다중열 서브 쿼리
     1) 하이유 사원과 같은 부서 코드, 같은 직급 코드에 해당하는 사원들을 조회

SELECT dept_code,
			job_code
FROM employee
WHERE emp_name = '하이유';  --((D5, J5))

2) 부서 코드가 D5이면서, 직급 코드가 J5인 사원들을 조회

SELECT emp_name,
		dept_code,
		job_code
FROM employee
-- WHERE dept_code = 'D5' AND job_code = 'J5';
-- WHERE(dept_code, job_code) = ('D5', 'J5');
-- WHERE(dept_code, job_code) = (('D5', 'J5'));
WHERE (dept_code, job_code) IN (
		SELECT dept_code,
				job_code
		FROM employee
		WHERE emp_name = '하이유'
);

실행이 안되는데, 이유를 찾아서 코드를 고쳐보자!

• 1) 박나라 사원의 직급 코드와 사수 사번을 조회

SELECT dept_code,
			manager_id
FROM employee
WHERE (dept_code, manager_id) = ('D5', '207');

• 2) 박나라 사원과 직급 코드가 일치하면서 같은 사수를 가지고 있는 사원 조회

SELECT emp_id,
		emp_name,
		dept_code,
		manager_id
FROM employee
			-- WHERE (dept_code, manager_id) = ('D5', '207');
		WHERE(dept_code, manager_id) = (
		SELECT dept_code,
				manager_id
		FROM employee
		);

실행이 안되는데, 이유를 찾아서 고쳐보자!

• 4. 다중행, 다중열 서브 쿼리

서브 쿼리의 조회 결과값이 여러 행, 여러 열일 경우, 각 부서별 최고 급여를 받는 직원의 사번, 직원명, 부서 코드, 급여 조회.

• 1) 부서별 최고 급여 조회

SELECT dept_code,
		MAX(salary)
FROM employee
GROUP BY dept_code; -- (('D1', 3660000), ('D2', 2490000), ...)

• 2) 각 부서별 최고 급여를 받는 직원 조회
  방법 1)

SELECT	 emp_id,
			emp_name,
			dept_code,
			salary
FROM employee
WHERE (dept_code = 'D1' AND salary = '3660000') 
	OR(dept_code = 'D2' AND salary = '2490000')
	OR(dept_code = 'D3' AND salary = '3760000')  
	ORDER BY dept_code;

방법 2)

SELECT	 emp_id,
			emp_name,
			dept_code,
			salary
FROM employee
-- WHERE (dept_code = 'D1' AND salary = '3660000') 
--	OR(dept_code = 'D2' AND salary = '2490000')
--	OR(dept_code = 'D3' AND salary = '3760000') 
WHERE (dept_code, salary) IN (('D1', 3660000), ('D2', 2490000), ('D5', 3760000)) 
	ORDER BY dept_code;

2) 각 부서별 최고 급여를 받는 직원 조회

SELECT	 emp_id,
			emp_name,
			dept_code,
			salary
FROM employee
-- WHERE (dept_code = 'D1' AND salary = '3660000') 
--	OR(dept_code = 'D2' AND salary = '2490000')
--	OR(dept_code = 'D3' AND salary = '3760000') 
-- WHERE (dept_code, salary) IN (('D1', 3660000), ('D2', 2490000), ('D5', 3760000)) 
			WHERE (dept_code, salary) IN (
			SELECT dept_code,
			MAX(salary)
			FROM employee
			GROUP BY dept_code
			)
	ORDER BY dept_code;

• 각 직급별로 최소 급여를 받는 사원들의 사번, 이름, 부서 코드, 급여조회

1) 각 직급별로 최소 급여 조회

SELECT job_code,
		MIN(salary)	
FROM employee
GROUP BY job_code;

2) 각 직급별 최소 급여를 받는 사원 조회

SELECT emp_id,
		emp_name,
		job_code,
		salary
FROM employee
WHERE (job_code, salary) IN (
SELECT job_code,
		MIN(salary)	
FROM employee
GROUP BY job_code
)
ORDER BY job_code;

응용) 각 직급 별, 최소 급여를 받는 사원들의 사번, 이름, 직급 코드, 급여 조회

인라인 뷰

• FROM 절에 서브 쿼리를 작성하고
• 서브 쿼리를 수행한 결과를 테이블 대신에 사용한다.

• employee 테이블에서 급여로 순위를 매겨서 출력.

SELECT emp_id AS '사번',
		emp_name AS '이름',
		salary AS '급여',
		salary *12 AS '연봉'
FROM employee;

SELECT *
FROM (
	SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS `rank`,
	       emp_name,
	       salary
	FROM employee
) emp
WHERE `rank` >= 10 AND `rank` <= 20;

오늘은 일찍 끝났당~! 유후우우 서브 쿼리 개념도 짝꿍 덕분에 잘 정리했당!