《数据的查询操作》

《数据的查询操作》
第八次作业1
1．检索年龄大于18岁的女学生的学号和姓名(注意：要给列起别名)；
use学生课程数据库
select sno,sname
from student
where sage>18 and ssex='女'
2.检索所有男学生选修的课程号和课程名称（仅在结果中列出10%的数据行）。

use学生课程数据库
select top(10)percent o,cname
from student,sc,course
where student.sno=sc.sno and o=o and ssex='男'
3.查询年龄大于18，并不是信息管理也不是计算机科学的学生的姓名和性别（复合查询）；
select sname,ssex
from student
where sage>18 and sdept not in('信息管理','计算机科学')
4..查询以“DB_”开头，和倒数第二个汉字为“导”字的课程信息（模糊查询）；
select*
from course
where cname like'DB_%'or cname like'%导_'
5.查询student表和sc表的交叉连接；
select*
from student cross join sc
6.检索王同学不学的课程的课程号和课程名称；
select cno,cname
from course
where cno not in(select cno from sc where sno in(select sno
from 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 o=o))
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 ssex='女')and ssex='男'
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<18
union
select*
from student
where sage>20
14.查询即选修了‘1001’号课程有选修了‘1003’号课程的学生的学号（要求用用交运
算实现intersect）；
select sno
from sc
where cno='1001'
intersect
select sno
from sc
where cno='1003'
15.查询选修了‘1002’号课程，但不是工业工程系的学生的学号（要求用差运算实现except）
select sno
from sc
where cno=1002
except
select sno
from student
where sdept!='工业工程'
16.创建一个公用表，该表由课程号、人数和最高分组成，用于存放各门课程的课程号、选修该课的人数及该门课的最高分数，查询该公用表，并按照课程号排序。

WITH gongyongbiao(cno,amount,maxgrade)
AS(SELECT o,COUNT(*),MAX(grade)
FROM course,sc
WHERE o IS NOT NULL
GROUP BY o)
SELECT cno,amount,maxgrade
FROM gongyongbiao
第八次作业2
1.在学生信息表中进行自联接查询，并在连接时使用内链接，返回籍贯是“河南”或者“北
京”的学生学号、姓名、班级编号及籍贯信息。

select student.sno,sname,class,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.ssex,a.jiguan,b.class,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.class,o,b.grade
From 学生信息a inner join 成绩表b
On a.学号=b.学号
Where grade>any(select avg(grade) from 成绩表)
5.查询学生姓名为“李文东”所在班级的学生信息，要求返回学生的学号、姓名、性别、
班级编号、班级名称和年级信息
Select a.sno,a.sname,a.ssex,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。