Oracle DML SQL题目篇1

最新推荐文章于 2021-04-05 00:35:56 发布
bear_cab
最新推荐文章于 2021-04-05 00:35:56 发布
阅读量905
点赞数 2
本文链接：https://blog.csdn.net/phone13144830339/article/details/78779416
版权
/*
 1.创建表 
 商品表（商品编号，商品名称，商品单价）
 定单表（订单编号，订单时间，商品用户，购买用户，购买数量，商品编号，商品单价，是否付款）
 发货表（发货编号，定单编号，发货时间，快递，快递费用，快递是否接收）  
 2.插入测试数据
 3.查询所有表数据
 4.sql查询
    (1) 查询用户x 8月份消费的金额总数
    (2) 查询商家剩余的未被签收的订单总数
    (3) 查询所有商家的姓名 未发货的订单总数
    (4) 查询所有商家的姓名，商家的成交百分比
	  (5) 查询商家中所有订单都成交的商家姓名
	  (6) 查询购买了商家a商品 也购买了商家b的所有用户
	  (7) 查询使用天天快递，并且平均快递费用最高的商家姓名，
	  (8) 查询发货速度最快的商家姓名
	  (9) 查询修改商品单价最频繁的商家
	  (10) 查询张三所有商品中 8月销售量最高的商品名称
*/ 
--商品表 (商品编号，商品名称，商品单价)
  create table commdity(
           cid number primary key,--商品编号
           cname varchar2(20),--商品名称
           cprice number  --商品的预定价格
  )
--订单表（订单编号，订单时间，商家用户，购买用户，购买数量，商品编号，商品单价，是否付款）    
  create table myorder(
      oid number primary key,--订单编号
      otime date,--订单时间
      salep varchar2(20),--商家用户
      purp varchar(20),--购买用户
      pcount number,--购买数量
      cid number, --商品编号 关联到商品表
      cprice number,  --商品价格 讨价还价后的价格 可能和商品价格不一样
      ifpay number --是否付款 0表示未付款 1表示付款
  )     
--发货表 （发货编号，订单编号，发货时间，快递，快递费用，快递是否接收）
  create table sendmy(
     sid number primary key,--发货编号
     oid number,  --关联到订单表  订单编号
     stime date, --发货时间
     fastno varchar(20), --快递单号
     fastprice number, --快递费用
     ifaccept number --是否接收  0未接收 1表示已接收
  )
--商品表插入数据
 
--查询表中数据 DELETE FROM commdity;
  insert into commdity values(1,'水杯',29);
  insert into commdity values(2,'手表',1500);
  insert into commdity values(3,'英雄钢笔',32);
  insert into commdity values(4,'魅蓝E',1300);
  insert into commdity values(5,'魅蓝E2',1700);
  insert into commdity values(6,'格力空调',5300);
  insert into commdity values(7,'防盗门',799);
  insert into commdity values(8,'华硕笔记本电脑',6300);
  insert into commdity values(9,'高级草稿纸',1.9);
  insert into commdity values(10,'垃圾桶',19.8);
  insert into commdity values(11,'蓝牙耳机',78);
  insert into commdity values(12,'背包',199);
  insert into commdity values(13,'雨伞',29);
  insert into commdity values(14,'画笔',78);
  insert into commdity values(15,'无毒白板笔盒装',15);
  insert into commdity values(16,'线控耳机',15);
  insert into commdity values(17,'抽纸',15);
  insert into commdity values(18,'海尔冰箱',6300);
  insert into commdity values(19,'扫地机器人',798);
  insert into commdity values(20,'手提带',9);
--订单表插入数据
  DELETE FROM myorder;
  insert into myorder values(001,to_date('2016-12-21','yyyy-MM-dd'),'水货','钱二',2,1,29,1);
  insert into myorder values(002,to_date('2017-01-23','yyyy-MM-dd'),'表哥','王五',1,2,1500,1);
  insert into myorder values(003,to_date('2012-05-11','yyyy-MM-dd'),'马良','张三',3,3,32,0);
  insert into myorder values(004,to_date('2015-02-24','yyyy-MM-dd'),'MMEE','钱二',5,4,1300,0);
  insert into myorder values(005,to_date('2017-03-20','yyyy-MM-dd'),'MMEE','张三',3,5,1700,0);
  insert into myorder values(006,to_date('2017-05-30','yyyy-MM-dd'),'高用','赵六',1,6,5300,1);
  insert into myorder values(007,to_date('2016-12-20','yyyy-MM-dd'),'强盗','王五',2,7,799,1);
  insert into myorder values(008,to_date('2017-08-12','yyyy-MM-dd'),'智慧','张三',2,8,6300,1);
  insert into myorder values(009,to_date('2017-08-11','yyyy-MM-dd'),'马良','李四',5,9,1.9,1);
  insert into myorder values(010,to_date('2017-07-15','yyyy-MM-dd'),'如家','钱二',3,10,19.8,1);
  insert into myorder values(011,to_date('2017-08-05','yyyy-MM-dd'),'左右','王五',1,11,78,0);
  insert into myorder values(012,to_date('2017-08-10','yyyy-MM-dd'),'贝贝','刘一',3,12,199,1);
  insert into myorder values(013,to_date('2017-11-11','yyyy-MM-dd'),'贝贝','王五',2,13,29,1);
  insert into myorder values(014,to_date('2017-08-11','yyyy-MM-dd'),'马良','李四',5,9,1.9,1);
  insert into myorder values(015,to_date('2017-07-15','yyyy-MM-dd'),'如家','钱二',3,10,19.8,1);
  insert into myorder values(016,to_date('2017-08-05','yyyy-MM-dd'),'左右','张三',1,11,78,1);
  insert into myorder values(017,to_date('2017-08-10','yyyy-MM-dd'),'贝贝','刘一',3,12,199,0);
  insert into myorder values(018,to_date('2017-11-11','yyyy-MM-dd'),'贝贝','张三',2,13,29,1);
  insert into myorder values(019,to_date('2016-12-21','yyyy-MM-dd'),'马良','刘一',2,14,79,1);
  insert into myorder values(020,to_date('2017-01-23','yyyy-MM-dd'),'马良','刘一',1,15,15,0);
--发货表插入数据数    
  DELETE FROM sendmy; 
  insert into sendmy values(01,001,to_date('2016-12-21','yyyy-MM-dd'),'天天快递',9,1);
  insert into sendmy values(02,002,to_date('2017-01-23','yyyy-MM-dd'),'天天快递',19,1);
  insert into sendmy values(03,006,to_date('2017-05-30','yyyy-MM-dd'),'中通快递',12,1);
  insert into sendmy values(04,007,to_date('2016-12-20','yyyy-MM-dd'),'天天快递',49,1);
  insert into sendmy values(05,008,to_date('2017-08-12','yyyy-MM-dd'),'天天快递',15,1);
  insert into sendmy values(06,009,to_date('2017-08-12','yyyy-MM-dd'),'韵达快递',9,1);
  insert into sendmy values(07,010,to_date('2017-07-15','yyyy-MM-dd'),'申通快递',5,0);
  insert into sendmy values(08,012,to_date('2017-08-11','yyyy-MM-dd'),'天天快递',15,1);
  insert into sendmy values(09,013,to_date('2017-11-13','yyyy-MM-dd'),'邮政速递',19,1);
  insert into sendmy values(10,014,to_date('2017-08-11','yyyy-MM-dd'),'圆通快递',9,1);
  insert into sendmy values(11,015,to_date('2017-07-15','yyyy-MM-dd'),'韵达快递',15,1);
  insert into sendmy values(12,016,to_date('2017-08-06','yyyy-MM-dd'),'中通快递',5,0);
  insert into sendmy values(13,018,to_date('2017-11-15','yyyy-MM-dd'),'天天快递',9,1);
  insert into sendmy values(14,019,to_date('2017-12-21','yyyy-MM-dd'),'韵达快递',19,1);
  select * from commdity;
  select * from myorder;
  select * from sendmy;
  select * from commdity natural join myorder natural join sendmy;
--(1) 查询用户 张三 8月份消费的金额总数
  --张三买过的商品
  select * from myorder where purp = '张三';
  --张三8月已付款的商品
  select * from myorder where purp = '张三' and to_char(otime,'mm') = '08' and ifpay = 1;
  --张三8月消费总额
  select purp,sum(pcount*cprice) 消费总额 from myorder where purp = '张三' and to_char(otime,'mm') = '08' and ifpay = 1 group by purp;
--(2) 查询商家剩余的未被签收的订单总数
  select salep,count(salep) from myorder natural join sendmy where ifaccept = 0 group by salep;
--(3) 查询所有商家的姓名 未发货的订单总数
  select m.salep,count(rowid) from myorder m where m.oid not in (select s.oid from sendmy s) group by m.salep
--(4) 查询所有商家的姓名，商家的成交百分比
  select salep, nvl((select count(salep) from myorder m1 where m1.ifpay = 1 group by m1.salep having m1.salep = m.salep),0)*100/count(rowid)||'%' 成交几率 
  from myorder m group by salep;
--(5) 查询商家中所有订单都成交的商家姓名
  select salep from myorder m MINUS select m.salep from myorder m where m.ifpay = 0 group by m.salep;
--(6) 查询购买了商家马良商品 也购买了商家贝贝的所有用户
  select m1.purp from myorder m1,myorder m2 where m1.purp = m2.purp and ((m1.salep = '马良' and m2.salep = '贝贝')or(m1.salep = '贝贝' and m2.salep ='马良')) group by m1.purp;
--(7) 查询使用天天快递，并且平均快递费用最高的商家姓名
 select salep from (select salep,round(avg(fastprice)) from myorder natural join sendmy group by salep order by round(avg(fastprice)) desc) t where rownum = 1;
--(8) 查询张三所有商品中 8月销售量最高的商品名称
 select salep,销量 from (select salep,sum(pcount) 销量 from myorder where to_char(otime,'mm') = '08' and ifpay = 1 group by salep order by sum(pcount) desc) where rownum=1;