实验目的：
1.熟练掌握各种连接查询及其连接条件。
2.掌握各种嵌套查询的使用。
3.掌握复杂的集合查询。
内容和主要步骤：
第2题：
1>
select student.sno,sname,ssex,sage,sdept,cno,grade
from a.student,a.sc
where a.student.sno=a.sc.sno
2>
select first.cno,second.cpno
from a.course first,a.course second
where first.cpno=second.cno
3>
select a.student.sno,sname,ssex,sage,sdept,cno,grade
from a.student right outer join a.sc on a.student.sno=sc.sno
4>
select sname,sdept
from a.student
where sno in(select sno from a.sc group by sno having min(grade)<60)
5>
select sname
from a.student
where sno in(select sno from a.sc group by sno having min(grade)>90)
6>
select a.student.sno,sname
from a.student inner join a.sc on a.student.sno=sc.sno
where cno='3' and sc.sno in
(select sno
from a.sc
where cno='2')
7>
select a.student.sno,sname
from a.student
where sname!='刘晨' and sage=
(select sage
from a.student
where sname='刘晨')
8>
select sname,sage
from a.student
where sno in
(select sno
from a.sc
where cno in
(select cno
from a.course
where cname='数据库'))
9>
select student.sno,sname
from a.student
where sdept<>'IS' and
sage<any
(select sage
from a.student
where sdept='IS')
10>
select student.sno,sname
from a.student
where sdept<>'IS' and
sage<all
(select sage
from a.student
where sdept='IS')
11>
select sname
from a.student
where Sno in
(select Sno from a.sc
group by Sno
having count(*) = (select count(*) from a.course ))
12>
select a.student.sno,sname
from a.student
where sdept='IS' and ssex='男'
13>
select *
from a.sc where cno='001'and sno not in(select sno from a.sc where cno='002')
14>
select cno
from a.course
where cno not in
(select cno
from a.sc
where sno in
(select sno
from a.student
where sname='李丽'))
15>
select AVG(sage) as avgsage
from a.student inner join a.sc on student.sno=sc.sno
where cno='3'
16>
select cno,AVG(grade) as avggrade
from a.sc
group by cno
17>
select a.course.cno '课程号', count(sc.sno) '人数'
from a.course,a.sc
where a.course.cno=a.sc.cno
group by a.course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,a.course.cno asc
18>
select sname
from a.student
where sno>
(select sno from a.student where sname='刘晨')and
sage<(select sage from a.student where sname='刘晨')
19>
select sname,sage from a.student
where sage>(select avg(sage) from a.student where ssex='女')
and ssex='男'
20>
select sname,sage
from a.student
where ssex='男'and sage>
(select MAX(sage) from a.student where ssex='女')
21>
select a.Student.Sno,Sname
from a.Student,a.SC
where a.Student.Sno=a.SC.Sno
and Cno =
(select Cno from a.SC
where Sno='95002')
22>
select *
from a.course where cno in (select cno from a.sc where sno='95001')
and cno in (select cno from a.sc where sno='95002')
遇到的问题及体会：
1.头晕眼花，三个实验报告同时交
2.总觉得一直再win+shift+s+ctrl+a+ctrl+c+ctrl+v
3.知识点：
1>求总数可以用COUNT（）函数
2>分组group by 要用having来限制条件
3>order by是排序要求 desc是降序 ，asc是升序
4>any（）函数是任意的意思，all（）是所有
4.建表要合适否则很多查询出来是空的
