sql练习题

--1)编写SQL语句，根据范例，建立销售数据库Sales的以上三个表。
--要求要满足基本的实体完整性。
--2)编写SQL语句将示范数据插入到数据 库中。
--建立客户表Customer
create table Customer(
    CusNo varchar2(10) primary key,   
    CusName varchar2(20) not null,
    Address varchar2(50),
    Tel varchar2(20)  unique
)
--建立产品表Product
create table Product(
   ProNo varchar2(10) primary key,
   ProName varchar2(20),
   price number(10,2),
   Stocks int
)
--建立销售表ProOut
create table ProOut(
   SalaDate date, 
   CusNo  varchar2(10),
   ProNo  varchar2(10),
   Quantity int,
   foreign key (cusno) references Customer(cusno),
   foreign key (prono) references Product(prono)
)
--在Customer中插入数据
insert into Customer values('C001','杨婷','北京','010-5328953');
insert into Customer values('C002','李和平','上海','021-6235965');
insert into Customer values('C003','叶新','成都','024-3222781');
insert into Customer values('C004','冯辰诚','上海','021-8726596');
insert into Customer values('C005','张展','郑州','0371-8907654');
select * from Customer;
--在Product中插入数据
insert into Product values('P0001','液晶电视',5600.00,800);
insert into Product values('P0002','空调',2690.00,460);
insert into Product values('P0003','洗衣机',3700.00,600);
insert into Product values('P0004','电热水器',890.00,120);
select * from Product;
--在ProOut中插入数据
insert into ProOut values('27-10月-2007','C001','P0001',3);
insert into ProOut values('06-11月-2007','C004','P0003',40);
insert into ProOut values('27-12月-2007','C001','P0003',5);
insert into ProOut values('15-3月-2008','C002','P0002',12);
insert into ProOut values('2-5月-2008','C003','P0002',21);
insert into ProOut values('2-5月-2008','C003','P0001',9);
insert into ProOut values('21-9月-2008','C004','P0001',30);
insert into ProOut values('21-11月-2008','C004','P0001',73);
select * from ProOut;
--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 CusNo, CusName, Tel
  from Customer
 where CusNo in (select CusNo from ProOut where ProNo = 'P0002')

 order by CusName desc;

--內连

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, p2.prono, p2.proname
  from Customer c
  left join ProOut p1
    on c.cusno = p1.cusno
  left join Product p2
    on p1.prono = p2.prono;
--7)查询客户表中“姓张”的客户的姓名，地址，电话。
select CusName,Address,Tel from Customer where CusName like '张%';
--8)查询产品表中产品名称中带有“电”字的产品编号、产品名
--称、单价
select ProNo,ProName,Price from Product where 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(Quantity), sum(Quantity) * p2.price
  from Product p2
 inner join ProOut p1
    on p1.prono = p2.prono
 where to_char(p1.saladate, 'yyyy') = '2008'
 group by p2.price, p2.proname
having p2.proname = '液晶电视';
--或者
select sum(Quantity), sum(Quantity * p2.price)
  from Product p2
 inner join ProOut p1
    on p1.prono = p2.prono
 where p2.proname = '液晶电视'
   and to_char(p1.saladate, 'yyyy') = '2008';
--11)查询出2008年销售给上海客户的客户们，商品名称，销售数量。
select c.cusname, pr.proname, p.quantity
  from Customer c
 inner join ProOut p
    on c.cusno = p.cusno
 inner join Product pr
    on p.prono = pr.prono
 where c.address = '上海'
   and to_char(p.saladate, 'yyyy') = 2008;
--12)更新液晶电视的价格为8800元，库存数量为888。
update Product set price=8800,Stocks=888 where ProName='液晶电视';
--13)查询电话号码以021开头且最后一位不是5的客户姓名、
--客户电话、客户地址。 
select CusName, Tel, Address
  from Customer
 where Tel like '021%'
   and Tel not like '%5';
--14)查询在2007年内购订购过产品的客户编号，客户名以及产品
--名和单价
select c.CusNo, c.CusName, p2.ProName, p2.Price
  from Customer c
 inner join ProOut p1
    on c.cusno = p1.cusno
 inner join Product p2
    on p1.prono = p2.prono
 where to_char(p1.saladate, 'yyyy') = '2007';
--15)查询定购过产品的客户编号、客户名和电话。
--查询结果按客户编号升序排列 
select distinct c.Cusno, c.Cusname, c.Tel
  from Customer c
 inner join ProOut p
    on c.cusno = p.cusno
 order by c.cusno;
--16)统计销售数量超过100的产品名。
select p1.Proname, sum(p2.quantity)
  from Product p1
 inner join ProOut p2
    on p1.prono = p2.prono
 group by p1.proname
having sum(p2.quantity) > 100;
--17) 删除销售表中销售数量低于6的销售记录 。
delete  proout p where p.quantity<6;
--18) 更改地址为北京的用户姓名为杨婷婷。
update Customer set Cusname='杨婷婷' where address='北京';  
--19) 删除销售记录表。
drop table Proout;
--20) 删除产品表中所有数据
delete from Product;


 


--行转列