三
下面是一个学生与课程的数据库，三个关系表为：学生表A（Sid，SNAME,AGE,SEX）
成绩表B（Sid，Cid，GRADE）
课程表C（Cid，CNAME，TEACHER）
其中Sid为学号，SNAME为学生名字，AGE为年龄，SEX为性别，
Cid为课程号，GRADE为成绩，CNAME为课程名字，TEACHER为教师姓名。
SQL脚本如下：
#学生表
#create table A(Sid int(1),SNAME char(20),AGE int(1),SEX int(1));
#成绩表
#create table B(Sid int(1),Cid char(5),GRADE char(20)) ;
#课程表
#create table C(Cid char(5),CNAME char(20),TEACHER char(20));
学生表A（Sid，SNAME,AGE,SEX）
成绩表B（Sid，Cid，GRADE）
课程表C（Cid，CNAME，TEACHER）
create table A(Sid int(1),SNAME char(20),AGE int(1),SEX int(1));
create table B(Sid int(1),Cid char(5),GRADE char(20));
create table C(Cid char(5),CNAME char(20),TEACHER char(20));
insert into A(Sid,SNAME,AGE,SEX)values(1111,'David',28,1),(1112,'Dany',23,0),(1113,'TOM',38,1),(1114,'Lily',20,0);
insert into B(Sid,Cid,GRADE)values(1113,'C1','38'),(1112,'C2','80'),(1112,'C3','67'),(1111,'C4','88'),(1114,'C4','66');
insert into C(Cid,CNAME,TEACHER)values('C4','Maths','zhangsan'),('C3','English','lisi'),('C2','Chinese','wangwu'),('C1','Computer','zhaoliu');
select * from A;
select * from B;
select * from C;
请用SQL完成以下的操作：
1.查询出学习成绩及格以上的学生姓名与成绩；
select A.sname,B.grade
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where B.grade>60;
2.查询姓名以ny结尾的学生姓名及其任课老师姓名；
select A.sname,C.teacher
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where A.sname like '%ny';
3.选修课名为Maths的学生学号与姓名;
select A.SID,A.sname
from A
left join B
on A.sid=B.sid
left join C
on B.Cid=C.cid
where C.cname='Maths';
4.选修课号为C2和C4的学生学号；
selectA.sid
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where C.cid='C2' or C.cid='C4';
5.请问没有参加考试的学生的姓名及其学号；
select A.sid,A.sname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where B.grade is null;
6.请问总分在80分以上的学生姓名；
select A.sname
from A
left join(select * from B group by sid having sum(grade)>80)newB
on A.sid=newB.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 C.teacher
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where C.cid in(select cid from B group by sid having sid=(select sid from A where sname='Dany')and max(grade));
9.各个科目的平均成绩各是多少；
select C.cname,avg(grade)
from B
left join C
on B.cid=C.cid
group by B.cid
10.由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正；
update B
left join A
on A.sid=B.sid
set B.grade=61
where A.sname='TOM' and B.grade<60;
11.显示出参加考试的学生的学号和姓名；
select A.sid,sname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where grade is not null group by sid
12.请问TOM一共参加了几门科目的考试；
select count(B.cid)
from A
lect join B
on A.sid=B.sid
where sname='TOM';
13.期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正；
alter table A add(class varchar(20));
14.学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5；
insert into C values('C5','art','baixue');
15.赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除；
delete from C where teacher like ('zhao%');
16.请求出TOM同学所有任课老师的名字及其所教的课程名；
select C.teacher,C.cname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where A.sname='TOM';
17.求每个同学的各科(总分或者平均)成绩，并以每一个人的成绩的先大后小的顺序显示；
select avg(grade),A.sname,C.cname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
group by A.sname order by grade desc
18.请问张老师教什么课程，以及他班上所有学生的姓名
select cname,sname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where teachar='zhangsan'
页:
[1]