实验五：数据库单表查询

一、实验目的

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 Sagego

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.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

六、出现问题及解决办法

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