实验数据库单表查询.docx

实验数据库单表查询.docx

《实验数据库单表查询.docx》由会员分享，可在线阅读，更多相关《实验数据库单表查询.docx（13页珍藏版）》请在冰豆网上搜索。

实验数据库单表查询

实验：

数据库单表查询

一、实验目的

1.掌握SELECT语句的基本语法和查询条件表示方法；

2.掌握查询条件表达式和使用方法；

3.掌握GROUPBY子句的作用和使用方法；

4.掌握HAVING子句的作用和使用方法；

5.掌握ORDERBY子句的作用和使用方法。

二、实验环境

已安装SQLServer2005企业版的计算机；

具有局域网环境,有固定IP;

三、实验学时

2学时

四、实验要求

1.了解数据库查询；

2.了解数据库查询的实现方式；

3.完成实验报告；

五、实验内容及步骤

以《实验：

管理SQLServer表数据》数据库中数据为基础，请使用T-SQL语句实现以下操作：

1.

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

USEstudent

GO

select*fromstudent

wheresnamenotlike'刘%'

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

USEstudent

GO

select*fromstudent

wheresnamelike'王_[^]'

3.显示在1985年以后出生的学生的基本信息；

USEstudent

GO

select*fromstudent

wheresage<（year（getdate（））-1985）

4.

按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：

性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”；

USEstudent

GO

selectssex=

casessex

when'男'then'男生'

when'女'then'女生'

else'条件不明'

end,sno,sname,sage,sdept

fromstudent

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

USEstudent

GO

select*fromcourse

wherecnamelike'%数据%'

6.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

USEstudent

GO

selectsno,sname,ssex,sage,sdeptfromstudent

wheresnolike'_______[1-4,9]%'orsnolike'________[1-4,9]'

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

USEstudent

GO

selectstudent.sno,sname,ssex,sage,sdept,gradefromstudent,sc

wherecno=1andstudent.sno=sc.sno

orderbygradeDESC

GO

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

USEstudent

GO

selecta.snofromsca,scb

whereo=1ando=2anda.sno=b.sno

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

USEstudent

go

select*fromcourse

orderbycpnoASC

Go

10.

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

USEstudent

go

select*fromstudent

wheresage>（selectavg（sage）fromstudent）

orderbysageDESC

11.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

USEstudent

go

selectsno学号,sname姓名,ssex性别,（year（getdate（））-sage）出生年份,sdept院系fromstudent

orderby（year（getdate（））-sage）ASC

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

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

USEstudent

go

select院系=

casesdept

when'CS'then'计算机系'

when'IS'then'信息系'

when'MA'then'数学系'

when'EN'then'外语系'

when'CM'then'中医系'

when'WM'then'西医系'

else'院系不明'

end,sno学号,sname姓名,ssex性别,sage年龄fromstudent

orderbysdeptDESC

13.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

USEstudent

go

selectdistinct'院系'=sdept,'院系规模'=

case

whencount（*）>=5then'规模很大'

whencount（*）>=4andcount（*）<5then'规模一般'

whencount（*）>=2andcount（*）<4then'规模稍小'

else'规模很小'

endfromstudent

groupbysdept

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

USEstudent

go

selectsno,cno,gradefromsc

wheregradebetween70and80

orderbycnoDESC,gradeDESC

go

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

USEstudent

go

select'学生总人数'=count（*）,'平均年龄'=AVG（sage）fromstudent

16.显示选修的课程数大于3的各个学生的选修课程数；

USEstudent

go

selectsno学号,'选修课程数'=count（cno）fromsc

groupbysno

havingcount（cno）>3

17.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

usestudent

go

selectdistinctcno,'选修人数'=count（sno）,'最高成绩'=MAX（grade）,'最低成绩'=MIN（grade）,

'平均成绩'=AVG（grade）FROMsc

groupbycno

orderbycnoDESC

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

usestudent

go

selectsno学号,'平均成绩'=avg（grade）fromsc

groupbysno

havingavg（grade）>（selectavg（grade）fromsc

wheresno='200515001'）

19.显示选修各个课程的及格的人数、及格比率；

usestudent

go

selectdistinctcno,'及格人数'=sum（casewhengrade>60then1else0end）,

'及格比率'=sum（casewhengrade>60then1else0end）/count（*）fromsc

groupbycno

20.显示选修课程数最多的学号及选修课程数最少的学号；

usestudent

go

selectdistinct'学修课程最多的学号'=sno,'选修课程数'=count（cno）fromsc

groupbysno

havingcount（cno）>=all（selectcount（cno）fromsc

groupbysno）

selectdistinct'学修课程最少的学号'=sno,'选修课程数'=count（cno）fromsc

groupbysno

havingcount（cno）<=all（selectcount（cno）fromsc

groupbysno）

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

usestudent

go

selectdistinctsdept院系名称,'男生人数'=sum（casessexwhen'男'then1else0end）,

'女生人数'=sum（casessexwhen'女'THEN1ELSE0end）fromSTUDENT

groupbysdept

22.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

USEstudent

go

selectdistinct'不及格学生学号'=sno,'平均分数'=avg（grade）fromsc

groupbysno

havingsum（casewhengrade<60then1else0end）>=2

六、出现问题及解决办法

如：

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