오라클 대학교예제 Student, Professor, Dept, Salgrade DDL

gatsby-demo-spring-mybatis

오라클 11g  환경에서 작업합니다.

 

 

 

create table Stu(
    stuno number primary key,
    name varchar2(20),
    userid varchar2(20),
    grade varchar2(20),
    birthdate varchar2(20),
    tel varchar2(20),
    deptno number,
    profno number,
    height number
);
 
 
create table Professor(
    profno number primary key,
    name varchar2(20),
    userid varchar2(20),
    position varchar2(20),
    sal number,
    hiredate varchar2(20),
    comm number,
    deptno number
);
 
 
 
create table Dept(
    deptno number primary key,
    dname varchar2(20),
    college number,
    loc varchar2(20)
);
 
create table Salgrade(
    grade number primary key,
    losal number,
    hisal number
);
 
/*Dept*/
 insert into Dept (deptno,dname,college,loc)   values (101,'컴공',100,'1호관');
 insert into Dept (deptno,dname,college,loc)   values (102,'멀티미디어',100,'2호관');
 insert into Dept (deptno,dname,college,loc)   values (201,'전자공학',100,'3호관');
 insert into Dept (deptno,dname,college,loc)   values (202,'기계공학',100,'4호관');
 insert into Dept (deptno,dname,college,loc)   values (100,'정보미디어',10,null);
 insert into Dept (deptno,dname,college,loc)   values (200,'메카',10,null);
 insert into Dept (deptno,dname,college,loc)   values (10,'공과대학',0,null);
 
 /*Salgrade*/
 insert into Salgrade (grade,losal,hisal)   values (1,100,300);
 insert into Salgrade (grade,losal,hisal)   values (2,301,400);
 insert into Salgrade (grade,losal,hisal)   values (3,401,500);
 
 /*professor*/
insert into professor values(9001, '김유신', 'kim', '교수', 500, 2014, 20, 101);
insert into professor values(9002, '홍길동', 'hong', '조교수', 320, 2013, 0, 201);
insert into professor values(9003, '이순신', 'lee', '전임강사', 240, 2015, 0, 102);
insert into professor values(9004, '유관순', 'you', '부교수', 400, 2010, 17, 202);
insert into professor values(9005, '엘리자베스', 'eli', '교수', 600, 2012, 18, 200);
/*student*/
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1000,'송중기','song','1','1985-09-19','010-1234-1234','101','9001','178');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1001,'박보검','park','2','1990-01-02','010-1234-1234','101','9001','183');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1002,'공지찬','gong','3','1979-07-10','010-1234-1234','101','9001','184');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1003,'강동원','gang','4','1981-01-18','010-1234-1234','101','9001','186');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1004,'하정우','ha','1','1978-03-11','010-1234-1234','101','9001','183');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1005,'김남길','kim','2','1980-01-08','010-1234-1234','101','9001','170');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1006,'서강준','seo','3','1993-01-01','010-1234-1234','101','9001','183');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1007,'장근석','jang','4','1990-01-01','010-1234-1234','101','9001','165');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1008,'전소미','somi','1','2001-03-09','010-1234-1234','101','9001','168');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1009,'김청하','cheng','2','1995-06-05','010-1234-1234','101','9001','155');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1010,'김도연','doyeoun','3','1999-12-04','010-1234-1234','101','9001','160');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1011,'김소혜','sohae','4','1999-10-24','010-1234-1234','101','9001','154');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1012,'전소미','somsom','1','2001-03-09','010-1234-1234','101','9001','170');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1013,'최유정','yoojung','2','1993-07-01','010-1234-1234','101','9001','156');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1014,'김세정','seajung','3','1994-06-14','010-1234-1234','101','9001','163');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1015,'정채연','chae','4','1993-07-02','010-1234-1234','101','9001','165');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1016,'임나영','nayong','1','1990-01-01','010-1234-1234','101','9001','145');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1017,'강미나','mina','2','1990-01-01','010-1234-1234','101','9001','147');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1018,'유연정','yooyeon','3','1990-03-25','010-1234-1234','101','9001','167');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1019,'이승민','min','4','1989-11-02','010-1234-1234','101','9001','187');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1020,'이석훈','hun','4','1989-08-11','010-1234-1234','200','9005','174');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1021,'권재승','seong','1','1996-11-25','010-1234-1234','200','9005','166');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1022,'임시환','sisi','2','1993-06-25','010-1234-1234','200','9005','163');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1023,'강승현','hyun','3','1997-04-08','010-1234-1234','200','9005','163');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1024,'한동근','dong','4','1988-06-28','010-1234-1234','200','9005','180');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1025,'남주혁','joo','1','1994-01-29','010-1234-1234','200','9005','194');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1026,'정수정','soo','2','1994-07-01','010-1234-1234','200','9005','165');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1027,'susan','susan','3','1993-12-22','010-1234-1234','200','9005','163');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1028,'james','jamjam','4','1985-05-19','010-1234-1234','200','9005','188');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1029,'sera','sese','1','1988-04-01','010-1234-1234','200','9005','168');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1030,'christine','christy','2','1996-11-21','010-1234-1234','200','9005','167');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1031,'chris','chirissy','3','1990-05-18','010-1234-1234','200','9005','174');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1032,'paul','paully','3','1993-10-13','010-1234-1234','200','9005','172');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1033,'alex','huabun','4','1990-11-11','010-1234-1234','200','9005','189');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1034,'michael','pope','1','1998-01-01','010-1234-1234','200','9005','180');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1035,'dave','daviy','2','1988-12-01','010-1234-1234','200','9005','190');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1036,'shopia','sophie','3','1990-01-01','010-1234-1234','200','9005','157');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1037,'eden','edens','4','1990-01-01','010-1234-1234','200','9005','154');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1038,'chole','choleee','1','1990-01-13','010-1234-1234','200','9005','167');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1039,'장문복','moonb','1','1998-02-14','010-1234-1234','200','9005','167');
insert into Stu (stuno,name,userid,grade,birthdate,tel,deptno,profno,height) values (1040,'강다니엘','danny','1','1999-08-13','010-1234-1234','200','9005','167');
 
select * from tab;
desc professor;
desc stu;
desc dept;
desc salgrade;