2021-11-08

简单的查询 实验五 基础篇

 --创建SHOP表格
create table SHOP (      
 ShopNo char(3) not null primary key,
 ShopName char(10),
 ShopAddress char(20) not null,
);
 --创建PRODUCT表格
create table PRODUCT(
ProNo char(3) not null primary key,
ProName char(10),
ProPrice decimal,
);
--创建SALE表格
create table SALE(
ShopNo char(3) not null ,
ProNo char(3) not null ,
Amount int,
primary key(ShopNo,ProNo),
foreign key(ShopNo) references SHOP(ShopNo),
foreign key(ProNo)  references PRODUCT(ProNo),
);
--插入数据在表PRODUCT
insert into PRODUCT values
('P01','海尔冰箱',2400),
('P02','春兰空调',2000),
('P03','海尔电视',1200),
('P04','长虹电视',1200);
--插入数据在表SALE
insert into SALE values
('S01','P01',100),
('S01','P02',200),
('S01','P03',150),
('S02','P01',120),
('S02','P02',80),
('S03','P01',100),
('S03','P03',200),
('S03','P04 ',null);
--查询所有商店的商店号、商店名
select ShopNo ShopName from SHOP;
--查询所有商品的详细信息
select ShopNo,ShopName,ShopAddress from SHOP;
--查询销售了商品的商店号
select ShopNo from SALE ;
--查询销售了p01商品的商店编号
select ShopNo from SALE where ProNo = 'p01';
--查询价格在2000~3000的商品号、商品名
select ProNo ProName from PRODUCT where ProPrice between 2000 and 3000;
--查询销售了p01或p02商品的商店号
select ShopNo from SALE where ProNo = 'p01' or ProNo = 'p02';
--查询所有电视商品的品牌、价格
select ProName ProPrice from PRODUCT where ProName like '__电视';
--查询销售表中无销售数量的销售记录
select ShopNo,ProNo,Amount  from SALE where Amount is  null ;
--查询价格在2000元以上的海尔品牌商品
select ProName from PRODUCT where ProPrice >= 2000 and ProName like '海尔%';
--查询所有商品的信息，结果按价格降序排列，价格相同时按商品名升序排列
select ProNo,ProName,ProPrice  from  PRODUCT  order by ProPrice DESC,ProName ASC;
--查询销售了商品p01的商店数以及p01商品的销售总量、平均销售量、最大销售量和最小销售量
select COUNT( Amount)  from SALE where ProNo = 'p01'; 
select  SUM(Amount) from SALE where ProNo = 'p01';
select  AVG(Amount) from SALE where ProNo ='p01';
select  MIN(Amount) from SALE where ProNo = 'p01';
select  MAX(Amount) from SALE where ProNo = 'p01';
--查询各商品的销售总量，只显示销售总量在300以上的商品及销售总量
select ShopNo,SUM(Amount) from SALE group by ShopNO having  SUM(Amount) >= 300;
--查询每个商店及其销售情况  (用= 是等值连接)
select ShopName,Amount from SHOP,SALE where Shop.ShopNo = SALE.ShopNo;  
--对上例用自然连接完成
select SHOP.ShopNo,ShopName,ShopAddress,ProNo,Amount from SHOP,SALE where SHOP.ShopNo = SALE.ShopNo;
--查询每个商店及其销售情况，无任何销售记录的商店也要显示其基本信息  (外连接查询)
select ShopName,Amount from SHOP left outer join SALE on (SHOP.ShopNo = SALE.ShopNo);
select ShopName,Amount from SHOP right outer join SALE on (SHOP.ShopNo = SALE.ShopNo);
--查询与红星商店在同一地区的商店信息  (不相关子查询)
select ShopNo,ShopName,ShopAddress from SHOP  where ShopAddress in (select ShopAddress
 from SHOP where ShopName = '红星');
 --查询至少销售了商店s02所销售的全部商品的商店号  (相关子查询)  SHOP.ShopNo
 select distinct ShopNo from SALE x where exists 
 (select ProNo from SALE y where y.ShopNo='s02')  and ShopNo<>'s03';
--查询上海及北京地区的商店信息
select ShopNo,ShopName,ShopAddress from SHOP where ShopAddress='上海' 
union select ShopNo,ShopName,ShopAddress from SHOP where ShopAddress='北京';