제약조건

유니크 제약조건 / 유니크 키

회원가입 시 - 전화번호 / 이메일 중복 X (유니크)
DB에서 중복값을 막을 수 있음

not null 제약조건

회원가입, 로그인 시 아이디 필수 (null값 허용 X)
DB에서 없는 값을 막을 수 있음

check 제약조건

성별은 2가지 뿐
(남자, 여자 이외의 다른 내용 X)

primary key (유니크 + not null)

모든 행은 유니크해야 한다
(중복 데이터 방지, 모든 행은 조금씩이라도 달라야 함)

foreign key

DB는 컬럼에 객체를 저장할 수 없다.
중복이 될 수도 있다
(관련 테이블의 기본키, 유니크키 기록 후 연계하여 데이터 가져옴)

Join

곱집합 (cartision product)

가능한 모든 행 조인

-- 조인
-- 직원의 first_name과 직업명 출력

-- 카티션 곱(곱집합)
SELECT e.first_name, e.job_id AS 직원직업, j.job_id AS 직업아이디
-- 조인을 할 때 조건을 걸지 않으면 맞지 않는 데이터들이 연결 될 수 있다 ()
FROM employees AS e JOIN jobs AS j
ORDER BY e.job_id;

동등 조인(inner join)

조건이 정확히 일치하는 경우에 출력

-- 거의 대부분의 조인은 inner 조인(동등 조인)
SELECT e.first_name 이름,
		j.job_title 직업명
FROM employees e
	-- INNER JOIN : employees e, jobs j 로 조인이 가능하지만 가시성을 좋게 하기 위해
	INNER JOIN jobs j
	-- ON : 조인 전용 조건문(WHERE)
	ON e.job_id = j.job_id;
	
-- 직원의 first_name과 department_name을 출력
-- departments_id가 null인 경우는 출력이 안됨
SELECT e.first_name 성,
		d.department_name 부서명
FROM employees e 
	INNER JOIN departments d
	ON e.department_id = d.department_id;

외부 조인(outer join)

조건이 정확히 일치하지 않는 경우도 출력

-- department_id가 null인 경우에도 출력
-- OUTER JOIN
SELECT e.first_name 성,
		d.department_name 부서명
FROM employees e 
	-- 다 보여줄 테이블을 기준으로 OUTER JOIN 한다
	-- employees 직원을 다 보여줘야 하기 때문에 left
	LEFT OUTER JOIN departments d
	ON e.department_id = d.department_id;

자체 조인(self join)

자체 테이블에서 조인 하려고 할 때

-- 자체 조인
-- 직원의 first_name과 사수의 first_name 출력
SELECT e.first_name, e2.first_name 
FROM employees e 
	INNER JOIN employees e2
	ON e.manager_id = e2.employee_id;

조인 실습

-- 조인

-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT e.last_name 이름, e.department_id 부서번호, d.department_name 부서명
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id;


-- 문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라
SELECT e.last_name 이름, j.job_title 직업, d.department_name 부서명
FROM employees e JOIN jobs j
                    on e.job_id = j.job_id
                  JOIN departments d
                    on e.department_id = d.department_id;

                   
-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라
SELECT e.last_name 이름, j.job_title 직업, e.department_id 부서번호,d.department_name 부서명
FROM employees e JOIN jobs j
                    on e.job_id = j.job_id
                  JOIN departments d
                    on e.department_id = d.department_id
WHERE e.commission_pct IS NOT NULL;
             

-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업,부서번호,부서명을 출력하라
SELECT e.last_name 이름, j.job_title 직업, e.department_id 부서번호,d.department_name 부서명
FROM employees e JOIN jobs j
                    on e.job_id = j.job_id
                  JOIN departments d
                    on e.department_id = d.department_id
WHERE d.location_id = 2500;


-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT e.first_name AS 이름, d.department_name AS 부서이름
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id
WHERE e.first_name LIKE '%A';


-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT e.first_name AS 사원이름, e2.first_name AS 관리자이름
FROM employees e
	INNER JOIN employees e2
	ON e.manager_id = e2.employee_id;


-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
SELECT e.first_name AS 사원이름, d.department_name AS 부서명, e.salary AS 월급
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id
WHERE e.salary >= 3000;


-- 문제8) TJ 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
-- cross join
SELECT e.first_name AS 이름, e.hire_date AS 입사일, e2.first_name, e2.hire_date
FROM employees e
	JOIN employees e2
	ON e2.first_name = 'TJ'
WHERE e.hire_date > e2.hire_date;

-- 서브쿼리 사용
SELECT first_name 이름, hire_date 입사일
FROM employees e
WHERE hire_date > (
	SELECT hire_date
	FROM employees e
	WHERE first_name = 'TJ'
);

-- 문제9) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
SELECT e.first_name 이름, d.department_name 소속부서명
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id
WHERE e.salary BETWEEN 3000 AND 5000;
	
-- 문제10) ACCOUNTING 부서 소속 사원의 이름과 입사일 출력하라
SELECT e.first_name 이름, e.hire_date 입사일
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id
WHERE d.department_name = 'ACCOUNTING';

-- 문제11) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라
SELECT e.first_name 이름, e.salary 급여, l.street_address
FROM employees e
	INNER JOIN departments d 
	ON e.department_id = d.department_id
	INNER JOIN locations l 
	ON d.location_id = l.location_id
WHERE e.salary <= 3000;

서브쿼리

쿼리문 안에 적는 쿼리문

서브쿼리(where 서브쿼리)

-- 이름이 Jonathon인 사람의 월급보다 월급이 같거나 많은 사원의 이름과 월급
SELECT first_name, salary
FROM employees
WHERE first_name = 'Jonathon';

SELECT first_name, salary
FROM employees
-- 값이 조건이 아니라 유동적인 데이터일 경우 서브쿼리 이용
WHERE salary >= (
	SELECT salary
	FROM employees
	WHERE first_name = 'Jonathon'
);

-- first_name이 J로 시작하는 사람들의 월급과 같은 월급을 가진 사람들의 first_name과 salary
-- 서브쿼리를 사용할 때에는 본쿼리보다 서브쿼리를 먼저 작성해본다.

-- 서브쿼리 부분
SELECT salary
FROM employees
WHERE first_name LIKE 'J%';

SELECT *
FROM employees
WHERE salary IN (8200, 7800, 3200);


SELECT first_name, salary
FROM employees
-- in절을 걸 때는 1개 이상의 행이 나오도록 서브쿼리를 작성할 수 있다
-- 단일 비교 조건일 경우에는 서브쿼리 결과가 단일행 단일열(하나의 값)이어야 한다
WHERE salary IN (
	SELECT salary
	FROM employees
	WHERE first_name LIKE 'J%'
);

스칼라 서브쿼리 (select 서브쿼리)

-- 직원의 first_name과 department_name을 출력 (department_name을 서브 쿼리로)
-- 스칼라 서브쿼리는 쿼리 횟수가 결과 행 만큼 실행될 수 있다
-- join으로 해결 가능하다면 join 추천
SELECT e.first_name, (	
	SELECT department_name
	FROM departments d
	WHERE e.department_id = d.department_id
)
FROM employees e;

인라인뷰 서브쿼리(from 서브쿼리)

-- 인라인뷰 서브쿼리
-- FROM절 데이터를 줄여서 가져오거나
-- 가공해서 가져오거나 이름을 바꿔서 가져오거나 할 때 사용

SELECT concat(first_name, '', last_name) name, salary 
FROM employees;

SELECT *
-- 인라인뷰 서브쿼리
FROM (
	SELECT concat(first_name, '', last_name) name, salary 
	FROM employees
)AS emp
-- FROM에서 서브쿼리로 name열을 이미 만들었기 때문에 name열 사용 가능
WHERE name LIKE 'S%';

SELECT concat(first_name, '', last_name) name, salary 
FROM employees
-- 서순 오류 (employees 테이블에 name 열이 없다)
WHERE name LIKE 'S%';

서브쿼리 실습

-- 문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.
-- sub
SELECT salary
FROM employees
WHERE last_name = 'Kochhar';

SELECT employee_id 사원번호, first_name 이름, salary 급여
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Kochhar'
);

-- 문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.  
-- sub
SELECT avg(salary)
FROM employees;

SELECT *
FROM employees
WHERE salary < (
	SELECT avg(salary)
	FROM employees
);

-- 문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라
-- sub
SELECT department_id ,min(salary)
FROM employees
GROUP BY department_id;

SELECT min(salary)
FROM employees
WHERE department_id = 100;

SELECT *
FROM employees
GROUP BY department_id
HAVING min(salary) > (
	SELECT min(salary)
	FROM employees
	WHERE department_id = 100
)

SELECT d.department_name 부서이름, emp.최소급여
FROM (
	SELECT department_id AS 부서ID, min(salary) AS 최소급여
	FROM employees
	GROUP BY department_id
)AS emp
INNER JOIN departments d
ON emp.부서ID = department_id
WHERE emp.최소급여 > (
	SELECT min(salary)
	FROM employees
	WHERE department_id = 100
);

-- 문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라. 
-- 단 업무별로 정렬하여라.
-- 서브쿼리 사용 이유 : 그룹으로 묶을경우 그룹컬럼과 그룹함수밖에 못쓰기 때문에.. 서브쿼리를 그룹으로 묶어주고 
-- 메인쿼리에선 모든 컬럼을 불러줄 수 있다.
-- sub
SELECT job_id, min(salary)
FROM employees
GROUP BY job_id;

SELECT *
FROM employees
WHERE (job_id, salary) IN (
	SELECT job_id, min(salary)
	FROM employees
	GROUP BY job_id
);


SELECT e.employee_id 사원번호, e.first_name 이름, e.job_id 업무, e.department_id 부서번호, e.salary
FROM (
	SELECT job_id, min(salary) AS 최소급여
	FROM employees
	GROUP BY job_id
) AS emp
INNER JOIN employees e
ON e.job_id  = emp.job_id AND e.salary = emp.최소급여;


-- 문제5) EMPLOYEES 과 DEPARTMENTS 테이블에서 업무가 SA_MAN 사원의 정보를 이름,업무,부서명,근무지를 출력하라.
-- 사원의 id를 찾아서 
-- sub
SELECT e.first_name , j.job_title, e.department_id, l.city
FROM employees e 
	INNER JOIN departments d
	ON e.department_id = d.department_id
	INNER JOIN jobs j
	ON e.job_id = j.job_id
	INNER JOIN locations l
	ON d.location_id = l.location_id
WHERE e.job_id = 'SA_MAN';


-- 
SELECT e.first_name 이름, e.job_id 업무, d.department_name 부서명, l.street_address 근무지
FROM employees e
	INNER JOIN departments d
	ON e.department_id = d.department_id
	INNER JOIN locations l
	ON d.location_id = l.location_id
WHERE job_id = 'SA_MAN';


-- 문제6) EMPLOYEES 테이블에서 (가장 많은 사원)을 갖는 MANAGER의 사원번호를 출력하라.
SELECT manager_id ,count(manager_id) AS mycount
FROM employees
GROUP BY manager_id
HAVING mycount = (
	SELECT max(mycount)
	FROM (
		SELECT count(manager_id) AS mycount
		FROM employees
		GROUP BY manager_id
	)AS maxcount
);

SELECT max(mycount)
FROM (
	SELECT count(manager_id) AS mycount
	FROM employees
	WHERE manager_id IS NOT NULL
	GROUP BY manager_id
)AS maxcount



-- 문제7) EMPLOYEES 테이블에서 (가장 많은 사원이 속해 있는 부서 번호)와 사원수를  출력하라.
SELECT max(mycount)
FROM (
	SELECT count(employee_id)
	FROM employees
	GROUP BY department_id;
);

SELECT  department_id, count(employee_id) AS 사원수
FROM employees
GROUP BY department_id
HAVING 사원수 = (
	SELECT max(mycount)
	FROM (
		SELECT count(employee_id) AS mycount
		FROM employees
		GROUP BY department_id
	) AS maxcount
);


-- 문제8) EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 
-- 사원번호,이름,직업,급여를 출력하라.
-- sub
SELECT job_id
FROM employees
WHERE employee_id = 123;

SELECT salary
FROM employees
WHERE employee_id = 192;

-- main
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 123
	) AND
	salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 192
	);

        
-- 문제9)직업(JOB)별로 최소급여를 받는 사원의 정보를 사원번호,이름,업무,부서명을 출력하라.
-- 조건1 :직업별로 내림차순정렬
SELECT job_id, min(salary)
FROM employees
GROUP BY job_id;

SELECT e.employee_id, e.first_name, j.job_title, d.department_id
FROM employees e
	INNER JOIN jobs j
	ON e.job_id = j.job_id
	INNER JOIN departments d
	ON e.department_id = d.department_id
WHERE (e.job_id, e.salary) IN (
	SELECT job_id, min(salary)
	FROM employees
	GROUP BY job_id
);

SELECT *
FROM employees
WHERE (job_id, salary) IN (
	SELECT job_id, min(salary)
	FROM employees
	GROUP BY job_id
);

-- 문제10) EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는 
-- 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. 
-- 단 50번은 제외
SELECT min(salary)
FROM employees
WHERE department_id = 50;

SELECT *
FROM employees e
	LEFT OUTER JOIN departments d
	ON e.department_id = d.department_id;

SELECT e.employee_id , e.first_name , j.job_title , e.hire_date , d.department_id
FROM employees e
	LEFT OUTER JOIN departments d
	ON e.department_id = d.department_id
	INNER JOIN jobs j
	ON e.job_id = j.job_id
WHERE salary > (
	SELECT min(salary)
	FROM employees
	WHERE department_id = 50
)

-- 문제11) EMPLOYEES 테이블에서 (50번 부서의 최고 급여)를 받는 사원 보다 많은 급여를 받는 사원의 
-- 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. 
SELECT e.employee_id , e.first_name , j.job_title , e.hire_date , d.department_id
FROM employees e
	LEFT OUTER JOIN departments d
	ON e.department_id = d.department_id
	INNER JOIN jobs j
	ON e.job_id = j.job_id
WHERE salary > (
	SELECT max(salary)
	FROM employees
	WHERE department_id = 50
)

SQL 작동 순서, 별칭

-- employees 테이블에서 employee_id가 10 이상인 직원에 대해 
-- job_id별로 그룹화하여 job_id별 총 급여와 job_id별 평균 급여를 구하고, 
-- job_id별 총 급여를 기준으로 내림차순 정렬하세요.

select job_id, SUM(salary) 총급여, AVG(salary) 평균급여 from employees
where employee_id >= 10
group by job_id
-- 별명을 컬럼명 대신 사용 가능
order by 총급여 desc;


-- SQL 작동 순서
-- SELECT 에서 설정한 별칭은 그 전의 실행 순서에서 사용 불가
-- 5 
SELECT *
-- 1
FROM employees
-- 2
WHERE employee_id > 0
-- 3
GROUP BY manager_id
-- 4
HAVING employee_id IS NOT NULL 
-- 6
ORDER BY first_name;