[2주차] 24.01.16 화요일

SQL

 

-- SQL 4일차(join, subquery)

/*
join : N개 이상의 테이블을 서로 묶어서 하나의 결과집합을 만들어 내는 것
-- 관계형 데이터베이스의 가장 큰 특징
-- 테이블의 관계(1:1, 1:N)

ANSI 표준 구문
SELECT	
FROM		TABLE01	-- 테이블 여러개를 열거하면 안됨!! from 절에는 무조건 테이블 하나만 가능
[INNER] JOIN	TABLE02	ON(조건)	 	-- 조건이라는 건 연산자를 사용할 수 있다
[INNER]	JOIN	TABLE02	USING(컬럼명)	-- 부모의 기본키와 자식의 외래키 컬럼명이 동일해야함.

NATURAL	[INNER]	JOIN	TABLE02	-- 알 필요 X

LEFT|RIGHT|FULL	[OUTER]	JOIN	TABLE02	ON(조건)	
LEFT|RIGHT|FULL	[OUTER]	JOIN	TABLE02	USING(컬럼명)
-- 마리아db는 full outer join을 지원하지 않음
-- 조건에 만족하지 않는 데이터까지 포함하여 join을 사용하는게 outer join. 누락된 데이터까지 포함하는 결과를 보고 싶을 때 사용.

CROSS	JOIN	TABLE02	-- 알 필요 X
*/

 

USE encore;

SELECT	*
FROM	department;

SELECT	*
FROM	employee;

SELECT	d.dept_name,
	e.emp_name
FROM	department d, employee e; -- join시 조건을 걸어주지 않으면 1:N관계로 매핑해서 7 * 22 = 154개의 레코드가 생성된다

SELECT	d.dept_name,
	d.dept_id,
	e.emp_name
FROM	department d, employee e
WHERE	d.DEPT_ID = e.DEPT_ID;

SELECT		emp_name,
		dept_name,
		loc_describe
FROM		employee e
INNER JOIN	department d	USING(dept_id)	-- USING(d.dept_id) 처럼 USING 구문에 특정 테이블의 별칭을 사용하는 것은 불가능하다.
INNER JOIN	location l	ON(location_id = loc_id)	-- ON(l.location_id = d.loc_id) 처럼 ON 구문에서는 테이블의 별칭을 사용할 수 있다.
WHERE		dept_name LIKE '해외%'
ORDER BY	3, 1;

USE sqldb;

SELECT	*
FROM	usertbl;

SELECT	*
FROM	buytbl;

 

-- 사용자가 JYP인 유저의 이름과 구매상품을 조회한다면?
SELECT		NAME,
		prodName
FROM		usertbl u
INNER JOIN	buytbl b	USING(userID)
WHERE		userID = 'JYP';

SELECT		NAME,
		prodName
FROM		usertbl u
INNER JOIN	buytbl b	ON(u.userID = b.userID)
WHERE		u.userID = 'JYP';	-- 테이블 별칭을 붙여줘야함

-- 사용자의 아이디, 이름, 구매상품, 주소, 연락처를 조회하고 싶다면?
SELECT	u.userID,
	NAME,
	prodName,
	addr,
	CONCAT(mobile1, mobile2) AS mobile
FROM	usertbl u
JOIN	buytbl b	ON(u.userID = b.userID);

-- 위 요구사항에서 구매이력이 있는 회원만 조회한다면?
SELECT	distinct u.userID, NAME, addr, CONCAT(mobile1, mobile2) AS mobile
FROM	usertbl u
JOIN	buytbl b	ON(u.userID = b.userID);

SELECT	u.userID, NAME, addr, CONCAT(mobile1, mobile2) AS mobile
FROM	usertbl u
WHERE	EXISTS (SELECT	*
		FROM	buytbl b
		WHERE	u.userid = b.userid); -- 유무만 판별하고 데이터 내용은 중요하지 않은 키워드 exists

 

USE encore;

SELECT	*
FROM	sal_grade;

-- 업무적인 연관성이 없는 테이블도 조인이 가능하다. (ON() 구문을 이용한 조인)
SELECT		emp_name,
		salary,
		slevel
FROM		employee e
JOIN		sal_grade s ON(e.salary BETWEEN s.lowest AND s.highest)
ORDER BY	3;

-- outer join : join의 조건에 만족하지 않는 모든 행을 조회할 때
SELECT		emp_name,
		dept_name
FROM		employee e
RIGHT JOIN	department d USING(dept_id); -- left: join이라는 키워드를 기준으로 join의 왼쪽에 있는건 left, 오른쪽에 있는 건 right.
-- left에 있는 모든 키워드를 반환한다. 즉 employee 테이블에 있는 모든 컬럼 출력.

USE sqldb;

-- 구매이력이 없는 사용자만 조회?
SELECT		u.userid,
		u.name,
		b.prodname,
		u.addr
FROM		usertbl	u
left JOIN	buytbl	b	ON(u.userid = b.userid)
WHERE		prodname IS NULL;

USE encore;

-- 부서배치를 받지 않은 사원의 이름, 부서명을 조회한다면?
SELECT		emp_name,
		dept_name
FROM		employee e
LEFT JOIN	department d USING(dept_id)
WHERE		dept_name IS null;

-- 사원테이블에서 사원의 이름과 사수의 이름을 조회하고 싶다면? self join
SELECT		e.emp_name,
		m.emp_name AS mgr_name,
		s.emp_name AS spr_name
FROM		employee e
LEFT	JOIN	employee m ON(e.mgr_id = m.emp_id)
LEFT	JOIN	employee s ON(m.mgr_id = s.emp_id);

 

-- 서브쿼리 : 하나의 쿼리가 다른 쿼리에 포함되는 구조
-- 유형 : 단일행 서브쿼리(리턴dl 1건)(단일 열, 다중 열), 다중행 서브쿼리(리턴이 여러건)(단일 열, 다중 열)
-- 유형에 따라서 사용할 수 있는 연산자가 다르다
-- select절(scalar subquery), from절(inline view), where절(subquery)
-- 나승원 사원과 같은 부서원을 조회한다면?
SELECT	dept_id
FROM	employee
WHERE	emp_name = '나승원';

SELECT	*
FROM	employee
WHERE	dept_id = '50';

SELECT	*					-- mainquery
FROM	employee
WHERE	dept_id = (SELECT	dept_id
		   FROM		employee
		   WHERE	emp_name = '나승원'); -- subquery

-- 나승원 사원과 같은 직급이고 나승원 사원보다 많은 급여를 받는 사원의 정보를 조회
SELECT	*
FROM	employee
WHERE	job_id = (	SELECT	job_id
			FROM	employee
			WHERE	emp_name = '나승원') && salary > (SELECT	salary
								FROM	employee
								WHERE	emp_name = '나승원');

 

-- 부서별 급여총합이 가장 많은 부서의 부서명, 급여총합을 조회
SELECT	d.dept_name,
	max(sum(salary))
FROM	employee	e
JOIN	department	d	USING(dept_id)
WHERE	MAX(SUM(salary)) = (SELECT	MAX(SUM(salary)) -- error. 집계함수가 집계함수를 중첩할 수 없다
			    FROM	employee
			    GROUP BY	dept_id);

SELECT	e.dept_id,
	MAX(total)
FROM	(SELECT	    dept_id,
		    SUM(salary) AS 'total'
	FROM	    employee
	GROUP BY    dept_id) v -- inline view
JOIN	employee e ON(e.dept_id = v.dept_id);

SELECT	d.dept_name,
	MAX(total)
FROM	(SELECT	dept_id,
		SUM(salary) AS 'total'
	FROM	employee
	GROUP BY	dept_id) v -- inline view
JOIN	employee e ON(e.dept_id = v.dept_id)
JOIN	department d ON(d.dept_id = e.dept_id);

-- 부서별 최소 급여를 확인하고 싶다면?
SELECT		MIN(salary)
FROM		employee e
GROUP BY	dept_id;

SELECT	*
FROM	employee
WHERE	(dept_id, salary) IN (SELECT	  dept_id,
					  MIN(salary)
			      FROM	  employee e
			      GROUP BY    dept_id);

 

-- 과장직급의 급여를 확인한다면?

SELECT	salary
FROM	employee e
JOIN	job	j	ON(e.job_id = j.job_id)
WHERE	job_title = '과장';

SELECT	salary
FROM	employee e
JOIN	job	j	ON(e.job_id = j.job_id)
WHERE	job_title = '대리';

-- 과장직급보다 많은 급여를 받는 대리사원의 정보를 조회한다면?
-- > ANY : 비교대상 중 최솟값보다 큰 것
-- < ANY : 비교대상 중 최댓값보다 작은 것
-- > ALL : 비교대상 중 최댓값보다 큰 것
-- < ALL : 비교대상 중 최솟값보다 작은 것

SELECT	emp_name,
	salary
FROM	employee e
JOIN	job 	j	ON(e.job_id = j.job_id)
WHERE	job_title = '대리' && salary > ALL	(SELECT	salary
						 FROM		employee e
						 JOIN		job	j	ON(e.job_id = j.job_id)
						 WHERE		job_title = '과장');