SQL查询疑难整理

• 利用Teach数据库中的现有的表生成新表S_avg，新表中包括学号、姓名和平均成绩，其中平均成绩保留一位小数

create table S_avg as
select students.sno, Sname, round(avg(Score), 1) Avgscore
from student, sc
where student.sno = sc.sno and score is not null
group by student.sno, sname
order by student.sno

• 查询至少讲授两门课程的教师编号，结果按教师编号的升序排序

select distinct tc1.tno 
from tc tc1
inner join tc tc2 on tc1.tno = tc2.tno
where tc1.tno != tc2.tno

• 查询每位教室的授课门数，结果包含教室编号和授课门数，并按教师编号升序排列

select tno, count(cno)
from tc
group by tno

gruop by :按指定列分组（个人理解：把聚合函数的对象的结果，通过group by来指定所要进行聚合的列）

• 查询网工专业的班级所开设的所有课程的课程号及开课学期，将查询结果按课程号升序排序，如果课程号相同，再按开课学期降序排序

select cno, semester
from tc
where classname like '%网工'
order by cno, semester desc

• 查询讲授全部课程的教师的教师编号

select tno
from teacher where not exists (
    select * from course where not exists
    (
        select * from tc where cno = course.cno and tno = teacher.tno
    )
)

”全部“量词总结：大多都是用两个嵌套的not exists，根据exists的查询机制：是先得到它所属的查询的第一条记录带到括号里。所以，

先找所要查询的属性是哪一个表的主属性，得到记录后，然后再用not exists来得到另一个关键字所在的以它为主属性的表，然后讲两个字段带入该exists里去与两个属性同时存在的一张表或者两个属性相关的那张表或者与该查询询问语义相符的那张表进行匹配

全部：某一个学生，没有一门课是他不选的

或者还能解释一下那个语句：教授关系的表中，固定一个老师，看看是不是所有的课他抖教

注意，顺序不能换，语义就变了，如果查询的内外关系换就成了是问：是不是每一个课程都有老师教

• 查询刑林老师不教的课程的课程号

select cno from course
where cno not in
(
    select cno from tc where tno = 
    (
        select tno from teacher where tname = '刑林'
    )
)

• 查询讲授全部学分是4分课程的教师的教师编号和教师姓名

select tno, tname
from teacher
where not exists
(
    select *
    from course
    where credit = 4 and not exists
    (
        select *
        from tc
        where course.cno = cno and teacher.tno = tno
    )
)

• 查询至少讲授T5讲师所讲授的全部课程的教师的教师编号

select tno
from teacher
where tno != 'T5' and not exists
(
    select * from tc  tc2
    where tc2.tno = 'T5' and not exists
    (
        select * from tc tc3
        where tc3.tno = teacher.tno and tc3.cno = tc2.cno
    )
)

删除属性用delete

• 在任课表的任课班级和开课学期两列上创建复合索引IDC_Classname_Semester，按开课学期的升序排列，开课学期相同时按任课班级的降序排列

create index IDC_Classname_Semester on tc(Semeterm, Classname desc)