模拟面试编写sql

最新推荐文章于 2025-06-12 17:56:07 发布

原创

最新推荐文章于 2025-06-12 17:56:07 发布 · 383 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#面试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);</