SELECT tableA.id, tableA.value 
FROM tableA INNER JOIN tableB 
ON tableA.id = tableB.id; 

-- 별명 주기! 
-- note: 별명주면 별명만 사용해야 함, table 별명 as 사용 불가
SELECT a.id, a.value 
FROM tableA a INNER JOIN tableB b 
ON a.id = b.id; 

-- mysql 자체 문법
SELECT a.id, a.value 
FROM tableA a, tableB b 
WHERE a.id = b.id; 

-- 직원의 사번, 이름, 업무, 부서번호, 부서명을 조회
SELECT empno, ename, job, scott_emp.deptno, dname 
FROM scott_emp INNER JOIN scott_dept 
ON scott_emp.deptno = scott_dept.deptno;

-- salesman에 대해서 직원의 사번, 이름, 업무, 부서번호, 부서명을 조회
-- ! 조건을 조인하면서 같이줄 때
SELECT empno, ename, job, e.deptno, dname 
FROM scott_emp e INNER JOIN scott_dept d 
ON e.deptno = d.deptno AND job = 'salesman';

-- ! 조건을 조인 하고 나서 뒤에 줄 때
SELECT empno, ename, job, e.deptno, dname 
FROM scott_emp e INNER JOIN scott_dept d 
ON e.deptno = d.deptno 
WHERE job = 'salesman';

-- New york에서 근무하는 직원의 이름, 업무, 부서명 조회
SELECT ename, job, scott_dept.dname 
FROM scott_dept INNER JOIN scott_emp 
ON scott_emp.deptno = scott_dept.deptno 
WHERE loc = 'NEW YORK';

SELECT a.id, a.value 
FROM tableA a LEFT OUTER JOIN tableB b 
ON a.id = b.id;

SELECT b.id, b.value 
FROM tableA a RIGHT OUTER JOIN tableB b 
ON a.id = b.id;

-- 직원들의 이름, 급여, 근무지를 조회. 단, 부서명과 근무지는 모두 출력할 수 있도록 하시오
SELECT ename, sal, dname, loc 
FROM scott_emp RIGHT OUTER JOIN scott_dept 
ON scott_emp.deptno = scott_dept.deptno;

---직원들의 사번, 이름, 급여, 급여등급 조회
SELECT empno, ename, sal, grade, losal, hisal 
FROM scott_emp e INNER JOIN scott_salgrade s 
ON e.sal >= s.losal AND e.sal <= s.hisal;

-- 직원의 사번, 이름, 업무, 관리자, 관리자이름 조회
-- 별명주기 필수
SELECT e.empno, e.ename, e.job, e.mgr, m.ename 
FROM scott_emp e INNER JOIN scott_emp m 
ON e.mgr = m.empno;

SELECT a.id, a.value, b.id, b.value, c.id, c.value
FROM tableA a INNER JOIN tableB b
ON a.id = b.id
INNER JOIN tableC c
ON b.id = c.id;

-- mysql 자체 문법
SELECT a.id, a.value, b.id, b.value, c.id, c.value
FROM tableA a, tableB b, tableC c
WHERE a.id = b.id AND b.id = c.id;

SELECT deptno FROM scott_emp 
UNION 
SELECT deptno FROM scott_dept;

SELECT deptno FROM scott_emp 
UNION ALL 
SELECT deptno FROM scott_dept;

CREATE TABLE tableEXAM( 
	id	int, 
    name	varchar(10) 
);

INSERT INTO tableEXAM(id) VALUES(1);
INSERT INTO tableEXAM(id) VALUES(1);

-- PRIMARY KEY: 한 테이블 당 한개만 소유
ALTER TABLE tableEXAM ADD CONSTRAINT pk_id PRIMARY KEY(id);
DELETE FROM tableEXAM;
ALTER TABLE tableEXAM ADD CONSTRAINT pk_id PRIMARY KEY(id);

-- ND
INSERT INTO tableEXAM(id) VALUES(1);
INSERT INTO tableEXAM(id) VALUES(1); -- ERROR

-- NN
INSERT INTO tableEXAM(name) VALUES('kimmy'); -- ERROR

-- PRIMARY KEY 지정 방법
DROP TABLE tableEXAM;

--하나의 field에만 primary key
CREATE TABLE tableEXAM(
	id	int	PRIMARY KEY, 
	name varchar(10)
);

DROP TABLE tableEXAM;

-- 여러 field를 하나의 primary key로 가능 (권장)
CREATE TABLE tableEXAM( 
	id	int, 
	name varchar(10), 
	CONSTRAINT pk_id PRIMARY KEY(id) 
);

-- NN
CREATE TABLE tableEXAM( 
	id	int	not null, 
    name	varchar(10)	not null 
);

-- name을 null 로
ALTER TABLE tableEXAM MODIFY name varchar(10) null;
ALTER TABLE tableEXAM ADD age int null;

-- UNIQUE: ND 속성을 지켜줌
DROP TABLE tableEXAM;
CREATE TABLE tableEXAM( 
	id	int, 
	name varchar(10), 
	age int
);
ALTER TABLE tableEXAM ADD CONSTRAINT uk_id UNIQUE(id); 
ALTER TABLE tableEXAM ADD CONSTRAINT uk_name UNIQUE(name);

INSERT INTO tableEXAM(id, name, age) VALUES(1, 'kimmy', 20);

-- ND
INSERT INTO tableEXAM(id, name, age) VALUES(1, 'lee', 20);
	--> ERROR: Duplicate entry '1' for key 'uk_id'
INSERT INTO tableEXAM(id, name, age) VALUES(2, 'kimmy', 20);
	--> ERROR: Duplicate entry 'kimmy' for key 'uk_name'

-- NN 속성은 방지 x
INSERT INTO tableEXAM(id, name, age) VALUES(null, 'jimmy', 20);
	--> Query OK, 1 row affected
    
-- null값 중복은 ok
INSERT INTO tableEXAM(id, name, age) VALUES(null, 'elsa', 20);
	--> Query OK, 1 row affected

/* NN & ND 선처리 방식 (제약 조건은 x)
NN: DEFAULT
ND: AUTO_INCREMENT
*/
DROP TABLE tableEXAM;

CREATE TABLE tableEXAM( 
	id	int	AUTO_INCREMENT, 
	name varchar(10) DEFAULT 'no name', 
	age int	DEFAULT 20,
	CONSTRAINT uk_id UNIQUE(id)
);

INSERT INTO tableEXAM(name, age) VALUES ('scott', 20);
INSERT INTO tableEXAM(age) VALUES (30);
INSERT INTO tableEXAM(name) VALUES ('miller');

CREATE TABLE tableDEPT(
	deptno int,
	dname varchar(10)
);

INSERT INTO tableDEPT VALUES(100, 'SALES');
INSERT INTO tableDEPT VALUES(101, 'MARKETING');
INSERT INTO tableDEPT VALUES(102, 'RESEARCH');

CREATE TABLE tableEMP(
	empno int,
	ename varchar(20),
	hiredate datetime,
	deptno int
);

-- deptno에 제약이 없으면 참조키를 달 수 없음
ALTER TABLE tableDEPT ADD CONSTRAINT pk_deptno PRIMARY KEY(deptno); 

ALTER TABLE tableEMP 
ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) 
REFERENCES tableDEPT(deptno);

INSERT INTO tableEMP(empno, ename, hiredate, deptno) VALUES(1, 'scott', now(), 103);
	--> ERROR: Cannot add or update a child row: a foreign key constraint fails

INSERT INTO tableEMP(empno, ename, hiredate, deptno) VALUES(1, 'scott', now(), 102);
	--> Query OK, 1 row affected

-- NC
UPDATE tableDEPT SET deptno = 1002 WHERE deptno = 102;
	--> ERROR: Cannot delete or update a parent row: a foreign key constraint fails

-- NC (삭제 x)
DROP TABLE tableDEPT;
	--> ERROR: Cannot delete or update a parent row: a foreign key constraint fails

CREATE TABLE POST(
	post1 char(3),
	post2 char(3),
	addr varchar(60) DEFAULT 'no addr',
	CONSTRAINT pk_post PRIMARY KEY(post1, post2)
);

CREATE TABLE MEMBER(
	id int(4) PRIMARY KEY,
	name varchar(10) DEFAULT 'kimmy',
	sex char(1), 
	jumin1 char(6),
	jumin2 char(7),
	tel varchar(15),
	post1 char(3),
	post2 char(3),
	addr varchar(60)
);
ALTER TABLE MEMBER ADD CONSTRAINT uk_jumin UNIQUE(jumin1, jumin2);
ALTER TABLE MEMBER ADD CONSTRAINT fk_post FOREIGN KEY(post1, post2) REFERENCES POST(post1, post2);