学号 12051134 姓名 张宏源 班级 物联网1班

上机实验四——SELECT语句基本格式的使用

一、实习目的：

掌握SELECT的基本使用格式，能使用SQL Server对表作简单查询。

二、实习准备：

1. 复习第三章3.4节中SELECT语句的基本使用格式。

2. 复习SQL中五种库函数：AVG、SUM、MAX、MIN、COUNT；

3. 完成习题三第12题中的各项操作的SQL语句。

三、实习内容：

1. 验证习题三第12题中的各项操作的SQL语句。

①? 找出所有被学生选修了的课程号；

Select Distinct Cno

From Grade

Order by Cno

② 找出01311班女学生的个人信息；

Select *

From Student

where Ssex='女' and Clno=01311

③ 找出01311班、01312班的学生姓名、性别、出生年份；

Select Sname,Ssex,2014-Sage as birthyear

From Student

where Clno='01311'or Clno='01312'

④ 找出所有姓李的学生的个人信息；

Select *

From Student

where Sname like '李%'

⑤ 找出学生李勇所在班级的学生人数；

Select number

FROM Class

where clno=

(select Clno from Student

where Sname='李勇' )

⑥ 找出课程名为操作系统的平均成绩、最高分、最低分；

Select AVG(Gmark),MAX(Gmark),MIN(Gmark)

FROM Grade

where Cno=

(Select Cno from Course where Cname='操作系统' )

⑦ 找出选修了课程的学生人数；

Select COUNT(Sno) as renshu

FROM Grade

⑧ 找出选修了课程操作系统的学生人数。

Select COUNT(Sno) as renshu

FROM Grade

where Cno=

(Select Cno from Course

where Cname='操作系统'

)

2. 试一下以下语句是否正确：

SELECT eno，basepay，service

FROM salary

WHERE basepay<AVG（basepay）

答：不对，聚合函数不应该出现在where语句中，除非该聚合位于HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用

四、上机实验收获（感想）：

更加熟悉了SQL基础语言的运用，实际操作后弥补了理论想象的不足

五、思考题：

什么情况下需要使用关系的别名？别名的作用范围是什么？

1. 简化书写

2. 子查询

3. 为了把先后查询的同一关系区分开来

学号 姓名 班级 上机时间

上机实验五——SELECT语句高级格式和完整格式的使用

一、实习目的：

掌握SELECT语句的嵌套使用方法，能使用SQL Server2000对表作复杂查询。

二、实习准备：

1. 复习第三章3.4节中SELECT语句的高级格式和完整格式的使用。

2. 了解库函数在分组查询中的使用规则；

3. 完成习题三第13、14题中的各项操作的SQL语句。

三、实习内容：

完成以下各项操作的SQL语句：

① 找出与李勇在同一个班级的学生信息；

Select *

FROM Student

where Clno=

(Select Clno from student where Sname='李勇' )

② 找出选修了课程操作系统的学生学号和姓名；

Select Sno,Sname

from student

where sno in

( Select distinct Sno from Grade where Cno = (Select Cno from Course where Cname='操作系统' ) )

③ 找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁） Select *

from student

where Sage in

(Select Sage from Student )

and Sage<25

④ 找出所有没有选修1号课程的学生姓名

Select distinct s.sname

from Student s LEFT JOIN Grade g

on s.sno=g.sno

where CNO != 1

⑤ 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；

Select Sno,Gmark

from grade

where Cno=3

Order by Gmark DESC

⑥ 求每个课程号及相应的选课人数；

Select Cno,count(cno) as 人数

from grade

group by cno

order by cno

⑦查询选修了3门以上课程的学生学号。

Select sno

from Grade

group by sno

having COUNT(sno)>3

四、上机实验收获（感想）：

五、思考题：

1． 用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？。

UNION 命令只会选取不同的值，UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值

2． 当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？为什么？

选用连接词查询。 因为连接词查询,效率比嵌套查询高

3． 库函数能否直接使用在：SELECT选取目标、HAVING子句、WHERE子句、GROUP BY

列名中？

库函数可以使用在SELECT选取目标、HAVING字句、WHERE子句，不能使用在GROUP BY列名

学号 姓名 班级 上机时间

上机实验六——SQL的存储操作

一、实习目的：

掌握用交互式SQL语句对已建基本表进行存储操作：修改、删除、插入，加深对数据的完整性的理解。

二、实习准备：

1. 复习数据的完整性，在进行数据的修改、删除、插入时，要注意保持数据的一致性。

2. 复习第三章3.5节UPDATE、DELETE、INSERT语句与子查询的结合使用；

3. 完成习题三15题中的各项操作的SQL语句。

三、实习内容：

1、完成以下各项操作的SQL语句：

①将01311班的全体学生的成绩置零

update Grade set Gmark=0

where Sno in

(select Sno from Student

where Clno='01311')

)

②删除01311班全体学生的选课记录；

Delete from Grade

where sno in

(

select sno

from Student

where clno='01311'

)

③学生李勇已退学，从数据库中删除有关他的记录；

delete from Grade

where Sno in

(select Sno from Student

where Sname='李勇')

update Class set Number=Number-1

where Clno in

(select Clno from Student

where Sname='李勇')

update Class set Monitor=case

where Clno in

(select Clno from Student

where Sname='李勇')

delete from Student

where Sname='李勇'

④对每个班，求学生的平均年龄，并把结果存入数据库；

alter table Class add pingjunnianlin smallint null

update Class

set pingjunnianlin = (select AVG(Sage) from Student where

Clno='00311')

where Clno='00311'

update Class

set pingjunnianlin = (select AVG(Sage) from Student where

Clno='00312')

where Clno='00312'

update Class

set pingjunnianlin = (select AVG(Sage) from Student where

Clno='01311')

where Clno='01311'

2、把所有工程师的基本工资（Basepay）增加100，试一试以下的UPDATE语句对不对：

UPDATE salary

SET basepay=basepay+100

WHERE eno in

(SELECT eno

FROM employee

WHERE title=’工程师’

)

对的

四、上机实验收获（感想）：

五、思考题：

DROP命令和DELETE命令的本质区别是什么？ DROP是删除表

DELETE是删除表中的记录

学号 姓名 班级 上机时间

上机实验七——视图的建立及操作

一、实习目的：

掌握创建、删除和查询视图的方法，验证可更新视图和不可更新视图。

二、实习准备：

1. 复习第三章3.6节视图；

2. 完成习题三第16题中的各项操作的SQL语句。

3. 了解可更新视图和不可更新视图。

三、实习内容：

1. 验证习题三第16题中各项操作的SQL语句；

① 建立01312班选修了1号课程的学生视图Stu_01312_1；

create view Stu_01312_1

AS select *

From student,Grade

where clno='01312' and sno='1'

② 建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；

create view Stu_00312_2

as

select * from Student

where Sno in (select Sno from Grade where Grade.Cno='1'and

Grade.Gmark<='60') and Clno='00312'

③ 建立视图Stu_year，由学生学号、姓名、出生年份组成。

create view Stu_year

as

select sno as '学号',sname as '姓名','出生年份'=year(getdate())-sage from Student

④ 查询19xx年以后出生的学生姓名

Create view v_day

As select Sname from Student

where Sage<=year(getdate())-1990

⑤ 查询01312班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。

Create view v_avg

As

select Student.Sno,Sname, year(getdate())-sage as '出生年份' from Student inner join Grade on student.sno=grade.sno

where Grade.Cno='1' and Student.Clno='00312' and gmark<60

2. 建立一视图Class_grade，用来反映每个班的所有选修课的平均成绩。并对其进行

更新操作。

create view Class_grade

as

select Student.clno as clno,AVG(Grade.gmark) as Gmark_avg from Student full join Grade on student.sno=grade.sno

Group by Student.clno

四、上机实验收获（感想）：

学号 姓名 班级 上机时间

上机实验八*——完整性约束的实现

一、实习目的：

掌握SQL中实现数据完整性的方法，加深理解关系数据模型的三类完整性约束。

二、实习准备：

1. 复习第4章“完整性约束SQL定义”；

2. 完成习题四第10题中四个表结构的SQL定义。

3. 了解SQL Server中实体完整性、参照完整性和用户自定义完整性的实现手段。

三、实习内容：

验证习题四第10题四个表结构的SQL定义。

create table course (

cno char(1) primary key,

cname varchar(20) not null,

credit smallint check（credit>=1 and credit<=6） )

clno char(5) primary key,

speciality varchar(20) not null,

inyear char(4) not null,

number integer check(number>1 and number<100), monitor char(7) )

create table student3 (

sno char(7) primary key, sname varchar(20) not null,

ssex char(2) not null default('男'), sage smallint check(sage>14 and sage<65), clno char(5) not null references class(clno) on delete cascade on update cascade )

alter table class

add constraint fk_monitor foreign key (monitor) references student(sno) on delete no action

sno char(7) not null references student(sno) on delete cascade on update cascade,

cno char(1) not null references course(cno) on delete cascade on update cascade,

gmark decimal(4,1) check(gmark>0 and gmark <100), primary key (sno,cno) )

四、上机实验收获（感想）：

五、思考题：

SQL Server中提供了哪些方法实现实体完整性、参照完整性和用户自定义完整性。 实体完整性：not null， unique 和 primary key

参照完整性：foreign key 的级联操作策略（级联更新、级联删除、置空） 用户定义：check约束

第二篇：数据库的上机实验试题

102.请为下边关系

图书（学号，姓名，性别，系部，书号，书名，作者，出版社，借书日期，还书日期,是否续借）;

医疗（患者编号,患者姓名,患者性别,医生编号，医生姓名，诊断日期，诊断结果，恢复情况，科室编号，科室名称，医生电话，医生职称）;

教学(学号，姓名，性别，出生日期，系部，系主任，任课教师，课程号，课程名，成绩，学分，教师职务，联系电话);

仓库（管理员号，管理员姓名，性别，年龄，商品号，商品名，类别，购入日期，库存量，单位，数量，库房编号，库房名称）

化分为第三范式？并指出各关系主码及可能存在的外码。

1、图书（学号，姓名，性别，系部，书号，书名，作者，出版社，借书日期，还书日期,是否续借）

图书（书号，书名，作者，出版社）主码：书号

读者（学号，姓名，性别,系部）主码：学号

借书情况（书号, 学号,借书日期,还书日期,是否续借）主码：书号，借书日期 外码：书号，学号

2、医疗（患者编号,患者姓名,患者性别,医生编号，医生姓名，诊断日期，诊断结果，恢复情况，科室编号，科室名称，医生电话，医生职称）

患者（患者编号,患者姓名,患者性别，）主码：患者编号

医生（医生编号，医生姓名）主码：医生编号

科室（科室编号，科室名称，医生电话，医生职称）主码：科室编号

患者情况（患者编号,患者姓名, 诊断日期，诊断结果，恢复情况）主码：患者编号，诊断日期 外码：患者编号,患者姓名

3、教学(学号，姓名，性别，出生日期，系部，系主任，任课教师，课程号，课程名，成绩，学分，教师职务，联系电话)

学生（学号，姓名，性别，出生日期，系部）主码：学号

课程（课程号，课程名，成绩，学分，任课教师）主码：课程号

系（系部，系主任）主码：系部

教师（教师职务，联系电话，任课教师，系部）主码：教书职务 外码：系部

4、仓库（管理员号，管理员姓名，性别，年龄，商品号，商品名，类别，购入日期，库存量，单位，数量，库房编号，库房名称）

管理员（管理员号，管理员姓名，性别，年龄）主码：管理员号

商品（商品号，商品名，类别，购入日期，单位，数量）主码：商品号

库房（库房编号，库房名称，库存量，管理员号）主码：库房编号 外码：商品号

库存（商品号，库房编号，库存量）主码：商品号，库房编号 外码：商品号，库房编号

103、请设计一个宾馆管理系统数据模型,要求：分析并写出系统有可能涉及的全部数据表（字段名称，类型，长度等等），并指出各表主码及相互间的关联.（提示：系统涉及了房间信息，客户信息，管理人员信息，住宿信息，收费信息等相关内容）

[客房登记表:Roomlogin]

（宾馆客房管理系统的数据流——客人信息）   

（宾馆客房管理系统的数据流——入住登记）

（宾馆客房管理系统的数据流——客房信息）

（宾馆客房管理系统的数据流——房间状态）