山东大学威海第九章实验代码《数据库原理与应用教程 SQL Server》(尹志宇 郭晴 著)

第九章数据库查询实验报告一实验准备

建立学生表

use教学库

go

create table学生

(学生号char(7)primary key,

姓名char(8)not null,

性别char(2),

年龄int,

)

建立选课表

use教学库

go

create table选课

(学生号char(8),

课程号char(16)not null,

成绩int

constraint pk_js primary key(学生号,课程号)

)

建立课程表

use教学库

go

create table课程

(课程号char(8)primary key,

课程名char(16)not null,

学分int

)

学生表插入数据

use教学库

insert into学生(学生号,姓名,性别,年龄)

values ('0101001','王华','男','19'),

('0101002','王通','男','20'),

('0101003','王菲','女','21'),

('0101004','冷芳','男','21'),

('0101005','王密','女','20'),

('0101006','刘浩','女','19'),

('0101007','孙艳','女','19')

选课表中插入数据

use教学库

insert into选课(学生号,课程号,成绩)

values ('0101001','C001','91'),

('0101001','C002','89'),

('0101001','C003','93'),

('0101001','C004','85'),

('0101002','C002','81'),

('0101002','C003','84'),

('0101002','C005','83'),

('0101002','C004','89'),

('0101003','C004','91'),

('0101004','C001','85'),

('0101004','C003','81'),

('0101005','C001','94'),

('0101005','C003','86'),

('0101006','C001','87'),

('0101007','C003','91'),

('0101007','C005','95'),

('0101007','C004','90')

课程表中插入数据

use教学库

insert into课程(课程号,课程名,学分)

values ('C001','计算机网络','3'),

('C002','计算机文化基础','3'),

('C003','专业英语','2'),

('C004','信号与系统','4'),

('C005','数字信号处理','4')

二

1 针对教学库中的三个表完成下面问题（1）统计有学生选修的课程门数

use教学库

select COUNT(distinct课程号)课程门数

from选课

结果

（2）求选修C004课程学生的平均年龄

use教学库

select avg(年龄)平均年龄

from学生,选课

where学生.学生号=选课.学生号and课程号='C004'

(3)求学分为3的每一门课程的学生平均成绩

use教学库

select课程.课程名,avg(成绩)平均成绩

from课程,选课

where课程.课程号=选课.课程号and学分=3

group by课程.课程名

(4)统计每一门课程的学生选修人数，超过3人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，而人数相同按降序排列

use教学库

select课程号,COUNT(*)as选修人数from选课

group by课程号

having count(*)>3

order by COUNT(*)desc,课程号

(5)检索学号比王菲同学大，而年龄比他小的学生姓名use教学库

select姓名from学生

where学生号>(select学生号from学生where姓名='王菲')

and年龄<(select年龄from学生where姓名='王菲')

（6）检索姓名以王打头的所有学生的姓名和年龄

use教学库

select姓名,年龄from学生

where姓名like'王%'

（7）检索成绩为空值的学生学号和课程号

use教学库

select学生号,课程号from选课

where成绩is null

(8)求年龄大于女同学的平均年龄的男生的姓名和年龄

use教学库

select姓名,年龄from学生

where性别='男'and年龄>(select avg(年龄)平均年龄from学生where性别='女')

(9) 求年龄所有大于女同学的平均年龄的男生的姓名和年龄

use教学库

select姓名,年龄from学生

where性别='男'and年龄>all(select avg(年龄)平均年龄from学生where性别='女')

(10)检索所有比王华年龄大的学生的姓名，年龄，性别

use教学库

select姓名,年龄性别from学生

where年龄>(select年龄from学生where姓名='王华')

(11)检索选修课程C001的学生的成绩中最高的学生的学号

use教学库

select学生号from选课

where课程号='C001'and成绩=(select MAX(成绩)from选课

where课程号='C001')