SQL练习题

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) 删除销售记录表。
drop table proout;
--20) 删除产品表中所有数据。
delete from product;