SQL SERVER实验-- 数据检索

实验3  数据检索

一、实验目的

（1）理解T-SQL语言的使用。

（2）熟练掌握数据查询语句。

SELECT [ALL|DISTINCT] {*|<表达式>,…,<表达式>}

FROM <表名>[, <表名>…]

[WHERE <条件>]

[GROUP BY <列名>[, <列名>…][HAVING <谓词>]]

[ORDER BY <列名> [ASC|DESC] [, <列名> ASC|DESC]…]

[COMPUTE …]

二、实验内容

1、 [3.2]b USE CAP
select aid from AGENTS where [percent]=(select max([percent] )from AGENTS
[3.5] Select cid,aid from customers c, agents a  Where cid not in (select cid from orders x
Where x.cid = c.cid and x.aid = a.aid)  And
aid not in (select aid from orders v  Where v.aid = a.aid and v.cid = c.cid)
[3.8]a   select cid,max(dollars) as MAXSPEND from orders group by cid
[3.8]b   select avg(MAXSPEND) average,cid from  (select cid,max(dollars) as MAXSPEND from orders group by cid)as S group by cid
[3.11]b  Select avg(temp.maxspend)avgspend,temp.cid
from (select cid,max(dollars) as maxspend from orders  
Group by cid) as temp group by temp.cid
[3.11]f  select pid from products p
where not exists(select cid from customers c where city='Duluth'
and not exists (select * from orders o where o.cid=c.cid and o.pid=p.pid))
[3.11]j  update products
set price=price*1.1 where city='Duluth' or city='Dallas'
[3.11]l  select aid,[percent] from agents a where not exists(select * from customers c
where city='Duluth' and not exists
(select * from orders o where o.cid=c.cid  and o.aid=a.aid)) order by [percent]DESC

<选做>[3.15]、[3.16]、[3.17]

2、在“学生管理数据库”中建立四张表，分别如下：

学生表：（主键：学号）

课程表：（主键：课程号）

成绩表：（主键：学号、课程号）

授课表：（主键：课程号、班级名）

（1）列出软件2班女同学的名单

select * from 学生表 where 性别='女'  and 班级='软件2班'
（2）列出2002年元旦前注册的学生名单
select 姓名 ,入学日期 from 学生表  where 入学日期 < ' 2002-1-1 ';
（3）列出所有年龄在19岁以下，或者女同学的名单

select 姓名,性别,年龄  学生表 where 年龄<19 or  性别='女'
（4）列出没有先修课的课程名
select 课程名,先修课 from 课程表  where 先修课 is null
（5）列出既不是“电子系”，也不是“会计系”的学生的学号、姓名和所在院系

select 学号,姓名,所在院系 from 学生表
where 所在院系<>'会计学' and 所在院系<>'计算机'
（6）查询选修“C 801 ” 课程的学生的最高分

select 课程号,max(成绩) 最高分 from 成绩表  where 课程号='C801' group by 课程号
（7）统计男、女学生各自的人数和平均年龄

select 性别,count(学生表.学号) 个数,avg(年龄) 平均年龄 
from 学生表,成绩表 group by 性别
（8）列出选修了一门以上课程的学生学号，及其选修门数

select C1.学号,count(C1.课程号)选课门数 from 成绩表 C1,成绩表 C2
where C1.学号=C2.学号 AND C1.课程号<>C2.课程号  GROUP BY C1.学号
（9）查询“沈香娜”同学所在班级的所有学生的学号和姓名

select 学号,姓名 ,班级 from 学生表 
where 班级 in(select 班级 from 学生表 where 姓名='沈香娜')
（10）统计每一年龄选修课程的学生人数

SELECT 年龄,COUNT(课程号) 选课门数 FROM 学生表,成绩表
WHERE 学生表.学号=成绩表.学号  GROUP BY 年龄

难题：

1)        在基本表“成绩表”中检索平均成绩最高的学生学号

select C.学号 from(select avg(成绩) 平均分,学号 from 成绩表 group by 学号) C 
where C.平均分>=all(select 平均分 from
(select avg(成绩) 平均分,学号 from 成绩表 group by 学号) C  )

2)        求表S中男同学的每一年龄组（超过2人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。select 年龄 as 男生年龄,count(年龄) 个数 from 学生表 where 性别='男'
group by 年龄 having count(年龄)>=2

3)        列出选修了全部课程的学生学号和姓名 select s.学号,s.姓名 from 学生表  where not exists (select * from 课程表 where not exists (select * from 成绩表 a where a.学号 = s.学号 and a.课程号 = c.课程号));

4)        查询这样的学生，该生至少选修了学生20026001所选修的全部课程
select x.* from 成绩表 x, 成绩表  where x.课程号=y.课程号 and y.学号='20026001' and X.学号<>' 20026001’

四、实验结果

3.2
USE CAP
select aid from AGENTS where [percent]=(select max([percent] )from AGENTS)

3.5
Select cid,aid from customers c, agents a  Where cid not in (select cid from orders x
Where x.cid = c.cid and x.aid = a.aid)  And
aid not in (select aid from orders v  Where v.aid = a.aid and v.cid = c.cid)

3.8 (a)  select cid,max(dollars) as MAXSPEND from orders group by cid

(b) select avg(MAXSPEND) average,cid from  (select cid,max(dollars) as MAXSPEND from orders group by cid)as S group by cid

3.11(b) Select avg(temp.maxspend)avgspend,temp.cid
from (select cid,max(dollars) as maxspend from orders
Group by cid) as temp group by temp.cid

(f) select pid from products p
where not exists(select cid from customers c where city=' Duluth '
and not exists  (select * from orders o where o.cid=c.cid and o.pid=p.pid))

(j) update products  set price=price*1.1 where city=' Duluth ' or city=' Dallas '

(l) select aid,[percent] from agents a  where not exists(select * from customers c
where city=' Duluth ' and not exists
(select * from orders o where o.cid=c.cid  and o.aid=a.aid)) order by [percent]DESC

2.
1）use student
select * from 学生表 where 性别='女'

2)select *from

3) select 姓名,性别,年龄  学生表 where 年龄<19 or  性别='女'

4) select 课程名,先修课 from 课程表  where 先修课 is null

5) select 学号,姓名,所在院系 from 学生表
where 所在院系<>'会计学' and 所在院系<>'计算机'

6) select 课程号,max(成绩) 最高分 from 成绩表  where 课程号='C801' group by 课程号

7) select 性别,count(学生表.学号) 个数,avg(年龄) 平均年龄 
from 学生表,成绩表 group by 性别

8) select C1.学号,count(C1.课程号)选课门数 from 成绩表 C1,成绩表 C2
where C1.学号=C2.学号 AND C1.课程号<>C2.课程号  GROUP BY C1.学号

9) select 学号,姓名 ,班级 from 学生表 
where 班级 in(select 班级 from 学生表 where 姓名='沈香娜')

10) SELECT 年龄,COUNT(课程号) 选课门数 FROM 学生表,成绩表
WHERE 学生表.学号=成绩表.学号  GROUP BY 年龄

11) select C.学号 from(select avg(成绩) 平均分,学号 from 成绩表 group by 学号) C 
where C.平均分>=all(select 平均分 from
(select avg(成绩) 平均分,学号 from 成绩表 group by 学号) C  )

12）select 年龄 as 男生年龄,count(年龄) 个数 from 学生表 where 性别='男'
group by 年龄 having count(年龄)>=2

13）select s.学号,s.姓名 from 学生表  where not exists (select * from 课程表 c
where not exists (select * from 成绩表 a where a.学号 = s.学号 and a.课程号 = c.课程号));

14）select X.* from 成绩表 x, 成绩表  where x.课程号=y.课程号 and y.学号='20026001' and X.学号<>'20026001

学生表where 入学期 < ' 2002-1-1 ';