一、实验目的
1.掌握select语句的基本语法和查询条件表示方法
2.掌握查询条件表达式和使用方法；
3.掌握group by子句的作用和使用方法；
4.掌握having子句的作用和使用方法；
5.掌握order by子句的作用和使用方法。

1）查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。
select sno,sname,ssex,sage from student  where sdept='D2';
2)查询学号为S006的学生的姓名。
select sname from student  where sno='S006';
3）查询成绩在60-85之间的学生的学号。
select sno from sc where grade=83;
4）查询所有姓王，并且姓名为两个字的学生的信息。
select * from student where sname like '王%';
5)查询选修课程号为‘C1’的学生学号和成绩，成绩按150分制输出（每个成绩乘以系数1.5）
select sno,grade*1.5 成绩 from sc where cno='c1' and grade is not null;
6)查询有选课记录的所有学生的选号，用distinct限制结果中学号不重复。
select distinct(sno) from sc;
7)查询选修课程'c1'的学生学号和成绩，结果按成绩的升序排序，如果成绩相同则按学号的降序排列。
select sno,grade  from sc   where cno='c1'
order by grade asc,sno desc;

2.以数据库实验三数据库中数据为基础请使用T-SQL语句实现一下操作：

总表如下：

http://s13/middle/753e6cc9gf613c730476c&690

http://s2/middle/753e6cc9gbcec71f31141&690

http://s13/middle/753e6cc9gbcec7200de6c&690


1）列出所有不姓刘的学生；
select
*
from  student
where sname not like'刘%'
2)列出所有姓刘且全名为三个汉字的学生；
select
*
from  student
where sname  like'刘__'
3)显示在1985年以后出生的学生的基本信息
select
*
from  student
where 2012-sage=1985
4）按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示；性别为男显示为男生，性别为女显示为女生，其他
显示为“条件不明”
select
sno,sage,sname,sdept,ssex=
case ssex when '女' then '女生'
          when '男' then '男生'
          else '条件不明'
         end
from  student
5)select
sno,sage,sname,sdept,ssex=
case ssex when '女' then '女生'
          when '男' then '男生'
          else '条件不明'
         end
from  student
6)显示学号第八位或者第九位是1、2、3、4或者9的学生的学号,姓名，性别，年龄及院系
select
*
from  student
where  sno like'_______[12349][12349]%'
7）列出选修了‘1’课程的学生，按成绩的降序排列
select
sno
from sc
where  cno=1
order by grade asc
8）列出同时选修了‘1’课程和‘2’课程所有学生的学号
select
sno
from sc
where  sno in(select
 sno
from desc
where cno=1) and cno=2
9）列出课程表中全部信息，按先修课的降序排列
select
*
from course
order by cpno asc
10)列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select
sno
from student
where sage>(select avg(sage) from student )
order by sage asc
11）按照出生年份升序显示所有学生的学号，姓名，系别，出生年份及院系,在结果集中列出标题分别指定为“学号，性别，姓名，出生年份，院系”。
select
sno 学号,sname 姓名,ssex 性别,2012-sage 出生年份,sdept 院系
from  student
order by 出生年份 desc
12）显示所有学生院系（要求不能重复，不包括空值），并在结果集中增加一个字段“院系规模”，其中若该院规模》=5则该字段值为“规模很大”，若该字段人数大于等于4小于5则该字段值为“规模一般”，若该字段人数大于等于2小于4则该字段值为“规模稍小”，否则显示规模很小。
select distinct
sdept,
case  when count(*)>5 then '规模很大'
      when count(*)>=4 and count(*)<5 then '规模一般'
when count(*)>=2 and count(*)<4 then '规模稍小'
          else '条件不明'
          end as 院系规模
from  student
where sdept is not null
group by sdept
13）按照课程号，成绩降序显示课程成绩在70-80之间的学生的学号，课程号，成绩。
select distinct
sno,cno,grade
from  sc
where grade between 70 and 80
order by sno asc,grade asc
14)显示学生信息表中的学生总人数及平均年龄，在结果列表中标题分别为“学生总人数，平均年龄”
select
count(sno) 学生总人数,avg(sage) 平均年龄
from  student
15)显示选修课程数大于3的各个学生的选修课程数（聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。）
select distinct
sno,count(*) 课程数
from sc
group by sno
having count(*)>3
15)按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩
错误代码：
select distinct
cno,count(sno) 总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from sc
where sno is not NULL  and grade is not null
group by cno
order by to_number(cno) desc
修改后的代码:
select
cno,count(sno) 总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from sc
where sno is not NULL  and grade is not null
group by cno
order by cast(cno as int)  desc

选做题
17）显示平均成绩大于‘200515001’的学生平均成绩的各个学生的学号，平均成绩
错误的代码：select
sno,avg(grade) 平均成绩
from sc
where avg(grade)>(select grade from sc where sno='200515001')
group by sno

不理解的错误提示：聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
修改后的代码：
select
sno,avg(grade) 平均成绩
from sc
group by sno
having avg(grade)>(select avg(grade) from sc where sno='200515001')
18)显示选修各个课程的及格的人数
select distinct  cno,count(sno)  课程及格人数
from sc
where grade>=60 and grade is not null
group by cno

select cno, sum(case when Grade>=60 then 1 else 0 end) as 及格人数
from sc
group by cno
order by cast(cno as int) desc

19)显示选修最多的学生的课程数和选修最少的学生的课程数
select distinct  sno,count(*)  课程数
from sc
group by sno
having count(cno)>=all(select count(cno) from sc group by sno)
or
count(cno)<=all(select distinct count(cno) from sc group by sno)
20）显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称，男生人数，女生人数”）
select distinct Sdept
院系名称,
sum(case when Ssex='男' then 1 else 0 end)
as 男生人数,
sum(case when Ssex='女' then 1 else 0 end)
as 女生人数
from student
group by sdept
21)列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩。
select sno, sum(case when Grade<60 then 1 else 0 end) as 不及格科目数量,avg(grade) 平均成绩
from sc
group by sno
having sum(case when Grade<60 then 1 else 0 end)>=2
order by cast(sno as int) desc

http://s2/middle/753e6cc9gbcec74372ea1&690