具体思路如下:
1基础信息单盒饮料建一条记录,1x24包装的建一条,1x12包装的建一条,1x24包装物和1x12包装物也各建一条记录
2每条商品信息都有一个打包属性,单盒饮料,包装物之类都是假,成包装的都是真.
3另建一个新表记录打包明细,用商品id做关联.
4 1x24包装饮料的打包明细就是24盒饮料和一个包装物.
说的太多了,如果到这儿你还不明白的话,我就帮不了你了.
解题思路分析:第一步:创建表,确定数据类型,建立约束
--删除数据表
drop table purcase
drop table product
drop table customer
---创建数据表
---解题思路分析:
---第一步:创建表,确定数据类型,建立约束
----创建商品表product
create table product (
productid varchar2(10) ,
productname varchar2(20) NOT NULL,
unitprice number,
category varchar2(20),
provider varchar2(20),
CONSTRAINT pk_productid primary key (productid),
CONSTRAINT CK_unitprice CHECK (unitprice>0)
)
--创建顾客表customer:
create table customer(
customerid varchar2(10),
name varchar2(20) NOT NULL,
location varchar2(20),
CONSTRAINT pk_customerid primary key(customerid)
)
--创建购买记录表 purcase:
create table purcase(
customerid varchar2(10),
productid varchar2(10),
quantity number,
CONSTRAINT FK_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) on delete cascade,
CONSTRAINT FK_productid FOREIGN KEY(productid) REFERENCES product(productid) on delete cascade,
CONSTRAINT CK_quantity CHECK(quantity BETWEEN 0 AND 20)
)
---测试数据的编写:
insert into product (productid,productname,unitprice,category,provider)
values('M01','佳洁士',8.00,'牙膏','宝洁')
insert into product (productid,productname,unitprice,category,provider)
values('M02','高露洁',6.50,'牙膏','高露洁')
insert into product (productid,productname,unitprice,category,provider)
values('M03','洁诺',5.00,'牙膏','联合利华')
insert into product (productid,productname,unitprice,category,provider)
values('M04','舒肤佳',3.00,'香皂','宝洁')
insert into product (productid,productname,unitprice,category,provider)
values('M05','夏士莲',5.00,'香皂','联合利华')
insert into product (productid,productname,unitprice,category,provider)
values('M06','雕牌',8.00,'洗衣粉','纳爱斯')
insert into product (productid,productname,unitprice,category,provider)
values('M07','中华',3.50,'牙膏','联合利华')
insert into product (productid,productname,unitprice,category,provider)
values('M08','汰渍',3.00,'洗衣粉','宝洁')
insert into product (productid,productname,unitprice,category,provider)
values('M09','碧浪',4.00,'洗衣粉','宝洁')
insert into customer (customerid, name ,location)
values('C01','Dennis','海淀')
insert into customer (customerid, name ,location)
values('C02','John','朝阳')
insert into customer (customerid, name ,location)
values('C03','Tom','东城')
insert into customer (customerid, name ,location)
values('C04','Jenny','东城')
insert into customer (customerid, name ,location)
values('C05','Rick','西城')
insert into purcase(customerid,productid,quantity)
values('C01','M01',3)
insert into purcase(customerid,productid,quantity)
values('C01','M05',2)
insert into purcase(customerid,productid,quantity)
values('C01','M08',2)
insert into purcase(customerid,productid,quantity)
values('C02','M02',5)
insert into purcase(customerid,productid,quantity)
values('C02','M06',4)
insert into purcase(customerid,productid,quantity)
values('C03','M01',1)
insert into purcase(customerid,productid,quantity)
values('C03','M05',1)
insert into purcase(customerid,productid,quantity)
values('C03','M06',3)
insert into purcase(customerid,productid,quantity)
values('C03','M08',1)
insert into purcase(customerid,productid,quantity)
values('C04','M03',7)
insert into purcase(customerid,productid,quantity)
values('C04','M04',3)
insert into purcase(customerid,productid,quantity)
values('C05','M06',2)
insert into purcase(customerid,productid,quantity)
values('C05','M07',8)
---提交事务
commit;
---问题分析
--(1)求购买了供应商"宝洁"产品的所有顾客;
1、确定要使用的表
product 表:供应商信息
customer表:顾客信息
purcase表:顾客的购买记录
2、确定关联关系
purcase.customerid=customer.customerid
purcase.productid=customer.productid
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)