上机练习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(课程号)

17.

select 所在系,count(*) 学生总数,avg(成绩) 平均成绩 from student st join sc on st.学号=sc,学号 group by 所在系

18.

select 学号,count(课程号) 选课门数,avg(成绩) 平均成绩 from sc group by 学号 having count(课程号)>2

19.

select 学号,sum(成绩) 总成绩 from sc group by 学号 having sum(成绩)>200 20.

use pubs

select type ,avg(price) 平均价格,max(price) 最高价格

from titles group by type having avg(price)>12.0

21.

select avg(price) 平均价格 from titles where royalty=10 group by type 22.

select sum(price) 总价格 from titles group by type having count(type)>3 23.

use sqllx

select 姓名,所在系 from student st,sc where st.学号=sc.学号 and sc.课程号='c02'

24.

select 姓名,课程号,成绩 from student st,sc where st.学号=sc.学号 and sc.成绩>80

order by 成绩 desc

25.

select 姓名,性别,成绩 from student st , course co,sc where st.学号=sc.学号 and

sc.课程号=co.课程号 and st.所在系='计算机系'and co.课程名='数据库基础' and st.性别='男'

26.

select student.学号,姓名,课程号,成绩 from student left join sc on student.学号=sc.学号 27.

select top 3 st.学号,姓名,所在系,成绩 from student st,course co ,sc

where st.学号=sc.学号 and co.课程号=sc.课程号 and 课程名='数据库基础' order by 成绩 desc 28.

select distinct s1.学号, s1.课程号 from sc s1 join sc s2 on s1.课程号=s2.课程号 order by s1.课程号

29.

select distinct s1.姓名,s1.年龄 from student s1 join student s2 on s1.年龄=s2.年龄 order by s1.年龄

30.

select co.课程号,co.课程名 from course co , sc

where co.课程号=sc.课程号 and co.课程号 not in(select 课程号 from sc) 31.

select 姓名 student_name,课程名 course_name,成绩 grade into new_sc

from student st join sc on st.学号=sc.学号 join course co on co.课程号=sc.课程号 where sc.成绩 is not null

32.

select st.所在系,st.姓名,st.性别,co.课程名,sc.成绩 from student st,course co,sc where sc. 学号=st.学号 and sc.课程号=co.课程号 and st.所在系='计算机系' union

select st.所在系,st.姓名,st.性别,co.课程名,sc.成绩 from student st,course co,sc where sc. 学号=st.学号 and sc.课程号=co.课程号 and st.所在系='信息系'

33.

select distinct city from Employees union

select distinct city from Customers order by city asc 34.

select ProductID, ProductName, case

when UnitPrice <=10 then '很便宜'

when UnitPrice >10 and UnitPrice<=20 then '较便宜' when UnitPrice >20 and UnitPrice<=30 then '中等' when UnitPrice >30 and UnitPrice<=40 then '较贵' when UnitPrice >40 and UnitPrice<=100 then '很贵' when UnitPrice >100 then '价格过高' end

from products

where UnitsInStock>10 35.

select s.sno 学号,sname 姓名, case sdept

when '计算机系' then 'CS' when '信息系' then 'IS' when '数学系' then 'MA' else 'OTHER'

end 所在系,grade 成绩

from student s join sc on s.sno = sc.sno join course c on sc.cno=c.cno where cname='vb'

参考以上代码进行调试，发现错误并处理。

本文来源：https://www.bwwdw.com/article/m89o.html