1、实验四：数据库单表查询一、实验目的1. 掌握 SELECT语句的基本语法和查询条件表示方法；2. 掌握查询条件表达式和使用方法；3. 掌握 GROUP BY 子句的作用和使用方法；4. 掌握 HAVING子句的作用和使用方法；5. 掌握 ORDER BY子句的作用和使用方法。二、实验环境已安装 SQL Server 企业版的计算机(120 台)；具有局域网环境,有固定 IP;三、实验学时2学时四、实验要求1. 了解数据库查询；2. 了解数据库查询的实现方式；3. 完成实验报告；五、实验内容及步骤（一） 在学生选课库中，用 Transact-SQL语句实现下列简单数据查询操作。学号 姓名 性别
2、年龄 电话 系编号S001 王明 男 19 86824571 D2S002 李勇 男 23 89454321 D3S003 刘燕 女 21 D1S004 王萍 女 23 D1S005 王佳 男 24 13098765892 D3S006 赵婷 女 20 D1Student表学号 课程号 成绩S001 C1 83S001 C2 89S001 C3 65S001 C4 85S001 C5 69S002 C3 78S002 C4 75S005 C1 95S004 C1 85S005 C1 92S005 C3 76SC 表1）查询系编号为D2学生的基本信息（学号、姓名、性别、年龄） 。select 学
3、号,姓名,性别,年龄 from student where 系编号 like d22） 查询学号为 S006的学生的姓名。select 姓名 from student where 学号 =S0063） 查询成绩在 60-85之间的学生的学号。select distinct学号 from sc where 成绩 between 60 and 854） 查询所有姓王，并且姓名为两个字的学生的信息。select * from student where 姓名 like 王_5） 查询选修课程号为C1且成绩非空的学生学号和成绩，成绩按 150分制1课程号 课程名称 学分C1 数据库系统原理 4C2 C
4、 程序设计 4C3 计算机体系结构 3C4 自动控制原理 2C5 数据结构 4Course 表输出（每个成绩乘以系数 1.5） 。select 学号,成绩*1.5 from sc where 课程号=c1and 成绩 is not null6） 查询有选课记录的所有学生的学号，用 DISTINCT限制结果中学号不重复。select distinct 学号 from sc where 课程号 is not null7） 查询选修课程C1 的学生学号和成绩，结果按成绩的升序排列，如果成绩相同则按学号的降序排列。select 学号 ,成绩 from sc where 课程号 like c1 orde
5、r by 成绩 ,学号 desc（二） 、以数据库原理实验 3数据库中数据为基础，请使用 T-SQL 语句实现以下操作：1. 列出所有不姓刘的所有学生；select sname from student where sname not like 刘%2. 列出姓“沈”且全名为 3个汉字的学生；select sname from student where sname like 沈_;3. 显示在 1985年以后出生的学生的基本信息；select sno,sname,ssex, sage,year(getdate()-sage birther,sdept from student where y
6、ear(getdate()-sage1985;4. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明” ；select 性别=case when ssex=男 then男生 when ssex=女 then女生 else条件不明 end,学号=sno,sname as 姓名,sage 年龄,院系=sdept from 学生5. 查询出课程名含有“数据”字串的所有课程基本信息；select * from 课程 where cname like %数据%6. 显示学号第八位或者第九位是 1、2、3、4
7、或者 9的学生的学号、姓名、性别、年龄及院系；select sno,sname,ssex,sage,sdept from 学生 where sno like _1-4,9_ or _1-4,9 7. 列出选修了1课程的学生，按成绩的降序排列；select * from 选课 where cno like 1 order by grade desc8. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；select sno from 选课 where cno like 1 and cno like 29. 列出课程表中全部信息，按先修课的升序排列；select * from course
8、order by cpno;10.列出年龄超过平均值的所有学生名单，按年龄的降序显示；select * from student where sage (select avg(sage) from student) order by sage desc;11.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系” ；select sno 学号,sname 姓名,ssex 性别,year(getdate()-sage birthday from studentorder by year(getdate()-sage;12.按
9、照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为 CS显示为计算机系，院系为 IS显示为信息系，院系为 MA显示为数学系，院系为 EN显示为外语系，院系为 CM显示为中医系，院系为 WM显示为西医系，其他显示为院系不明；select 院系 = case when sdept = CS then 计算机系when sdept = IS then 信息系when sdept = MA then 数学系when sdept = EN then 外语系when sdept = CM then 中医系when sdept = WN then 西医系els
10、e 院系不明end,sno 学号, sname 姓名,ssex 性别 from student order by sdept;13.显示所有院系（要求不能重复，不包括空值） ，并在结果集中增加一列字段“院系规模” ，其中若该院系人数=5 则该字段值为“规模很大” ，若该院系人数大于等于 4小于 5则该字段值为“规模一般” ， 若该院系人数大于等于 2小于 4则该字段值为“规模稍小” ，否则显示“规模很小” ；select distinct sdept,院系人数=count(*) into student2 from student where sdept is not nullgroup by
11、 sdept;select * 院系规模 = case when 院系人数=5 then 规模很大when 院系人数=4 then 规模一般when 院系人数=2 then 规模稍小else 规模很小endfrom student2;14.按照课程号、成绩降序显示课程成绩在 70-80之间的学生的学号、课程号及成绩；select * from sc where grade between 70 and 80order by cno desc,grade desc15.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄” ；select count(sno)
12、 学生总人数,avg(sage) 平均年龄 from student16.显示选修的课程数大于 3的各个学生的选修课程数；select sno,count(cno) cno_count from sc group by sno having count(cno)3;17.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；select count(cno) con_count,max(grade) max_grade,min(grade) min_grade,avg(grade) avg_grade from scgroup by cno order by cno desc;（
13、三） 、选做题18.显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；select sno,avg(grade) avg_grade from sc group by snohaving avg(grade)(select avg(grade) from sc where sno = 200515001);19.显示选修各个课程的及格的人数;select cno,count(sno) sno_count from scwhere grade=60group by cno;20.显示选修最多的课程数和最少的课程数；select sno 学号,count(cno) 选修最多的课程数 from sc group by snohaving count(cno) = all(select count(cno) from sc group by cno) select sno 学号,count(cno) 选修最少的课程数 from sc group by snohaving count(cno) =2;六、出现问题及解决办法如：某些查询操作无法执行，如何解决？
