SQL练习题

逻辑树分析法，对复杂问题进行拆解

create table student(
	sid varchar(6) not null primary key comment '学号',
	sname varchar(10) comment '学生姓名',
	sage datetime comment '出生日期',
	ssex varchar(10) comment '性别'
)engine=InnoDB default charset=utf8;
alter table student comment '学生';
insert into student(sid,sname,sage,ssex) values ('01','赵风','1990-01-01','男');
insert into student(sid,sname,sage,ssex) values ('02','钱雨','1990-12-21','男');
insert into student(sid,sname,sage,ssex) values ('03','孙雷','1990-05-20','男');
insert into student(sid,sname,sage,ssex) values ('04','李电','1990-08-06','男');
insert into student(sid,sname,sage,ssex) values ('05','周梅','1991-12-01','女');
insert into student(sid,sname,sage,ssex) values ('06','吴兰','1992-03-01','女');
insert into student(sid,sname,sage,ssex) values ('07','郑竹','1989-07-01','女');
insert into student(sid,sname,sage,ssex) values ('08','王菊','1990-01-20','女');

create table course(
	cid varchar(10) not null primary key comment '课程编号',
	cname varchar(10) comment '课程名称',
	tid varchar(10) comment '老师编号'
)engine=InnoDB default charset=utf8;
alter table course comment '课程';
insert into course(cid,cname,tid) values ('01','语文','02');
insert into course(cid,cname,tid) values ('02','数学','01');
insert into course(cid,cname,tid) values ('03','英语','03');

create table teacher(
	tid varchar(10) not null primary key comment '老师编号',
	tname varchar(10) comment '老师姓名'
)engine=InnoDB default charset=utf8;
alter table teacher comment '老师';
insert into teacher(tid,tname) values ('01','张三');
insert into teacher(tid,tname) values ('02','李四');
insert into teacher(tid,tname) values ('03','王五');

create table sc(
	sid varchar(10) comment '学号',
	cid varchar(10) comment '课程编号',
	score decimal(18,1) comment '分数',
	primary key(sid,cid)
)engine=InnoDB default charset=utf8;
alter table sc comment '成绩';
insert into sc(sid,cid,score) values ('01','01',80);
insert into sc(sid,cid,score) values ('01','02',90);
insert into sc(sid,cid,score) values ('01','03',99);
insert into sc(sid,cid,score) values ('02'