上机练习3 - 数据查询

1．按照下面要求创建三个基本表，写出对应语句，可以建在pubs数据库上。这三个基本表是后续练习的基础。

学生表(student) (

学号(sno) 普通编码定长字符类型，长度7，主码， 姓名(sname) 普通编码定长字符类型，长度8，非空， 性别(ssex) 统一编码定长字符类型，长度1， 年龄(sage) 微整型，

所在系(sdept) 统一编码可变长字符类型，长度20）

课程表(course) (

课程号(cno) 普通编码定长字符类型，长度6，主码， 课程名(cname) 统一编码定长字符类型，长度10，非空， 学分(credit) 小整型， 学期(semester) 小整型）

修课表（sc）(

学号(sno) 普通编码定长字符类型，长度7，主码，外码 课程号(cno) 普通编码定长字符类型，长度6，主码，外码 成绩(grade) 小整型，

修课类别（type）普通编码定长字符类型，长度4） 2．为学生添加专业（spec）列char(30)。 3．将专业列的长度改为40。 4．删除专业列

数据查询与修改

1．分别查询学生表和学生修课表中的全部数据。 2．查询计算机系的学生的姓名、年龄。 3．查询选修了c01号课程的学生的学号和成绩。

4. 查询成绩在70到80分之间的学生的学号、课程号和成绩。

5．查询计算机系年龄在18到20之间且性别为'男'的学生的姓名、年龄。 6．查询9512101号学生的修课情况。 7．查询c01号课程成绩最高的分数。

8．查询学生都修了那些课程,要求列出课程号。

9. 查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate，并将最新的定购日期（OrderDate）列在前边。

10. 查询Northwind数据库中orders表的ShipCountry列以B,C,D,F开始且第三个字符为'a'的OrderID、CustomerID和ShipCountry的信息。

11. 查询Northwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字母是'a'的OrderID、CustomerID和ShipCountry的信息。 12. 查询学生的最大的年龄和最小的年龄

13．查询修了c02号课程的所有学生的平均成绩、最高成绩和最低成绩。 14. 统计每个系的学生人数。

15. 统计每门课程的修课人数和考试最高分。

16. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。 17. 统计各系修课的学生总数和考试的平均成绩

18. 查询选课门数超过2门的学生的平均成绩和选课门数。 19. 列出总成绩超过200分的学生，要求列出学号、总成绩。

20. 查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型、平均价格和最高价格，要求只计算有确定价格的图书的情况。

21. 查询pubs数据库的titles表中版税（royalty）为10的每类图书（type）的平均价格。

22. 查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。

group by type having count(*) > 3

23. 查询选修了c02号课程的学生的姓名和所在系。

24. 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。

25. 查询计算机系修了\数据库基础\的学生的姓名、性别、成绩。

26. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、修课号、修课成绩。

27. 列出\数据库基础\课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

28. 查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。 29. 查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄。 30. 查询哪些课程没有人选，要求列出课程号和课程名。

31. 查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表（假设新表名为new_sc）中，新表的列名分别为： 32. 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

33.在Northwind数据库中，查询雇员（Employees表）和顾客（Customers表） 都来自哪些城市（City)，并降结果按城市的字母升序排序

34.在Northwind数据库的产品表（Products）中，查询库存数量（UnitsInStock） 大于10的产品的编号（ProductID）、产品名（ProductName）和单价（UnitPrice）， 并将单价小于等于10元的显示为“很便宜”； 单价超过10元但小于等于20元的显示为“较便宜”； 单价超过20元但小于30元的显示为“中等”； 单价超过30元但小于40元的显示为“较贵”； 单价超过40元但小于100元的显示为“很贵”； 单价超过100元的显示为“价格过高”。

35. 查询选修了vb课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息系”时，显示“IS”； 当所在系为“数学系”时，显示“MA”； 对其他系，均显示“OTHER”。

参考代码：

CREATE TABLE 学生 ( 学号 char(7) primary key , 姓名 char (8) NOT NULL, 性别 nchar (1), 年龄 int NULL , 所在系 nvarchar(20)

)

create table 课程表(

课程号 char(6) primary key, 课程名 char(10) not null, 学分 float, 学期 float )

create table 修课表(

学号 char(7) not null, 课程号 char(6) not null, 成绩 float,

修课类别 char(4)

primary key(学号,课程号),

foreign key(学号) references 学生表(学号),

foreign key(课程号) references 课程表(课程号)) 1

select*from 学生表 select*from 修课表

2

select 姓名,年龄 from 学生表 where 系别='计算机系' 3

select 学号,成绩 from 修课表 where 课程号='c01'

4

select 学号,课程号,成绩 from 修课表 where 成绩 between 70 and 80

5

select 姓名,年龄 from 学生表 where 系别='计算机系' and 性别='男' and 年龄 between 18 and 20

6

select * from 修课表 where 学号='9512101'

7

select max(成绩) 最高分 from 修课表 where 课程号='c01'

8

select distinct 课程号 from 修课表 9.

use Northwind

select OrderDate,OrderID,CustomerID from orders 10.

select OrderID,CustomerID,ShipCountry from orders where ShipCountry like '[BCDF]_a%' 11.

select OrderID,CustomerID,ShipCountry from orders where ShipCountry like '[^ABCDEF]%A' 12.

select max(年龄) 最大年龄,min(年龄) 最小年龄 from student

13.

select avg(成绩) 平均成绩,max(成绩) 最高成绩,min(成绩) 最低成绩 from sc where 课程号='c02'

14.

select 所在系, count(*) 人数 from student group by 所在系

15.

select 课程号,count(*) 修课人数,max(成绩) 最高分 from sc group by 课程号 16.

select 学号,count(课程号) 选课门数 from sc group by 学号 order by count(课程号)