编辑代码

create database 学生选课;
use 学生选课; 
create table studentinfo
(sno char(8) not null primary key,
sname varchar(10) not null,
sgender char(2),
sbirth datetime,
sclass varchar(20)
);

insert into studentinfo
values('10101001','张永峰','男','1993-8-1','电子商务101'),
('10101002','何小丽','女','1992-11-3','电子商务101'),
('10101003','张宇','男','1992-8-21','电子商务101'),
('10102001','王斌','男','1991-7-14','网络技术101'),
('10102002','包玉明','男','1993-11-15','网络技术101'),
('10102003','孙平平','女','1992-2-27','网络技术101'),
('10102004','翁静静','女','1992-5-9','网络技术101'),
('11101001','刘淑芳','女','1994-6-10','电子商务111'),
('11101002','王亚旭','男','1993-3-18','电子商务111'),
('11101003','高磊','男','1993-5-11','电子商务111');

create table teacher
(
tno char(4) not null primary key,
tname varchar(10) not null,
tgender char(1),
tedu varchar(10),
tpro varchar(8)
);

insert into teacher
values('t001','吴亚飞','男','本科','讲师'),
('t002','李琦','男','硕士研究生','副教授'),
('t003','王艳红','女','硕士研究生','讲师'),
('t004','马志超','男','博士研究生','教授'),
('t005','万丽','女','硕士研究生','助理讲师');

create table course
(
cno char(4) not null primary key,
cname varchar(40) unique,
cperiod int,
credit decimal(3,1),
ctno char(4),
foreign key(ctno) references teacher(tno)
);

insert into course
values('c001','文学欣赏',40,1.5,'t001'),
('c002','中国历史文化',60,2,'t003'),
('c003','视频编辑',70,2.5,'t002'),
('c004','音乐欣赏',40,1.5,'t005');

create table elective
(
sno char(8),
cno char(4),
score int,
primary key (sno,cno),
foreign key(sno) references studentinfo(sno),
foreign key(cno) references course(cno)
);

insert into elective
values('10101001','c001',73),('10101001','c003',81),
('10101001','c004',51),('10101002','c001',78),
('10101003','c003',69),('10102001','c001',50),
('10102002','c003',95),('10102002','c004',75),
('10102002','c002',68),('10102003','c001',85),
('10102003','c002',78),('10102003','c003',67),
('10102003','c004',null),('11101001','c001',49),
('11101001','c002',67),('11101001','c004',62),
('11101002','c001',67),('11101002','c002',null),
('11101003','c002',88),('11101003','c003',90),
('11101003','c004',82);