实验交互式SQL实验报告

精品文档
试验一交互式 SQL
一实验目的
1．熟悉数据库的交互式SQL工具。

2．熟悉通过 SQL对数据库进行操作。

3．完成作业的上机练习。

二实验工具 SQL Server 2005
利用 SQL Server 2005 及其交互式查询来熟悉SQL语句。

三实验内容和要求
1．在 SQLServer 2005 中建立一个数据库，进行实验所要求的各种操作，所
有的 SQL操作均在建立的新库里进行；
2．根据以下要求认真填写实验报告，记录所有的实验用例的SQL语言；3.1 数据定义
（1）熟悉基本表的创建、修改及删除。

（2）熟悉索引的创建和删除。

3.2 数据操作
（1）完成各类查询操作（单表查询，连接查询，嵌套查询）等；
（2）完成各类更新操作（插入数据，修改数据，删除数据）；
3.3 视图的操作
视图的定义（创建和删除），查询，更新（注意更新的条件）。

3.4 具体操作内容
在 MS SQL Server 中创建学生－课程数据库，要求有学生表（Student）、课程表 (Course)和选课表（ SC），向三个表中分别插入相关数据，再用 SQL 语句完成一下要求的查询。

1.使用 SQL 语言创建下面的三个表。

表一：学生信息表（ Student）
列名说明数据类型约束
Sno学号字符串，长度为 10非空
Sname姓名字符串，长度为 20非空
Ssex性别字符串，长度为 10非空
Sage年龄字符串，长度为 10非空
Sdept所在系字符串，长度为 10非空
表二：课程信息表（ Course）
列名说明数据类型约束
Cno课程号整形非空
Cname课程名字符串，长度为 20非空
Cpno先行课整型允许为空
Ccredit学分整型非空
表三：学生选课信息表（SC）
.
精品文档
列名说明数据类型约束
Sno学号字符串，长度为 10主码，引用 Student 的外码Cno课程号整型主码，引用 Course 的外码Grade成绩字符串，长度为 10取值 0~100
2.在以上的三个表中，分别插入下面的数据；
表一：学生信息表（ Student）
Sno Sname Ssex Sage Sdept
200215121李勇男20CS
200215122刘晨女19CS
200215123王敏女18ＭＡ
200215125张立男19IS
表二：课程信息表（ Course）
Cno Cname Cpno Ccredit
1数据库54
2数学2
3信息系统14
4操作系统63
5数据结构74
6数据处理2
7PASCAl 语言64
表三：学生选课信息表（SC）
Sno Cno Grade
200215121192
200215121285
200215121388
200215122290
200215122380
3.根据上面的三种表，写出完成如下查询功能的SQL 语句：
(1)查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名
(2)查询选修了课程的学生学号
(3)查询年龄在 20~23 岁（包括 20 岁和 23 岁）之间的学生的姓名、系别、年龄
(4)查询计算机科学系（ CS）、数学系（ MA ）和信息系（ IS）学生的姓名和性别
(5)查询以“ DB_ ”开头，且倒数第 3 个字符为 i 的课程的详细情况
(6)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排
列
(7)查询选修 1号课程的学生最高分数、最低分数、平均分数
(8)查询选修了 3 门课程的学生学号
(9)查询每一门课的间接先修课（即先修课的先修课）
(10) 查询选修 2 号课程且成绩大于等于90 分的所有学生
(11)查询与‘刘晨’在同一个系学习的学生
.
(12)查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄
(13)将一个新学生元组（学号： 200215128；姓名：陈冬；性别：男；所在系：IS；年龄：
18 岁）插入到Student 表中
(14)将学生 200215121 的年龄改为 22 岁
(15)删除学号为 200215128 的学生记录
(16)建立信息系学生的视图
(17)在信息系学生的视图中找出年龄小于20 岁的学生
(18)将信息系学生视图 is_Student中学号为200215122的学生姓名改为“刘辰”
四实验报告
4.1 实验环境：
Windows XP
Microsoft SQL server Management Studio 2005
4.2 实验内容与完成情况：
(1)查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名
SELECT Sname NAME, 'Year of Birth:'BIRTH , 2004 - Sage BIRTHDAY , LOWER( Sdept ) DEPARTMENT FROM Student;
(2)查询选修了课程的学生学号
SELECT DISTINCT Sno FROM SC ;
（3）查询年龄在20~23 岁（包括20 岁和 23 岁）之间的学生的姓名、系别、年龄
SELECT Sname , Sdept , Sage FROM Student WHERE Sage BETWEEN 20 AND 23 ;
（4）查询计算机科学系（CS）、数学系（ MA ）和信息系（IS）学生的姓名和性别
SELECT Sname , Ssex FROM Student WHERE Sdept IN( 'CS' , 'MA' , 'IS' );
（5）查询以“ DB_ ”开头，且倒数第 3 个字符为i 的课程的详细情况
.
SELECT * FROM Course WHERE Cname LIKE 'DB\_%__'ESCAPE'\' ;
(6)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT * FROM Student ORDER BY Sdept , Sage DESC;
(7)查询选修 1号课程的学生最高分数、最低分数、平均分数
SELECT MAX( Grade ) MAX, MIN ( Grade ) MIN , AVG( Grade ) AVG FROM SC WHERECno = '1' ;
(8)查询选修了 3 门课程的学生学号
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)> 3;
(8))查询每个学生及其选修课程的情况
SELECT Student. Sno , Sname , Ssex , Sage , Sdept , Cno , Grade FROM Student, SC WHERE Student. Sno = SC. Sno ;
(9)①查询每一门课的间接先修课（即先修课的先修课）
SELECT first. Cno , second . Cpno FROM Course first, Course second
WHERE first. Cpno =second . Cno ;
②查询每一门课的间接先修课（即先修课的先修课）（消除有空的行）
SELECT first. Cno , second . Cpno FROM Course first, Course second
WHERE first. Cpno =second . Cno and second . Cpno is not null;
(10) 查询选修 2 号课程且成绩大于等于90 分的所有学生
① select Student . Sno , Sname , Ssex , Sage , Sdept from Student, SC where
Student . Sno =SC. Sno and Cno = '2'and Grade>= 90 ;
② select Sno , Sname , Ssex , Sage , Sdept from Student where Sno =( select Sno
from Sc where Cno ='2'and grade>= '90' );
.
(11)查询与‘刘晨’在同一个系学习的学生
①select Sno , Sname , Sdept from Student where Sdept in ( select Sdept from
Student where Sname =' 刘晨 ' );
②select S1 . Sno , S1 . Sname , S1 . sdept from Student S1, student S2where
S1 . Sdept=s2 . Sdept and S2. Sname = ' 刘晨 ' ;
③select Sno , Sname , Sdept from Student where Sdept=( select Sdept from student where Sname =' 刘晨 ' );
④select Sno , Sname , Sdept from Student S1 where exists( select* from Student
S2 where S2 . Sdept= S1 . Sdept and S2. Sname = ' 刘晨 ' );
(12)查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄
①s electSname , Sage from Student
where Sage <all( select Sage from Student where Sdept = 'CS' )
and Sdept <> 'CS' ;
②s electSname , Sage from Student
where Sage <( select min ( Sage ) from Student where Sdept ='CS' )
and Sdept <> 'CS' ;
（13）将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：
18 岁）插入到 Student 表中
insert into Student( Sno , Sname , Ssex , Sdept, Sage )
values( '200215128',' 陈冬' ,'男' ,'IS' ,'18');
select* from Student;
（14）将学生 200215121 的年龄改为 22 岁
update Student set Sage = '22'where Sno = '200215121';
select* from Student;
.
（15）删除学号为 200215128 的学生记录
delete from Student where Sno = '200215128';
select* from Student;
（16）建立信息系学生的视图
create view is_Student as select Sno , Sname , Sage from Student where
Sdept ='IS' ;
(17)在信息系学生的视图中找出年龄小于20岁的学生
select Sno , Sage from is_Student where Sage < 20 ;
（18 ）将信息系学生视图is_Student中学号为200215122的学生姓名改为“刘辰”
update is_Student set Sname = ' 刘辰 ' where Sno ='200215122';
select* from is_Student;
（19 ）删除信息系学生视图is_Student中学号为200215125的记录
delete from is_Student where Sno = '200215125';
select* from is_Student;
4.3 出现的问题：
在写第（ 9）个查询功能的时候用
select o,second.Cpno from Course first,Course second where first.Cpno=o;
这句 SQL语句输出的结果中有的课程没有先修课。

4.4 解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：
原因：没有排除掉那些没有先修课的课程，所以查询结果中会有空值。

解决方案：这样写就会把没有先修课的课程给排除掉。

.
select o,second.Cpno from Course first,Course second where first.Cpno=o and second.Cpno is not null;
五实验感想
通过这次实验课，让我对数据库的书写规范有了更深刻的印象，同时对数据查询，数
据更新，视图的创建、删除、查询、更新有了进一步的了解，对他们的认识不再是那么抽象。

现在感觉，理论加实践这种学习方法真的很好，对于巩固知识很有效。

.。