数据库3表
三表题目下面是一个学生与课程的数据库，三个关系表为：
学生表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;
#左
select *
from A
left join B
on A.sid =B.sid
LEFT JOIN C
on B.cid =C.cid ;
请用SQL完成以下的操作：
1.查询出学习成绩及格以上的学生姓名与成绩；
select *
from B
where 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.sname,A.Sid
from A
left join B
on A.sid =B.sid
LEFT JOIN C
on B.cid =C.cid
where CNAME ='Maths';
4.选修课号为C2和C4的学生学号；
select A.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.请问没有参加考试的学生的姓名及其学号；
selectA.Sid,A.sname
from A
left join B
on A.sid =B.sid
LEFT JOIN C
on B.cid =C.cid
where grade is null;
6.请问总分在80分以上的学生姓名；
select A.sname
from A
left join B
on A.sid =B.sid
LEFT JOIN C
on B.cid =C.cid
where grade >80;
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 grade <60;
8.找出Dany的所有考试后成绩中最高的科目的任课老师姓名；
select sname
from A
where sname ='tom';
9.各个科目的平均成绩各是多少；
select cname,avg(grade)
from B
LEFT JOIN C
on B.cid =C.cid
group by B.cid;
10.由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正；
update B
set grade =65
where grade <60 and sid =(select sid
from A where sname ='tom');
11.显示出参加考试的学生的学号和姓名；
select sname ,A.sid
from A
leftjoin B
ON A.sid=B.sid
where grade is null group by B.sid;
12.请问TOM一共参加了几门科目的考试；
select *
from A
left join B
on A.sid =B.SID
LEFT JOIN C
ON B.CID =C.CID
where sname ='tom';
13.期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正；
alter table A add (class char);
14.学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5；
insert into C values ('C5', 'art' ,'baixue');
15.赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除；
delete from C where teacher like '%zhao';
16.请求出TOM同学所有任课老师的名字及其所教的课程名；
select C.cname,teacher
from A
LEFT JOIN B
ON A.SID=B.SID
LEFT JOIN C
ON B.CID= C.CID
WHERE A.sname ='TOM';
17.求每个同学的各科(总分或者平均)成绩，并以每一个人的成绩的先大后小的顺序显示；
select sum(grade),SNAME
from A
LEFT JOIN B
ON A.SID=B.SID
LEFT JOIN C
ON B.CID= C.CID
GROUP BY SNAME ORDER BY sum(GRADE) DESC;
18.请问张老师教什么课程，以及他班上所有学生的姓名；
select A.SNAME, CNAME
from A
LEFT JOIN B
ON A.SID=B.SID
LEFT JOIN C
ON B.CID= C.CID
where TEACHER LIKE 'zhang%';
页:
[1]