实验二、数据库查询
一、实验目的
1、本次实验了解sql语言的SELECT语句对数据的查询。
2、学会SQL server 2000的查询分析器中用SQL语言对表中的数据进行简单查询、连接查
询、嵌套查询和组合查询等。
二、实验内容
1、创建三个关系模式：student（学生表）：
字段名中文意思数据类型及宽度约束条件
sno 学号Char(4) 主键
sname 姓名Char(8) 允许空
sage 年龄Int 允许空
Sdept 系别datetime 允许空
程序：create table student(
sno学号char(4) primary key,
sname姓名char(8),
Sage年龄int,
sdept系别datetime,
);
Course（课程表）：
字段名中文意思数据类型及宽度约束条件
cno 课程编号Char(4) 主键
cname 课程名称Char(50) 允许空
cpno 先行课Char(4) 允许空
Credit 学分Int 允许空
程序：create table course(
cno课程编号char(4) primary key,
cname课程名字char(50),
cpno先行课char(4),
credit学分int,
);
Grade（成绩表）：
字段名中文意思数据类型及宽度约束条件
cno 课程编号Char(4) 外码（参照课程表）、
主属性
sno 学号Char(4) 外码（参照学生表）、
主属性
score 分数Int 允许空
程序：create table grade(
cno课程编号char(4),
sno学号char(4),
score分数int,
primary key(cno课程编号,sno学号),
foreign key(cno课程编号)references course(cno课程编号),
foreign key(sno学号)references student(sno学号),
);
2、分别往上述三张表中插入不少于20条的记录。
程序：
student表
alter table student alter column sdept系别char(20);
insert into student values('01','aiai','19','math');
insert into student values('02','bibi','18','math');
insert into student values('03','cici','21','English');
insert into student values('04','didi','20','math');
insert into student values('05','eiei','17','English');
insert into student values('06','fifi','22','computer');
insert into student values('07','gigi','18','math');
insert into student values('08','hihi','19','computer');
insert into student values('09','iiii','19','English');
insert into student values('10','jiji','20','math');
insert into student values('11','kiki','21','chinese');
insert into student values('12','lili','19','math');
insert into student values('13','mimi','19','English');
insert into student values('14','nini','22','math');
insert into student values('15','oioi','18','English');
insert into student values('16','pipi','19','math');
insert into student values('17','qiqi','19','dance');
insert into student values('18','riri','19','math');
insert into student values('19','sisi','21','music');
insert into student values('20','titi','19','math');
course表
insert into course values('01','math','e','98');
insert into course values('02','math','m','98');
insert into course values('04','chinese','math','89');
insert into course values('05','math','n','97');
insert into course values('06','computer','math','90');
insert into course values('07','music','m','98');
insert into course values('08','music','math','78');
insert into course values('09','math','m','97');
insert into course values('10','computer','m','80');
insert into course values('11','dance','m','98');
insert into course values('12','math','m','68');
insert into course values('13','English','math','98');
insert into course values('14','math','m','88');
insert into course values('16','music','math','98');
insert into course values('17','math','k','98');
insert into course values('18','chinese','m','98'); insert into course values('19','math','p','98');
insert into course values('20','math','j','98');
grade表
insert into grade values('02','01',99);
insert into grade values('04','05',99);
insert into grade values('05','02',96);
insert into grade values('06','03',88);
insert into grade values('07','06',65);
insert into grade values('08','04',49);
insert into grade values('09','20',99);
insert into grade values('10','07',58);
insert into grade values('11','13',74);
insert into grade values('12','07',85);
insert into grade values('16','08',79);
insert into grade values('13','10',89);
insert into grade values('14','12',85);
insert into grade values('17','11',96);
insert into grade values('19','13',56);
insert into grade values('18','14',83);
insert into grade values('20','18',69);
insert into grade values('05','02',96);
insert into grade values('06','03',88);
insert into grade values('07','06',65);
insert into grade values('08','04',49);
insert into grade values('09','20',99);
insert into grade values('10','01',58);
insert into grade values('11','01',74);
insert into grade values('12','01',85);
insert into grade values('16','01',79);
insert into grade values('13','01',89);
insert into grade values('14','01',85);
insert into grade values('17','01',96);
insert into grade values('19','01',56);
insert into grade values('18','01',83);
insert into grade values('20','01',69);
3、查询计算机（math）系学生的学号和姓名。程序：
select sno学号,sname姓名from student where sdept系别='math';
4、查询每个学生的学号、姓名、选修课的课程名以及成绩。
程序：
select student.sno学号,sname姓名,https://www.doczj.com/doc/887531092.html,o课程编号,grade.score分数
from student,course,grade where student .sno学号=grade .sno学号and
course .cno课程编号=grade .cno课程编号;
5、求课程号为C1（02）课程的成绩低于张三（cici）的学生的学号和成绩。程序：
select distinct grade.sno学号,score分数
from student,grade
where https://www.doczj.com/doc/887531092.html,o课程编号='02'and score分数<(
select score分数
from grade,student
where grade.sno学号=student.sno学号and sname姓名='cici'and cno课程编号='02');
6、求同时选修了C2（05）课程和C3（06）课程的学生的学号。
程序：
select sno学号
from student
where sno学号in(select sno学号
from grade
where cno课程编号='05' and sno学号in(select sno学号
from grade
where cno课程编号='06'));
7、求计算机（math）系年龄在20岁以下的学生的详细信息。
程序：
select sno学号,sname姓名,sage年龄
from student
where sdept系别='math'and sage年龄in (select sage年龄
from student
where sage年龄<'20');
8、查询所有学生的详细信息，结果按学号降序排列。
程序：
select sno学号,sname姓名,sage年龄
order by sno学号desc;
9、查询每个系的学生的人数。查询每个系学生的平均年龄。
程序：
select count(sdept系别) as 每系人数, avg(sage年龄) as 每系平均年龄from student,grade
where student.sno学号= grade.sno学号
and https://www.doczj.com/doc/887531092.html,o课程编号in (select cno课程编号from course)
group by sdept系别,sage年龄;
10、查询计算机(math)系学生的最大年龄和最小年龄。
程序：
select max(sage年龄) as 最大年龄,min(sage年龄) as 最小年龄
where sdept系别='math';
11、查询每个学生的平均分。
程序：
select student.sno学号,sname姓名,avg(score分数) as 平均分
from grade,student
where student.sno学号=grade.sno学号
group by student.sno学号,sname姓名;
12、查询每个学生的选课门数。
程序：
select student.sno学号,sname姓名,count(https://www.doczj.com/doc/887531092.html,o课程编号) as 选课门数
from grade ,student,course
where student.sno学号=grade.sno学号and https://www.doczj.com/doc/887531092.html,o课程编号=https://www.doczj.com/doc/887531092.html,o课程编号group by student.sno学号,sname姓名;
13、查询每门课程的选课人数。
程序：
select cno课程编号,count(sno学号) as 选课人数
from grade
group by cno课程编号;
14、查询选修课程C1（06）并且成绩在80分以上的学生的学号。程序：
select sno学号
from grade
where cno课程编号='06' and score分数>=80;
15、查询所有选修了C2（11）课程的学生的学号。程序：
select sno学号
from grade
where cno课程编号='11';
16、查询所有选了课的学生的学号。
程序：
select sno学号from grade where sno学号in
(select distinct sno学号from course);
17、查询选修人数大于2的课程号。
程序：
select cno课程编号
from grade
group by cno课程编号
having count(sno学号)>2;
18、查询选修人数大于2并且平均成绩大于80分的课程号。
程序：
select cno课程编号
from grade
group by cno课程编号
having count(sno学号)>2 and avg(score分数)>88;
19、查询与“刘晨”(cici)在同一个系学习的学生。
程序:
select sname姓名
from student
where sdept系别in (select sdept系别
from student
where sname姓名='cici');
20、查询选修了课程名为“信息系统”（English）的学生学号和姓名。
程序：
select student.sno学号,sname姓名
from student,course,grade
where student.sno学号=grade.sno学号and https://www.doczj.com/doc/887531092.html,o课程编号=https://www.doczj.com/doc/887531092.html,o课程编号and cname课程名字='English';
21、查询每个学生成绩高于其平均分的课程号。
程序：
select distinct https://www.doczj.com/doc/887531092.html,o课程编号
from course,grade
where grade.score分数>(select avg(grade.score分数)
from grade
where https://www.doczj.com/doc/887531092.html,o课程编号=https://www.doczj.com/doc/887531092.html,o课程编号);
22、查询其他系中比计算机科学（math）系任意一个(其中某一个)学生年龄小的学生姓
名和年龄。
程序：
select sname姓名,sage年龄
from student
where sage年龄< any (select sage年龄
from student
where sdept系别= 'math')
and sdept系别<> 'math';
23、查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
程序：
select sname姓名,sage年龄
from student
where sage年龄< (select min(sage年龄)
from student
where sdept系别='math'
);
24、查询所有选修了1（08）号课程的学生姓名。
程序：
select student.sname姓名
from student,grade
where https://www.doczj.com/doc/887531092.html,o课程编号='08'and student.sno学号=grade.sno学号;
25、查询没有选修1（08）号课程的学生姓名。
程序：
select student.sname姓名
from student,grade
where student.sno学号=grade.sno学号and cno课程编号not in(select cno课程编号
from grade
where cno课程编号='08');
三、实验总结
本次实验是对数据库查询分析器的简单应用。为以后进一步掌握SQL server2000的其他功能打下坚实的基础。