数据库实验报告

南昌航空大学实验报告

2011年 5 月 21 日

课程名称： 数据库原理及应用 实验名称： 实验一 班 级： 090832 学生姓名： 吕宝强 学号： 09046206 指导教师评定： 签名：

1、实验目的

熟悉SQL Server使用界面，掌握使用SSMS和SQL语句创建并设置数据库。

2、实验内容

使用SQL语句完成下题

（1） 使用CREATE DATABASE语句创建一个名为“st”数据库，要求①

主要数据文件：逻辑文件名为“stdata1”,实际文件名为

“stdata1.mdf”②事务日志文件：逻辑文件名为“stlog1”,实际文件名为“stlog1.ldf”③初始容量均为1MB，最大容量均为10MB，递增量均为1MB。

create database st on primary (name='stdata1',

Filename='D:\est\\stdata1.mdf', Size=4mb,maxsize=10mb,filegrowth=1mb)

Log on

(name='stlog1',

Filename='D:\est\\stlog1.ldf', Size=1mb,maxsize=10mb,filegrowth=1mb

)

（2） 使用CREATE DATABASE语句创建一个名为“student”数据库，要求

①主要数据文件：逻辑文件名为“studentdata1”,实际文件名为

“studentdata1.mdf”②非主要数据文件：逻辑文件名为

“studentdata2”,实际文件名为“studentdata2.ndf”③事务日志文件：逻辑文件名为“studentlog1”,实际文件名为

“studentlog1.ldf”④初始容量均为1MB，最大容量均为10MB，递增量均为1MB。

create database student On primary

(name='studentdata1',

Filename='D:\est\\studentdata1.mdf', Size=4mb,maxsize=10mb,filegrowth=1mb)

(name='studentdata2',

Filename='D:\est\\studentdata2.ndf', Size=4mb,maxsize=10mb,filegrowth=1mb)

Log on

(name='studentlog1',

Filename='D:\est\\studentlog1.ldf', Size=1mb,maxsize=10mb,filegrowth=1mb

)

（3） 使用ALTER DATABASE语句在“st” 数据库中添加一个数据文件和

一个事务日志文件，其逻辑文件名分别为“studentdata3”和“studentlog2”，其实际文件名分别为“studentdata3.ndf”和“studentlog2.ldf”，初始容量均为1MB，最大容量均为10MB，递增量均为1MB；然后将数据文件studentdata3的初始容量增加到5MB，上限增加到20MB。

alter database st add file

(name=studentdata3,

filename='d:\\data\\studentdata3.ndf', size=1mb, maxsize=10mb, filegrowth=1mb) go

alter database st modify file

(name=tudentdata3, size=10mb, maxsize=20mb filegrowth=5mb)

南昌航空大学实验报告

2011年 5 月 21 日

课程名称： 数据库原理及应用 实验名称： 实验二 班 级： 090832 学生姓名： 吕宝强 学号： 09046206 指导教师评定： 签名：

1、实验目的

掌握使用SSMS和SQL语句创建并管理数据库表.

2、实验内容

使用SQL语句完成下题

（1） 使用CREATE TABLE语句创建一个学生表，其中包含以下字段：学号

(int，自动编号，种子为200001，增量为1，定义为主键)；姓名(varchar(8)，not null)；专业(varchar(20)，null，)。

create table 学生表 (

学号int identity(200001,1) primary key clustered, 姓名char(8) not null, 专业char(20) null )

（2） 使用CREATE TABLE语句创建一个成绩表其中包含以下字段：学号

(int，not null)；课程号(int，not null)；成绩(real，null，)。

create table 成绩表 (学号int not null, 课程号int not null, 成绩real null)

（3） 使用alter table语句新增学生表和成绩表之间的外键联系，并新增

对成绩的检查性约束

create table 成绩表 (学号 int not null, 课程号 int not null, 成绩 int null) go

create table 学生表 (

学号 int identity(200001,1)

primary key clustered,

姓名 char(8) not null,专业 char(20) null ) go

alter table 成绩表

add check(成绩>=0 and 成绩<=100),

constraint fk_成绩表 foreign key(学号)references 学生表(学号)

南昌航空大学实验报告

2011年 5 月 21 日

课程名称： 数据库原理及应用 实验名称： 实验三 班 级： 090832 学生姓名： 吕宝强 学号： 09046206 指导教师评定： 签名：

1、实验目的

熟练掌握SELECT语句对数据库表的各项操作.

2、实验内容

1>设有三个关系模式：

业务员（业务员编号，业务员姓名，性别，年龄，月薪） 订单（订单号，日期，客户编号，业务员编号，金额） 客户（客编户号，客户姓名，地址，类别） 用SQL完成下列各题。

（1）检索年龄最大的业务员的姓名和年龄。

select 业务员姓名,年龄from 业务员

where 年龄=

(select max(年龄) from 业务员)

（2）显示所有业务员的姓名和月薪，并按月序排列。

select 业务员姓名,月薪from 业务员

order by 月薪desc

（3）检索月薪在1000到2000之间的业务员的所有信息，用BETWEEN关键词。

select * from 业务员

where 月薪between 1000 and 2000

（4）统计每个客户签订订单的数目和金额。

select 客户编号,count(订单号) as 订单数目, sum (金额)as 总金额 from 订单

group by 客户编号

（5）检所签订两个以上订单的业务员姓名。

select 业务员姓名 from 业务员x,订单y

where x.业务员编号= y.业务员编号 group by 业务员姓名

having count (业务员姓名)>=2

（6）将所有业务员的月薪增加%10.

update 业务员set 月薪=月薪*(1+0.1)

（7）检索超过平均月薪的业务员姓名和年龄。

1.select 业务员姓名,年龄from 业务员

where 月薪>=(select avg (月薪) as 平均月薪from 业务员)

2.select 业务员姓名,年龄from 业务员

group by 月薪,业务员姓名,年龄

having 月薪>=(select avg (月薪) from 业务员)

（8）将签订单总金额超过20000的月薪加%5.

update 业务员set 月薪=月薪*(1+0.05)

where 业务员编号in (select 业务员编号from 订单 group by 业务员编号 having sum(金额)>20000)

（9）写出SQL语句，实现关系代数式：

∏ 业务员，订单号，客户编号，金额（б金额>=2000（业务员*订单*客户））

select 业务员.业务员编号,订单号,客户.客户编号,金额from 业务员,订单,客户

where 金额>=2000 and 业务员.业务员编号=订单.业务员编号 and 订单.客户编号=客户.客户编号

2> 设有三个关系：

职工关系：EMP(ENO,ENAME,AGE,SEX,SCITY),其属性分别表示职工工号，姓名，年龄，性别和籍贯。

工作关系：WORKS(ENO,CNO,SALARY),其属性分别表示职工工号，公司编号和工资。 工作关系：COMP(CNO,CNAME,CITY),其属性分别表示公司编号，公司名称和公司所在城市。

请用SQL语句完成下列各查询。

（1）假设每个职工可在多个公司工作，检索在编号为C4和C8的公司兼职的职工工号和姓名。

SELECT EMP.ENO,ENAME FROM EMP,WORKS

WHERE EMP.ENO=WORKS.ENO AND CNO='C4'AND CNO='C8'

（2）检索职工的籍贯和所工作的公司城市的职工工号和姓名。

SELECT ENO,ENAME FROM EMP,COMP

WHERE EMP.ECITY=COMP.CITY

（3）假设每个职工可以在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示ENO,NUM,SUM_SALARY分别表示工号，公司数目和工资总数。

1.SELECT EMP.ENO,COUNT(WORKS.CNO)AS NUM,COUNT(SALARY)AS SUM_SALARY

FROM WORKS,EMP,COMP

WHERE EMP.ENO=WORKS.ENO AND WORKS.CNO=COMP.CNO

GROUP BY EMP.ENO

2.SELECT WORKS.ENO,COUNT(CNO)AS NUM,COUNT(SALARY)AS SUM_SALARY FROM WORKS

GROUP BY ENO

（4）工号为E6的职工在职工工号多个公司工作，检索在E6职工兼职过的所有公司工作的职工工号。

SELECT ENO FROM WORKS

WHERE ENO=(SELECT CNO FROM WORKS WHERE CNO='E6')

（5）在每一个公司中为50岁以上职工加薪100（若职工为多个公司工作，可重复加）。

UPDATE WORKS SET SALARY=SALARY+100

WHERE ENO IN (SELECT ENO FROM EMP WHERE AGE > 50 )

（6）在EMP关系中WORKS关系中删除年龄大于60岁的职工的元组。

DELETE

FROM WORKS FROM EMP,WORKS

WHERE EMP.ENO=WORKS.ENO AND AGE>60

（7）建立一个有关女职工信息的视图EMP_WOMAN，属性包括ENO,ENAME,CNO,CNAME和SALARY。

CREATE VIEW EMP_WOMAN

AS SELECT EMP.ENO,ENAME,WORKS.CNO,CNAME,SALARY FROM EMP,WORKS,COMP WHERE EMP.ENO=WORKS.ENO AND WORKS.CNO=COMP.CNO AND SEX='女'

（8）有如下关系代数表达式:

∏ CNO,ENO(бCITY='上海'（COMP*WORKS）) 请将其转化成相应的SQL语句。

SELECT WORKS.CNO,ENO FROM WORKS,COMP

WHERE COMP.CNO=WORKS.CNO AND CITY='上海'

（9）检索不为上海公司工作的职工名。

SELECT ENAME FROM EMP

WHERE '上海'NOT IN

(SELECT CNO FROM WORKS WHERE EMP.ENO=WORKS.ENO)

（10）检索在“IMB工作”工作、工资超过1000元的男性职工的工号和姓名。

SELECT EMP.ENO,ENAME FROM EMP,WORKS

WHERE EMP.ENO=WORKS.ENO AND SALARY>1000 AND CNO='IBM公司' AND SEX='男'

南昌航空大学实验报告

2011年 5 月 21 日

课程名称： 数据库原理及应用 实验名称： 实验四 班 级： 090832 学生姓名： 吕宝强 学号： 09046206 指导教师评定： 签名：

1、实验目的

熟悉数据库建立及应用的方法，系统的应用数据库。

2、实验内容

1、数据库的设计

根据实际情况建立E-R数据模型，需要考虑的实体有学生、班级、课程。其中E-R简图如下：

班级 其中班级实体的属性为班级名称、班长学号等；学生属性为学号、姓名等；课程属性为课程号、课程名称；学习联系的属性为学生号、课程号、成绩。其他属性自行设计。 2、数据库的建立

建立数据库及相应的数据库表。 create database student on primary

(name=student,

filename='d:\\sql\\student.mdf', size=3, maxsize=10, filegrowth=1 )

log on

(name=student,

filename='d:\\sql\\student.ldf',

属于 n n 学生 学习 1 m 课程 size=3, maxsize=10, filegrowth=1 )

create table 班级 (

班级名称 nchar(10) primary key clustered,

班长学号 nchar（10） null)

Create table 学生 （学号 nchar(10)

primary key clustered, 姓名 nchar(10) null 成绩 nchar （10）null)

Create table 课程 （课程编号 nchar(10) primary key clustered, 课程名称 nchar(10) null）

create table 学习 (学号 nchar(10) ， 课程编号 nchar(10)， 成绩 int）

班级 班级名称

学生 学号

学习 学号 课程编号 课程

成绩 姓名 成绩 班长学号 课程编号

3、数据库的使用 a. 在数据库表中插入数据 Alter table 学生 add 性别 char

b. 设计SQL语言使其分别满足下列要求： 查找学号为20070101的学生姓名；

Select 姓名 from 学生

where 学号=20070101

课程名称

查找“计算机基础”课程的课程编号； Select 课程编号 from 课程

where 课程名称='计算机基础'

查找20070102号同学学习的课程名；

Select 课程名from 课程

where 学习.课程编号=课程.课程编号 and 学号='20070102'

查找学习“C语言”课程的学生学号；

Select 学号 from 学习

where 学习.课程编号=课程.课程编号 and 课程名称='C

语言'

查找200701班班长的姓名；

Select 姓名 from 学生

where 学号 =(Select 班长学号 from 班级 where 班级名称='200701')

查找成绩高于80分的学生学号；

Select 学号 from 学习

where 学习.课程编号=课程.课程编号 and 成绩>'80'

查找“C语言课程”的平均成绩；

Select ave(成绩) from 学习

Group by 课程编号=(Select 课程编号 from 课程

where 课程名称='c

语言')