JWr95Ecn

1drop database if exists 장현준;
2create database 장현준;
3use 장현준;
4#테이블 생성
5create table 장현준_Department(
6	D_id int not null primary key,
7    name char(10)
8);
9create table 장현준_Student(
10	S_id int not null  primary key,
11	S_name char(10),
12	S_major int,
13	S_address char(30),
14	Gpa double,
15	Bdate  date,
16    foreign key(S_major) references 장현준_Department(D_id)
17);
18create table 장현준_Instructor(
19	P_id int not null primary key,
20    name char(10),
21    Dept int,
22    foreign key(dept) references 장현준_Department(D_id)
23);
24
25create table 장현준_Course(
26	C_id int not null primary key,
27    C_name char(10),
28    C_instuctor int,
29    C_prerequisite int,
30    foreign key(C_instuctor) references 장현준_Instructor(P_id),
31    foreign key(C_prerequisite) references 장현준_Course(C_id)
32);
33create table 장현준_Course_Taken(
34	Num int not null primary key auto_increment,
35	CT_Sid int,
36    CT_Cid int,
37    Grede char(5),
38    Year int,
39    foreign key(CT_Sid) references 장현준_Student(S_id),
40    foreign key(CT_Cid) references 장현준_Course(C_id)
41);
42#ì •ë³´ ìž…ë ¥
43insert into 장현준_Department 
44values (201, '소프트웨어학과'), 
45	   (210, '통계학과'),
46       (309, '기계공학과');
47
48insert into 장현준_Student
49values (32154024, '장현준', 201,'강원도 홍천군 내촌면 290번지', 3.5, 19960414),
50	   (32167842, '김철수', 210, '서울특별시 강남구 강남대로 233', 4.1, 19971203),
51       (32182258, '한영희', 309, '경기도 용인시 수지구 152번지', 2.8, 19990427),
52       (32144910, '김사람', 201, '부산광역시 남구 해운대로 152', 3.2, 19950209);
53
54insert into 장현준_Instructor
55values (20495, '강인아 교수님', 201), (38920, '구하나 교수님', 210),
56	   (30091, '박지훈 교수님', 309), (17602, '이성인 교수님', 201),
57       (25672, '이석균 교수님', 201);
58
59insert into 장현준_Course
60values (391020, '일반수학', 30091, null), (447940, '프로그래밍', 20495, null),
61	   (452660, '데이터베이스기초', 25672, null), (303470, '객체지향프로그래밍', 17602 , 447940),
62       (395042, '공학수학', 38920, 391020);
63
64insert into 장현준_Course_Taken (CT_Sid, CT_Cid, grede, year)
65values (32154024, 391020, 'B+', 2015), (32154024, 447940, 'B', 2015),
66	   (32167842, 447940, 'A', 2016), (32167842, 452660, 'C+', 2016),
67       (32182258, 452660, 'A+', 2018), (32182258, 303470, 'C', 2018),
68       (32144910, 303470, 'D+', 2015), (32144910, 391020, 'A', 2015),
69       (32154024, 452660, 'A+', 2018), (32167842, 303470, 'B', 2018);
70#결과 출력      
71select * from 장현준_Student;
72select * from 장현준_Department;
73select * from 장현준_Instructor;
74select * from 장현준_Course;
75select * from 장현준_Course_Taken;