经典案例

drop table proout;

drop table customer;

drop table product;

create table Customer(

cusno varchar2(20) primary key,

cusname varchar2(50) not null,

address varchar2(200),

tel varchar2(50) unique

);

create table Product(

prono varchar2(20) primary key,

proname varchar2(200),

price number(10,2),

stocks int

create table proout(

saledate date,

cusno varchar2(20),

prono varchar2(20),

Quantity int,

foreign key (cusno) references Customer(cusno),

foreign key (prono) references Product(prono)

–1) 编写SQL语句，根据范例，建立销售数据库Sales的以上三个表。要求要满足基本的实体完整性。

–2) 编写SQL语句将示范数据插入到数据库中。

insert into Customer values (‘C001’, ‘杨婷’, ‘北京’, ‘010-5328953’);

insert into Customer values (‘C003’, ‘叶新’, ‘成都’, ‘024-3222781’);

insert into Customer values (‘C004’, ‘冯辰诚’, ‘上海’, ‘021-8723596’);

insert into Customer values (‘C005’, ‘张展’, ‘郑州’, ‘0371-8907654’);

insert into Customer values (‘C002’, ‘李和平’,’上海’, ‘021-6235965’);

insert into Product values (‘P0001’, ‘液晶电视’, 5600.00, 800);

insert into Product values (‘P0002’, ‘空调’, 2390.00, 460);

insert into Product values (‘P0003’, ‘洗衣机’, 3700.00, 600);

insert into Product values (‘P0004’, ‘电热水器’, 890.00, 120);

insert into proout values(to_date(‘2007-10-27’,’yyyy-mm-dd’), ‘C001’,’P0001’,3);

insert into proout values(to_date(‘2007-11-06’,’yyyy-mm-dd’), ‘C004’,’P0003’,40);

insert into proout values(to_date(‘2007-12-27’ ,’yyyy-mm-dd’), ‘C001’,’P0003’, 5);

insert into proout values(to_date(‘2008-3-15’,’yyyy-mm-dd’), ‘C002’,’P0002’,12);

insert into proout values(to_date(‘2008-05-02’ ,’yyyy-mm-dd’), ‘C003’,’P0002’, 21);

insert into proout values(to_date(‘2008-05-02’ ,’yyyy-mm-dd’), ‘C003’,’P0001’, 9);

insert into proout values(to_date(‘2008-09-21’,’yyyy-mm-dd’), ‘C004’,’P0001’,30);

insert into proout values(to_date(‘2008-11-21’ ,’yyyy-mm-dd’), ‘C004’,’P0001’,73);

–3) 将产品“洗衣机”的单价加1000元。

update product set price=price+1000 where proname=’洗衣机’;

–4) 编号“P0005”的产品“电冰箱”，单价3000，库存1000，录入时遗漏，请编写SQL语句插入该记录。

insert into Product values (‘P0005’, ‘电冰箱’, 3000.00, 1000);

–5) 查询购买了产品编号“P0002”的客户编号、客户名和电话，查询结果按客户名降序排列。

select c.cusno,c.cusname,c.tel from proout p inner join Customer c on c.cusno = p.cusno where p.prono=’P0002’ ORDER by c.cusname desc;

–6) 查询所有的客户编号和客户名以及它们所购买的产品编号和产品名。（包括没买产品的顾客）。

select c.cusno,c.cusname,pr.prono,pr.proname from Customer c left join proout p on c.cusno=p.cusno left join product pr on pr.prono=p.prono;

–7) 查询客户表中“姓张”的客户的姓名，地址，电话。

select c.cusname,c.address,c.tel from Customer c where c.cusname like ‘张%’;

–8) 查询产品表中产品名称中带有“电”字的产品编号、产品名称、单价。

select p.prono,p.proname,p.price from product p where p.proname like ‘%电%’;

–9) 查询产品表中库存数量大于产品表中库存量的平均值的产品编号、产品名称、库存量。

select p.prono,p.proname,p.stocks from product p where p.stocks>(select avg(stocks) from product);

–10) 查询2008年液晶电视的销售总数量和销售总额（总额=销售总数量*单价）。

select sum(pr.quantity),sum(pr.quantity*p.price) from product p inner join proout pr on p.prono=pr.prono

where p.proname=’液晶电视’ and to_char(pr.saledate,’yyyy’)=2008;

–11) 查询出2008年销售给上海客户的客户名，商品名称，销售数量。

select c.cusname,p.proname,pr.quantity from proout pr inner join product p on p.prono=pr.prono inner join customer c on c.cusno= pr.cusno

where to_char(pr.saledate,’yyyy’)=2008 and c.address=’上海’;

–12) 更新液晶电视的价格为8800元，库存数量为888。

update product set stocks=888,price=8800 where proname=’液晶电视’;

–13) 查询电话号码以021开头且最后一位不是5的客户姓名、客户电话、客户地址。

select c.cusname,c.tel,c.address from customer c where c.tel like ‘021%’ and c.tel not like ‘%5’;

–14) 查询在2007年内购订购过产品的客户编号，客户名以及产品名和单价。

select c.cusno,c.cusname,p.proname,p.price from proout pr inner join product p on p.prono=pr.prono inner join customer c on c.cusno= pr.cusno

where to_char(pr.saledate,’yyyy’)=2007;

–15) 查询定购过产品的客户编号、客户名和电话。查询结果按客户编号升序排列。

select distinct c.cusno,c.cusname,c.tel from proout pr inner join customer c on c.cusno= pr.cusno

order by c.cusno;

–16) 统计销售数量超过100的产品名。

select p.proname,sum(pr.quantity) from proout pr inner join product p on p.prono=pr.prono

group by p.proname having sum(pr.quantity)>100;

–17) 删除销售表中销售数量低于6的销售记录 。

delete proout where quantity<6;

–18) 更改地址为北京的用户姓名为杨婷婷。

update customer set cusname=’杨婷婷’ where address=’北京’;

–19) 删除销售记录表。

–20) 删除产品表中所有数据。

delete from product;