数据库实验三数据库的组合查询和统计查询实验、嵌套查询实验

–(1)求选修了高等数学的学生学号和姓名。

select s.sno,s.sname from student s,sc,course c where s.sno=sc.sno and sc.cno=c.cno and cname='高等数学';

– （2）求C1课程的成绩高于李浩明的学生学号和成绩。

 select s.sno,grade from student s,sc where s.sno=sc.sno  and cno='C1'
 and sc.grade >ANY
 (select grade from student s,sc where s.sno=sc.sno and cno='C1' and sname='李浩明') ;

– （3）求其他系中年龄小于计算机系年龄最大者的学生。

  select * from student where sdept != 'CS'  and sage< ANY
 (select sage from student where sdept='CS') ;

– （4）求其他系中比计算机系学生年龄都小的学生。

select * from student where sdept != 'CS'  and sage< ALL
 (select sage from student where sdept='CS') ;

– （5）求选修了C1课程的学生姓名。

select sname from student s,sc where s.sno=sc.sno and 
	cno='C1';

– （6）求没有选修C1课程的学生姓名。

select sname from student s where not exists(
select  * from  sc where sc.sno=s.sno and 
	cno='C1');

或

select sname from student student where sname not in
(select sname from student s,sc where s.sno=sc.sno and 
	cno='C1');

–(7)查询选修了全部课程的学生的姓名。

select sname from student s
where not exists ( 
select * from course c
where not exists (
select * from sc 
where s.sno=sc.sno and sc.cno=c.cno))；

–（8）求选修了学号为“201815122”的学生所选修的全部课程的学生学号和姓名。

select sname,sno
from student a
where sno <> '201815122' and not exists ( 
select *
from sc b
where sno='201815122' and not exists (
select *
from sc c
where a.sno=c.sno and c.cno=b.cno))

– (9）求学生的总人数.

select count(sno) from student ;

– (10)求选修课程的学生人数.

select count(distinct sno) from sc ；

–(11)求课程和选修课程的人数.

select cno, count(distinct sno) from sc 
group by cno;

– (12)求选修课的学生学号

select distinct sno from sc;

–2、用Transact-SQL语句表示，在学生选课库中实现其统计和组合查询操作。
–(1)查询选修“计算机基础”课程的学生成绩比此学科的平均成绩大的学生学号和成绩。

select sno,grade from sc ,course c where c.cno=sc.cno and cname='计算机基础' and grade>
(select avg(grade) from sc,course c where c.cno=sc.cno and cname='计算机基础');

–（2）查询选修“计算机基础”课程的学生平均成绩。

select sno, avg(grade) from sc where sno in(
select sno from sc,course c where sc.cno=c.cno and cname ='计算机基础')
group by sno;

–（3）查询年龄大于女学生平均年龄的男学生的姓名和年龄。

select sname,sage from student where ssex='男' and sage>
(select avg(sage) from student where ssex='女')

–（4）列出各系学生的总人数，并按人数进行降序排序。

select sdept,count(sno) from student group by sdept order by count(sno) desc;

–（5）统计各系各门课程的平均成绩。

select sdept,cno,avg(grade) from student s,sc where s.sno=sc.sno 
	group by sdept,cno ;

–（6）查询选修计算机基础和离散数学的学生学号和平均成绩。

select sno, avg(grade) from sc,course c where sno in(

select sno from sc where cno ='C2'
intersect
select sno from sc where cno ='C4'
) and sc.cno=c.cno and cname in('计算机基础','离散数学')
group by sno;