第八次作业1
1．检索年龄大于18岁的女学生的学号和姓名(注意：要给列起别名)；use 学生课程数据库
select sno ,sname
from studentwhere sage>18 and ex=\'女\'
2.检索所有男学生选修的课程号和课程名称（仅在结果中列出10%的数据行）。use 学生课程数据库
select top(10)percent course.cno,cname
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and ex=\'男\'
3.查询年龄大于18，并不是信息管理也不是计算机科学的学生的姓名和性别（复合查询）；
select sname,ex
from studentwhere sage>18 and sdept not in (\'信息管理\',\'计算机科学\')
4..查询以“DB_”开头，和倒数第二个汉字为“导”字的课程信息（模糊查询）；select *
from course
where cname like \'DB_%\' or cname like \'%导_\'
5.查询student表和sc表的交叉连接；
select *
from student cro join sc
6.检索王同学不学的课程的课程号和课程名称；
select cno,cname
from course
where cno not in (select cno from sc where sno in (select snofrom student
where sname=\'王%\'))
7.检索全部学生都选修的课程号和课程名称；
SELECT cno,cname
FROM course
WHERE NOT EXISTS
(SELECT *FROM student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SC.Sno= Student.Sno AND SC.Cno= Course.Cno))
8.检索所有课程的成绩均高于90分的学生的姓名和年龄；
select sname, sage
from student
where sno in
(select sno
from sc
group by sno
having MIN(grade)>90)
go
9.求刘老师所授的每门课程的平均成绩；
select cno,AVG(grade)
from sc
where cno in (select cno from course where cname in (select job from
teacher
where ename like \'刘%\'))
Group by cno
10.求年龄大于所有女同学的男学生的姓名和年龄
select sname,sage
from student
where sage> all(select sage from student where ex=\'女\')and ex=\'男\'
11.求每个学生的总分，要求输出学号和总分数（要求依然保持学生原有的明细分数，并按学号分组汇总）
select sno,grade
from sc
order by sno
COMPUTE SUM(grade) by sno
12.查询各不同平均成绩所对应的学生人数（要求显示各组的单项人数）
select avgr,COUNT(*)
from (select sno,AVG(grade)from sc
group by sno)as sg(sno,avgr)GROUP BY avgr
13.查询年龄小于18岁和大于20岁的学生的所有信息（要求用并运算实现union）；select *
from student
where sage
union
select *
from student
where sage>20
14.查询即选修了‘1001’号课程有选修了‘1003’号课程的学生的学号（要求用用交运算实现intersect）；
select sno
from sc
where cno=\'1001\'
intersect
select sno
from scwhere cno=\'1003\'
15.查询选修了‘1002’号课程，但不是工业工程系的学生的学号（要求用差运算实现except）
select sno
from sc
where cno=1002
exceptselect sno
from student
where sdept!=\'工业工程\'
16.创建一个公用表，该表由课程号、人数和最高分组成，用于存放各门课程的课程号、选修该课的人数及该门课的最高分数，查询该公用表，并按照课程号排序。
WITH gongyongbiao(cno,amount,maxgrade)
AS(SELECT sc.cno, COUNT(*), MAX(grade)
FROM course,sc
WHERE sc.cno IS NOT NULL
GROUP BY sc.cno)
SELECT cno,amount,maxgrade
FROM gongyongbiao
第八次作业
21.在学生信息表中进行自联接查询，并在连接时使用内链接，返回籍贯是“河南”或者“北
京”的学生学号、姓名、班级编号及籍贯信息。
select student.sno,sname,cla,jiguan
from student
where jiguan=‘河南’or jiguan=‘北京’
2.查询人数超过45人所在班级中的学生的学号、姓名和班级名称。
Select 学号，姓名，班级名称
From 学生信息a，班级信息b
Where a.班级编号=b.班级编号 and a.班级编号 in（select 班级编号 from 班级信息 where 人数>45）
3.对于成绩大于等于90的数据进行查询，并返回考试成绩的学生的学号、姓名、性别、籍
贯和所在班级名称及年级信息。(用EXISTS编写语句)
Select a.sno,a.sname,a.ex,a.jiguan,b.cla,b.grade
From 学生信息a inner join 班级信息b
On a.班级编号=b.班级编号
Where exists(select* from 成绩表 where 学号=a.学号 and grade>=90)
4.查询出任意一个大于平均成绩的学生的学号、姓名、班级编号、课程编号和成绩信息。
Select a.sno,a.sname,a.cla,b.cno,b.grade
From 学生信息a inner join 成绩表b
On a.学号=b.学号
Where grade>any(select avg(grade) from 成绩表)
5.查询学生姓名为“李文东”所在班级的学生信息，要求返回学生的学号、姓名、性别、
班级编号、班级名称和年级信息
Select a.sno,a.sname,a.ex,b.banjibianhao,b.banjimingcheng,b.nianjixinxi From 学生信息a inner join 班级信息b on a.班级编号=b.班级编号
Where a.班级编号=(select 班级编号 from 学生信息 where sname=‘李文东’)
6.查询出所有非计算机系的学生信息，并显示出这些学生的考试成绩，成绩按降序排列显示，要求返回学生的学号、姓名、班级编号、课程编号和成绩信息。
Select a.学号，a.姓名，a.班级编号，b.课程编号，b.成绩
From 学生信息a inner join 成绩表b
On a.学号=b.学号
Where a.学号 not in（select 学号 from 学生信息 where 班级编号 in
（select 班级编号 from 课程信息 where 开课系别=‘计算机系’））
Order by 成绩 desc
或
Select a.学号，a.姓名，a.班级编号，b.课程编号，b.成绩
From 学生信息a inner join 成绩表b
On a.学号=b.学号
Where a.班级编号 not in（
（select 班级编号 from 课程信息 where 开课系别=‘计算机系’））Order by 成绩 desc
