实验五：数据库单表查询
一、实验目的
1. 掌握SELECT语句的基本语法和查询条件表示方法； 2. 掌握查询条件表达式和使用方法； 3. 掌握GROUP BY 子句的作用和使用方法； 4. 掌握HAVING子句的作用和使用方法； 5. 掌握ORDER BY子句的作用和使用方法。 二、实验环境
已安装SQL Server 2005 企业版的计算机(13台)； 具有局域网环境,有固定IP; 三、实验学时
2学时 四、实验要求
1. 了解数据库查询；
2. 了解数据库查询的实现方式； 3. 完成实验报告； 五、实验内容及步骤
以数据库原理实验4数据库中数据为基础，请使用T-SQL 语句实现以下操作： 1. 列出所有不姓刘的所有学生；
select * from student
where Sname not like'刘%' go
2. 列出姓?沈?且全名为3个汉字的学生；
3. 显示在1985年以后出生的学生的基本信息；
use student go select* from student
where Sage 4. 按照?性别、学号、姓名、年龄、院系?的顺序列出学生信息，其中性别按 以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为?条件不明?； use student go select ssex=( case when ssex='男'then'男' when ssex='女'then'女' else '条件不明' end ) ,sno,sname,sage,sdept from student go 5. 查询出课程名含有?数据?字串的所有课程基本信息； use student go select* from course where cname like'数据%' go 6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性 别、年龄及院系； use student go select sno,sname,ssex,sdept from student where sno like'_______1%'or sno like'_______2%' or sno like'_______3%'or sno like'_______4%' or sno like'_______9%'or sno like'________1%' or sno like'________2%'or sno like'________3%' or sno like'________4%'or sno like'________9%' go 7. 列出选修了‘1’课程的学生，按成绩的降序排列； use student go select sc.sno,sname,ssex,sage from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and sc.cno=1 go 8. 列出同时选修?1?号课程和?2?号课程的所有学生的学号； use student go select sno from sc where cno in(1,2) go 9. 列出课程表中全部信息，按先修课的升序排列； use student go select* from course order by cpno 10. 列出年龄超过平均值的所有学生名单，按年龄的降序显示； use student go select* from student where sage>(select avg(sage) from student) order by sage desc go 11. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在 结果集中列标题分别指定为?学号，姓名，性别，出生年份，院系?； use student go select sno as 学号,sname as 姓名,ssex as 性别,出生年份=year(getdate())-sage,sdept as 院系 from student order by sage desc go 12. 按照院系降序显示所有学生的 ?院系，学号、姓名、性别、年龄?等信息， 其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明； 13. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段 ?院系规模?，其中若该院系人数>=5则该字段值为?规模很大?，若该院系人数大于等于4小于5则该字段值为?规模一般?， 若该院系人数大于等于2小于4则该字段值为?规模稍小?，否则显示?规模很小?； use student go select distinct sdept,院系规模=( case when count(sdept)>=5 then '规模很大' when count(sdept)>=4 and count(sdept)<5 then'规模一般' when count(sdept)>=2 and count(sdept)<4 then'规模稍小' else '规模很小' end ) from student where sdept is not null group by sdept go 14. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及 成绩； use student go select sno as 学号,cno as 课程号,grade as 成绩 from sc where grade between 70 and 80 order by cno/grade desc go 15. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为 ?学生总人数，平均年龄?； use student go select count(*) as 学生总人数,avg(sage)as 平均年龄 from student go 16. 显示选修的课程数大于3的各个学生的选修课程数； use student go select sno as 学号,count(*) as 选修课程数 from sc group by sno having count(*)>3 go 17. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； use student go select cno as 课程号,count(*)as 总人数,max(grade) as 最高成绩,min(grade)as 最低成绩,avg(grade)as 平均成绩 from sc group by cno order by cno desc go 18. 显示平均成绩大于?200515001?学生平均成绩的各个学生的学号、平均成 绩； use student go select sno as 学号,avg(grade)as 平均分 from sc group by sno having avg(grade)>any ( select avg(grade) from sc group by sno having sno=200515001) go 19. 显示选修各个课程的及格的人数、及格比率； use student go select pass as 及格人数, pass*100.0/total as 及格比率 from ( select count(*) as pass, Cno from sc where Grade >= 60 group by Cno ) passtb inner join ( select count(*) as total, Cno from sc group by Cno ) totaltb on passtb.Cno=totaltb.Cno 20. 显示选修课程数最多的学号及选修课程数最少的学号； select distinct'学修课程最多的学号'=sno,'选修课程数'=count(cno) from sc group by sno having count(cno)>=all(select count(cno) from sc group by sno) select distinct'学修课程最少的学号'=sno,'选修课程数'=count(cno) from sc group by sno having count(cno)<=all(select count(cno)from sc group by sno) go 21. 显示各个院系男女生人数，其中在结果集中列标题分别指定为?院系名称、 男生人数、女生人数?； 22. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩； 六、出现问题及解决办法 如：某些查询操作无法执行，如何解决？