本帖最后由 宝安22班-陈玉璇 于 2022-3-16 11:09 编辑
-- 请用SQL完成以下的操作:
select *
from a
left join b
ON a.sid=b.sid
left join c
on b.cid=c.cid;
1.查询出学习成绩及格以上的学生姓名与成绩;
select sname,grade
FROM a,b
where a.sid=b.sid
and 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的学生学号;
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.请问没有参加考试的学生的姓名及其学号;
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 ,(select SUM(grade)'总分',Sid
from b
GROUP by Sid
having SUM(grade) > 80)t
where a.sid = t.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 c,(select max(grade),b.cid from a left join b on a.sid=b.sid
where a.sname='Dany')t
where c.cid=t.cid;
9.各个科目的平均成绩各是多少;
select cid,avg(grade)
from b
GROUP BY cid;
10.由于录入员的失误,导致TOM同学有成绩不及格的科目,请帮忙将其合理修正;
select *
from b
where sid=(select sid from a where a.sname='Tom');
update b
set grade=60
where sid=(select sid from a where a.sname='Tom')
AND GRADE < 60;
11.显示出参加考试的学生的学号和姓名;
select a.sname,a.sid
from a,(select b.sid from b where b.grade is not NULL)t
where a.sid=t.sid;
12.请问TOM一共参加了几门科目的考试;
select COUNT(cid)
from (select cid
from a
left join b
ON a.sid=b.sid
where a.sname='tom')t;
13.期末,DBA发现学生表中没有班级字段,请帮忙将其合理修正;
alter table a add class varchar(8) not null;
select * from a ;
14.学校新招了1位美术老师叫“白雪”,请在课程表中增加一条相应的记录,课程编号为C5;
insert into c
values('C5','art','baixue');
select * from c ;
15.赵姓的老师很早就离职了,然而课程表中却还有其上课的记录,请帮忙删除;
delete from c where teacher like 'zhao%';
select * from c ;
16.请求出TOM同学所有任课老师的名字及其所教的课程名;
insert into C values ('C1','Computer','zhaoliu');
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 a.sname,b.sid,b.cid,AVG(GRADE)
from a
left join b
on a.sid= b.sid
left join c
on b.cid=c.cid
group by sid
order by avg(grade) desc;
18.请问张老师教什么课程,以及他班上所有学生的姓名;
select a.sname,c.teacher
from a
left join b
on a.sid= b.sid
left join c
on b.cid=c.cid
where c.teacher like 'zhang%';
|
|