oracle数据库开发实战

来源：互联网发布：网络通俗歌手大赛冠军编辑：程序博客网时间：2024/06/05 22:36

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：
商店 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);

0 0