数据库实验示例带答案

示例1

实验一

实验名称：SQL语句的应用（6课时）

一、实验目的

通过使用SQL SERVER企业管理器建立表。模式为人事表、客户表、销售表、销售明细表、产品表。理解数据库模式的概念，理解主键约束、外键约束、UNIQUE约束和CHECK 约束。通过SQL SERVER企业管理器建立表间的约束。将得到的表生成脚本，保存。

二、实验环境

是MS SQL SERVER 2000的中文客户端。

三、实验示例

create table employee1(

emp_no char(5) not null,

constraint emp_nochk check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'),

emp_name char(10) not null,

emp_sex char(1) not null,

constraint emp_sexchk check(emp_sex in ('m','f')),

emp_phone char (13) not null,

constraint emp_phonechk check(emp_phone like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), emp_add varchar(60) not null,

emp_salary char(5) not null,

constraint emp_salarychk check (emp_salary between 60000 and 80000)

)

go

四、实验内容与步骤

1、建立五张表，每张表至少需要20条记录。

（1）/*员工人事表employee */

（3）/*销售主表sales */

（4）/*销货明细表sale_item */

2、建立表的同时创建表的约束。

（1）为每张表建立主键约束。

（2）通过拖放操作加入外键。

（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

（4）为销售主表sales中的发票编号字段建立UNIQUE约束。

3、通过快捷菜单得到脚本。

五、实验报告

实验二

实验名称：使用SELECT、UPDA TE/INSERT/DELETE语句。（2课时）

一、实验目的

理解SELECT语句的操作和基本使用方法，熟练使用UPDA TE/INSERT/DELETE语句进行表操作。

二、实验环境

三、实验示例

1、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。

select emp_name,title,sex

from employee

where title='经理'or title='职员'and sex='F'

2、选取销售数量最多的前5条订单订单号、数量。

select top 5 with ties order_no,sup_id,qty

from sale_item

order by qty desc

3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。select prod_id,qty,unit_price,(qty*unit_price)tot_amt

from sale_item

order by tot_amt desc

四、实验内容与步骤

1、查找所有经理的姓名、职称、薪水。

select emp_name,title,salary from employee where title='经理';

2、查找出姓“王”并且姓名的最后一个字为“功”的员工。

select*from employee where emp_name like'王_功'

3、添加一条记录至employee表（用INSERT…..INTO）。

insert into employee values('E1014','a14','m','办公','经理','2012-01-20','1999-12-23',60000,'北京');

4、将每个员工的薪水上调3%。

update employee set salary =salary*(1+0.03);

5、查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。

select emp_name,dept,title,addr from employee where sex='f'and (addr='上海'or addr='北京');

6、在表sales中挑出销售金额大于等于10000元订单。

select*from sales where tot_amt>=10000;

7、选取订单金额最高的前10%的订单数据。

select top 10 percent*from sales order by tot_amt desc;

8、查找出职称为经理或职称为职员的女员工的信息。

select*from employee where title='经理'or title='职员'and sex='f';

9、删除sales表中作废的订单（其发票号码为I000000004），其订货明细数据也一并删除。

delete from sales where invoice_no='I000000004';

sales 表和sale_item 要建立关联，删除原则设为“层叠”

10、计算出一共销售了几种产品。

select count(distinct prod_id ) from sale_item ;

11、显示sale_item表中每种个别产品的订购销售金额总和，并且依据销售金额由大到小排

列来显示出每一种产品的排行榜。

select prod_id,sum(qty*unit_price)as销售金额from sale_item group by prod_id order by sum(qty*unit_price)desc;

12、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

select month(order_date)as月份,prod_id,sum(qty*unit_price)as销售金额from sale_item group by prod_id ,month(order_date);

五、实验报告

实验名称：表连接JOIN（2课时）

一、实验目的

理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。二、实验环境

三、实验示例

1、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、

单价。

select a.prod_id,a.qty,a.unit_price,b.prod_name

from sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/

on (a.prod_id=b.pro_id) and a.qty>2

order by a.prod_id

2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr

from employee as a inner join employee as b

on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)

四、实验内容与步骤

1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

select

a.emp_name,a.sex,a.title,a.dept,a.salary,a.addr,

b.emp_name,b.sex,b.ti tle,b.dept,b.salary,b.addr

from employee as a join employee as b

on(a.emp_no!=b.emp_no)and(a.emp_name>b.emp_name)and(a.dept=b.dept) and(a.addr=b.addr) and a.sex = 'm' and b.sex = 'm';

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join sale_item as b on a.prod_id=b.prod_id order by prod_id ;

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、

数量、单价。

select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;

4、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价

高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a left join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;

inner join left join right join full join

五、实验报告

实验名称：SELECT的嵌套使用，实现复杂查询（2课时）

一、实验目的

掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。

二、实验环境

三、实验示例

1、由employee表中查找出薪水最高的员工信息。

select *

from employee

where salary=

(select max(salary )

from employee )

2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接每一张订单的金额”的所有订单。

select*from sales where tot_amt >all

(select tot_amt

from sales

where sale_id='E0107'and order_date='2012-01-02')

order by tot_amt

3、利用begin...end语句实现数据查询，查询出employee表中所有女职工的平均工资和

住址在＂上海市＂的所有女职工的平均工资

begin

select avg(salary)

from employee

where sex like'f'

union

select avg(salary)

from employee

where sex like'f'and addr like'上海'

end

四、实验内容与步骤

1、由sales表中查找出销售金额最高的订单。

select*from sales

where tot_amt=

(select max(tot_amt )

from sales )

2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接任一张订单的金

额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

select sale_id,tot_amt from sales where tot_amt >all

(select tot_amt

from sales

where sale_id='E0107'and order_date='2012-01-02')

order by tot_amt

3、找出公司女业务员所接的订单。

select*

from sales

where sale_id in

(select emp_no

from employee where sex='f')

select*from sales a join employee b on a.sale_id=b.emp_no and b.sex='f' order by sale_id

select a.order_no,a.cust_id,a.sale_id,a.tot_amt from sales a,employee b where a.sale_id=b.emp_no and b.sex='f'

4、找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。

select*from employee where emp_name in

(select emp_name from employee group by emp_name having

count(emp_name)>1

)order by emp_name,emp_no;

select*from employee as a where exists

(select * from employee b where a.emp_no!=b.emp_no and

a.emp_name=

b.emp_name )

order by emp_name,emp_no

5、找出目前业绩未超过200000元的员工。

select*from employee where emp_no in

(select sale_id from sales where tot_amt<50000)

6、计算公司内各个部门的工资支出总和。

select dept,sum(salary)from employee group by dept

7、计算每一产品销售数量总和与平均销售单价。

select prod_id,sum(qty),avg(unit_price)from sale_item group by

prod_id

五、实验报告

实验五

实验名称：视图的建立与使用（2课时）

一、实验目的

掌握视图的定义与工作原理。

二、实验环境

三、实验示例

1、

create view emp_view as

select emp_no,emp_name,salary

from employee

where sex='f'

2、

create view sale_item_view as

select sales.order_no,sales.order_date,prod_id,qty

from sales,sale_item

where sales.order_no=sale_item.order_no

/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。*/

insert into sale_item_view(order_no,order_date)

values(10010,'1996/12/20')

insert into sale_item_view(prod_id,qty)

values('p0010',8)

四、实验内容与步骤

1、创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。

2、对视图添加一条记录数据。（注意：分别查看customer表和该视图的结果。）

3、删除视图中所有姓“王”的客户数据。

4、通过视图修改表内某一客户的姓名。

5、有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、

订单号、销售总金额。

6、将上述视图中订单号为10001的记录的销售总金额改为60000。

7、给上述视图添加一条记录数据。

8、删除上述视图。

五、实验报告

实验六

实验名称：存储过程建立与调用（含带参存储过程的建立与调用）（2课时）

一、实验目的

理解存储过程的概念、建立和调用方法。

二、实验环境

三、实验示例

1、模糊查询

create procedure sp_empname @E_name varchar(10) as

select a.emp_name,a.dept,b.tot_amt

from employee a inner join sales b

on a.emp_no=b.sale_id

where a.emp_name like @E_name

go

exec sp_empname '陈%'

2、利用存储过程计算出’E0014’业务员的销售总金额。

create procedure sp_saletot @E_no char(5),@p_tot int output as

select @p_tot=sum(tot_amt)

from sales

where sale_id=@E_no

go

declare @tot_amt int

exec sp_saletot E0014, @tot_amt output

select @tot_amt

四、实验内容与步骤

1、利用存储过程，给employee表添加一条业务部门员工的信息。

2、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓

名、销售金额。

3、利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。

4、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金

额。

5、利用存储过程计算出订单编号为10003的订单的销售金额。

五、实验报告

实验七

实验名称：触发器的建立与使用（2课时）

一、实验目的

理解触发器的概念、定义方法和触发条件。

二、实验环境

采用Client/Server模式，学生为客户端，是MS SQL SERVER 2000的中文客户端。登录用户名是：学号；密码为：****** 。用户名和密码以任课老师给出为准。

三、实验示例

(一)After类型触发器

1.创建AFTER-INSERT触发器：在sale_item表上创建dml触发器inssale_item_trg,当向sale_item插入一条记录时，--检查prod_id 是否在product中，order_no是否在sales表中，只要一项不存在，则给出错误提示，并撤销插入操作。

USE company

GO

CREATE TRIGGER inssale_item_trg

ON sale_item

FOR INSERT

AS /* 这触发器定义要执行的操作*/

IF EXISTS(SELECT * FROM inserted a

WHERE (a.prod_id NOT IN(SELECT prod_id FROM product)

OR a.order_no NOT IN(SELECT order_no FROM sales)))

BEGIN

RAISERROR('插入的数据违背了数据的一致性',16,1) /* 错误提示*/

ROLLBACK TRANSACTION /* 事务回滚*/

END;

GO

*/

insert into sale_item(order_no,prod_id)values('24','21')

2.创建AFTER-DELETE触发器：在表sales上创建触发器delsales_trg 当从sales表中删除一条记录时，检查sale_item中是否存在被删记录的order_no,如果存在，则从sale_item表中删除该记录。

/*

USE company

GO

CREATE TRIGGER delsales_trg

ON sales

FOR DELETE

AS /* 为触发器定义要执行的操作*/

DELETE sale_item WHERE sale_item.order_no IN

(SELECT order_no FROM deleted)

GO

*/

delete from sales where order_no='24'

写一个允许用户一次只删除一条记录的触发器。

/*

create trigger tr_emp

on employee for delete as

declare @row_cnt int

select @row_cnt=count(*)from deleted

if @row_cnt>1

begin

print'此删除操作可能回删除多条人事表数据!!!'

rollback transaction

end

*/

delete from employee

where sex='m'/*结果：所影响的行数为*/

3 创建AFTER-UPDA TE触发器，在product表上创建触发器updproduct_trg,当对表product 的记录进行修改时，触发器将给出修改时间信息。

USE company

GO

CREA TE TRIGGER updproduct_trg

ON product

FOR UPDA TE

AS /* 为触发器定义要执行的操作*/

PRINT '修改的时间为:'+CONVERT(char, getdate(),110) /* 显示修改的时间信息*/

GO

update product set prod_name='sql'where prod_id='20'

（二）Instead of类型触发器

1替代触发器能够使无法更新的视图支持更新功能。基于多表的视图必须使用Instead of 触发器来支持基表数据的增、删、改操作。

建立视图：

Create View View_prod_saleitem as

SELECT dbo.product.prod_id, dbo.product.prod_name, dbo.sale_item.order_no, dbo.sale_item.qty, dbo.sale_item.unit_price, dbo.sale_item.order_date

FROM dbo.product INNER JOIN

dbo.sale_item ON dbo.product.prod_id = dbo.sale_item.prod_id

USE company

GO

CREATE TRIGGER InsViewprod_saleitem_Trg

ON View_prod_saleitem

INSTEAD OF INSERT

AS

BEGIN /* 定义触发器的行为*/

DECLARE @prod_id char(5),@prod_name char(20) /* 定义局部变量*/ DECLARE @order_no int,@qty int,@unit_price numeric(7,2) ,@order_date datetime

/* 从逻辑表inserted中选择相应的数据存放到变量中*/

SELECT

@prod_id=prod_id,@prod_name=prod_name,@order_no=order_no,@qty=qty,@un it_price=unit_price,@order_date=order_date

FROM inserted

/* 将相应变量值插入到两个基表中*/

INSERT INTO product

(prod_id,prod_name)

VALUES(@prod_id,@prod_name)

INSERT INTO sale_item

(order_no,prod_id,qty,unit_price,order_date)

VALUES (@order_no, @prod_id, @qty,@unit_price,@order_date)

END;

GO

*/

insert into View_prod_saleitem values('26','javaee',50,100,23.5,'2012-12-25')

示例2

数据库说明：

这是一个大学入学注册管理系统，包含五张表

Registered_students学生选修

Rooms教室

Students学生

students表描述学生信息

id唯一标识学生，major是专业，current_credits是该学生已修的学分数

classes表描述课程信息

department是开课系，course是课程号，department和course唯一决定该课程

max_students是该课程能容纳的学生数，current_students是当前选修的学生数

num_credits是该课程的学分数，room_id是所在的教室位置（和rooms表相关联）rooms教室信息

room_id唯一标识教室，building是所在的教学楼，room_number是房间号

number_seats是教室的座位数

registered_students 学生课程的成绩

student_id是学生号（和students表关联），department和course描述具体课程

grade是该门课的成绩

1．查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）2．查询所有学生所有课程的成绩

3．查询特定专业的所有学生名字（比如history专业）

4．列出所有专业的学生名字

5．查询cs系开的课所在教室的位置信息

6．查询所有系开的课所在教室的位置信息

7．统计musi c专业的学生总数和学分数

8．统计所有专业的学生总数和学分数

9．在students表中新加一个学生

10.该学生选了一门新课（比如his系开的301课），请改变相应的表

11.该学生修完新课，成绩为B，请改变相应表

12 .一个学生毕业，请删除该学生所有相应信息

13找出所有姓中以S.开头的学生。

14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。

15找出所有教室的座位数，最大教室的座位数、最小座位数。

16找出各大楼的最大教室，最小教室，平均座位数、座位总数。

17找出各课程尚可选修的人数。

18找出开课最多的系。

19找出开课最少的系。

20找出选课最多的学生。

21找出选课最少的学生。

22找出不及格的学生。

23找出各课程平均分以下的学生。

24找出各系所占教室的座位数。

25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。

26找出选修了HIS系开的所有课程的学生。

参考答案：

1查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）select first_name last_name Grade from Registered_students and Students

where first_name=david and last_name=dinsmore and students.ID=Regjested_students.Student_id.

2查询所有学生所有课程的成绩

select ID course Grade from Registered_students and Students where students.ID=Regjested_students.Student_id.

3查询特定专业的所有学生名字（比如history专业）

select Firstname Lastname from students where major=”history”

4列出所有专业的学生名字

select major Firstname lastname from students group by major (order by major asc?)

5查询cs系开的课所在教室的位置信息

select course roomid building roomnumber from classes and rooms

where classes.roomid =rooms.roomid and department=”cs”加引号

6查询所有系开的课所在教室的位置信息

select department course building roomnumber from classes and roomes

where classes.roomid=rooms.roomid group by department

7统计music专业的学生总数和学分数

select totalstudent tatalcredits from major_stats where major=”music”

8统计所有专业的学生总数和学分数

select* from major_stats group by major

9在students表中新加一个学生

insert into students (id, firstname, lastname, major, currentcredits ) values(“4445”,”m”,”ml”,”cs”,”133”)

10该学生选了一门新课（比如his系开的301课），请改变相应的表

insert into registered_students(studentid, department,course) values(“4445”,”his”,”301”)

update classes set currentstudents+=1 where department=”his” and course=”301”

11该学生修完新课，成绩为B，请改变相应表

Update registeredstudents set grade=”B” where studentid=”4445”

Update students set currentcredits+=2 where studentid=”4445”

update classes set current_students=current_students-1

where department="HIS" and course="301"

12一个学生毕业，请删除该学生所有相应信息

delete from students

where id=1205

delete from regidtered_students

where student_id=1205

update major_stats

set total_students=total_students-1,total_credits=total_credits-12

where major_stats.major="Computer Science"

13找出所有姓中以S.开头的学生。

Select *

From students

Where Last_name like “S%”

14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。

Select major,count(distinct id),max(current_credits),min(current_credits),avg(current_credits),sum(current_credits)

from students

group by major

15找出所有教室的座位数，最大教室的座位数、最小座位数。

Select (Number_seats_Seats,Max(Number_Seats),Min(Number_Seats)

From Rooms

16找出各大楼的最大教室，最小教室，平均座位数、座位总数。

找出各大楼的最大教室，

select building,room_number as biggest_room

from rooms as R

where number_seats>=all(select number_seats

from rooms as T

where R.building=T.building)

最小教室，

select building,room_number as biggest_room

from rooms as R

where number_seats<=all(select number_seats

from rooms as T

where R.building=T.building)

平均座位数、座位总数。

select avg(number_seats),sum(number_seats)

from rooms

group by building

17找出各课程尚可选修的人数。

select course,max_students-current_students as remain

from classes

18找出开课最多的系。

select department

from classes

group by department

having count(course)>=all(select count(course) from classes

group by department)

19找出开课最少的系。

select department

from classes

group by department

having count(course)<=all(select count(course) from classes

group by department)

20找出选课最多的学生。

select student_id,count(course)

from registered_students

group by student_id

having count(course)>=all(select count(course) from registered_students

group by student_id)

21找出选课最少的学生。

select student_id,count(course)

from registered_students

group by student_id

having count(course)<=all(select count(course) from registered_students

group by student_id)

22找出不及格的学生。

select distinct id,first_name,last_name

from registered_students,students

where id=student_id and grade="E"

23找出各课程平均分以下的学生。

select student_id,course,grade

from registered_students as R

where grade<=(select avg(grade)

from registered_students as G

where R.course=G.course)

24找出各系所占教室的座位数。

select department,sum(number_seats)

from classes,rooms

where classes.room_id=rooms.room_id

25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。

creat table history_table

(student_id integer,

first_name char(20),

last_name char(20),

department char(3),

course integer,

register_time date,

primary key (student_id,course),

check (student_id in (select id from students)))

将当前学生、选修信息全部存储到该历史表。

select id,first_name,last_name,department,course,2002-5-1

from students,registered_students as R

where students.id=R.studnet_id and T.student_id=students.id and T.first_name=students.first_name and https://www.360docs.net/doc/e45734847.html,st_naem=https://www.360docs.net/doc/e45734847.html,st_name and T.department=R.department and T.course=R.course

当前学生毕业，将当前学生删除。

delete from history_table

where register_time=2002-5-1

26找出选修了HIS系开的所有课程的学生。

select id,first_name,last_name

from students as S

where not exists ((select course

from classes

where department="HIS")

except

(select R.course

from registered_students as R

where R.student_id=S.id))

或者

Select student_id

From registered_students

Where department=”HIS”

Group by student_id

Having count(student_id)=

（select count(distinct course)

from registered_students

where department=”HIS”

)