[大数据]数据库实验四

用Select语句检索下列问题，写成正确的Select语句并对检索结果进行截图（为便于实验结果的多样性，大家可以在STC的各个表中自行增加元组）。截图要求：包含SQL Server Management Studio的上菜单、右侧数据库菜单、查询编辑界面和结果显示，并要求结果的列名显示必须是无重复的、有意义的列名。

1检索有薪水差额的任意两位教师的姓名及二者的工资差额；

select distinct T1.Tname,T2.Tname,T1.Salary-T2.Salary as'差额'
from Teacher as T1,Teacher as T2
where T1.Salary>T2.Salary

distinct //防止结果重复

Teacher as T1? ，Teacher as T2? ? ? //创建分表

2检索选修了001号课程的学生的学号和姓名；

select Student.S#,Sname
from Student,SC
where Student.S#=SC.S# and C#='001'

Student.S#? ? ? ? //?两个表都有同一域名时，要表明找的是那个表里的域名

Student.S#=SC.S#? ? ? ? ?// 链接两个表

3检索同时选修‘001’号课程的任意两位同学的学号、各自成绩和成绩差；

select S1.S#,S2.S#,S1.Score,S2.Score,S1.Score-S2.Score 
from SC as S1,SC as S2
where S1.S#=S2.S# and S1.C#='001' and S1.Score>S2.Score and S2.C#='001'  

Select Sname as '列明'? ? ? ? //自定义列明（域名）

4检索有两门及以上不及格课程同学的学号、姓名及该同学的平均成绩；

select Student.S#,Student.Sname,AVG(Score)
from Student,SC
where Student.S#=SC.S# and Score<60
group by Student.Sname,Student.S#
having COUNT(*)>1

group by? ? ? ? //将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中;

having COUNT(*)>1? ? ? ? //结果数量大于1

5当某同学001号课的成绩高于该课程平均成绩时，将该同学该门课成绩提高5%。

use SCT
update SC
set Score=Score*1.05
where C#='001' and Score>all(select AVG(Score)from SC where C#='001')

updata? ? ? ? //更新数据

Score>all(...)? ? ? ? //子句查询,

6找出001号课成绩不是最高的所有学生的学号、姓名和001号课成绩。

select Student.S#,Sname,Score
from Student,SC
where C#='001' and Student.S#=SC.S# and Score<some
(select Score from SC where C#='001')

>=all? ? 求最大? ? <=all? ?求最小? ? ?<same? 除了最大的? ? ? >same? ? ? 除了最小的

??7找出“全红婵”同学成绩最低的课程号、课程名及该课程成绩

select SC.C#,Course.Cname,Score
from Student,SC,Course
where  Student.S#=SC.S# and Sname='全红婵' and SC.C#=Course.C# 
and Score<=all (select Score from SC,Student 
where Student.S#=SC.S# and Sname='全红婵')

子查询锁定’全红婵‘的最低成绩，父查询锁定全红婵这门课

8列出选修了98030101号同学学过的所有课程的同学的学号和姓名；

 use SCT
select distinct Student.S#,Sname
from Student,SC
where C# in
(select C# from SC,Student where Student.S#='98030101')

9列出没学过“张五”老师讲授任何一门课程的所有同学的姓名；

select distinct Sname
from Student,SC
where Student.S#=SC.S# and C# not in
(select C# from Course,Teacher where Teacher.T#='张三')

not in(...)? ? ? ? //子查询，不在结果之中的

10新建Table: SCt(S#, C#, Score), 将检索到的成绩不及格同学的记录新增到该表中；

use SCT
create table SCt( S# char(20),C# char(20),Score char(20));use SCT
insert into SCt(S#,C#,Score)
select S#,C#,Score from SC
where Score<60

insert into? ? ? ? //新增元素，

11从SCt表中删除有两门不及格课程的所有同学；

delete from SCt 
where S# in(select S# from SCt 
where Score<60
group by S# 
having COUNT(*)=2)

delete? ? ? ? //删除元素

12找出001号课成绩最高的学生的学号、姓名和该同学的平均成绩；

use SCT
select  Student.S#,Sname,AVG(Score)'平均成绩'
from Student,SC where Student.S#=SC.S# and Student.S# in
(	select Student.S# from Student,SC
where Student.S#=SC.S# and Score>=all(select Score from SC where C#='001')
group by Student.S#,SC.S#,Student.Sname)
group by Student.S#,Student.Sname

在子查询中在嵌套一个子查询，子子查询锁定001最高成绩，子查询锁定这个人学号，父查询找出这个人的平均成绩

13将“马龙”同学001号课的成绩置为“该门课的平均成绩。我不理解。”