SQL Server经典案例问答~！

2008-06-25 18:13:19

　标签：SQL 案例　　　[推送到技术圈]

SQL Server经典案例问答~！

1.要求在yuangong表上增加一列为录入员,并将该字段设为默认约束,值为:张红.请用SQL语句写出从创建默认约束到删除的一系列过程.

答：

新建yuangong表
create table yuangong
(
姓名 varchar(20)not null
)

添加列名：录入员
alter table yuangong add 录入员 varchar(10)

新建默认值约束 DK_lry
alter table yuangong add constraint DK_lry default '张红' for 录入员

进行测试
insert into yuangong(姓名)values ('张正涛')

查看张红是否自动添加到录入员列
select * from yuangong

删除默认约束 DK_lry
alter table yuangong drop constraint DK_lry

进行测试
insert into yuangong (姓名) values ('张涛')

查看
select * from yuangong

2.现在你们公司在北京设置了一个专卖店，这个专场店只能针对北京五大区的用户进行销售。也就是说填写客户资料时，客户所在地只能是海淀区，朝阳区，通州区、崇文区、顺义区,并且客户邮编必须正确（全部是数字）,每一条记录都会有一个录入日期字段，取当前日期，请自拟建表实现，使用SQL语句写出实现过程

答：

新建表yuangong
create table yuangong
(
    姓名 Varchar(11) NOT NULL,
    地址 varchar(10) not null,
    邮编 Varchar(20) not null,
    日期 datetime DEFAULT GETDATE()
)

建立邮编的check约束
ALTER TABLE table2 add constraint CK_YB check (邮编 like '[0-9][0-9][0-9][0-9][0-9][0-9]')

进行测试
insert into table2 (姓名,邮编) values ('测试1',100009)
insert into table2 (姓名,邮编) values ('测试2',1000098)
insert into table2 (姓名,邮编) values ('测试3',中文邮编)

建立地址check约束
alter table yuangong add constraint CK_DZ check (地址='海淀' or 地址='朝阳' or 地址='通州' or 地址='崇文' or 地址='顺义')

进行测试
insert into yuangong (地址)values ('大兴')
insert into yuangong (地址)values ('海淀')

3.创建用于存储收货人资料的recipient表。下面是要存储到表中的收货人资料

--创建表
create table Recipient
(    OrderNo char(6) not null,
    userName varchar(20)not null,
    Address varchar(20) null,
    City char(15) null,
    CountryId char(3) null,
    ZipCode char(10) null,
    Phone char(15) null
)

考虑上面的表结构：
就以下问题参考这些表结构：
a.创建Category(种类)表。在创建表时实施下面的数据完整性规则：
CategoryID属性应作为主健
Category属性应为唯一，但不是主健
Description的描述可存储NULL值

答：

create table Category
(
    CategoryID char (6) primary key not null,
    Category varchar(10) not null,
    Description varchar(20)
)

alter table Category add constraint UK_ZL unique(CategoryID)

b.创建ToyBrand(商标)表。在创建表时实施下面的数据完整性规则：
BrandID属性应作为主健
BrandName属性应为唯一，但不是主健

答：

create table ToyBrand
(
    ToyBrandID char (6) primary key not null,
    BrandName varchar(10) not null,

)
alter table ToyBrand add constraint UK_SB unique(ToyBrandID)

c.创建Toys表。在创建表时实施下面的数据完整性规则：
ToysID属性应作为主健
ToyName和ToyDescription属性不可取NULL值
玩具的siToyQoh现有数量应在0到200之间
玩具的最低年龄LowerAge缺省为1
CategoryId属性的值应出现在Category表中

答：

create table toys
(
    ToysID char (6) primary key not null,
    ToyName varchar(10) not null,
    ToyDescription varchar(20) not null,
    SiToyQoh char(10) not null,
    constraint CK_SL check (siToyQoh>=0 and siToyQoh<=200),
    LowerAge int default 1,
    BrandID char (6),
    CategoryID char (6)

)

alter table toys add constraint FK_Category foreign key(CategoryID) references Category(Categoryid)
on update cascade on delete cascade

d.修改Toys表。实施下面的数据完整性规则：
BrandID属性的值应出现在ToyBrand表中

答：

alter table toys add constraint FK_ToyBrand foreign key(BrandID) references toyBrand(toybrandid)
on update cascade on delete cascade

4. 现有两个表学生表和学生借书记录表
结构如下：
Create Table Student   --学生表
(
       StudentID int primary key，      --学号
       studentname varchar(10)       -- 学生姓名
)
     Create Table BorrowRecord   --学生借书记录表
(
       BorrowRecord int identity(1,1),      --流水号
       StudentID     int ,                   --学号
       BorrowDate    datetime,               --借出时间
       ReturnDAte    Datetime,               --归还时间
)

要求使用触发器实现以下功能

A．如果更改了学生的学号,希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
B．如果该学生已经毕业，希望删除他的学号的同时，也删除它的借书记录。

可以使用外键约束或是触发器来实现

答：
外键约束
alter table BorrowRecord add constraint FK_studentID foreign key(studentID) references Student(studentID)
on update cascade（级联更新-针对题目A）
on delete cascade（级联删除-针对题目B）

测试：
student表中写入两名学生
insert into student (studentID,studentName)values ('1','张三')
insert into student (studentID,studentName)values ('2','李四')

BorrowRecord表中写入这两名学生的借书时间
insert into BorrowRecord (studentID,BorrowDate,ReturnDate )values ('1','2006-05-01','2006-08-02')
insert into BorrowRecord (studentID,BorrowDate,ReturnDate )values ('2','2007-04-01','2007-06-02')

更改张三的学号为10
update student set studentid=10 where studentName='张三'

查看结果
select * from student
select * from BorrowRecord
删除学员张三
delete from student where studentname ='张三'

查看结果
select * from student
select * from BorrowRecord

5. 要求建立2个表：
cust_test 和 order_test

cust_test: CustomerID char(5) PK                   order_test: CustomerID char(5)--对应关系
           Custcity        所在城市                            Orderid    PK     --订单序号
           Custname        产品名称                            OrderNames        --订购的产品名称
           CStatus    int 库存状态                            OStatus    int    --状态
           Cstorage   int 库存量                              Orders     int    --定购
           Cdate      date 日期                                Odate      date   --日期
题目1：
在cust_test表中建立删除触发器，实现上述2表的级联删除。
从库存表中删除一个产品，相应的订单也会删除

create table cust_test
(
     customerID char(5) primary key,
     custcity   varchar(20),
     custname   varchar(5),
     cstatus    int,
     cstorage   int,
     cdate       datetime
)

create table order_test
(
     customerID char(5) ,
     orderid    char(5) primary key,
     ordername varchar(20),
     ostatus    int,
     ostorage   int,
     odate      datetime
)

create trigger del_CP
on cust_test for delete
as
begin
if exists (select * from deleted)
begin
delete from order_test where CustomerID in (select CustomerID from deleted )
end
end

题目2：
在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。
create trigger in_DD
on order_test for insert
as
begin
if exists (select orders from inserted)
begin
update cust_test set Cstorage= Cstorage-inserted.orders where CustomerID=inserted.crustomerid
end
end

题目3：
在order_test表上建立一个更新触发器，规定订单日期(Odate)不能修改。

答：
Create trigger odate
on order_test for update
as
begin
if update(odate)
begin
rollback
print '抱歉，订单日期无法修改，请与管理员联系'
end
end

题目4：
要求订购的物品一定要在仓库中有的，并且数量足够。

答：
Create trigger TR_SL
on order_test for insert
as
begin
if exists (select * from inserted)
begin
    if 'Orders' > 'Cstorage'
      begin
      Rollback
      end
end
end

本文出自 “絕版男→①號” 博客，谢绝转载！

昵称：
验证码：		点击图片可刷新验证码　　博客过2级，无需填写验证码
内容：