--5月7日 SQL Server实验内容
--一、用SQL语句对表中数据进行操作
--1.根据系部表、班级表、学生表、教师表已有数据（如果没有数据，请使用管理平台或者SQL语句先为以上表添加数据），使用SQL语句在课程表course、选课表s_c和授课表t_c三个表中添加适当的数据
insert s_c
values('2016010203','002','80')--刘晓信息
insert s_c
values('2016010101','001','80')
insert s_c
values('2016010102','002','80')
insert t_c
values('0101','001','5')
insert t_c
values('0103','001','6')
insert t_c
values('0104','002','3')
--2.将教师编号为“0001”的教师的姓名修改为“刘清华”，并将所有教师的工资提高500元。
update teacher
set t_name='刘清华'
where t_id='0001'
update teacher
set t_salay=t_salay+500
--3.教师“于林“已经离开学校，需要将他的信息删除
Delete teacher
where t_name='于林';
--4.使用SQL语句将学号为2016010101的学生的姓名修改为“白沧铭”。
update student
set s_name='白沧铭'
where s_id='2016010101'
--5.由于“刘晓”同学已经退学，需要将该同学的选课信息和基本信息删除。
delete s_c
where s_id='2016010203'
delete student
where s_id='2016010203'
--6.将20160101班的学生的成绩如果不足60分，全部提高5分
update s_c
set result=result+5
where s_id in(select c_id from class where c_id ='20160101')
AND result<60
--7.将教师刘清华的工资修改为平均工资
update teacher
set t_salay=(select AVG(t_salay) from teacher)
where t_name='刘清华'
--8.将学号为“2016010101”的同学的“计算机基础”的成绩改为该课的平均成绩
update s_c
set result=(select AVG(result) from s_c where course_id='003' )
where s_id='2016010101'
--9.把成绩低于总平均成绩的女同学的成绩提高5%
update s_c
set result=result*(1+0.05)
where result<all(select AVG(result) from s_c) and s_id in(select s_id from student where s_sex ='女')
--10、学生王丽在0002号课程考试中作弊，该课成绩应作零分计
update s_c
set result=0
where course_id='0002'and s_id=(select s_id from student where s_name='王丽')
--11、把学号为2016010101的学生的性别和系改成与2016010102学号的学生一样
update student
set s_sex=(select s_sex from student where s_id='2016010102')
where s_id ='2016010101'
--11、把学号为2016010101的学生的性别和系改成与2016010102学号的学生一样
update student
set s_sex=(select s_sex from student where s_id='2016010102')
where s_id ='2016010101'
update department
set dep_id=(select dep_id from class,student where class.c_id=student.c_id and s_id='2016010102')
where dep_id=(select dep_id from class,student where class.c_id=student.c_id and s_id='2016010101')
--12、删除没有选课的学生的基本信息
delete from student
where s_id not in(select s_id from s_c)
--13、删除“白沧铭”同学的计算机基础课程的选课记录。
Begin transaction;
delete from s_c
where s_id=(select s_id from student where s_name='白沧铭') and course_id=(select course_id from course where course_name='计算机基础')
Rollback transaction
--二、数据的导入和导出
--1、检索出studentmanager数据库中表student和teacher表的信息并插入表student2和teacher2中（提示：用select……into命令）
select * into student2 from student
select * into teacher2 from teacher
--2.将学生表student中的数据导出为文本文件，文件名为student.txt
--3、将教师表teacher中的数据导出为excel文件，文件名为teacher.xls
--4、将文件teacher.xls中的数据库导入teacher2表中
--5.将文件student.txt中的数据导入student2表中
--6、检索出20160101班学生的学号、课程号和成绩并插入表s_c2中（提示：用insert命令）
insert into s_c2(s_id,course_id,result)--s_c2需存在
select s_id,course_id,result from s_c
where s_id in(select s_id from student where c_id='20160101')
--三、简单查询及排序显示（在studentmanager数据库中完成以下各题目）
--（1）查询讲授了课程的教师的教师号
select t_id
from t_c
--（2）查询每个教师的工龄
select t_name as 姓名, year(getdate())-year(t_entrydate)as 工龄
from teacher
--（3）查询教师所属的部门
select t_name as 姓名, dep_name as 部门
from teacher,department
where teacher.dep_id=department.dep_id
--（4）查询姓名中第2个字为“沧”的学生的个人信息。
select *
from student
where s_name LIKE '_沧%'
--（5）检索全体学生的情况，结果按做在班级升序排序，同一班级的按出生日期升序排列。
select *
from student
order by c_id ASC , s_borndate DESC
--（6）查询0003号课程成绩排在前两名的学生的学号和成绩。
select s_id 学号,result 成绩
from s_c
where course_id='0003'and s_id in (select top 2 s_id from s_c where course_id='0003' order by result DESC)
--（7）查询年龄在前5位的教师的信息。
select top 5 * from teacher order by year(getdate())-year(t_borndate) DESC
--四、集合运算
--（1）查询所有副教授和教授的信息（要求用union，不要用or）
select *
from teacher
where t_professor='副教授'
union
select *
from teacher
where t_professor='教授'
--（2）对student表使用交intersect和差except运算，并分析查询结果。
--查询班级号为20160101与自年龄不大于19的学生信息交集
select *
from student
where c_id='20160101'
intersect
select *
from student
where s_id in(select s_id from student where (year(getdate())-year(s_borndate))<=22)
--查询班级号为20160101与自年龄不大于19的学生信息差集
select *
from student
where c_id='20160101'
except
select *
from student
where s_id in(select s_id from student where (year(getdate())-year(s_borndate))<=22)
--五、使用分组和汇总检索数据
--（1）查询学生总数
select COUNT(*) 学生总数
from student
--（2）检索每个学生的总成绩
select s_id as 学号,SUM(result)as 总成绩
from s_c
GROUP BY s_id
--（3）查询选修课课程的学生的人数
select COUNT(DISTINCT s_id) as 总人数
from s_c
--（4）查询选修0001号课程的学生的平均成绩
select AVG(result)平均成绩
from s_c
where course_id='0001'
--（5）检索每门课程的平均分
select course_id 课程号,AVG(result)平均分
from s_c
GROUP BY course_id
--（6）检索0002号课程的最高成绩、最低成绩和平均成绩
select MAX(result)最高成绩,MIN(result)最低成绩,AVG(result)平均分
from s_c
where course_id='0002'
--（7）检索男女生人数
select s_sex 性别,COUNT(*) 总人数
from student
GROUP BY s_sex
--（8）检索平均分大于85分的课程的课程号和平均分
select course_id 课程号,AVG(result) 平均分
from s_c
GROUP BY course_id
HAVING AVG(result)>85
--（9）统计各班级人数
select c_id 班级,COUNT(DISTINCT c_id)人数
from student
where c_id is not NULL
GROUP BY c_id
--（10）统计学生表student中20160101班的男女生人数
select s_sex 性别,COUNT(*) 人数
from student
where c_id='20160101'
GROUP BY s_sex
--（11）统计各部门的教师总人数
select dep_id 系部,COUNT(*) 人数
from teacher
GROUP BY dep_id
--（12）统计各门课程的选课人数
select course_id 课程号,COUNT(*) 人数
from s_c
GROUP BY course_id
--（13）统计选修了2门以上课程的学生的学号和课程门数
select s_id 学号,COUNT(*) 课程门数
from s_c
GROUP BY s_id
having COUNT(*)>=2
--（14）在teacher表上验证count（*）、count（sal）、count（distinct t_professor）的使用区别
select COUNT(*)
from teacher
select COUNT(t_salary)--提示sal列明无效
from teacher
select COUNT(distinct t_professor)--根据t_professor去掉了重复值
from teacher
--六、多表数据的检索（用连接查询）
--（1）检索学生的学号、姓名和所在班级名称
select s_id,s_name,c_name
from student,class
where student.c_id=class.c_id
--（2）检索选修“数据库原理与应用”课程的学生的姓名和成绩
select s_name,result
from student,s_c
where student.s_id=s_c.s_id and course_id=(select course_id from course where course_name='数据库原理与应用')
或
select s_name,result
from student,s_c,course
where student.s_id=s_c.s_id and s_c.course_id=course.course_id and course_name='数据库原理与应用'
--（3）查询每门课程的课程号、任课教师姓名及其选课人数
select s_c.course_id 课程号,t_name 教师姓名,COUNT(*)选课人数
from teacher,t_c,s_c
where teacher.t_id=t_c.t_id and t_c.course_id=s_c.course_id
GROUP BY s_c.course_id,t_name
--（4）检索与“王丽”在同一班级的其他学生的姓名
select b.s_name
from student a,student b
where a.c_id=b.c_id and a.s_name='王丽'and b.s_name<>'王丽'
--（5）查询“大学英语”，成绩在85分以上的学生的学号和姓名
select student.s_id,s_name,result
from student,s_c,course
where student.s_id=s_c.s_id and s_c.course_id=course.course_id and course_name='大学英语' and result>85
--（6）查询讲授“大学英语”的教师的姓名和所在部门名-----------交叉链接
select t_name,dep_name
from department,teacher,t_c
where department.dep_id=teacher.dep_id and teacher.t_id=t_c.t_id and course_id='大学英语'
--（7）查询与“刘清华”在同一部门的其他教师的姓名
select b.t_name
from teacher a,teacher b
where a.t_id=b.t_id and a.t_name='刘清华'and b.t_name<>'刘清华'
--七、子查询（嵌套查询）
--（1）查询选修0003号课程且成绩低于该门课程平均分的学生的学号
select s_id
from s_c
where course_id='0003'and result<(select AVG(result)from s_c where course_id='0003')
--（2）查询年龄高于20160101班所有学生年龄的其他班的学生的学号和姓名
select s_id,s_name
from student
where c_id <>'20160101' and
s_borndate <all(select s_borndate from student where c_id='20160101')
--（3）查询所有成绩大于90分的学生的姓名
select s_name
from student
where s_id in(select s_id from s_c where result>90)
--（4）查询没有选修大学英语的学生的学号和姓名
select s_id,s_name
from student
where s_id not in (select s_id from s_c where course_id=(select course_id from course where course_name='大学英语'))
--（5）查询成绩高于平均分的学生的学号和课程号
select s_id,course_id
from s_c
where result >all(select AVG(result) from s_c)
--（6）查询未选修任何课程的学生的学号和姓名
select s_id,s_name
from student
where not EXISTS (select * from s_c where s_c.s_id=student.s_id)
--（7）查询所有工资高于平均工资的教师的教师名
select t_name
from teacher
where t_salary>(select AVG(t_salary)from teacher)
--（8）查询年龄比“白沧铭”小的学生的姓名，结果按学号降序排序udsfdvq c
select s_name
from student
where (select year(getdate())-year(s_borndate) from student where s_name='白沧铭')<all(select year(getdate())-year(s_borndate))
--（9）查询出生日期大于所有女同学的出生日期的男同学的姓名