08计科《数据库原理》实验4

实验四：数据库单表查询答案

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法； 2. 掌握查询条件表达式和使用方法； 3. 掌握GROUP BY 子句的作用和使用方法； 4. 掌握HAVING子句的作用和使用方法； 5. 掌握ORDER BY子句的作用和使用方法。 二、实验环境

已安装SQL Server 企业版的计算机(120台)； 具有局域网环境,有固定IP; 三、实验学时

2学时 四、实验要求

1. 了解数据库查询；

2. 了解数据库查询的实现方式； 3. 完成实验报告； 五、实验内容及步骤

（一）．在学生选课库中，用Transact-SQL语句实现下列简单数据查询操作。 学号 Snum S001 S002 S003 S004 S005 S006 姓名 Sname 王明 李勇 刘燕 王萍 王佳 赵婷 性别 年龄 Sex 男 男 女 女 男 女 Sage 19 23 21 23 24 20 电话 Sphone 86824571 89454321 13098765892 系编号 Dnum D2 D3 D1 D1 D3 D1 Student表

学号 Snum S001 S001 S001 S001 S001 S002 S002 S005 S004 S005 S005 课程号 Cnum C1 C2 C3 C4 C5 C3 C4 C1 C1 C1 C3 成绩 Score 83 89 65 C2 C程序设计 4 C1 数据库系统原理 4 1 课程号 Cnum 课程名称 Cname 学分 Cfreq 85 69 78 75 95 85 92 76 C5 数据结构 4 C3 计算机体系结构 3 C4 自动控制原理 2 SC表 Course 表

1）查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。

Select 学号,姓名,性别,年龄

From 学生

Where 系编号=’D2’

2） 查询学号为S006的学生的姓名。

Select 姓名 From 学生

Where 学号=’S006’

3） 查询成绩在60-85之间的学生的学号。

select 学生.学号 from 学生,选课

where 选课.学号=学生.学号 and 成绩>=60 and 成绩<=85

4） 查询所有姓王，并且姓名为from 学生 两个字的学生的信息。

Select 学生.* From 学生

Where 姓名 like ‘王_’

5） 查询选修课程号为‘C1’且成绩非空的学生学号和成绩，成绩按150分制输出（每个成绩乘以系数1.5）。

select 学生.学号 ,成绩=成绩*1.5 from 选课,学生

where 选课.成绩 is not null and 选课.学号=学生.学号 and 选课.课程号='C1'

6） 查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重复。

Select distinct 学号 From 选课

7） 查询选修课程‘C1’的学生学号和成绩，结果按成绩的升序排列，如果成绩相同则按学号的降序排列。

select 学号,成绩 from 选课

where 课程号='C1' order by 成绩 , 学号 desc

（二）、以数据库原理实验3数据库中数据为基础，请使用T-SQL 语句实现以下操作：

1. 列出所有不姓刘的所有学生；

select * from student where Sname not like '刘%'

2. 列出姓“沈”且全名为3个汉字的学生；

select * from student where Sname like ‘沈__’ 3. 显示在1985年以后出生的学生的基本信息；

select * from student where year(getdate())-Sage>1985

4. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按

以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；

select 性别=case

when 性别='男' then '男生' when 性别='女' then '女生'

else '条件不明'end ,学号,姓名,年龄,系编号 院系from student

5. 查询出课程名含有“数据”字串的所有课程基本信息；

select * from course where Cname like '%数据%'

6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性

别、年龄及院系；

select Sno,Sname,Ssex,Sage,Sdept from student

where Sno like '%[[1-4],9]_' or Sno like '%[1-4,9]'

7. 列出选修了‘1’课程的学生，按成绩的降序排列；

select student.* from student,sc

where student.sno=sc.sno and 课程号='C1' order by 成绩 desc

8. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；

select Sno from SC

where Cno=1 and Sno in (select Sno from SC where Cno=2)

9. 列出课程表中全部信息，按先修课的升序排列；

select * from Course order by cpno

10. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；

select Sname from Student

where Sage > (select avg(Sage) from Student)order by Sage desc

11. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在

结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

select sno as 学号,sname as 姓名, ssex as 性别,

year(getdate())-sage as 出生年份,sdept as 院系 from student

order by 出生年份

12. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，

其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

select Sdept = case

when Sdept='CS' then '计算机系' when Sdept='IS' then '信息系' when Sdept='MA' then '数学系' when Sdept='EN' then '外语系' when Sdept='CM' then '中医系' when Sdept='WM' then '西医系' else '条件不明'

end ,Sno,Sname,Ssex,Sage from student

13. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段

“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

select distinct Sdept, 院系规模=case

when count(*)>=5 then '规模较大' when count(*)>=4 then '规模一般' when count(*)>=2 then '规模较小' else '规模很小'

end

from student group by Sdept

14. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及

成绩；

select 学号=Sno,课程号=Cno,成绩=Grade from SC where Grade between 70 and 80 order by Cno desc,Grade desc

15. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

select 学生总人数=COUNT(*),平均年龄=AVG(Sage) from student 16. 显示选修的课程数大于3的各个学生的选修课程数；

select distinct 学号=Sno,选修课程数=COUNT(Cno)

from SC group by Sno having COUNT(Cno)>3

17. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； select count(sno) as 选修总人数 ,max(grade),min(grade),avg(grade) from sc group by cno

（三）、选做题

18. 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；

select sno,avg(grade) from sc group by sno

having avg(grade)>(select avg(grade) from sc where sno='200515001')

19. 显示选修各个课程的及格的人数;

select cno,count(sno) as 及格人数 from sc where grade>60 group by cno

20. 显示选修最多的课程数和最少的课程数；

select max(选修课程数),min(选修课程数) from

(select sno, 选修课程数=count(cno) from sc group by sno) a

21. 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

select sdept,男生人数=sum(case when ssex='男' then 1 else 0 end),sum(case when ssex='女' then 1 else 0 end) as 女生人数 from student group by sdept

22. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩； select sno,avg(grade) from sc where grade<60 group by sno having count(cno)>=2

六、出现问题及解决办法

如：某些查询操作无法执行，如何解决？

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