SQL数据库练习题

create database Student;
use Student;




create table Student(Sno varchar(20)not null,primary key(Sno),  Sname varchar(20)not null,
Ssex varchar(20)not null,Sbirthday datetime,Class varchar(20));
create table Course(Cno varchar(20)not null ,primary key(Cno),Cname varchar(20)
not null,Tno varchar(20)not null );
create table Score(Sno varchar(20)not null,Cno varchar(20)not null,Degree decimal(4,1));
create table Teacher(Tno varchar(20)not null,primary key(Tno),Tname varchar(20)not null,
Tsex varchar(20)not null,Tbirthday datetime,Prof varchar(20),Depart varchar(20));




alter table Course add constraint fk1 foreign key(Tno) references Teacher(Tno);
alter table Score add constraint fk2 foreign key(Cno) references Course(Cno);
alter table Score add constraint fk3 foreign key(Sno) references Student(Sno);




alter table Score drop foreign key kf3;
insert into Student values('108','曾华','男','1977-09-01','95033');
insert into Student values('105','匡明','男','1975-10-02','95031');
insert into Student values('107','王丽','女','1976-01-23','95033');
insert into Student values('101','李军','男','1976-02-20','95033');
insert into Student values('109','王芳','女','1975-02-10','95031');
insert into Student values('103','陆君','男','1974-06-03','95031');




insert into Course values('3-105','计算机导论','825');
insert into Course values('3-245','操作系统','804');
insert into Course values('6-166','数字电路','856');
insert into Course values('9-888','高等数学','831');




select * from Course;
insert into Score values('103','3-245','86');
insert into Score values('105','3-245','75');
insert into Score value