DEPT, SAWON, GOGEK 필터명

1. TABLE dept
   deptno 부서번호
   dname 부서이름
   loc 지역

2. TABLE sawon
   sabun 사번
   saname 지원명
   deptno 부서번호
   sajob 직위
   sapav 급여
   sahire 입사일
   sasex 성별
   samgr 멘토

3. TABLE gogek
   gobun 고객번호
   goname 고객이름
   gotel 고객전화
   gojumin 주민번호
   godam 담당직원

codebook dept, sawon, gogek

drop TABLE gogek;
purge recyclebin;
drop TABLE sawon;
purge recyclebin;
drop TABLE dept;
purge recyclebin;

CREATE TABLE dept(
deptno number(3),
dname varchar2(10),
loc varchar2(10),
constraint dept_deptno_PK PRIMARY KEY(deptno),
constraint dept_dname_uk unique(dname)
);

CREATE TABLE sawon(
sabun number(3),
saname varchar2(10) constraint sawon_saname_NN not null,
deptno number(3),
sajob varchar2(10),
sapav number(10),
sahire date default sysdate,
sasex varchar2(6),
samgr number(3),
constraint sawon_sabun_PK PRIMARY KEY(sabun),
constraint sawon_deptno_FK FOREIGN KEY(deptno) REFERENCES dept(deptno),
constraint sawon_sasex_ck CHECK(sasex in('남자','여자')),
constraint sawon_samgr_FK FOREIGN KEY(samgr) REFERENCES sawon(sabun)
);

CREATE TABLE gogek(
gobun number(3),
goname varchar2(10),
gotel varchar2(20),
gojumin varchar2(14),
godam number(3),
constraint gogek_gobun_PK PRIMARY KEY(gobun),
constraint gogek_godam_FK FOREIGN KEY(godam) REFERENCES sawon(sabun)
);

insert into dept(deptno,dname,loc)
values('10','영업부','서울');

insert into dept(deptno,dname,loc)
values('20','전산부','대전');

insert into dept(deptno,dname,loc)
values('30','관리부','부산');

insert into dept(deptno,dname,loc)
values('40','총무부','광주');

insert into sawon Values(1,'홍길동',10,'회장',5000,'2000/01/01','남자',null);
insert into sawon Values(2,'한국남',20,'부장',3000,'2008/11/01','남자',1);
insert into sawon Values(3,'이순신',20,'과장',3500,'2005/03/01','남자',2);
insert into sawon Values(5,'이순라',20,'사원',1200,'2010/05/01','여자',3);
insert into sawon Values(7,'놀기만',20,'과정',2300,'2006/06/01','여자',2);
insert into sawon Values(11,'류별나',20,'과장',1600,'2009/12/01','여자',2);
insert into sawon Values(14,'채시라',20,'수원',3400,'2013/01/01','여자',3);
insert into sawon Values(17,'이성계',30,'부장',2803,'2004/05/01','남자',1);
insert into sawon Values(13,'무궁화',10,'부장',3000,'2013/11/01','여자',1);
insert into sawon Values(19,'임꺽정',20,'사원',2200,'2008/04/01','남자',7);
insert into sawon Values(20,'깨똥이',10,'과장',4500,'2010/05/01','남자',13);
insert into sawon Values(6,'공부만',30,'과정',4003,'2013/05/01','남자',17);
insert into sawon Values(8,'채송화',30,'대리',1703,'2012/06/01','여자',17);
insert into sawon Values(12,'류명한',10,'대리',1800,'2010/10/01','남자',20);
insert into sawon Values(9,'무궁화',10,'사원',1100,'2004/08/01','여자',12);
insert into sawon Values(4,'이미라',30,'대리',2503,'2003/04/01','여자',17);
insert into sawon Values(10,'공부해',30,'사원',1303,'2008/11/01','남자',4);
insert into sawon Values(15,'최진실',10,'사원',2000,'2011/04/01','여자',12);
insert into sawon Values(16,'김유신',30,'사원',400,'2001/04/01','남자',4);
insert into sawon Values(18,'강감찬',30,'사원',1003,'2006/07/01','남자',4);

insert into gogek values(1,'뷰민','123-1234','700113-1537915',3);
insert into gogek values(2,'강민','343-1455','690216-1627914',2);
insert into gogek values(3,'영희','144-1655','750320-2636215',null);
insert into gogek values(4,'철이','673-1674','770430-1234567',4);
insert into gogek values(5,'뷰완','123-1674','720521-1123675',3);
insert into gogek values(6,'캔디','673-1764','650725-2534566',null);
insert into gogek values(7,'똘이','176-7677','630608-1648614',7);
insert into gogek values(8,'쇠돌','673-6774','800804-1346574',9);
insert into gogek values(9,'홍이','767-1234','731225-1234689',13);
insert into gogek values(10,'안나','767-1677','751015-2432168',4);

select * from dept;
select * from sawon;
select * from gogek;

MenberT01

drop table memberT01;
purge recyclebin;

CREATE TABLE memberT01(
mem_id varchar2(10),
mem_pwd varchar2(10),
mem_name varchar2(10),
mem_email varchar2(20),
mem_phone varchar2(13),
mem_addr varchar2(20)
);

insert into memberT01(mem_id,mem_pwd,mem_name,mem_email,mem_phone,mem_addr)
values('orange','1234','오렌지','orange@test.com','043','우송대');

insert into memberT01(mem_id,mem_pwd,mem_name,mem_email,mem_phone,mem_addr)
values('red','1234','장미','red@test.com','062','전남대');

insert into memberT01(mem_id,mem_pwd,mem_name,mem_email,mem_phone,mem_addr)
values('yellow','1234','개나리','yellow@test.com','062','전남대');

insert into memberT01(mem_id,mem_pwd,mem_name,mem_email,mem_phone,mem_addr)
values('green','1234','소나무','green@test.com','043','우송대');

insert into memberT01(mem_id,mem_pwd,mem_name,mem_email,mem_phone,mem_addr)
values('blue','1234','바다','blue@test.com','062','전남대');

SELECT * FROM memberT01;