数据库原理与应用--实验三

实验内容：

在SM数据库中进行以下操作：

1. 查询选修了数据库的学生的学号；
2. 查询数据库成绩不及格的学生名单（输出学号、姓名、成绩）；
3. 使用内连接查询每个学生及其选修课程情况；
4. 使用左连接查询查询每个学生及其选修课程情况；
5. 使用右连接查询查询每个学生及其选修课程情况；
6. 在表Teacher中，查询职工张三的上级领导信息；
7. 使用IN查询修读课程名为数据库的所有学生的学号和姓名；
8. 使用EXISTS查询所有选修了001号课程的学生的姓名；
9. 使用NOT EXISTS查询未选修了001号课程的学生的姓名；
10. 使用NOT EXISTS查询选修了课程学分为４的全部课程的学生的姓名；
11. 统计教职工的总人数、最高工资、最少工资、平均工资；
12. 查询选修两门课程以上的学生学号；
13. 查询选修了三门课程的学生学号；
14. 使用分组查询选修了所有课程的学生学号、姓名。
15. 在选修课001中，使用ALL查询比学号00001和学号00004的成绩都低的学生学号；
16. 在选修课001中，使用ANY查询比学号00001或学号00004的成绩低的学生学号；
17. 在选修课001中，查询比平均成绩低的学生的学号；
18. 给出所有课程（不包括001这门课程）都及格的学生的平均成绩，按平均成绩降序排序；
19. 查询既选修了课程001又选修了课程003的学生学号
20. 更新表student中的年龄，年龄为当前年份减去出生年份（提示，使用year()）；
21. 更新表SC中的成绩，把选修了“数据库”的成绩加1；
22. 在表student中删除学号为00001的学生信息；
23. 在表course中删除“数据库”课程的信息；
24. 在表SC中删除男同学的信息；
25. 建立一个系号为03的学生表（学号，姓名），要求把student的系号为03的学生的学号和姓名插入该表（分别使用insert into和select into两种方法创建）
26. 快速删除上题建立的表的数据。
27. 建立一个表结构和course一样的表newcourse，表newcourse中不含任何数据。

源代码：

1.查询选修了数据库的学生的学号：
USE SM;
SELECT DISTINCT SNO
FROM SC,COURSE
WHERE CNAME='数据库'; 
2.查询数据库成绩不及格的学生名单（输出学号、姓名、成绩）：
select distinct student.sno,sname,score
from student,sc
where cno=
	(select cno
	from course
	where cname='数据库') and score<60 
	and sc.sno=student.sno;
3.使用内连接查询每个学生及其选修课程情况：
select *
from student
inner join sc on sc.sno=student.sno;
4.使用左连接查询每个学生及其选修课程情况：
select *
from student
left join sc on sc.sno=student.sno;
5.使用右链接查询每个学生及其选修课程情况：
select *
from student
right join sc on sc.sno=student.sno;
6.在表Teacher中，查询职工张三的上级领导信息:
select t2.tname,t2.tno
from teacher t1,teacher t2
where t1.tname='张三'and t1.mgr=t2.tno;
7.使用IN查询修读课程名为数据库的所有学生的学号和姓名:
select distinct student.sno,sname
from student,sc
where cno in
	(select cno
	from course where cname='数据库')and sc.sno=student.sno;
8.使用EXISTS查询所有选修了001号课程的学生的姓名    
select distinct sname
from Student
where exists
	(select *
	from sc
	where sc.sno=student.sno and sc.cno='001');
9.使用NOT EXISTS查询未选修了001号课程的学生的姓名:
select distinct SName
from Student
where not exists
	(select * 
	from sc 
	where sc.SNo = Student.SNo and sc.CNo = '001');
10.使用NOT EXISTS查询选修了课程学分为４的全部课程的学生的姓名:
select distinct sname
from student 
where not exists
	(select *
	from course
	where course.ccredits=4 and not exists
	(select *
	from sc
	where sc.sno=student.sno and sc.cno=course.cno));
11.统计教职工的总人数、最高工资、最少工资、平均工资；
select count(*)'总人数',max(sal)'最高工资',min(sal)'最低工资',avg(sal)'平均工资'
from teacher;
12.查询选修两门课程以上的学生学号；
select sno
from sc
group by sno having count(*)>1;
13.查询选修了三门课程的学生学号；
select sno
from sc 
group by sno having count(*)=3;
14.使用分组查询选修了所有课程的学生学号、姓名。
select sno,sname
from student
where student.sno in
	(select sc.sno
	from sc 
	group by sc.sno having(count(*)=(select count(*)from course)));
15.在选修课001中，使用ALL查询比学号00001和学号00004的成绩都低的学生学号；
select SNo
from sc
where CNo = 001 and Score < all(select Score from sc where (SNo = 00001 or SNo = 00004) and CNo = 001)
16.在选修课001中，使用ANY查询比学号00001或学号00004的成绩低的学生学号；
select sno
from sc 
where cno=001 and score<any(select score
	from sc
	where(sno=00001 or sno=00004)and
	cno=001)
17.在选修课001中，查询比平均成绩低的学生的学号；
select SNo
from sc
where CNo = 001 and Score < (select avg(Score) from sc where CNo = 001)
18.给出所有课程（不包括001这门课程）都及格的学生的平均成绩，按平均成绩降序排序；
select avg(score)'平均成绩'
from sc
where cno<>001 and sno in
(select sno
from sc
where cno<>001 group by sno having min(score)>=60)
group by sno
order by avg(score)desc
19.查询既选修了课程001又选修了课程003的学生学号
select distinct sno
from sc 
where cno = 001
intersect
select distinct Sno
from sc 
where cno = 003;
20.更新表student中的年龄，年龄为当前年份减去出生年份（提示，使用year()）
update Student
set sage = year(GETDATE())-year(sbir);
select *
from Student;
21.更新表SC中的成绩，把选修了“数据库”的成绩加1
update sc
set Score = Score + 1 
where CNo in(
select CNo
from course
where CName = '数据库'
select *
from sc
where CNo in (
select CNo
from course
where CName = '数据库');
22.在表student中删除学号为00001的学生信息
exec sp_helpconstraint student
alter table sc
drop constraint FK_sc_Student
delete
from student
where SNo = 00001
select *
from student;
23.在表course中删除“数据库”课程的信息
exec sp_helpconstraint course
alter table sc
drop constraint FK__sc__CNo__22AA2996
delete 
from course
where CName = '数据库'
select *
from course;
24.在表SC中删除男同学的信息
delete
from sc
where Sno in(
select SNo
from Student
where student.SSex = '男') 
select *
from sc
25.建立一个系号为03的学生表（学号，姓名），要求把student的系号为03的学生的学号和姓名插入该表（分别使用insert into和select into两种方法创建）
①　Insert into
create table student03(
sno char(5),
sname varchar(8))
insert 
into Student03
select SNo,SName
from student 
where CLno = '03'
select *
from student03;
②　Select into
select SNo,SName
into student04
from Student
where CLno = 03
select *
from student04;
26.快速删除上题建立的表的数据
delete 
from student03
from student04;
27.建立一个表结构和course一样的表newcourse，表newcourse中不含任何数据
select *
into newcourse
from course
where 0 = 1