[Day17] 2022-02-18(금) MariaDB 3 - SQL 고급 - 이진만 강사님

[1] JOIN

  1. INNER JOIN(내부 조인)

-- 1. INNER JOIN(내부 조인)
SELECT e.empname, d.deptname, t.titlename
		FROM emp e
		INNER JOIN dept d
		ON e.deptno = d.deptno
		INNER JOIN title t
		ON e.titleno = t.titleno;

SELECT e.empname, d.deptname, t.titlename, e.salary
	FROM emp e
		INNER JOIN dept d
		ON e.deptno = d.deptno
		INNER JOIN title t
		ON e.titleno = t.titleno
WHERE e.salary > 3000
ORDER BY e.empname;

SELECT d.deptname, AVG(e.salary) AS av 
	FROM emp e
		INNER JOIN dept d
		ON e.deptno = d.deptno
GROUP BY d.deptname;

SELECT t.titlename, AVG(e.salary) AS av
	FROM emp e
		INNER JOIN title t
		ON e.titleno = t.titleno
GROUP BY t.titlename;

SELECT t.titlename, AVG(e.salary) AS av
	FROM emp e
		INNER JOIN title t
		ON e.titleno = t.titleno
GROUP BY t.titlename
HAVING av > 4000;

-- 부서별 사원과 대리의 연봉 평균을 구하시오
SELECT d.deptname, t.titlename, AVG(e.salary) AS av
	FROM emp e
		INNER JOIN dept d
		ON e.deptno = d.deptno
		INNER JOIN title t
		ON e.titleno = t.titleno
WHERE t.titlename IN ('사원', '대리')
GROUP BY deptname;
-- 부서별로, 사원별, 대리별 연봉 평균을 구하시오.
SELECT d.deptname, t.titlename, AVG(e.salary) AS av
	FROM emp e
		INNER JOIN dept d
		ON e.deptno = d.deptno
		INNER JOIN title t
		ON e.titleno = t.titleno
WHERE t.titlename IN ('사원', '대리')
GROUP BY deptname, t.titlename;

 

  2. OUTER JOIN(외부 조인)

-- 2. OUTER JOIN(외부 조인)
INSERT INTO emp VALUES ('9999', NULL, NULL, '이신입', NULL, 3000, '2022-01-01');
INSERT INTO emp VALUES ('9998', NULL, NULL, '김신입', NULL, 3000, '2022-01-01');

INSERT INTO title VALUES ('50', '고문');

SELECT * 
	FROM emp e
		INNER JOIN title t
		ON e.titleno = t.titleno;
		
-- 직급이 할당이 안된 직원은 조회가 안됨. 그래서 OUTER JOIN 필요

-- LEFT OUTER JOIN
SELECT * 
	FROM emp e
		LEFT OUTER JOIN title t
		ON e.titleno = t.titleno;

-- RIGHT OUTER JOIN
SELECT * 
	FROM emp e
		RIGHT OUTER JOIN title t
		ON e.titleno = t.titleno;

-- FULL OUTER JOIN
-- MariaDB 에서는 FULL JOIN 지원이 안되어 UNION으로 합쳐야 함
SELECT * 
	FROM emp e
		LEFT OUTER JOIN title t
		ON e.titleno = t.titleno
UNION
SELECT * 
	FROM emp e
		RIGHT OUTER JOIN title t
		ON e.titleno = t.titleno;

 

  3. SELF JOIN(자체 조인)

-- 3. SELF JOIN(자체 조인)

-- 사원정보를 조회 한다. 사원의 manager의 이름 정보도 조회 한다.
SELECT e.empname, m.empname
	FROM emp e
		INNER JOIN emp m
		ON e.manager = m.empno;
		
SELECT e.empname, m.empname
	FROM emp e
		LEFT OUTER JOIN emp m
		ON e.manager = m.empno;

SELECT e.empname, m.empname
	FROM emp e
		RIGHT OUTER JOIN emp m
		ON e.manager = m.empno;

  4. Worshop

-- workshop1
-- 1. 2000 년 이후 입사 한 사원들의 정보를 출력
-- 사번, 이름, 타이틀, 부서, 지역
SELECT e.empno, e.empname, t.titlename, d.deptname, d.deptloc
	FROM emp e
		INNER JOIN title t ON e.titleno = t.titleno
		INNER JOIN dept d ON e.deptno = d.deptno
WHERE YEAR(e.hdate) >= 2000;
​
-- 2. 부서 이름 별 연봉의 평균을 구하시오
-- 단, 평균이 3500 이상인 부서만 출력
SELECT d.deptname, AVG(e.salary) AS avgsalary
	FROM emp e
		INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptname
HAVING avgsalary >= 3500;

​-- 3. 대구 지역의 직원 들의 평균 연봉을 구하시오
SELECT AVG(e.salary) AS avgsalary
	FROM emp e
		INNER JOIN dept d ON e.deptno = d.deptno
WHERE d.deptloc = '대구';

-- 4. 홍영자 직원와 같은 부서 직원들(홍영자 제외)의 근무 월수를 구하시오 
SELECT d.deptname, e.empname,
	PERIOD_DIFF(
		DATE_FORMAT(CURDATE(), '%Y%m'),
		DATE_FORMAT(e.hdate, '%Y%m')
		) AS workmonth
	FROM emp e
		INNER JOIN dept d	ON e.deptno = d.deptno
WHERE e.deptno = (SELECT deptno FROM emp WHERE empname = '홍영자')
AND empname NOT IN ('홍영자');

-- 5. 입사 년수가 가장 많은 직원 순으로 정렬 하여 순위를 정한다.
-- 이름, 부서명, 직위, 년수
SELECT RANK() OVER(ORDER BY yr DESC) AS rk,
	e.empname, d.deptname, t.titlename,
	YEAR(CURDATE()) - YEAR(e.hdate) AS yr
	FROM emp e
		LEFT OUTER JOIN dept d	ON e.deptno = d.deptno
		LEFT OUTER JOIN title t ON e.titleno = t.titleno;
		



-- workshop2
-- 1. emp 정보를 조회 한다. 
-- emp 직원들의 모든 정보와 사원의 Manager 정보 까지 조회 한다.
-- 출력 정보 
-- empno, empname, titlename, mgrname
SELECT e.empno, e.empname, t.titlename, m.empname AS mgrname
	FROM emp e
		LEFT OUTER JOIN title t ON e.titleno = t.titleno
		LEFT OUTER JOIN emp m ON e.manager = m.empno;

-- 2. emp 정보를 조회 한다. 
-- 모든 emp 직원들의 모든 정보와 사원의 Manager명 정보 까지 조회 한다.
-- 추가적으로 의 부서명,타이틀명 까지 출력 한다.
SELECT e.*, d.deptname, t.titlename, m.empname AS mgrname
	FROM emp e
		LEFT OUTER JOIN dept d ON e.deptno = d.deptno
		LEFT OUTER JOIN title t ON e.titleno = t.titleno
		LEFT OUTER JOIN emp m ON e.manager = m.empno;

  5. Database, Table 생성 및 제약 조건 설정

DROP DATABASE IF EXISTS company2;
CREATE DATABASE company2;

USE company2;
DROP TABLE IF EXISTS emp, dept, title;
CREATE TABLE dept(
	deptno CHAR(2) PRIMARY KEY,
	deptname VARCHAR(20),
	deptloc VARCHAR(20)
);
ALTER TABLE dept
	ADD CONSTRAINT dept_ch1
		CHECK (deptname IS NOT NULL);
ALTER TABLE dept
	ADD CONSTRAINT dept_ch2
		CHECK (deptloc IS NOT NULL);


CREATE TABLE title(
	titleno CHAR(2) PRIMARY KEY,
	titlename VARCHAR(20)
);
ALTER TABLE title
	ADD CONSTRAINT title_uq1
		UNIQUE (titlename);


CREATE TABLE emp(
	empno INT AUTO_INCREMENT PRIMARY KEY,
	empname VARCHAR(30),
	deptno CHAR(2),
	titleno CHAR(2),
	manager INT,
	salary INT,
	hdate DATE
);

-- AUTO_INCREMENT 시작값 설정
ALTER TABLE emp AUTO_INCREMENT = 100;
-- 추가 - 제약조건 - 외래키 설정
ALTER TABLE emp
	ADD CONSTRAINT fk1
		FOREIGN KEY (deptno) REFERENCES dept(deptno);
ALTER TABLE emp
	ADD CONSTRAINT fk2
		FOREIGN KEY (titleno) REFERENCES title(titleno);
-- 추가 - 제약조건 - UNIQUE 설정(중복 불허)
ALTER TABLE emp
	ADD CONSTRAINT uq1
		UNIQUE (empname);
-- 추가 - 제약조건 - CHECK 설정 - NULL 불허
ALTER TABLE emp
	ADD CONSTRAINT ch1
		CHECK (salary IS NOT NULL);
-- 변경 - 컬럼 - DEFAULT 설정
ALTER TABLE emp
	ALTER COLUMN hdate
		SET DEFAULT CURDATE();