数据库SQL Server实验报告 之 SQL语言进行复杂查询（4/8）

SQL语言进行复杂查询
注 意	原版word在下载 资源里面	今日 感想		历经千万种悲伤 然后从悲伤种走出来
实验目的及要求： 掌握SELECT语句的基本语法和查询条件表示方法； 掌握查询条件表达式和使用方法； 掌握GROUP BY 子句的作用和使用方法； 掌握HAVING子句的作用和使用方法； 掌握ORDER BY子句的作用和使用方法。 了解数据库查询； 了解数据库查询的实现方式； 完成实验报告；
实验内容及步骤： 以XSGL数据库中数据为基础，请使用T-SQL 语句实现以下操作： 列出所有不姓刘的所有学生； select sname,sno--查询学生即查询学生姓名和学号 from student where sname not like'刘%';--利用not like''语句筛选条件 列出姓“张”且全名为3个汉字的学生； select sname from student where sname like'张__'and len(sname)=3;--“len（）=”语句用来限制sname的长度 显示在1993年以后出生的学生的基本信息； select sname,sno,sage,year(getdate())-sage yearsage--后面空一格表示给该列属性起的名字 from student where (year(getdate())-sage)>1993;--year(getdate())表示当前年份 查询出课程名含有“数据”字串的所有课程基本信息； select course.*,sno,grade--课程表和选课表包含课程的所有信息 from course,sc where course.cno=sc.cno--课程表的cno是其主码，需要连接sc表中的cno外码 and cname like '数据%' 列出选修了‘1’课程的学生，按成绩的降序排列； select sname,student.sno,grade--sno是student中的主码，grade在选课表中所以需要连接sc表中的sno外码 from student,sc--查询学生一般是指查询姓名学号 where cno='1' order by grade desc--order by   对某属性进行排序，desc为倒序，acs为正序 列出同时选修“1”号课程和“2”号课程的所有学生的学号； select sno--课程号和学号在sc表中均有，所以不需要连接表 from sc where cno='1'and--cno不能同时为1和2，所以再嵌套一个选了2号课程的语句 sno in(select sno from sc where cno='2'); 列出课程表中全部信息，按先修课的升序排列； select * from course order by cpno asc;--asc代表升序，也可以省略 列出年龄超过平均值的所有学生名单，按年龄的降序显示； select sname,sno,sage from student where sage>(select avg(sage) from student)--where关键词后面不能跟聚合函数，所以嵌套一个表示平均值 order by sage desc; 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”； select sno 学号,sname 姓名,ssex 性别, year(getdate())-sage 出生年份,sdept 院系 from student order by year(getdate())-sage; 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩； select student.sno,cno,grade from student,sc where student.sno=sc.sno and grade>=70 and grade<80 order by cno,grade--两者顺序不能颠倒，不然结果会不一样 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄” select count(sno) 学生总人数,--count表示计算行数，sno为学生表的主码，即唯一，所以计算学号个数 avg(sage) 平均年龄 from student 显示选修的课程数大于3的各个学生的选修课程数 select sno,count(sno)--只要求显示选修课程数，需要计算一个学号的行 from sc group by sno--需要计算一个学号的行，对学号分组，则求得每个学生的选课数 having count(sno)>3;--不能用where，因为那是分组前的限制，分组前本来行就大于3所以起不到限制的作用，意思就是说一般group by和having连用 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； select cno 课程号,count(sno) 课程总人数, max(grade) 最高成绩,min(grade) 最低成绩, avg(grade) 平均成绩 from sc group by cno--按照课程号进行，即对每一个课程进行统计，需要分组 order by cno desc; 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；（已给出答案） select sdept 院系名称,count(case when ssex='男'then 1 end) 男生人数,--case when..then 1 end这里的1仅仅代表记号 count(case when ssex='女'then 1  end) 女生人数--当ssex=女时count加1，如果含有else 0 则无论男女count均加1造成女生数量与总人数相同 from student group by sdept;--各个院系，则需要对院系分组然后计算男女人数 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”； select case when ssex='男' then '男生' when ssex='女' then '女生' else '条件不明' end 性别,--case when ..then..when..then..else..end sno 学号,sname 姓名,sage 年龄,sdept 院系 from student 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系； select sno,sname,ssex,sage,sdept from student--[..]表示取框内任意一个 where sno like '_______[1,2,3,4,9]_' or sno like '________[1,2,3,4,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='WM' then '西医系' else '院系不明' end  sno,sname,ssex,sage from student order by sdept desc 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”； select sdept 院系,case when count(sno)>=5 then '规模一般' when count(sno)>=4 then '规模一般' when count(sno)>=3 then '规模稍小' else '规模很小' end--when 之间没有，且主义字符串要加'' from student where sdept is not null group by sdept;--必须对院系进行分组 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩。 select sno,avg(grade) 平均成绩 from sc group by sno--平均成绩需要分组确定每个学生 having avg(grade)>(select avg(grade) --分组后的限制语句having一般用聚合函数，聚合函数比较可以用嵌套表示另外一个聚合函数 from sc where sno='200515001') 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩； select sno,avg(grade) 平均成绩 from sc--不能先用where限制grade<60 否则无法计算>60的成绩 group by sno--必须对学号分组区分不同的学生 having count(case when grade<=60 then 1 end)>=2