实验二 - 交互式SQL实验报告

数据库实验报告 软件101 李丹 201001014109

试验一 交互式SQL

一 实验目的

1．熟悉数据库的交互式SQL工具。 2．熟悉通过SQL对数据库进行操作。 3．完成作业的上机练习。

二 实验工具SQL Server 2005

利用SQL Server 2005及其交互式查询来熟悉SQL语句。

三 实验内容和要求

1．在SQL Server 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）

1

数据库实验报告 软件101 李丹 201001014109

列名 说明 数据类型 Sno 学号 字符串，长度为10 Cno 课程号 整型 Grade 成绩 字符串，长度为10 2.在以上的三个表中，分别插入下面的数据； 表一：学生信息表（Student） Sno Sname Ssex 男 女 女 男 200215121 李勇 200215122 刘晨 200215123 王敏 200215125 张立 表二：课程信息表（Course） Cno Cname 1 2 3 4 5 6 7 数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAl语言 20 19 18 19 约束 主码，引用Student的外码 主码，引用Course的外码 取值0~100 Sage Sdept CS CS ＭＡ IS Ccredit 4 2 4 3 4 2 4 Cpno 5 1 6 7 6 表三：学生选课信息表（SC） Sno Cno Grade 200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80 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) 查询与‘刘晨’在同一个系学习的学生

2

数据库实验报告 软件101 李丹 201001014109

(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的课程的详细情况

3

数据库实验报告 软件101 李丹 201001014109

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 WHERE Cno='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');

4

数据库实验报告 软件101 李丹 201001014109

(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 S2 where

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)查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄 ①select Sname ,Sage from Student

where Sage 'CS';

②select Sname ,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;

5

本文来源：https://www.bwwdw.com/article/s3sd.html