SQL实验 - SQL实验五—数据库单表查询综合 - 《SQL数据库》

实验目的
实验环境
实验学时
实验要求
实验内容及步骤

实验目的

掌握SELECT语句的基本语法和查询条件表示方法；
掌握查询条件表达式和使用方法；
掌握GROUP BY 子句的作用和使用方法；
掌握HAVING子句的作用和使用方法；
掌握ORDER BY子句的作用和使用方法。

实验环境
已安装SQL Server 2008开发版的计算机；
具有局域网环境,有固定IP;
实验学时

1学时
实验要求

了解数据库查询；
了解数据库查询的实现方式；
完成实验报告；
实验内容及步骤
定义学生选课数据库，在学生选课库中使用数据导入的方式生成下面三张表。
S (Snum,Sname,Sex,Sage,Sphone,Dnum)
C (Cnum,Cname, Cfreq)
SC(Snum,Cnum,Score)
Snum表示学号,Sname表示姓名,Sex表示性别,Sage表示电话,Dnum表示系编号,Cnum表示课程号,Cname表示课程名,Cfreq表示学分,Score表示分数.
S

Snum	Sname	Sex	Sage	Sphone	Dnum
S001	王明	男	19	86824571	D2
S002	李勇	男	23	89454321	D3
S003	刘燕	女	21		D1
S004	王萍	女	23		D1
S005	王佳	男	24	13098765892	D3
S006	赵婷	女	20		D1

Snum	Cnum	Score
S001	C1	83
S001	C2	89
S001	C3	65
S001	C4	85
S001	C5	69
S002	C3	78
S002	C4	75
S005	C1	95
S004	C1	85
S005	C1	92
S005	C3	76

Cnum	Cname	Cfreq
C1	数据库系统原理	4
C2	C程序设计	4
C3	计算机体系结构	3
C4	自动控制原理	2
C5	数据结构	4

以学生选课库中数据为基础，请使用T-SQL 语句实现以下操作：

查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。
```
select Sname,Sname,Sex,Sage from S
where Dnum = 'D2'
```

查询学号为S006的学生的姓名。

select Sname from S
where Snum = 'S006'

查询成绩在60-85之间的学生的学号。

select Snum,Score from SC
where Score between 60 and 85

查询所有姓王，并且姓名为两个字的学生的信息。
```
select * from S
where Sname LIKE '王_'
```
查询选修课程号为‘C1’且成绩非空的学生学号和成绩，成绩按150分制输出（每个成绩乘以系数1.5）。
```
select Snum,(Score*1.5) as Score from SC
where Cnum = 'C1' and Score is not null
```
查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重复。
```
select distinct snum from sc
```
查询选修课程‘C1’的学生学号和成绩，结果按成绩的升序排列，如果成绩相同则按学号的降序排列。
```
select snum,score from sc    
where cnum = 'C1'   
order by  score, snum desc
```

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

select sname from S
where sname  not like '刘%'

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

select sname from student
where sname like '沈__'

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

select snum,sname,sex, sage,year(getdate())-sage as 'birther year' from S  
where year(getdate())-sage>'1985'

查询出课程名含有“数据”字串的所有课程基本信息；
```
select * from C 
where cname like '%数据%'
```
显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
```
select * from s   
where snum like'_______[1,2,3,4,9]%' or snum like'________[1,2,3,4,9]%'
```
列出选修了‘C1’课程的学生，按成绩的降序排列；
```
select * from sc 
where Cnum = 'C1' order by Score desc
```

列出同时选修“C1”号课程和“C2”号课程的所有学生的学号；

select cnum from sc   
where cnum = 'C1' and cnum in (select cnum from sc where cnum='C2' )

列出课程表中全部信息，按学分的升序排列；
```
select * from c
order by cfreq
```
列出年龄超过平均值的所有学生名单，按年龄的降序显示；
```
select * from s
where sage > (select avg(sage) from s)
order by sage desc
```
按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
```
select snum as '学号',sname as '姓名',sex as '性别',year(getdate())-sage 出生年月,Dnum as 院系 from s 
order by year(getdate())-sage
```
按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
```
select * from sc    
where Score between 70 and 80  order by cnum desc,score desc
```
显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
```
select count(snum) as '学生总人数',avg(sage) as '平均年龄' from s
```

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

select snum,count(cnum) as cno_count from sc   
group by snum   
having count(cnum)>3

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

select cnum,count(cnum) as 选修课程总人数,max(score) as 最高分,min(score)as 最低分,avg(score)as 平均分 from sc  
group by cnum
order by cnum desc

选做题：

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

select snum,avg(score) as 平均分 from sc
group by snum   
having avg(score)>(select avg(score) from sc where snum = 'S001')

显示选修各个课程的及格的人数;

select  cnum,count(snum) as 人数 from sc 
where score>=60  group by cnum

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

select dnum '院系名称',   
     男生人数 = case when sex = '男' then count(snum) end,    
     女生人数 = case when sex = '女' then count(snum) end
from s group by dnum,sex

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

select snum,avg(score) as 平均分 from sc    
where score<60   
group by snum   
having count(cnum)>=2

SQL实验五—数据库单表查询综合

实验目的

实验环境

实验学时

实验要求

实验内容及步骤

定义学生选课数据库，在学生选课库中使用数据导入的方式生成下面三张表。

以学生选课库中数据为基础，请使用T-SQL 语句实现以下操作：

选做题：