Oracle面试复习（二）

 

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

商品product（商品号productid，商品名productname，单价unitprice，商品类别category，供应商provider，上架日期startdate）；

顾客customer（顾客号customerid，姓名name，住址location）；

购买purchase（顾客号customerid，商品号productid，购买数量quantity）；

 

试用SQL语言完成下列功能：

1. 建表:在定义中要求声明：

(1) 每个表的主外码；

(2) 顾客的姓名和商品名不能为空值；

(3) 单价必须大于0，购买数量必须再0到20之间；

 

 

 

create table rxy_product (
  product_id    varchar2(10) not null primary key,
  product_name  varchar2(30) not null,
  unitprice     number(8,2) default 0.00,
  category      varchar2(20),
  provider      varchar2(30),
  startdate     date,
  
  constraint RXY_UNITPRICE check ( unitprice>0 )
);
  
create table rxy_customer (
  customer_id   varchar2(10) not null primary key,
  customer_name varchar2(12) not null,
  location      varchar2(50)
);
  
create table rxy_purchase (
  customer_id  varchar2(10) not null,
  product_id   varchar2(10) not null,
  quantity     number(4,0) default 0,
   
  constraint FK_PRODUCT foreign key(product_id) references rxy_product,
  constraint FK_CUSTOMER foreign key(customer_id) references rxy_customer,
  constraint RXY_QUANTITY check ( quantity>=0 and quantity<=20 )
);
 
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M01','佳洁士',8.00,'牙膏','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M02','高露洁',6.50,'牙膏','高露洁' ); 
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M03','洁诺',5.00,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M04','舒肤佳',3.00,'香皂','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M05','夏士莲',5.00,'香皂','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M06','雕牌',2.50,'洗衣粉','纳爱斯' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M07','中华',3.50,'牙膏','联合利华' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M08','汰渍',3.00,'洗衣粉','宝洁' );
insert into rxy_product (product_id,product_name,unitprice,category,provider) values( 'M09','碧浪',4.00,'洗衣粉','宝洁' );

insert into rxy_customer values( 'C01','Dennis','海淀' );
insert into rxy_customer values( 'C02','John','朝阳' );
insert into rxy_customer values( 'C03','Tom','东城' );
insert into rxy_customer values( 'C04','Jenny','东城' );
insert into rxy_customer values( 'C05','Rick','西城' );

insert into rxy_purchase values( 'C01','M01',3 );
insert into rxy_purchase values( 'C01','M05',2 );
insert into rxy_purchase values( 'C01','M08',2 );
insert into rxy_purchase values( 'C02','M02',5 );
insert into rxy_purchase values( 'C02','M06',4 );
insert into rxy_purchase values( 'C03','M01',1 );
insert into rxy_purchase values( 'C03','M05',1 );
insert into rxy_purchase values( 'C03','M06',3 );
insert into rxy_purchase values( 'C03','M08',1 );
insert into rxy_purchase values( 'C04','M03',7 );
insert into rxy_purchase values( 'C04','M04',3 );
insert into rxy_purchase values( 'C05','M06',2 );
insert into rxy_purchase values( 'C05','M07',8 );
/

--（1）求购买了供应商"宝洁"产品的所有顾客；
select customer_name 
     from rxy_product p,rxy_customer c,rxy_purchase pc
    where p.product_id=pc.product_id 
      and pc.customer_id=c.customer_id 
      and p.provider='宝洁';
      
--（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；
select customer_name from rxy_customer where customer_id in (
    select customer_id from rxy_purchase 
    where product_id in 
        (select pc.product_id from rxy_customer c, rxy_purchase pc where c.customer_id = pc.customer_id and c.customer_name='Dennis')
    group by customer_id 
    having count(product_id)>=
        (select count(pc.product_id) from rxy_customer c, rxy_purchase pc where c.customer_id = pc.customer_id and c.customer_name='Dennis')
);

--（3）求牙膏卖出数量最多的供应商。
select rownum, a.* from (
select p.provider, sum(pc.quantity) s from rxy_product p, rxy_purchase pc 
where pc.product_id = p.product_id and p.category='牙膏' 
group by p.provider
order by s desc) a where rownum=1;

select provider , NUM 
from ( select distinct pd.provider , 
           (select sum(SUM_NUM) from (
		 select p.provider pp, (select sum(quantity) from rxy_purchase pc
						   where pc.product_id=p.product_id
                                                  ) "SUM_NUM"
                 from rxy_product p
                 where category='牙膏'
                )
            where pp=pd.provider
            ) "NUM"
       from rxy_product pd
       where  category='牙膏'
       order by NUM desc
 ) 
where rownum=1;

--4 将所有的牙膏商品单价增加10%。
update rxy_product set unitprice=unitprice*1.1 where category='牙膏';
 
--5 删除从未被购买的商品记录。
select * from rxy_product where product_id not in(select distinct(product_id) from rxy_purchase);

select * from rxy_product p where not exists(select 1 from rxy_purchase pc where p.product_id = pc.product_id);

select * from rxy_product
where product_id in (
  select p.product_id from rxy_product p
    minus
  select pc.product_id  from  rxy_purchase pc
);