数据库基础项目表的练习

一、数据库操作：（20分）
1．创建数据库：（5分）
创建数据库名为LJGL的数据库，其中包含一个主数据文件和一个事务日志文件，要求文件初始大小为2M，其他设置采用默认设置。

数据文件和事务日志文件都保存存在E盘根目录。

2．导入数据：（5分）
将E盘考试文件夹下的LJGL.XLS 文件中的三张表（零件、项目、使用）导入到LJGL数据库中。

3．修改表：（10分）
（1）修改三张表中零件号、项目号列，将其数据类型设置为char__，长度为3；数量、单价列数据类型设置为int，使用时间数据类型是datetime。

（3分）
（2）为零件、项目表设置主键列，根据所设置主键表为使用表设置外键。

（3分）
（3）零件号、项目号、数量、单价设置不允许为空，设置单价、数量必须大于0，使用日期的默认值为当天日期。

（4分）
二、数据编辑（15分）
1．根据各表中的数据规律，向使用表中添加项目五洲酒店今天使用500个零件P3的一条信息。

2．将所有使用表的数量少于100的增加使用数量10。

3．删除项目国际大酒店的信息。

三、查询操作（65分，每题5分）
1．查询所有项目的项目名称、项目负责人，结果保存在a1表中。

select 项目名 ,项目负责人
into a1
from 项目
2．查询单价在1000～2000之间的零件信息，结果保存在a2表中。

select *
into a2
from 零件
where 单价>1000 and 单价<2000
3．在使用表查询2009年的使用信息，查询结果保存在a3 表中。

select * into a3
from 使用
where year(使用时间)=2009
4．查询姓张的所负责的项目名称，结果保存在a4表中。

select 项目名 into a4
from 项目
where 项目负责人 like '张%'
5．查询所有饭店项目的项目信息，结果保存在a5表中。

select * into a5
from 项目
where 项目名 like '%饭店%'
6．统计查询各项目使用的零件种数，结果包括项目号、零件种数，结果保存在a6表中。

select 使用.项目号,count(零件号) as 零件总数
from 使用,项目
where 使用.项目号=项目.项目号
group by 使用.项目号
7．统计查询各零件使用总数量不少于500的零件号、使用总数量，结果保存在a7表中。

select 使用.零件号,sum(数量) as 总数量 into a7
from 使用,零件
where 使用.零件号=零件.零件号
group by 使用.零件号
having sum(数量)>=500
8．查询没有被使用的零件信息，结果保存在a8表中。

select * into a8
from 零件
where 零件号 not in
(select 零件号 from 使用)
9．查询项目国贸大厦使用零件PN2的数量，结果保存在a9表中。

select 零件名称 into a9
from 零件
where 零件号 in
(select 零件号
from 使用
where 项目号 in
(select 项目号
from 项目
where 项目名='国贸大厦'))
10．查询使用最近一天使用零件的零件号，结果保存在a10表中中。

select 零件号 into a10
from 使用
where year(使用时间)=2010
group by 零件号
11．查询项目长城饭店所使用的零件名、使用数量、单价，结果保存在a11表。

select 零件名称,数量,单价
into a11
from 项目,使用,零件
where 项目.项目号=使用.项目号 and 使用.零件号=零件.零件号 and 项目.项目名='长城饭店'
12．创建视图v_sp，查询使用零件PN1的项目号、数量、使用时间。

create view v_sp
as
select 项目号,数量,使用时间
from 使用
where 零件号 in
(select 零件号
from 零件
where 零件名称='PN1')
13．创建存储过程p_kh，输入指定项目人姓名，查询所负责的项目信
息。

create proc p_kh
@xx varchar(20)
as
select *
from 项目
where 项目负责人=@xx
四、备份数据库后上交作品。

注意：所有操作完成后备份数据库，备份文件名为“学号_ljgl ”，前面的学号为学生本的学号，注意不要写错。