实验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、在“学生管理数据库”中建立四张表，分别如下：

学生表：（主键：学号）

学号

姓名

性别

年龄

所在院系

班级

入学日期

20009001

葛文卿

女

22

国际贸易

国贸2班

2000－8－29

20014019

郑秀丽

女

21

会计学

会计1班

2001－9－2

20023001

刘成铠

男

18

计算机

软件2班

2002－8－27

20026002

李涛

女

19

电子学

电子1班

2002－8－27

20023002

沈香娜

女

18

计算机

软件2班

2002－8－27

20026003

李涛

男

19

计算机

软件1班

2002－8－27

20023003

肖一竹

女

19

计算机

软件2班

2002－8－27

 

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

课程号

课程名

先修课

学分

C801

高等数学

 

4

C802

C++语言

C807

3

C803

数据结构

C802

4

C804

数据库原理

C803

4

C805

操作系统

C807

4

C806

编译原理

C803

4

C807

离散数学

 

4

 

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

学号

课程号

成绩              

学分

20023001

C801

98

4

20023002

C804

70

4

20026001

C801

85

4

20023001

C802

99

3

20026002

C803

82

4

 

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

教师名

课程号

学时数

班级名

苏亚步

C801

72

软件2班

王文山

C802

64

软件2班

张珊

C803

72

软件2班

王文山

C804

64

软件2班

苏亚步

C801

72

软件1班

 

（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 ';