肖sir__mysql之综合题练习__013

数据库题（10*5）
下面是一个学生与课程的数据库，三个关系表为：
学生表S（Sid，SNAME,AGE,SEX）
成绩表SC（Sid，Cid，GRADE）
课程表C（Cid，CNAME，TEACHER）
其中Sid为学号，SNAME为学生名字，AGE为年龄，SEX为性别，
Cid为课程号，GRADE为成绩，CNAME为课程名字，TEACHER为教师姓名。

SQL脚本如下：
#学生表#create table S(Sid int(1),SNAME char(20),AGE int(1),SEX int(1));
#成绩表#create table SC(Sid int(1),Cid char(5),GRADE int(20)) ;
#课程表#create table C(Cid char(5),CNAME char(20),TEACHER char(20));
/*
insert into S(Sid,SNAME,AGE,SEX)values(1111,‘David’,28,1);
insert into S(Sid,SNAME,AGE,SEX)values(1112,‘Dany’,23,0);
insert into S(Sid,SNAME,AGE,SEX)values(1113,‘TOM’,38,1);
insert into S(Sid,SNAME,AGE,SEX)values(1114,‘Lily’,20,0); */

insert into SC(Sid,Cid,GRADE)values(1113,‘C1’,‘38’);
insert into SC(Sid,Cid,GRADE)values(1112,‘C2’,‘80’);
insert into SC(Sid, Cid,GRADE)values(1112,‘C3’,‘67’);
insert into SC(Sid,Cid,GRADE)values(1111,‘C4’,‘88’);
insert into SC(Sid,Cid,GRADE)values(1114,‘C4’,‘66’);
*/

insert into C(Cid,CNAME,TEACHER)values(‘C4’,‘Maths’,‘zhangsan’);
insert into C(Cid,CNAME,TEACHER)values(‘C3’,‘English’,‘lisi’);
insert into C(Cid,CNAME,TEACHER)values(‘C2’,‘Chinese’,‘wangwu’);
insert into C(Cid,CNAME,TEACHER)values(‘C1’,‘Computer’,‘zhaoliu’);/
/

– 查询出学习成绩及格以上的学生姓名与成绩
select s.sname,sc.grade from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
sc.grade >= 60 group by s.sname;
– 查询姓名以ny结尾的学生姓名及其任课老师姓名
select s.sname,c.teacher from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
s.sname like ‘%ny’ group by s.sname;
– 选修课名为Maths的学生学号与姓名
select s.sname,s.sid from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
c.cname = ‘Maths’;
– 选修课号为C2和C4的学生学号
select s.sid from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
c.cid in (‘C2’,‘C4’);
– 请问没有参加考试的学生的姓名及其学号
select S.sname,S.sid from S inner join SC on S.sid = SC.sid inner join C on SC.cid = C.cid where SC.grade = null;
– 请问总分在80分以上的学生姓名
select s.sname from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
sc.sid in (select sid from SC group by sid having sum(grade) > 80)group by s.sname;
– 请问考试不及格的考生姓名以及科目名称
select s.sname,c.cname from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
sc.grade < 60 group by s.sname;
– 显示出参加考试的学生的学号和姓名
select S.sname,S.sid from S inner join SC on S.sid = SC.sid inner join C on SC.cid = C.cid
where SC.grade is not null group by S.sname;
– 请问TOM参加了哪一门科目的考试
select c.cname from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
s.sname = ‘TOM’;
– 找出Dany的所有考试后成绩中最高的科目的任课老师姓名
select c.teacher from S s,C c,SC sc where s.sid = sc.sid and sc.cid = c.cid and
s.sname = ‘Dany’ and grade = (select max(grade) from SC where Sid = (select sid from S where sname = ‘Dany’));
S

SC

C