第三次数据库实验(自用)

为契合表中数据，部分题目已稍作更改

/*1、嵌套查询 
在学生选课库中实现嵌套查询操作。
1) 查询选修了 GO 课程的学生学号和姓名。
2) 查询比王华同学年龄大的学生学号和姓名。
3) 查询选修 18084412 课程的成绩低于张三的学生学号和成绩。
4) 查询其他学院中比数计学院学生年龄都小的学生。
5) 查询选修了 18084415 课程的学生姓名。
6) 查询选修了全部课程的学生姓名。
7) 查询至少选修了“2100001”学生所选修课程中一门课程的学生学号的姓名。
8) 查询至少选修了“2100001”学生所选修的全部课程的学生学号的姓名（查询选修课程
包含 “2100001”学生所选课程的学生学号和姓名）。
9）查询既选修了“数据结构”课程又选修了“数据库原理与应用”课程的学生姓名。
10）查询选修了“数据结构”课程或选修了“数据库原理与应用”课程的学生学号。
11）查询选修了“数据结构”课程而没有选修“数据库原理与应用”课程的学生学号。
12）查询全是男同学选修的课程号。
9）-11）任选一个，12）选做
*/

--1) 查询选修了 GO 课程的学生学号和姓名。
select s.sno,sname from student s 
left join SC sc on s.sno=sc.sno where sc.cno =(
select cno from Course where cname='GO');

--2) 查询比王华同学年龄大的学生学号和姓名。
select sno,sname,s1.sage from student s1 ,(select sage from student s2 where sname='王华') s3
where s1.sage > s3.sage;

--3) 查询选修 18083410 课程的成绩低于张亮的学生学号和成绩。
select sc1.sno,sc1.grade from SC sc1 ,
(select sc2.grade from SC sc2 
left join student s on sc2.sno=s.sno where s.sname='张亮') sc3 
where sc1.grade<sc3.grade and cno='18083410' ;

--4) 查询其他学院中比数计学院学生年龄都小的学生。
select * from student where sage < (select min(sage) from student where sdept='数计') and sdept <> '数计';

--5) 查询选修了 2 课程的学生姓名。
select sname from student where exists (select * from SC where student.sno=sno and cno='2');

--6) 查询选修了全部课程的学生姓名。
select sname from student where not exists(select * from Course where not exists(select * from SC where student.sno=sno and Course.Cno=cno))


--7) 查询至少选修了“2100001”学生所选修课程中一门课程的学生学号的姓名。
select
    stu.sno,stu.sname from student stu
where exists (
    select null 
    from sc 
    where sc.sno = stu.sno and sc.cno = '2100001'
) and exists (
    select null 
    from sc 
    where sc.sno = stu.sno and sc.cno != '2100001'
);

--8) 查询至少选修了“2100001”学生所选修的全部课程的学生学号的姓名（查询选修课程包含 “2100001”学生所选课程的学生学号和姓名）。
select sno,sname from student where not exists(
select * from SC a where a.sno=2100001 and not exists(
select * from SC b where student.sno=b.Sno and b.Cno=a.Cno))

--9）查询既选修了“大学物理”课程又选修了“环境”课程的学生姓名。
select  sname from student where sno in(
select distinct sno from sc where  sno in(
 select sno from sc where  cno=(select cno from course where cname='大学物理' )
 and  sno in (select sno  from sc where cno in (select  cno from course  where  cname='环境'))
 ));

 /*2、组合查询和统计查询 
在学生选课库中实现组合和统计查询操作。
1）使用集合运算查询既选修了“数据结构”课程又选修了“数据库原理与应用”课程的学生姓名。
2）使用集合运算查询选修了“数据结构”课程或选修了“数据库原理与应用”课程的学生学号。
3）使用集合运算查询选修了“数据结构”课程而没有选修了“数据库原理与应用”课程的学生学号。
4）统计选修了课程的学生人数。
5）查询选修成绩合格，并且选课门次超过 4 门以上学生的学生学号、总成绩。
6）统计各院系的学生人数。
7）统计各年龄的学生人数。
8）统计每个学生的选修课程数目和平均成绩。
9）查询每门课程的详细信息及选课人数。
*/

--1）使用集合运算查询既选修了“大学物理”课程又选修了“环境”课程的学生姓名。
select sname from student 
where sno in (
select sno from SC where cno = ( 
select cno from course where cname = '大学物理') 
union 
select sno from SC where cno = (select cno from Course 
where cname = '环境'));

--2）使用集合运算查询选修了“大学物理”课程或选修了“环境”课程的学生学号。
select sname from student 
where sno in( 
select sno from SC where cno =(
select cno from course where cname ='大学物理')
intersect 
select sno from SC where cno = (
select cno from course where cname ='环境'));

--3）使用集合运算查询选修了“大学物理”课程而没有选修了“数据库”课程的学生学号。
select sname from student 
where sno in ( 
select sno from SC where cno =( 
select cno from course where cname='大学物理')
except 
select sno from SC where cno = (
select cno from course where cname='数据库'));

--4）统计选修了课程的学生人数。
select count (distinct(sname)) as count 
from student s left join SC sc 
on s.sno=sc.sno where cno is not null;

--5）查询选修成绩合格，并且选课门次超过 4 门以上学生的学生学号、总成绩。
select sno,sum(grade) as '总成绩' 
from SC where grade >=60 
group by sno 
having count(cno)>4;

--6）统计各院系的学生人数。
go
select sdept,count( sdept) as sumdept
from student
group by sdept;

--7）统计各年龄的学生人数。
go 
select sage,count(sage) as sumage 
from student group by sage;

--8）统计每个学生的选修课程数目和平均成绩。
go
select sno,count(cno) as 'ccourse',avg(grade) as 'avggrade'
from SC
group by sno;

--9）查询每门课程的详细信息及选课人数。
go 
select Course.*,countstudent 
from Course left join (select cno,count(*) as countstudent 
from SC group by cno) as c on c.cno=Course.cno;