商店 product(商品号 productid,商品名 productname,单价 unitprice,商品类别 category,供应商 provider);
顾客 customer(顾客号 customerid,姓名 name,住址 location);
购买 purcase(顾客号 customerid,商品号 productid,购买数量 quantity);
每个顾客可以购买多件商品,每件商品可以被多个顾客购买。属于多对多的关系。
使用sql语言完成下列功能:
1、建表,在定义中声明如下约束:
(1)、每个表的主外键;
(2)、顾客的姓名和商品名不能为空值;
(3)、单价必须大于0,购买数量必须在0到20之间;
2、往表中插入数据:
商品( M01,佳洁士,8.00,牙膏,宝洁;
M02,高露洁,6.50,牙膏,高露洁;
M03,洁诺,5.00,牙膏,联合利华;
M04,舒肤佳,3.00,香皂,宝洁;
M05,夏士莲,5.00,香皂,联合利华;
M06,雕牌,2.50,洗衣粉,纳爱斯;
M07,中华,3.50,牙膏,联合利华;
M08,汰渍,3.00,洗衣粉,宝洁;
M09,碧浪,4.00,洗衣粉,宝洁;)
顾客( C01,Dennis,海淀;
C02,John,朝阳;
C03,Tom,东城;
C04,Jenny,东城;
C05,Rick,西城;)
购买( C01,M01,3;
C01,M05,2;
C01,M08,2;
C02,M02,5;
C02,M06,4;
C03,M01,1;
C03,M05,1;
C03,M06,3;
C03,M08,1;
C04,M03,7;
C04,M04,3;
C05,M06,2;
C05,M07,8;)
3、用sql语法完成下列查询:
(1)、求购买了供应商“宝洁”产品的所有顾客;
(2)、求购买的商品包含了顾客“Dennis”所购买的所有商品的顾客(姓名);
(3)、求牙膏卖出数量最多的供应商。
4、将所有的牙膏商品单价增加10%。
5、删除从未被购买的商品记录。
create table product(
productid varchar2(5),
productname varchar2(20) not null,
unitprice number(7,2),
category varchar2(10),
provider varchar2(20),
constraint ck_unitprice check(unitprice>0),
constraint pk_productid primary key(productid)
);
create table customer(
customerid varchar2(5),
name varchar2(10) not null,
location varchar2(13),
constraint pk_customerid primary key(customerid)
);
create table purcase(
customerid varchar2(5),
productid varchar2(5),
quantity number,
constraint ck_quantity check(quantity between 0 and 20),
constraint fk_productid foreign key(productid) references product(productid) on
delete cascade,
constraint fk_customerid foreign key(customerid) references customer(customerid)
on delete cascade);
3.(3)select provider from product where productid=(
select productid from purcase where productid in(
select productid from product where category='牙膏')
group by productid having sum(quantity)=(
select max(sum(quantity)) from purcase where productid in(
select productid from product where category='牙膏')
group by productid));
4.update product set unitprice=unitprice*1.1 where category='牙膏';
5.delete from product where productid not in (select productid from purcase);
3.(2)select*from customer ca
where not exists(
select p1.productid from purcase p1 where customerid=(
select customerid from customer where name='Dennis')
minus
select p2.productid from purcase p2 where customerid=ca.customerid)
and ca.name<>'Dennis';
select productid from purcase where customerid='C02';
select productid from purcase where customerid='C03';
select productid from purcase where customerid='C04';
select productid from purcase where customerid='C05';
3.(1)select pur.customerid,cus.name,pro.provider
from purcase pur,product pro,customer cus
where provider='宝洁' and pur.productid=pro.productid(+)
and pur.customerid=cus.customerid(+);
select*from customer where customerid in(
select customerid from purcase where productid in(
select productid from product where provider='宝洁'));
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','雕牌',2.50,'洗衣粉','纳爱斯'
);
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);