建表——建表——建表——建表——建表——建表——建表——建表
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'));
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');
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','肖家宝');
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元以上');
![在这里插入图片描述](https://img-blog.csdnimg.cn/29520aeff1fb41c69db87adbe1e7b5b4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54ix552h6KeJ55qE5bCP6aao,size_20,color_FFFFFF,t_70,g_se,x_16)
![在这里插入图片描述](https://img-blog.csdnimg.cn/5d2b2361aef347d69f63588c5c12fb9c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54ix552h6KeJ55qE5bCP6aao,size_20,color_FFFFFF,t_70,g_se,x_16)
![在这里插入图片描述](https://img-blog.csdnimg.cn/951e53eb0b6b41549ffebd25d91b810b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54ix552h6KeJ55qE5bCP6aao,size_20,color_FFFFFF,t_70,g_se,x_16)
![在这里插入图片描述](https://img-blog.csdnimg.cn/d573ce6564bd49e89ba9f6f883c5605c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54ix552h6KeJ55qE5bCP6aao,size_20,color_FFFFFF,t_70,g_se,x_16)
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);