3.14数据库三表题目--佘扬周
请用SQL完成以下的操作：1.查询出学习成绩及格以上的学生姓名与成绩；
select a.sname,grade from (select sid,grade from b where grade>60)t,a
where a.sid=t.sid;
2.查询姓名以ny结尾的学生姓名及其任课老师姓名；
select sname,teacher from (select sid,sname from a where sname like'%ny')t
left join b on t.sid=b.sid
left join c on b.cid=c.cid;
3.选修课名为Maths的学生学号与姓名;
select a.sid,sname from a
right join b on a.sid=b.sid
right join (select cid from c where cname='Maths')t on b.cid=t.cid;
4.选修课号为C2和C4的学生学号；
select a.sid from a
right join b on a.sid=b.sid
right join (select cid from c where cid in ('c2','c4'))t on b.cid=t.cid;
5.请问没有参加考试的学生的姓名及其学号；
select a.sid,a.sname from (select sid from b where grade is null)t
left join a on t.sid=a.sid;
6.请问总分在80分以上的学生姓名；
select a.sname from (select sid from b where grade>80)t
left join a on t.sid=a.sid;
7.请问考试不及格的考生姓名以及科目名称；
select a.sname,c.cname from a
left join b on a.sid=b.sid
left join c on b.cid=c.cid
where b.grade<60;
8.找出Dany的所有考试后成绩中最高的科目的任课老师姓名；
select max(grade),c.teacher from a
left join b on a.sid=b.sid
left join c on b.cid=c.cid
where a.sname='Dany';
9.各个科目的平均成绩各是多少；
select * from (select cid,avg(grade) from b where cid='c1')t
union
select * from (select cid,avg(grade) from b where cid='c2')u
union
select * from (select cid,avg(grade) from b where cid='c3')s
union
select * from (select cid,avg(grade) from b where cid='c4')q;
10.由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正；
update b set grade=60
where sid=(select sid from a where sname='TOM');
11.显示出参加考试的学生的学号和姓名；
select a.sid,a.sname from (select sid from b where grade is not null)t
left join a on t.sid=a.sid;
12.请问TOM一共参加了几门科目的考试；
select count(*) from b
where sid=(select sid from a where sname='TOM');
13.期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正；
alter table a add CLASS varchar(10) default'1832';
14.学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5；
insert into c(cid,cname,teacher)
values('C5','Fine Arts','baixue');
15.赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除；
update c set teacher='null' where teacher='zhaoliu';
16.请求出TOM同学所有任课老师的名字及其所教的课程名；
select cname,teacher from c
where cid=(select cid from b where sid=(select sid from a where sname='TOM'));
17.求每个同学的各科(总分或者平均)成绩，并以每一个人的成绩的先大后小的顺序显示；
select a.sid,a.sname,b.cid,b.grade from a,b
where a.sid=b.sid
order by grade desc;
18.请问张老师教什么课程，以及他班上所有学生的姓名；
select c.cname,a.sname from a
left join b on a.sid=b.sid
left join c on b.cid=c.cid
where c.teacher='zhangsan';
页:
[1]