tables.txt

来自「This project mainly deals with automatin」· 文本 代码 · 共 70 行

-------:Clerk('ramana','raju')
-------:Dept Head('narender','reddy',501 or any dept no)
-------:Manager('venkat','reddy')

create table inv_log_tab (userid varchar2(20),password varchar2(20));
insert into inv_log_tab values('venkat','reddy');

create table im_rawmaterial2(sno number(10) unique,itemno number(10) primary key, itemname varchar2(20),rate float);
create table raw_recieved(sno number(10) unique,itemno number(10),qty_ord float,qty_rec  float,dateofrec date,recno number(10) primary key,amount float,foreign key(itemno) references im_rawmaterial2(itemno));
create table raw_issues(sno number(10) unique,itemno number(10),qty_issued float,challanno  number(10) primary key,deptname  varchar2(20),issuedate date,foreign key(itemno) references im_rawmaterial2(itemno),foreign key(deptname) references dept_det(deptname));

select itemno,sum(qty_rec),sum(amount)  from raw_recieved group by itemno having itemno in(select itemno from raw_recieved where dateofrec='23-APR-01');
select itemno,sum(qty_issued)  from raw_issues group by itemno having itemno in(select itemno from raw_issues where issuedate='23-APR-01');
select itemno,sum(qty_rec),sum(amount)  from raw_recieved group by itemno having itemno in(select itemno from raw_recieved );
select * from stock2 where dateofrec='23-APR-01';
select * from sales3 where issuedate='23-APR-01';
with specific date  :--------------:select itemno,totalqty,totalamount  from stock2 where dateofrec='23-APR-01' ");
with specific date  :--------------:select itemno,totalsale  from sales3 where issuedate='23-APR-01' ");
without specific date  :--------------:select itemno,sum(totalqty),sum(totalamount)  from stock2 group by itemno having itemno in(select itemno from stock2 ) ");
without specific date  :--------------:select itemno,sum(totalsale)  from sales3 group by itemno having itemno in(select itemno from sales3 ) );


Raw specific date ::::::::::::::::/******
create or replace view stock2 as select itemno,sum(qty_rec) totalqty,sum(amount) totalamount,dateofrec from raw_recieved group by itemno,dateofrec;
create or replace view sales3 as select itemno,sum(qty_issued) totalsale,issuedate from raw_issues group by itemno,issuedate;
select s1.itemno,totalqty,totalsale,totalqty-totalsale remqty,totalamount from stock2 s1,sales3 s where s1.itemno=s.itemno and dateofrec='23-APR-01' and issuedate='23-APR-01';
select * from stock2 where dateofrec='23-apr-01' and itemno not in(select itemno from sales3 where issuedate='23-apr-01')     *********************/


Raw  general:::::::::::::::::::::::::/*****
create or replace view stock4 as select itemno,sum(qty_rec) totalqty,sum(amount) totalamount from raw_recieved group by itemno;
create or replace view sales4 as select itemno,sum(qty_issued) totalsale from raw_issues group by itemno;
select s2.itemno,totalqty,totalsale,totalqty-totalsale remqty,totalamount from stock4 s2,sales4 s3 where s2.itemno=s3.itemno ;
select * from stock4 where  itemno not in(select itemno from sales4 ) ;    *****/


create table im_finigoods(sno number(10) unique,itemnumber number(10) primary key, itemname varchar2(20),rate float);
create table fin_recieved2(sno number(10) unique,itemnumber number(10),qtyrec float,dateofrec date,deptname varchar2(20),recno number(10) primary key,foreign key(itemnumber) references im_finigoods(itemnumber),foreign key(deptname) references dept_det(deptname));
create table fin_despatch(sno number(10) unique,reqno number(10) primary key,itemnumber number(10),qtydes float,dateofdes date,dealername varchar2(20),amount float,foreign key(itemnumber) references im_finigoods(itemnumber),foreign key(dealername) references dealer(dealername));
create table dept_det(sno number(10) unique,deptno number(10),deptname varchar2(30) primary key,location varchar2(40),depthead varchar2(20));
create table dealer(sno number(10) unique,dealerno number(10) , dealername varchar2(20) primary key,address varchar2(200),phone number(10),category varchar2(20),creditlimit number(10));
select itemnumber from fin_recieved2 group by(itemnumber);
select * from stock2 where dateofrec='23-apr-01' and itemno not in(select itemno from sales3 ) ;



Fin specific date ::::::::::::::::/******
create or replace view stock8 as select itemnumber,sum(qtyrec) totalqty,dateofrec from fin_recieved2 group by itemno,dateofrec;
create or replace view sales9 as select itemnumber,sum(qtydes) totalsale,sum(amount) totalamount,dateofdes from fin_despatch group by itemnumber,dateofdes;
select s8.itemnumber,totalqty,totalsale,totalqty-totalsale remqty,totalamount from stock8 s8,sales9 s9 where s8.itemnumber=s9.itemnumber and dateofrec='23-APR-01' and dateofdes='23-APR-01';
select * from stock8 where dateofrec='23-apr-01' and itemnumber not in(select itemnumber from sales9 where dateofdes='23-apr-01')     *********************/


Fin general:::::::::::::::::::::::::/*****
create or replace view stock6 as select itemnumber,sum(qtyrec) totalqty from fin_recieved2 group by itemnumber;
create or replace view sales6 as select itemnumber,sum(qtydes) totalsale,sum(amount) totalamount from fin_despatch group by itemnumber;
select s5.itemnumber,totalqty,totalsale,totalqty-totalsale remqty,totalamount from stock6 s5,sales6 s4 where s5.itemnumber=s4.itemnumber ;
select * from stock6 where  itemnumber not in(select itemnumber from sales6 ) ;  *********/

//select s15.itemnumber,qtyrec,dateofrec,deptname,qtydes,dateofdes,dealername from fin_recieved2 s15,fin_despatch s14 where s15.itemnumber=s14.itemnumber ;

create or replace view stock12 as select itemnumber,sum(qtyrec) totalqty,deptname,dateofrec from fin_recieved2 group by itemnumber,dateofrec,deptname;
create or replace view sales12 as select itemnumber,sum(qtydes) totalqty,dealername,dateofdes from fin_despatch group by itemnumber,dateofdes,dealername;
select * from stock12 where deptname='assembling';

create or replace view stock22 as select itemno,sum(qty_issued) totalqty,deptname,issuedate from raw_issues group by itemno,issuedate,deptname;
select * from stock22 where deptname='assembling';

select * from sales12 where itemnumber in(select itemnumber from stock12 where deptname='melting');