实例(vip销售).txt

经典的sql资料

-- 综合题：

-- 1. 创建一个数据库 test



-- 2. 创建表vip(vip_id char(3) 主键,vip_name char(30) not null,vip_tel char(30),vip_opendate datetime not null,vip_closedate  datetime)
   -- 创建表procduct(product_id char(5) 主键,procduct_name char(30) not null,procduct_price decimal(10,2),product_amount	decimal(5) not null,product_close datetime)
   -- 创建表sale(sale_serial char(10) not null,sale_date datetime not null,sale_vip_id char(3) not null,sale_product_id	char(5) not null,sale_num decimal(10) not null)



-- 3. 为sale表sale_vip_id 设置外键fk_vip,参照vip 表的vip_id字段
   -- 为sale表sale_product_id 设置外键fk_proc,参照procduct表的product_id字段


-- 4. 插入表数据
insert into vip values('101','小王','12345678','20070101','20080101');
insert into vip values('102','小张','22345678','20070101','20080101');
insert into vip values('103','小李','32345678','20070101','20090101');
insert into vip values('104','小赵','42345678','20070101','20090101');

insert into product values('10001','白菜',4.2,1111,'20070101');
insert into product values('10002','电视机',4000,9999,'20070102');
insert into product values('10003','衣服',42,1000,null);
insert into product values('10004','电脑',10000,9999,null);
insert into product values('10005','酸奶',10,9999,'20070103');
insert into product values('10006','牛肉干',20,2000,'20070104');
insert into product values('10007','书本',.5,1000,null);
insert into product values('10008','运动鞋',20,2000,'20070105');

insert into sale values('10000001','20070108','101','10002',1);
insert into sale values('10000002','20070108','101','10003',1);
insert into sale values('10000003','20070108','101','10005',2);
insert into sale values('10000004','20070108','101','10006',5);
insert into sale values('10000005','20070108','101','10008',1);

insert into sale values('10000006','20070108','102','10001',1);
insert into sale values('10000007','20070108','102','10003',1);
insert into sale values('10000008','20070108','102','10005',2);
insert into sale values('10000009','20070108','102','10007',5);
insert into sale values('10000010','20070108','102','10008',1);

insert into sale values('10000011','20070108','103','10003',1);
insert into sale values('10000012','20070108','103','10004',1);
insert into sale values('10000013','20070108','103','10007',5);
insert into sale values('10000014','20070108','103','10008',1);

insert into sale values('10000015','20070108','104','10001',1);
insert into sale values('10000016','20070108','104','10003',1);
insert into sale values('10000017','20070108','104','10005',2);
insert into sale values('10000018','20070108','104','10007',5);


-- 5. 向product表插入数据'20001','耳机',40.8,100,NULL
   -- 向product表插入数据'20002','西瓜',2.2,1110,'20070711'


-- 6. 把刚插入的产品名是'西瓜'的记录修改为'南汇西瓜'


-- 7. 删除'20001'号产品


-- 8. 查询 vip号为101的购买商品记录


-- 9. 查询 vip号为101的购买商品总金额


-- 10. 查询 姓名为小张的销售额（使用多表查询）


-- 11. 显示每位vip(显示vip_id即可)销售额，并按销售额降序排序


-- 12. 查询 产品号为10003的销售记录


-- 13. 查询 产品号为10003的销售总额


-- 14. 显示每个产品(显示产品号即可)销售额，并按销售额降序排序


-- 15. 查询显示vip姓名、产品号、销售额小计


-- 16. 查询显示vip号、产品名称、销售额小计


-- 17. 查询显示vip姓名、产品名称、销售额小计


-- 18. 查询所有vip销售额大于4000的记录


-- 19. 查询所有产品销售额大于4000的记录


-- 20. 使用子查询，查询test数据库中购买了"白菜"的vip编号、vip姓名


-- 21. 使用标准SQL嵌套语句，查询test数据库中没有购买为'10001'的vip编号、vip姓名


-- 22. 查询test数据库中,购买商品超过2件的vip编号、vip姓名


-- 23. 查询 姓名为小张的购买商品记录（使用多表查询）


-- 24. 删除小张的购买记录(使用子查询)


-- 25. 创建一个视图v_p1，查询每个产品的销售额，显示字段为产品名称、销售额,并查看


-- 26. 修改视图v_p1，查询每个产品的销售额大于3000的记录，显示字段产品名称、销售额,并查看


-- 27. 创建一个唯一索引in_p，在product表的product_name字段上


-- 28. 创建一个存储过程pro_p1，查询显示每个vip用户的用户名、产品名称、购买数量（不带参数的存储过程）


-- 29. 创建一个存储过程pro_p2，查询显示某指定vip_id的销售记录（带参数的存储过程）


-- 30. 创建一个触发器t_p，当插入或修改数据的sale_num<1，拒绝插入，并验证












-- 综合题参考答案：

-- 1. 创建一个数据库 test
create database test

-- 2. 创建表vip(vip_id char(3) 主键,vip_name char(30) not null,vip_tel char(30),vip_opendate datetime not null,vip_closedate  datetime)
   -- 创建表procduct(product_id char(5) 主键,procduct_name char(30) not null,procduct_price decimal(10,2),product_amount	decimal(5) not null,product_close datetime)
   -- 创建表sale(sale_serial char(10) not null,sale_date datetime not null,sale_vip_id char(3) not null,sale_product_id	char(5) not null,sale_num decimal(10) not null)

create table vip(
vip_id   char(3) PRIMARY KEY,   	--VIP 卡号
vip_name   char(30) not null, 		--姓名
vip_tel    char(30),	  		--联系电话
vip_opendate   datetime not null,	--启用日期
vip_closedate  datetime
)

create table product(
product_id	char(5) PRIMARY KEY,
product_name	char(40) not null,	--品名
product_price	decimal(10,2) not null, --单价
product_amount	decimal(5) not null,    --数量
product_close	datetime)

-- 创建sale表
create table sale(
sale_serial	char(10) not null,
sale_date	datetime not null,   	--日期
sale_vip_id	char(3) not null,	--VIP客户
sale_product_id	char(5) not null,	--商品ID
sale_num	decimal(10) not null)

-- 3. 为sale表sale_vip_id 设置外键fk_vip,参照vip 表的vip_id字段
   -- 为sale表sale_product_id 设置外键fk_proc,参照procduct表的product_id字段
alter table sale add constraint fk_vip foreign key (sale_vip_id) references vip(vip_id)
alter table sale add constraint fk_product foreign key (sale_product_id) references product(product_id)


-- 4. 插入表数据
insert into vip values('101','小王','12345678','20070101','20080101');
insert into vip values('102','小张','22345678','20070101','20080101');
insert into vip values('103','小李','32345678','20070101','20090101');
insert into vip values('104','小赵','42345678','20070101','20090101');

insert into product values('10001','白菜',4.2,1111,'20070101');
insert into product values('10002','电视机',4000,9999,'20070102');
insert into product values('10003','衣服',42,1000,null);
insert into product values('10004','电脑',10000,9999,null);
insert into product values('10005','酸奶',10,9999,'20070103');
insert into product values('10006','牛肉干',20,2000,'20070104');
insert into product values('10007','书本',.5,1000,null);
insert into product values('10008','运动鞋',20,2000,'20070105');

insert into sale values('10000001','20070108','101','10002',1);
insert into sale values('10000002','20070108','101','10003',1);
insert into sale values('10000003','20070108','101','10005',2);
insert into sale values('10000004','20070108','101','10006',5);
insert into sale values('10000005','20070108','101','10008',1);

insert into sale values('10000006','20070108','102','10001',1);
insert into sale values('10000007','20070108','102','10003',1);
insert into sale values('10000008','20070108','102','10005',2);
insert into sale values('10000009','20070108','102','10007',5);
insert into sale values('10000010','20070108','102','10008',1);

insert into sale values('10000011','20070108','103','10003',1);
insert into sale values('10000012','20070108','103','10004',1);
insert into sale values('10000013','20070108','103','10007',5);
insert into sale values('10000014','20070108','103','10008',1);

insert into sale values('10000015','20070108','104','10001',1);
insert into sale values('10000016','20070108','104','10003',1);
insert into sale values('10000017','20070108','104','10005',2);
insert into sale values('10000018','20070108','104','10007',5);


-- 5. 向product表插入数据'20001','耳机',40.8,100,NULL
   -- 向product表插入数据'20002','西瓜',2.2,1110,'20070711'

insert into product values('20001','耳机',40.8,100,NULL)
insert into product values('20002','西瓜',2.2,1110,'20070711')

-- 6. 把刚插入的产品名是'西瓜'的记录修改为'南汇西瓜'
update product set product_name='南汇西瓜' where product_id='20002'

-- 7. 删除'20001'号产品
delete from course where c_number='20001'


-- 8. 查询 vip号为101的购买商品记录
select * from sale where sale_vip_id='101'


-- 9. 查询 vip号为101的购买商品总金额
select s.sale_vip_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id and s.sale_vip_id='101'
group by s.sale_vip_id

-- 10. 查询 姓名为小张的销售额（使用多表查询）
select v.vip_name,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p,vip as v 
where s.sale_product_id=p.product_id and s.sale_vip_id=v.vip_id and v.vip_name='小张'
group by v.vip_name


-- 11. 显示每位vip(显示vip_id即可)销售额，并按销售额降序排序
select s.sale_vip_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by s.sale_vip_id 
order by sum(p.product_price*s.sale_num) desc

-- 12. 查询 产品号为10003的销售记录
select * from sale where sale_product_id='10003'

-- 13. 查询 产品号为10003的销售总额
select s.sale_product_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id and s.sale_product_id='10003'
group by s.sale_product_id


-- 14. 显示每个产品(显示产品号即可)销售额，并按销售额降序排序
select s.sale_product_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by s.sale_product_id 
order by sum(p.product_price*s.sale_num) desc


-- 15. 查询显示vip姓名、产品号、销售额小计
select v.vip_name,s.sale_product_id,(p.product_price*s.sale_num) as 销售额小计 
from sale as s,vip as v,product as p 
where s.sale_vip_id=v.vip_id and s.sale_product_id=p.product_id 

-- 16. 查询显示vip号、产品名称、销售额小计
select s.sale_vip_id,p.product_name,(p.product_price*s.sale_num) as 销售额小计 
from sale as s,product as p 
where s.sale_product_id=p.product_id 

-- 17. 查询显示vip姓名、产品名称、销售额小计
select v.vip_name,p.product_name,(p.product_price*s.sale_num) as 销售额小计 
from sale as s,vip as v,product as p 
where s.sale_vip_id=v.vip_id and s.sale_product_id=p.product_id 

-- 18. 查询所有vip销售额大于4000的记录
select s.sale_vip_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by s.sale_vip_id 
having sum(p.product_price*s.sale_num)>4000

-- 19. 查询所有产品销售额大于4000的记录
select s.sale_product_id,sum(p.product_price*s.sale_num) as 销售额 from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by s.sale_product_id 
having sum(p.product_price*s.sale_num)>4000

-- 20. 使用子查询，查询test数据库中购买了"白菜"的vip编号、vip姓名
select vip_id,vip_name from vip  where vip_id in 
(select sale_vip_id from sale as s,product as p 
where s.sale_product_id=p.product_id and p.product_name='白菜')

-- 21. 使用标准SQL嵌套语句，查询test数据库中没有购买为'10001'的vip编号、vip姓名
select vip_id,vip_name from vip where vip_id not in 
(select sale_vip_id from sale as s where s.sale_product_id='10001')

-- 22. 查询test数据库中,购买商品超过2件的vip编号、vip姓名
select vip_id,vip_name from vip 
where vip_id in( select vip_id from sale 
group by vip_id 
having count(distinct sale_product_id)>2)

-- 23. 查询 姓名为小张的购买商品记录（使用多表查询）
select v.vip_id,v.vip_name,s.sale_product_id,s.sale_num 
from vip as v,sale as s 
where v.vip_id=s.sale_vip_id and v.vip_name='小张'


-- 24. 删除小张的购买记录(使用子查询)
delete from sale where sale_vip_id=(select vip_id from vip where vip_name='小张')

-- 25. 创建一个视图v_p1，查询每个产品的销售额，显示字段为产品名称、销售额,并查看
create view v_p1 as select p.product_name,sum(p.product_price*s.sale_num) as 销售额 
from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by p.product_name
select * from v_p1

-- 26. 修改视图v_p1，查询每个产品的销售额大于3000的记录，显示字段产品名称、销售额,并查看
alter view v_p1 as select p.product_name,sum(p.product_price*s.sale_num) as 销售额 
from sale as s,product as p 
where s.sale_product_id=p.product_id 
group by p.product_name
having sum(p.product_price*s.sale_num)>3000
select * from v_p1


-- 27. 创建一个唯一索引in_p，在product表的product_name字段上
create index in_p on product(product_name)

-- 28. 创建一个存储过程pro_p1，查询显示每个vip用户的用户名、产品名称、购买数量（不带参数的存储过程）
create procedure pro_p1 as 
select v.vip_name,p.product_name,s.sale_num 
from sale as s,product as p,vip as v
where s.sale_vip_id=v.vip_id and s.sale_product_id=p.product_id

pro_p1

-- 29. 创建一个存储过程pro_p2，查询显示某指定vip_id的销售记录（带参数的存储过程）
create procedure pro_p2 @v_id char(3) as select * from sale 
where sale_vip_id=@v_id

pro_p2 101


-- 30. 创建一个触发器t_p，当插入或修改数据的sale_num<1，拒绝插入，并验证
create trigger t_p on sale for insert,update as
if update(sale_num) 
begin
  if(select sale_num from inserted)<1
    begin
    print '数量不能小于 1'
    rollback transaction
    end
end
insert into sale values('10000019','20070108','104','10002',0);  -- 提示出错信息，拒绝插入
insert into sale values('10000019','20070108','104','10002',2);  -- 正常插入