模拟面试编写sql

1、原材料表(材料id,材料名称,材料单价,材料数量)
2、商品表(商品id,商品名称,商品单价,材料id,材料数量)
3、销售记录表(销售id,商品id,销售数量,销售日期)
4、每日帐目汇总表(日id,日商品总成本，日商品总收入，日纯收入，销售日期)----当天算出前一天的
5、每月帐目汇总表(月id,月商品总成本，月商品总收入，月纯收入，销售月份)----月初1号统计上一个月的

drop table material;
drop table goods;
drop table sales;

create table material(mid varchar2(50),mname varchar2(100),mprice number,mnum int);
insert into material values('m001','糖',10,100);
insert into material values('m002','可可豆',10,100);
insert into material values('m003','香料',10,100);
insert into material values('m004','纯奶',10,100);
insert into material values('m005','模具',10,100);
insert into material values('m006','面粉',10,100);
insert into material values('m007','泡打粉',10,100);
insert into material values('m008','鸡蛋',10,100);
insert into material values('m009','油',10,100);
insert into material values('m010','烤盘',10,100);
commit;

create table goods(gid varchar2(50),gname varchar2(100),gprice number,mid varchar2(50),mnum int);
insert into goods values('g001','香浓巧克力',1000,'m001',1);
insert into goods values('g001','香浓巧克力',1000,'m002',1);
insert into goods values('g001','香浓巧克力',1000,'m003',1);
insert into goods values('g001','香浓巧克力',1000,'m005',1);

insert into goods values('g002','原味巧克力',1000,'m002',1);
insert into goods values('g002','原味巧克力',1000,'m003',1);
insert into goods values('g002','原味巧克力',1000,'m004',1);
insert into goods values('g002','原味巧克力',1000,'m005',1);

insert into goods values('g003','香草蛋糕',2000,'m006',1);
insert into goods values('g003','香草蛋糕',2000,'m007',1);
insert into goods values('g003','香草蛋糕',2000,'m008',1);
insert into goods values('g003','香草蛋糕',2000,'m009',1);
insert into goods values('g003','香草蛋糕',2000,'m010',1);

insert into goods values('g004','草莓蛋糕',2000,'m003',1);
insert into goods values('g004','草莓蛋糕',2000,'m006',1);
insert into goods values('g004','草莓蛋糕',2000,'m009',1);
insert into goods values('g004','草莓蛋糕',2000,'m010',1);
commit;

create table sales(sid varchar2(50),gid varchar2(50),snum int,sdate date);
insert into sales values('s001','g001',10,sysdate-2);
insert into sales values('s002','g001',20,sysdate-1);
insert into sales values('s003','g001',30,sysdate);
insert into sales values('s004','g001',40,sysdate+1);

insert into sales values('s005','g002',10,sysdate-2);      
insert into sales values('s006','g002',20,sysdate-1);      
insert into sales values('s007','g002',30,sysdate);        
insert into sales values('s008','g002',40,sysdate+1);      

insert into sales values('s009','g003',10,sysdate-2);      
insert into sales values('s010','g003',20,sysdate-1);      
insert into sales values('s011','g003',30,sysdate);        
insert into sales values('s012','g003',40,sysdate+1);      

insert into sales values('s013','g004',10,sysdate-2);       
insert into sales values('s014','g004',20,sysdate-1);       
insert into sales values('s015','g004',30,sysdate);         
insert into sales values('s016','g004',40,sysdate+1);       
commit;

求出 指定日期销售出的 每种商品销售总数量
select gid,sum(snum) snum from sales where sdate>前天日期 and sdate<明天日期 group by gid

求出 指定日期销售出的 每种商品总收入
select ss.gid, snum * gprice
  from (select gid, sum(snum) snum
          from sales
         where sdate > 前天日期
           and sdate < 明天日期
         group by gid) ss,
       (select gid, gprice from goods group by gid, gprice) gg
 where ss.gid = gg.gid;

求出 指定日期销售出 所有商品总收入
select sum(zprice)
  from (select ss.gid, snum * gprice zprice
          from (select gid, sum(snum) snum
                  from sales
                 where sdate > 前天日期
                   and sdate < 明天日期
                 group by gid) ss,
               (select gid, gprice from goods group by gid, gprice) gg
         where ss.gid = gg.gid)

求每种商品成本
select gid, sum(gcost)
  from (select gid, gg.mnum * mprice gcost
          from goods gg, material mm
         where gg.mid = mm.mid) ss
 group by gid;

求指定日期销售出 所有商品总成本
select sum(zcb)
  from (select zc.gid, zc.zprice * snum zcb
          from (select gid, sum(gcost) zprice
                  from (select gid, gg.mnum * mprice gcost
                          from goods gg, material mm
                         where gg.mid = mm.mid) ss
                 group by gid) zc,
               (select gid, sum(snum) snum
                  from sales
                 where sdate > 前天日期
                   and sdate < 明天日期
                 group by gid) xs
         where zc.gid = xs.gid)

求指定日期销售出 所有商品 纯收入

select zs.zsr - zc.zcb
  from (select sum(zprice) zsr
          from (select ss.gid, snum * gprice zprice
                  from (select gid, sum(snum) snum
                          from sales
                         where sdate > 前天日期
                           and sdate < 明天日期
                         group by gid) ss,
                       (select gid, gprice from goods group by gid, gprice) gg
                 where ss.gid = gg.gid)) zs,
       (select sum(zcb) zcb
          from (select zc.gid, zc.zprice * snum zcb
                  from (select gid, sum(gcost) zprice
                          from (select gid, gg.mnum * mprice gcost
                                  from goods gg, material mm
                                 where gg.mid = mm.mid) ss
                         group by gid) zc,
                       (select gid, sum(snum) snum
                          from sales
                         where sdate > 前天日期
                           and sdate < 明天日期
                         group by gid) xs
                 where zc.gid = xs.gid)) zc

-------------------------------测试---------------------------

求出 指定日期销售出的 每种商品销售总数量
select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid

求出 指定日期销售出的 每种商品总收入
select ss.gid,snum*gprice from (
select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid
) ss,(select gid,gprice from goods group by gid,gprice) gg where ss.gid=gg.gid;

求出 指定日期销售出 所有商品总收入
select sum(zprice) from (
select ss.gid,snum*gprice zprice from (
select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid
) ss,(select gid,gprice from goods group by gid,gprice) gg where ss.gid=gg.gid
)

求每种商品成本
select gid, sum(gcost) from(
select  gid,gg.mnum*mprice gcost from goods gg,material mm where gg.mid=mm.mid
) ss group by gid;

求指定日期销售出 所有商品总成本
select sum(zcb) from (
select zc.gid,zc.zprice*snum zcb from (
select gid, sum(gcost) zprice from(
select  gid,gg.mnum*mprice gcost from goods gg,material mm where gg.mid=mm.mid
) ss group by gid) zc,(select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid) xs 
where zc.gid=xs.gid)

求指定日期销售出 所有商品 纯收入
select zs.zsr-zc.zcb from 
(select sum(zprice) zsr from (
select ss.gid,snum*gprice zprice from (
select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid
) ss,(select gid,gprice from goods group by gid,gprice) gg where ss.gid=gg.gid
)) zs,
(
select sum(zcb) zcb from (
select zc.gid,zc.zprice*snum zcb from (
select gid, sum(gcost) zprice from(
select  gid,gg.mnum*mprice gcost from goods gg,material mm where gg.mid=mm.mid
) ss group by gid) zc,(select gid,sum(snum) snum from sales where sdate>to_date('2016-10-31','yyyy-mm-dd') and sdate<to_date('2016-11-02','yyyy-mm-dd') group by gid) xs 
where zc.gid=xs.gid)) zc

------------------存储过程编写-------------------

4、每日帐目汇总表(日id,结帐日期,汇总日期，日商品总成本，日商品总收入，日纯收入)----当天算出前一天的
5、每月帐目汇总表(月id,结账月份,汇总日期,月商品总成本，月商品总收入，月纯收入)----月初1号统计上一个月的

drop table sum_day;
drop table sum_month;

create table sum_day(did int,djdate varchar2(20),dhdate varchar2(20),dcost number,drevenue number,dincome number);

create table sum_month(mid int,mjdate varchar2(20),mhdate varchar2(20),mcost number,mrevenue number,mincome number);

create sequence seq_day
minvalue 1  
nomaxvalue
start with 1
increment by 1
nocycle   
nocache;

CREATE OR REPLACE TRIGGER tg_day
BEFORE INSERT ON sum_day 
FOR EACH ROW WHEN (new.did is null)
begin
select seq_day.nextval into:new.did from dual;
end;
/

create sequence seq_month
minvalue 1  
nomaxvalue
start with 1
increment by 1
nocycle   
nocache;

CREATE OR REPLACE TRIGGER tg_month
BEFORE INSERT ON sum_month 
FOR EACH ROW WHEN (new.mid is null)
begin
select seq_month.nextval into:new.mid from dual;
end;
/