Oracle商品表建表+查询+答案

建表——建表——建表——建表——建表——建表——建表——建表

--创建商品信息表goods
drop table goods;
create table goods(gid number(7),gname varchar2(30),typeid varchar2(8),price number(6,2),stock number(3),soldnum number(2),scrq date);
--向商品信息表中插入记录
insert into goods values(2024551,'联想（Lenovo）天逸100','0301',3800,10,2,to_date('2016-03-24','yyyy-mm-dd'));
insert into goods values(1856588,'Apple iPhoe 6s(A1700)','010101',6400,10,2,to_date('2016-04-02','yyyy-mm-dd'));
insert into goods values(1912210,'创维(Skyworth)55M5','0201',3998,20,3,to_date('2016-05-02','yyyy-mm-dd'));
insert into goods values(1509661,'华为P8','010101',2058,20,5,to_date('2016-01-14','yyyy-mm-dd'));
insert into goods values(1514801,'小米Note白色','010101',1898,10,2,to_date('2016-05-18','yyyy-mm-dd'));
insert into goods values(2327134,'佳能(Canon)HFR76','0102',3570,10,2,to_date('2016-02-26','yyyy-mm-dd'));
insert into goods values(2365929,'索尼(SONY)数码摄像机','0102',9860,10,2,to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values(2381431,'联想（Lenovo）扬天A8000f','0301',8988,10,2,to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values(2571148,'小米红米Note增强版','010101',950,15,4,to_date('2015-12-28','yyyy-mm-dd'));
insert into goods values(2365929,'索尼(SONY)\数码摄像机','0102',9860,10,2,to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values(1440305,'松下（Panascinic)HC-V270gK-K','0201',3170,10,2,to_date('2015-09-15','yyyy-mm-dd'));
insert into goods values(2527431,'OPPOA33','010101',1280,25,9,to_date('201512-18','yyyy-mm-dd'));
insert into goods values(3451067,'魅族MX5','010101',1840,27,6,to_date('2015-11-14','yyyy-mm-dd'));
insert into goods values(1353858,'小天鹅（Little Swan)TG70-1229EDS','0202',2800,14,6,to_date('2015-11-14','yyyy-mm-dd'));
--创建商品类型表goodstype
drop table goodstype;
create table goodstype(typeid varchar2(8),typename varchar2(20),superid varchar2(4));
--向商品类型表中插入记录
insert into goodstype values('010101','手机','0101');
insert into goodstype values('0102','摄影机','01');
insert into goodstype values('0201','电视机','02');
insert into goodstype values('0301','笔记本','03');
insert into goodstype values('01','数码产品','0');
insert into goodstype values('0101','通讯产品','01');
insert into goodstype values('010102','对讲机','0101');
insert into goodstype values('010103','固定电话','0101');
insert into goodstype values('02','家电产品','0');
insert into goodstype values('0202','洗衣机','02');

--创建客户信息表customer
drop table customer;
create table customer(cid char(6),cname varchar2(20),address varchar2(50),iphone varchar2(20),tel varchar2(20),email varchar2(30),postcode char(6),icard char(18),ctype char(1));
--向客户信息表中插入记录
insert into customer values('100001','胡南','湖南省株洲市','13054158668','22786868','luck@126.com','412001','430224197512151227','1');
insert into customer values('100002','江北','湖南省株洲市','18956584566','22786868','good@163.com','412002','430204198405180024','2');
insert into customer(cid,cname) values('100003','林美熙');
insert into customer(cid,cname)  values('100004','肖家宝');
--创建客户类型表customertype
drop table customertype;
create table customertype(cid char(1),ctype varchar2(20),explain varchar2(50));
--向客户类型表插入记录
insert into customertype values(1,'普通客户','消费在10000元以下');
insert into customertype values(2,'银卡会员','消费在50000元以下');
insert into customertype values(3,'金卡会员','消费在50000元以上');

1. 查询商品信息表中生产日期在2016年1月16日之前的商品信息，查询结果仅包含商品名称和生产日期

select gname,scrq from gname where scrq=to_date('2016-1-16','yyyy-mm-dd');

2. 查询商品价格分别为950，2800和3800的商品信息，查询结果仅包括商品名称、价格和库存数量

select gname,price,scrq from goods where price in(950,2800,3800);

3. 查询商品名称中以“联想”开头的商品信息，查询结果仅包含商品名称

select gname from goods where gname like '联想%';

4. 查询商品价格在1000元至3900元之间的商品种数。

select count(*) from goods where price between 1000 and 3900;

5. 查询商品的总库存数量

select sum(stock) from goods; --这是所有商品的总库存数量
select gid,gname,sum(stock) from goods group by gid,gname; --查询每种商品的总库存数量

6. 查询不重复的商品类型数量

select typeid,count(*) from goods group by typeid; 

7. 查询商品的最高价、最低价和平均价格

select max(price),min(price),avg(price);

8. 查询商品价格在5000元以上的商品数据，要求按价格的升序输出。查询结果仅包含商品名称和商品价格两列

select gname,price from goods where price>5000 order by price;

9. 查询商品价格在3000元以下的商品数据，要求按生产日期的升序输出，生产日期相同的按商品价格的降序输出。查询结果只包含商品名称、生产日期和商品价格三列。

select gname,scrq,price from goods where price<3000 order by scrq,price desc;

10. 统计各类商品的平均价格和商品种数

select avg(price),count(*) 商品种数 from goods group by typeid; 

11. 查询2016年生产的价格高于3000元的商品信息，同时统计平均价格在2000元以上的各类商品的总金额，并按总金额的降序排列，查询结果包括类型编号，平均价格，和金额三列数据

select typeid,price,price,sum(price) from goods where to_char(scrq,'yyyy')='2016' and price>3000 group by typeid having avg(price)>2000 order by sum(price) desc;

12. 查询商品编号、商品名称、类型名称、商品价格和库存数量，并按商品编号的升序排列

select gid,gname,typeid,price,stock from goods order by gid;

13. 查询商品信息表中库存数量相等的不同商品，要求查询结果不能出现重复商品，只包括商品名称为”Apple iPhoe 6s(A1700)“的记录，查询结果包括商品编号，商品名称，商品价格和库存数量四列

14. 查询商品名称为“华为p8”的类型名称

select typename from goods join goodstype on goods.typeid=goodstype.typeid and gname='华为p8';

15. 查询价格最高的商品，查询结果包括商品名称、商品价格和库存数量

select gname,price,stick from goods where price = (select max(price) from goods);