· 8 years ago · Mar 13, 2018, 10:22 AM
1drop database if exists University;
2create database University;
3use University;
4show tables;
5
6create table Student (
7 StuID int,
8 StuName char(10),
9 Address char(20),
10 Major char(10),
11 primary key(StuID)
12);
13insert into Student
14values (32154024, '장현준', 'ê°•ì›ë„ í™ì²œêµ° 내촌면 290번지', '소프트웨어학과'),
15 (32167842, 'ê¹€ì² ìˆ˜', '서울특별시 강남구', 'ì‘용컴퓨터공학과'),
16 (32182258, 'í•œì˜í¬', 'ê²½ê¸°ë„ ìš©ì¸ì‹œ 수지구 152번지', '통계학과');
17create table Course(
18 CouNo char(10),
19 CouName char(10),
20 Instructor char(10),
21 primary key(CouNo)
22);
23insert into Course
24values ('ao-516', 'ë°ì´í„°ë² ì´ìŠ¤', 'ì´ì„ê· '),
25 ('bo-517', 'ì†Œí”„íŠ¸ì›¨ì–´í•™ê°œë¡ ', '김규한'),
26 ('co-406', 'ì´ì‚°ìˆ˜í•™', '차민주');
27create table Course_taken (
28 CtNo int not null auto_increment,
29 ID int,
30 CourseNo char(10),
31 Grade char(1),
32 primary key(CtNo),
33 foreign key(ID) references Student(StuID),
34 foreign key(CourseNo) references Course(CouNo)
35);
36insert into Course_taken (ID,CourseNo,Grade)
37values (32154024, 'ao-516', 'A'),
38 (32154024, 'co-406', 'B'),
39 (32167842, 'bo-517', 'C'),
40 (32182258, 'co-406', 'B'),
41 (32182258, 'ao-516', 'D');
42
43select * from Student;
44select * from Course;
45select * from Course_taken;
46SELECT Student.StuName, Course.CouName, Course_taken.Grade
47FROM Student, Course, Course_taken
48WHERE Student.StuID = Course_taken.ID AND Course.CouNo = Course_taken.CourseNo;