oracle数据库个人公司笔试问题之准备

最新推荐文章于 2023-09-07 13:23:07 发布

xyb100

最新推荐文章于 2023-09-07 13:23:07 发布

阅读量627

点赞数 1

分类专栏： oracle学习

本文链接：https://blog.csdn.net/xyb100/article/details/88845794

版权

oracle学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

首先，是五个表的建表语句

create table 物料表
(
  ID    number not null,
  name  varchar2(20) not null,
  price number(10,2)
)
;

create table 入库主表
(
  ID     VARCHAR2(10) not null,
  IPDATE DATE,
  TOTAL  NUMBER(12,2)
);
--

create table 入库明细表
(
  mainid varchar2(10),
  gid    number,
  amount number,
  price  number(10,2)
)
;

create table 出库主表
(
  ID      VARCHAR2(10) not null,
  OUTDATE DATE,
  TOTAL   NUMBER(12,2)
);

create table 出库明细表
(
  MAINID  VARCHAR2(10),
  GID     NUMBER,
  AMOUNT  NUMBER,
  PRICE   NUMBER(10,2),
  INPUTID VARCHAR2(10)
);

insert语句

insert into 物料表(ID,NAME,PRICE) values(1,'车轮',321.52);
insert into 物料表(ID,NAME,PRICE) values(2,'前轴',83.21);
insert into 物料表(ID,NAME,PRICE) values(3,'垫圈',0.15);
insert into 物料表(ID,NAME,PRICE) values(4,'螺母',1.87);
insert into 物料表(ID,NAME,PRICE) values(5,'螺钉',1.36);
insert into 物料表(ID,NAME,PRICE) values(6,'板簧',403.13);
insert into 物料表(ID,NAME,PRICE) values(7,'钢材',4003.62);
insert into 物料表(ID,NAME,PRICE) values(8,'吸管',28.12);
insert into 物料表(ID,NAME,PRICE) values(9,'基座',2186.48);  
insert into 物料表(ID,NAME,PRICE) values(10,'连杆',734.91);
insert into 物料表(ID,NAME,PRICE) values(11,'后视镜',0);  

insert into 入库主表(ID,IPDATE) values('RK20140001',to_date('2014-01-01','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140002',to_date('2014-01-14','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140003',to_date('2014-02-16','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140004',to_date('2014-02-22','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140005',to_date('2014-03-07','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140006',to_date('2014-03-12','yyyy-mm-dd'));
insert into 入库主表(ID,IPDATE) values('RK20140007',to_date('2014-04-09','yyyy-mm-dd'));

insert into 出库主表(ID,OUTDATE) values('CK20140001',to_date('2014-01-03','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140002',to_date('2014-01-04','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140003',to_date('2014-02-11','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140004',to_date('2014-02-17','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140005',to_date('2014-03-09','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140006',to_date('2014-03-23','yyyy-mm-dd'));
insert into 出库主表(ID,OUTDATE) values('CK20140007',to_date('2014-04-01','yyyy-mm-dd'));

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',1,150,200);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',2,142,60.32);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',3,321,0.07);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',4,471,0.94);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',5,70,0.42);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140001',6,93,263.75);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140002',8,320,13.31);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140002',9,631,1384.3);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140002',10,93,503.32);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140003',1,420,186);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140003',2,471,52.93);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140003',3,32,0.09);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140003',4,140,0.94);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140003',7,973,2108.39);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140004',8,320,13.31);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140004',9,631,1384.3);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140004',10,93,503.32);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140005',1,420,210.4);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140005',2,39,49.66);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140005',3,25,0.04);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140006',4,41,0.74);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140006',5,290,0.22);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140006',6,54,223.75);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140006',8,121,299.42);

insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',1,150,200);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',2,56,55.32);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',4,56,0.84);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',5,70,0.61);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',6,93,232.55);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',7,973,2162.74);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',8,320,15.31);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',9,631,1784.3);
insert into 入库明细表(MAINID,GID,AMOUNT,PRICE) values('RK20140007',10,93,483.62);

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',1,89,170.43,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',2,140,90.32,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',3,300,1.53,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',4,471,1.29,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',5,69,0.43,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',6,36,280.86,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',8,300,380.7,'RK20140002');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140001',10,90,500.35,'RK20140002');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',1,400,175.43,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',2,2,97.32,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',3,20,309.71,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',6,27,250.86,'RK20140001');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',8,20,341.7,'RK20140002');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140002',10,3,553.35,'RK20140002');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140003',1,252,246.43,'RK20140003');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140003',2,382,57.32,'RK20140003');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140003',3,27,1.14,'RK20140003');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140003',4,100,1.99,'RK20140003');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140004',8,188,535.98,'RK20140004');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140005',1,133,327.42,'RK20140005');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140005',3,25,1.73,'RK20140005');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',1,270,232.42,'RK20140005');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',2,30,40.12,'RK20140005');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',4,38,1.96,'RK20140006');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',5,281,0.19,'RK20140006');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',6,50,342.42,'RK20140006');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',7,900,3531.45,'RK20140003');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',8,100,329.63,'RK20140006');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140006',10,47,373.58,'RK20140004');

insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',1,132,262.42,'RK20140007');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',2,38,89.36,'RK20140007');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',3,20,0.72,'RK20140005');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',4,28,3.5,'RK20140007');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',6,83,427.46,'RK20140007');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',7,800,3000.02,'RK20140007');
insert into 出库明细表(MAINID,GID,AMOUNT,PRICE,INPUTID) values('CK20140007',10,88,501.85,'RK20140007');
commit;