2-1 《银行信贷管理系统》台帐管理模块

Create Database BankCreditLoanDB
on Primary
(name='BankCreditLoanDB',
filename='D:\boss\BankCreditLoanDB.mdf')
log on
(name='BankCreditLoanDB_log',
filename='D:\boss\BankCreditLoanDB_log.ldf')

use BankCreditLoanDB
go

Create table T_customer_info
(
cust_id char(10),
cust_name varchar(60),
legal_name varchar(10),
reg_address varchar(60),
post_code char(6),
primary key(cust_id),
)

insert into T_customer_info values('111','你好','零零','1233','999999')

Create table T_agency_info
(
agency_id char(3),
agency_name varchar(10),
agency_level char(2),
agency_phone varchar(20),
primary key(agency_id),
)
insert into T_agency_info values('001','经济','5','11223344')


Create table T_impawn_info
(
borrow_id char(10),
cust_id char(10),
agency_id char(3),
pawn_goods_name varchar(60),
pawn_goods_num int,
contract_date datetime,
primary key(borrow_id),
foreign key(cust_id) references T_customer_info(cust_id),
foreign key(agency_id) references T_agency_info(agency_id),
)
--根据你对逻辑数据模型的理解，分别向三个表中插入一条测试数据（样本数据包含下面题--
--目中使用的数据），其中客户经理编号为“001”；--
insert into T_impawn_info values('1001','111','001','不知道','56','2002-3-4')
--要求邮政编码由 6 位数字组成。--
alter table T_customer_info
add constraint CK_post_code check(post_code like'[0-9][0-9][0-9][0-9][0-9][0-9]')

--查询“XX 公司”质押的物品及数量（说明：“XX 公司”为插入测试数据中的公司名称）；--
select [pawn_goods_name],[pawn_goods_num] from  [dbo].[T_impawn_info]
--统计每个客户经理所经手的质押业务数，查询结果集应包含字段：客户经理姓名、质押业务数；--
select [agency_name],[pawn_goods_num] from [dbo].[T_agency_info],[dbo].[T_impawn_info] where [dbo].[T_agency_info].agency_id=[dbo].[T_impawn_info].agency_id
--创建存储过程 P_customer_info，删除指定客户编号的客户基本信息，同时也删除该客户在质押信息台帐中的所有记录。--
go
create procedure T_customer_info1
@cust_id char(10),
@cust_name varchar(60),
@legal_name varchar(10),
@reg_address varchar(60),
@post_code char(6)
as