經典案例

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;