总结的SQL语句

1：将所有提交时间晚于作业提交截止时间的作业提交分数设置为30
思路：提交时间 作业截止时间涉及到Submmission表和homework表，Score在Subbmmision，俩表的关联是No

update Submission set Score=30 where SID in(
select SID from Subbssion sub,Homework h where sub.No=h.No and
sub.Submit_time >h.Deadline)这样写会导致所有的都会改为30的

正确的：

update submission a set score = 30 where exists (
select 1 from homework b where a.cid = b.cid and a.no = b.no 
and  a.submit_time > b.deadline)

exists是个谓词，判断子查询是否存在，这代码可以理解为：只要提交时间晚于

所有提交的时间晚于，所有的是关键点

2：查询平均成绩最低的作业的课程号，作业序号和作业标题
思路：先求平均成绩，当做个表，得出最低的平均成绩，score等于这个最低的
select cid,no,avg(Score) avg_score,No from Submission group by cid,No 把这当做个表
注意：cid和no为非聚集的，所以都要group by

这个是正确的答案和解题思路

with ta as (select cid,no,avg(score) avg_score from submission 
group by cid,no)
select cid,no,title from homework a,ta 
where a.cid = ta.cid and a.no = ta.no
and ta.avg_score = (select min(avg_score) from ta )

思路：select cid,no,avg(score) avg_score from submission group by cid,no（查询每个作业的平均分作为子查询）

这个是求每个课程每个作业的平均分
然后ta.score=(select min(avg_score) from ta)

3：查询提交了CS001课程所有作业的学生的学号，姓名和所属院系
问题：如何判断是交了所有的作业呢？涉及Student和Submission两个表，这里是要用到exists吗？

问题：exists一般在什么情况下使用呢？

如何判断提交了所有作业呢？ 都用not exists来实现 这个是重点啊，必须掌握

select sid,name,department from student a
where not exists(select 1 from homework b where b.cid = ‘CS001’
and not exists (select 1 from submission c where a.sid = c.sid and c.cid = b.cid and c.no = b.no)
)  

不存在这门课的作业他没有完成，所以是做了所有的
注意：顺序不能出问题，CS001必须在第一个not exists中，后面的是连接表的

CS001 是条件，必须放在前面一个exists中的

4：已知CS001课程有4次作业，序号从1-4，请统计所有提交过CS001课程作业的学生这4次作业的得分情况，显示列名分别为SID Name HW1,HW2 HW3

HW4
思路：这里用到了子查询操作 这里的序号是No

select SID,Name，
max(case No when 1 then score else null end) HW1,
max(case No when 2 then score else null end) HW2,
max(case No when 3 then score else null end) HW3,
max(case No when 4 then score else null end) HW4,
FROM Submission natural left outer join homework 
where No in(1,2,3,4) and CID=”CS001”
group by SID,Name

5：使用标量子查询完成每个学校的得分前5为的用户编号，用户名，等级分，逆序排序。
这题完全没思路啊？？ 用到分页查询好像 只是针对一个表的Users 注意是User表

select school,uid,name,rating from(
select uid,name,rating,school,
(select count(*)+1 from users b where a.rating < b.rating 
and   a.school = b.school) rk
 from users a
)            这里体现的是标量子查询把？？下面那种应该不符合要求
where a.rk <= 5 
order by rating desc
group by school

这个是重点的啊，标量子查询就是这样用的，原来如此，把这个搞定，就不用怕了的

with ta as(
select school,uid,name,rating from Users group by school   )
select school,uid,name,rating,(select x.*,select(count(*)+1 from ta y 
where x.rating<y.rating) rk from ta  x) where rk<=5;
order by rating desc
group by school    这样操作也是可以的，但是不属于标量子查询

6：统计所有用户，比赛号为1-4比赛的比赛得分，如果没参加比赛，则为空值，UID,NAME,C1,C2,C3,C4,其中，C1 C2 C3 C4依次表示比赛1-4的用户

得分。

select uid,name,
max(case cid when 1 then score else null end) C1,
max(case cid when 2 then score else null end) C2,
max(case cid when 3 then score else null end) C3,
max(case cid when 4 then score else null end) C4
from users natural left outer join take
where cid in (1,2,3,4)
group by uid,name

7：查询Eric老师所教授的Database课程中成绩最好的前10位同学的修课年份，学号，姓名和成绩

with通常与as连用，叫做子查询部分，定义个sql片段，然后该片段会被整个SQL语句所用到

with ta as    问题：with as是什么操作？  ta做为子查询在后面用到
select year,semester,stu_id,name,grade
from section a natural join takes b natural join Student c natural join course d
where a.teacher =’Eric’ and c.title=’Database’

select year,semester,stu_id,name ,grade
from (select x.*,(select count(*)+1 from ta y where x.grade<y.grade) rk
from ta x) where rk<=10;

8：查询修过’Eric’老师教过的所有课程的学生学号和姓名(设计到Student Section Takes表)一样的解题方法

select stu_id,name from student a
where not exists(select 1 from section  b  where b.teacher=’Ecic’ 
and not exists (select 1 from takes c where a.stu_id =c.stu_id  
and b.sec_id=c.sec_id)
)

not exists是在哪里用得较多呢？？

9：求教授过不同课程最少的老师的信息 (问 题：不同课程最少的不知道如何表示)

with ta as(
select tid,name,dept_name,salary,count(distinct cid) num 
from teacher natural left outer join section
group by tid,name,dept_name,salary
)
select tid,name,dept_name,salary 
from ta
where num = (select min(num) from ta) 

10：查询教过所有课程以”CS”开头的课程的教师姓名。（问题：这里的所有课程该如何表示呢？用到exists语法吗？？） 涉及了3个表

select name from Teacher t
where not exists(select 1 from Course c  where  c.name like  'CS%' 
and not exists(select 1 from section b where  C.cid=s.cid and 
s.tid=t.tid )
)

11：查询各院工资收入前3位的老师的名称，工资

with  ta  as
select  name,salary  from  Teacher 
group by dept_name

select name,salary from (select x.*,(select count(*)+1 from ta y 
where x.salary<y.salary) rk from ta x)  where rk <=3;

exists和not exists的区别

1.用关系代数式和SQL语言两种方式表示如下功能的实现:（16分）
②查询学生关系中所有学生的姓名；（问：所有的使用exists来求解的吗？？）

select 姓名 from 学生
where exists(select * from 选课 where 学生.学号=学号)

④查询选修了全部课程的学生的学号；这个是重点啊，知道了exists和not exists的用处和区别

select 学号
from 学生
where not exists(select * from 课程  where not existsi(
            select * from 选课 where 学生.学号=学号 and 课程.课程号=课程号
))