数据库实验4.doc

实验四：数据库单表查询一、实验目的1. 掌握SELECT语句的基本语法和查询条件表示方法；2. 掌握查询条件表达式和使用方法；3. 掌握GROUP BY 子句的作用和使用方法；4. 掌握HAVING子句的作用和使用方法；5. 掌握ORDER BY子句的作用和使用方法。二、实验环境已安装SQL Server 企业版的计算机(120台)；具有局域网环境,有固定IP;三、实验学时2学时四、实验要求1. 了解数据库查询；2. 了解数据库查询的实现方式；3. 完成实验报告；五、实验内容及步骤（一）在学生选课库中，用Transact-SQL语句实现下列简单数据查询操作。学号Snum姓名Sname性别Sex年龄Sage电话Sphone系编号DnumS001王明男1986824571D2S002李勇男2389454321D3S003刘燕女21D1S004王萍女23D1S005王佳男2413098765892D3S006赵婷女20D1Student表学号Snum课程号Cnum1课程号Cnum课程名称Cname学分CfreqC1数据库系统原理4C2C程序设计4C3计算机体系结构3C4自动控制原理2C5数据结构4 SC表成绩ScoreS001C183S001C289S001C365S001C485S001C569S002C378S002C475S005C195S004C185S005C192S005C376Course 表 1）查询系编号为D2学生的基本信息（学号、姓名、性别、年龄）。Select Snum, Sname, Sex, SageFrom studentWhere Dnum =D2 2）查询学号为S006的学生的姓名。Select SnameFrom studentWhere Snum =S006 3） 查询成绩在60-85之间的学生的学号。Select SnumFrom CourseWhere Snum=Cnum and Score=60 and Score19854. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；Select Sage=casewhen Sage =男 then 男生when Sage =女 then 女生else 条件不明end ,Snum,Sname,Sage, SdeptFrom student5. 查询出课程名含有“数据”字串的所有课程基本信息；Select * From course Where Cname like %数据%6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；Select Sno,Sname,Ssex,Sage,Sdept From studentWhere Sno like %1-4,9_ or Sno like %1-4,97. 列出选修了1课程的学生，按成绩的降序排列；Select * From student,scWhere student.sno=sc.sno and Cname =C1 order by 成绩desc8. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；Select Sno From SCWhere Cno=1 and Sno in (Select Sno From SC Where Cno=2)9. 列出课程表中全部信息，按先修课的升序排列；Select * From Course order by cpno10. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；Select Sname From Student Where Sage (Select avg(Sage) From Student)order by Sage desc11. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；Select sno as Snum,sname as Sname, ssex as ssex, year(getdate()-sage as Sno,sdept as Sdept From studentorder by Sno12. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为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 student13. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；Select distinct Sdept, 院系规模=casewhen count(*)=5 then 规模较大when count(*)=4 then 规模一般when count(*)=2 then 规模较小else 规模很小end From student group by Sdept14. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；Select Snum=Sno, Cno, Grade From SC Where Grade between 70 and 80order by Cno desc,Grade desc15. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；Select COUNT(*),AVG(Sage) From student16. 显示选修的课程数大于3的各个学生的选修课程数；Select distinct Sno, COUNT(Cno) From SC group by Sno having COUNT(Cno)317. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；Select count(sno) as 选修总人数,max(grade),min(grade),avg(grade) From sc group by cno（3） 、选做题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 grade60 group by cno20. 显示选修最多的课程数和最少的课程数； 21. 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；Select sdept, sum(case when