Professor, Department, Student 실습용 테이블

기존 학원에서 Oracle 강의 들었을때 사용했던 실습용 테이블을 MySQL로 바꾼 테이블 입니다.

 

 

drop table professor;

create table professor
(profno int(4) primary key,
 name  varchar(10) not null, 
 id  varchar(15) not null,
 position varchar (30) not null,
 pay int(3) not null,
 hiredate  date not null,
 bonus int(4) ,
 deptno  int(3),
 email  varchar(50),
 hpage  varchar(50));

insert into professor
values(1001,'조인형','captain','정교수',550,date_format('19800623','%Y%m%d'),100,101,'captain@abc.net','http://www.abc.net');

insert into professor
values(1002,'박승곤','sweety','조교수',380,date_format('1987-01-30','%Y%m%d'),60,101,'sweety@abc.net','http://www.abc.net');

insert into professor
values (1003,'송도권','powerman','전임강사',270,date_format('1998-03-22','%Y%m%d'),null,101,'pman@power.com','http://www.power.com');

insert into professor
values (2001,'양선희','lamb1','전임강사',250,date_format('2001-09-01','%Y%m%d'),null,102,'lamb1@hamail.net',null);

insert into professor
values (2002,'김영조','number1','조교수',350,date_format('1985-11-30','%Y%m%d'),80,102,'number1@naver.com','http://num1.naver.com');

insert into professor
values (2003,'주승재','bluedragon','정교수',490,date_format('1982-04-29','%Y%m%d'),90,102,'bdragon@naver.com',null);

insert into professor
values (3001,'김도형','angel1004','정교수',530,date_format('1981-10-23','%Y%m%d'),110,103,'angel1004@hanmir.com',null);

insert into professor
values (3002,'나한열','naone10','조교수',330,date_format('1997-07-01','%Y%m%d'),50,103,'naone10@empal.com',null);

insert into professor
values (3003,'김현정','only-u','전임강사',290,date_format('2002-02-24','%Y%m%d'),null,103,'only_u@abc.com',null);

insert into professor
values (4001,'심슨','simson','정교수',570,date_format('1981-10-23','%Y%m%d'),130,201,'chebin@daum.net',null);

insert into professor
values (4002,'최슬기','gogogo','조교수',330,date_format('2009-08-30','%Y%m%d'),null,201,'gogogo@def.com',null);

insert into professor
values (4003,'박원범','mypride','조교수',310,date_format('1999-12-01','%Y%m%d'),50,202,'mypride@hanmail.net',null);

insert into professor
values (4004,'차범철','ironman','전임강사',260,date_format('2009-01-28','%Y%m%d'),null,202,'ironman@naver.com',null);

insert into professor
values (4005,'바비','standkang','정교수',500,date_format('1985-09-18','%Y%m%d'),80,203,'standkang@naver.com',null);

insert into professor 
values (4006,'전민','napeople','전임강사',220,date_format('2010-06-28','%Y%m%d'),null,301,'napeople@jass.com',null);

insert into professor
values (4007,'허은','silver-her','조교수',290,date_format('2001-05-23','%Y%m%d'),30,301,'silver-her@daum.net',null);

insert into professor 
values (4008,'김미지','kimmi','전임강사',230,date_format('2011-05-25','%Y%m%d'),null,301,'na@naver.com',null);

insert into professor
values (4009,'강나라','kangna','조교수',295,date_format('2003-07-21','%Y%m%d'),30,301,'sil@daum.net',null);


commit;

drop table department;
create table department
( deptno int(3) primary key ,
  dname varchar(100) not null,
  part int(3),
  build  varchar(40));

insert into department 
values (101,'컴퓨터공학과',100,'정보관');

insert into department
values (102,'멀티미디어공학과',100,'멀티미디어관');

insert into department
values (103,'소프트웨어공학과',100,'소프트웨어관');

insert into department
values (201,'전자공학과',200,'전자제어관');

insert into department
values (202,'기계공학과',200,'기계실험관');

insert into department
values (203,'화학공학과',200,'화학실습관');

insert into department
values (301,'문헌정보학과',300,'인문관');

insert into department
values (100,'컴퓨터정보학부',10,null);

insert into department
values (200,'메카트로닉스학부',10,null);

insert into department
values (300,'인문사회학부',20,null);

insert into department
values (10,'공과대학',null,null);

insert into department
values (20,'인문대학',null,null);

commit;
 
drop table student;

create table student
( studno int(4) primary key,
  name   varchar(10) not null,
  id varchar(20) not null unique,
  grade float check(grade between 1 and 6),
  jumin char(13) not null,
  birthday  date,
  tel varchar(15),
  height  int(4),
  weight  int(3),
  deptno1 int(3),
  deptno2 int(3),
  profno  int(4));

insert into student values (
9411,'서진수','75true',4,'9310231901813',date_format('1975-10-23','%Y%m%d'),'055)381-2158',180,72,101,201,1001);

insert into student values (
9412,'서재수','pooh94',4,'9302241128467',date_format('1975-02-24','%Y%m%d'),'051)426-1700',172,64,102,null,2001);

insert into student values (
9413,'이미경','angel000',4,'9306152123648',date_format('1975-06-15','%Y%m%d'),'053)266-8947',168,52,103,203,3002);

insert into student values (
9414,'김재수','gunmandu',4,'9312251063421',date_format('1975-12-25','%Y%m%d'),'02)6255-9875',177,83,201,null,4001);

insert into student values (
9415,'박동호','pincle1',4,'9303031639826',date_format('1975-03-03','%Y%m%d'),'031)740-6388',182,70,202,null,4003);

insert into student values (
9511,'김신영','bingo',3,'9401232186327',date_format('1976-01-23','%Y%m%d'),'055)333-6328',164,48,101,null,1002);

insert into student values (
9512,'신은경','jjang1',3,'9404122298371',date_format('1976-04-12','%Y%m%d'),'051)418-9627',161,42,102,201,2002);

insert into student values (
9513,'오나라','nara5',3,'9409112118379',date_format('1976-09-11','%Y%m%d'),'051)724-9618',177,55,202,null,4003);

insert into student values (
9514,'구유미','guyume',3,'9401202378641',date_format('1976-01-20','%Y%m%d'),'055)296-3784',160,58,301,101,4007);

insert into student values (
9515,'임세현','shyun1',3,'9410122196482',date_format('1976-10-12','%Y%m%d'),'02)312-9838',171,54,201,null,4001);

insert into student values (
9611,'일지매','onejimae',2,'9511291186223',date_format('1977-11-29','%Y%m%d'),'02)6788-4861',182,72,101,null,1002);

insert into student values (
9612,'김진욱','samjang7',2,'9504021358674',date_format('1977-04-02','%Y%m%d'),'055)488-2998',171,70,102,null,2001);

insert into student values (
9613,'안광훈','nonnon1',2,'9509131276431',date_format('1977-09-13','%Y%m%d'),'053)736-4981',175,82,201,null,4002);

insert into student values (
9614,'김문호','munho',2,'9502261196365',date_format('1977-02-26','%Y%m%d'),'02)6175-3945',166,51,201,null,4003);

insert into student values (
9615,'노정호','star123',2,'9512141254963',date_format('1977-12-14','%Y%m%d'),'051)785-6984',184,62,301,null,4007);

insert into student values (
9711,'이윤나','prettygirl',1,'9608192157498',date_format('1978-08-19','%Y%m%d'),'055)278-3649',162,48,101,null,null);

insert into student values (
9712,'안은수','silverwt',1,'9601051776346',str_to_date('19780105','%Y%m%d'),'02)381-5440',175,63,201,null,null);

insert into student values (
9713,'인영민','youngmin',1,'9608091786954',str_to_date('19780809','%Y%m%d'),'031)345-5677',173,69,201,null,null);

insert into student values (
9714,'김주현','kimjh',1,'9603241981987',str_to_date('19780324','%Y%m%d'),'055)423-9870',179,81,102,null,null);

insert into student values (
9715,'허우','wooya2702',1,'9602232116784',str_to_date('19780223','%Y%m%d'),'02)6122-2345',163,51,103,null,null);

commit;