few programs on bank project

12.create or replace procedure atmtxns5064 as
cursor c1 is select * from daily_transactions5064 where withdraw <>0;
t daily_transactions5064%rowtype;
begin
open c1;
loop
fetch c1 into t;
exit when c1%notfound;
dbms_output.put_line('Card No: '||t.cardno ||'Tran Date: '||t.trandate|| 'Balance : '||t.balance||'Withdraw'||t.withdraw);
end loop;
dbms_output.put_line('');
close c1;
end;
/
13.create or replace procedure postrans5064 as
cursor c1 is select * from daily_transactions5064 where withdraw<>0;
t daily_transactions5064%rowtype;
begin
open c1;
dbms_output.put_line('');
loop
fetch c1 into t;
exit when c1%notfound;
dbms_output.put_line('Transaction Ref Number: '||t.txn_ref_no ||'Customer Number: '||t.cardno ||'Transaction Type: Pos Amount: '|| t.withdraw || 'Transaction Date: '||t.trandate);

end loop;
dbms_output.put_line('');
close c1;

end;
/

14.create or replace procedure moretxns5064 as
cursor c1 is select cardno,count(trandate) from daily_transactions5064 group by cardno having count(trandate)>=2;
--t daily_transactions5064%rowtype;
--t1 customer_master5064%rowtype;
cdno daily_transactions5064.cardno%type;
cou number;
begin
open c1;
dbms_output.put_line('');
loop
fetch c1 into cdno,cou;
exit when c1%notfound;
--dbms_output.put_line('Customer Identification Number: '||t.cin ||'Customer Number: '||t.accno ||'Customer Name: '||t.custname ||'Account Opening Date: '|| t.opdate || 'Opening Balance: '||t.opbal|| 'Balance : '||t.balance);
dbms_output.put_line('Card Number: '||cdno);
end loop;
dbms_output.put_line('');
close c1;

end;

customer identification number

create or replace procedure abovelakh5064 as
cursor c1 is select * from customer_master5064 where balance>100000;
t customer_master5064%rowtype;
begin
open c1;
dbms_output.put_line('');
if (c1%rowcount<=0) then
dbms_output.put_line('No Customers Available Balance Above 100000');

end if;
loop
fetch c1 into t;
exit when c1%notfound;
dbms_output.put_line('Customer Identification Number: '||t.cin ||'Customer Number: '||t.accno ||'Customer Name: '||t.custname ||'Account Opening Date: '|| t.opdate || 'Opening Balance: '||t.opbal|| 'Balance : '||t.balance);

end loop;
dbms_output.put_line('');
close c1;
end;
/

bank programs

create or replace procedure postxns as
acno number:=&acno;
pamt number:=&pamt;
cou number;
cid varchar2(16);
bal number;
cdno varchar2(16);
pdt date:=&pdt;
bal1 number;
txnno number;
begin


select count(*) into cou from customer_master where accno=acno;
select cin,balance into cid,bal from customer_master where accno=acno;
select cardno into cdno from card_master where cin=cid;
bal1:=bal;
if(cou>0) then
if bal>pamt then
select to_char(pdt,'ddmmyyyyhhmiss')||transeq.nextval into txnno from dual;
bal1:=bal1-pamt;
dbms_output.put_line('bal1:'||bal1||cdno||pdt||pamt||txnno);
insert into daily_transactions values(cdno,pdt,bal1,0,pamt,txnno);
update customer_master set balance=bal-pamt where cin=cid;
commit;
--update daily_transactions set pos=tamt where cardnoo=cdno;
dbms_output.put_line('Account Balance Updated');
else
dbms_output.put_line('Insufficient Funds');
end if;
else
dbms_output.put_line('Account Number Does Not Exist');
end if;
end;
/

min balance program in oracle

create or replace procedure mini as
acno number:=&acno;
txnno varchar2(20);
mdate date:=&mdate;
cou number;
cdno varchar(16);
cid number;
bal number;
v daily_transactions%rowtype;
cursor c1 is select * from daily_transactions where cdno=(select cardno from card_master5064 where cin=(select cin from customer_master where accno=acno));

begin
select count(*) into cou from customer_master where accno=acno;
select cin,balance into cid,bal from customer_master where accno=acno;
select cardno into cdno from card_master where cin=cid;
if(cou>0) then
open c1;
loop
fetch c1 into v;
exit when c1%notfound;

dbms_output.put_line(v.cardno||v.trandate||v.balance||v.deposit||v.withdraw);
end loop;
close c1;

dbms_output.put_line('Total Balance Is:'||bal);
select to_char(mdate,'ddmmyyyyhhmiss')||transeq.nextval into txnno from dual;
insert into daily_transactions values(cdno,mdate,bal,0,0,txnno);
commit;

else
dbms_output.put_line('Account Number Does Not Exist');
end if;

cards balance

.create or replace procedure balance as
acno number:=&acno;
txnno varchar2(20);
bdate date:=&bdate;
cou number;
cdno varchar(16);
cid number;
bal number;
begin
select count(*) into cou from customer_master where accno=acno;
select cin,balance into cid,bal from customer_master where accno=acno;
select cardno into cdno from card_master where cin=cid;
if(cou>0) then
select to_char(bdate,'ddmmyyyyhhmiss')||transeq.nextval into txnno from dual;
insert into daily_transactions values(cdno,bdate,bal,0,0,txnno);
commit;
dbms_output.put_line('Your Account Balance is '||bal);
else
dbms_output.put_line('Account Number Does Not Exist');
end if;
end;
/

real time programs in bank project

create or replace procedure depo
as
acno number:=&acno;
damt number:=&damt;
txnno varchar2(20);
ddate date:=&ddate;
cou number;
cdno varchar(16);
cid number;
opb number;
begin
select count(*) into cou from customer_master where accno=acno;
select cin,opbal into cid,opb from customer_master where accno=acno;
select cardno into cdno from card_master where cin=cid;
if(cou>0) then
select to_char(ddate,'ddmmyyyyhhmiss')||transeq.nextval into txnno from dual;
opb:=opb+damt;
insert into daily_transactions values(cdno,ddate,opb,damt,0,txnno);
update customer_master set balance=balance+damt where accno=acno;
commit;
else
dbms_output.put_line('Account Number Does Not Exist');
end if;
end;
/

real time registration example

1.create or replace procedure register
as
name varchar2(10):=&name;
opamt number:=&opamt;
add1 varchar2(10):=&add1;
ph number:=&ph;
opdate date:=&opdate;

begin
if(opamt>5000) then
insert into customer_master values(name,cust64.nextval,acc64.nextval,opamt,add1,ph,opdt);
commit;
else
dbms_output.put_line('Amount should be greater then 5000');
end if;
end;
/

Creation of tables in oracle

1. create table customer_master(custname varchar2(10),cin number(10)constraint con_pk primary key,accno number(10),opbal number(10),address varchar(10),phno number(10),opdate date);


2.create table card_master(cin number(10),cardno varchar2(16));
alter table card_master add constraint con_fk foreign key(cin) references customer_master5064(cin);
alter table card_master add constraint cad_pk primary key(cardno);

3) create table daily_transactions(cardno varchar2(16),trandate date,tranamt number(10),balance number(10),deposit number(10),withdraw number(10),txn_ref_no varchar2(20));
alter table daily_transactions add constraint txn_fk foreign key(cardno)references card_master5064(cardno);
alter table daily_transactions5064 add constraint txn_pk primary key(TXN_REF_NO);

insert into customer_master values('&custname',cust64.nextval,acc64.nextval,&opbal,'&address',&phno,'&opdate');

insert into card_master values(1111122222,cdno64.nextval);

practice quries

11)Find the day of the week on which Smith joined .
a)select ename,hiredate ,extract(day from hiredate) from emp where ename like'SMITH';


12)Find out the time of the day in(hh24:mi:ss) on which “Ford” joined the company.

a) select to_char(to_date(hiredate,'dd-mon-yy'),'hh24:mi:ss') from emp where ename like 'FORD';


13)Retrieve the analyst record with the hire date formatted as the 3rd,December,1994.
a)select job,hiredate , to_char(to_date(hiredate,'dd-mon-yy'),'DDTH,month,yyyy')from emp where job like 'ANALYST';

14)Find the day of week on which Smith joined .
a)select ename,hiredate ,extract(day from hiredate) from emp where ename like'SMITH';
select to_char(to_date(hiredate,'dd-mon-yy'),'DAY') from emp where ename like'SMITH';


15)How many months has the president worked for the company .round the nearest whole number of months.
a) select job,round(months_between(sysdate,hiredate)) from emp where job ='PRESIDENT';


16) Write a query to display rows from emp table , if empno contains digit 2.
a) select * from emp where empno like '%2%';


17) write a query to display greatest date 12-jun-99,17-mar-99,13-jun-99.
a)select greatest('12-jun-99','17-mar-99','13-jun-99') from dual;


18) Write a query to display next Monday date and find the size of the date
a)select next_day(sysdate,'monday') from dual;


19) Write a query for making the all initial letters of the give string
“yalamanchili school of learning located in the diamond park junction and beside the sai ram parlour”. In caps.;
a)select initcap('yalamanchili school of learning located in the diamond park junction and beside the sai ram parlour') from dual;

20) Raise the salary of emp Adams.
a)update emp set sal=sal+1000 from emp where ename='ADAMS';


21)Write a query to display the details of all employees who earn salary greatest than the average salary.
a)select sal from emp where sal>any(select avg(sal) from emp);

22)Determine the employee in Scott department and also have worked in sales.


23)Write a sub query to display max salary from emp table.
a) select * from( select ename,sal from emp order by sal desc) where rownum<2;


24) Write a query to display details of employees in dept no 20 , who have the same job some one in sales dept has as well.
a) select sal,ename from emp where deptno=20 and job='SALESMAN';



25)Product a list of jobs for departments 10,20,50 in that order . Display job id, and dept id using set operates.
a)

26)Display last name ,job and salary for all employees whose job is sales representatives or stock clerk and salary is not equal to 2500 ,3500 0r 7000.
a)select ename,job from emp where job in(sales,representative,stockclerk)and salary not in(2500,3500,7000);

27)WAQ to display employees getting first two least salaries.
a)select * from(select sal from emp order by sal desc) where rownum<2;

28) WAQ to display employees working in smith's department.
a)select * from emp where deptno=(select deptno from emp where ename like 'SMITH');

quries

1) Give the details of employees who have been hired between 09-JUN-81 And 31-DEC-04.
a) select * from emp where hiredate between '09-JUN-81' And 31-DEC-04


2)Write a query to display dept no , lowest salary and highest salary in each department.
a) select deptno,min(sal),max(sal) from emp group by deptno;


3)Write a query to display the tomorrow date and next week date.
a)select sysdate+1,next_day(sysdate,'saturday') from dual;


4)Write a query to display the dates for one day after and one day before.
a)select sysdate+1,sysdate-1 from dual;


5)Write a query to display the system date and previous date.
a)select sysdate,sysdate-1 from dual;

6) Write a query to display the current date and time.
a)select to_char(to_date(sysdate,'dd-mm-yyyy'),'dd-hh24:mi:ss') from dual;


7)List the names, dept of all employees whose hire date anniversary does exits in the first quarter of the year.
a)select ename,deptno where to_char(hiredate,'q')<>1;

8)Find Century in which BALKE joined.
a)select to_char(hiredate,'cc') from emp where ename='BLAKE';


9)Find out which quarter of the year the employees joined.
a)select to_char(hiredate, 'q') from emp;

10)List the names and hire date of the employees in dept no 20, display hire date formated as 12/03/1984.
a) select to_char(hiredate,'dd/mm/yyyy') from emp where deptno=20;

reverse number

class reverse
{ static int n,val,r;
public static void main(String args[])
{
n=Integer.parseInt(args[0]);

while(n!=0)
{
r=(n % 10);
val=10*r+r;

}
System.out.println(val);

}

}

throws exception

class exe18 extends Exception
{
private int detail;
exe18()
{
System.out.println("default");
}

exe18(int detail)
{
this.detail=detail;
}

public String toString()
{
return"myexception["+detail+"]";
}


static void compute(int detail) throws exe18
{
System.out.println("called compute("+detail+")");
if(detail>10)
throw new exe18(detail);
System.out.println("normal exit");
}
}
class exe19
{
public static void main(String args[])
{

try
{
exe18.compute(8);
exe18.compute(54);
}
catch(exe18 e)
{
System.out.println("caught "+e);
System.out.println(e.toString());
}
}
}

throws exception

class exe18 extends Exception
{
private int detail;
exe18()
{
System.out.println("default");
}

exe18(int detail)
{
this.detail=detail;
}

public String toString()
{
return"myexception["+detail+"]";
}


static void compute(int detail) throws exe18
{
System.out.println("called compute("+detail+")");
if(detail>10)
throw new exe18(detail);
System.out.println("normal exit");
}
}
class exe19
{
public static void main(String args[])
{

try
{
exe18.compute(8);
exe18.compute(54);
}
catch(exe18 e)
{
System.out.println("caught "+e);
System.out.println(e.toString());
}
}
}

divide by zero

class exe6
{
static void nestry(int a)
{
try
{

if(a==1)
a=a/a-a;
if(a==2)
{
int[] c=new int[100];
c[100]=100;
System.out.println("this wont come");
}
}
catch(Exception e)
{
System.out.println("array index out of range"+e);
}

}



public static void main(String args[])
{
try
{
int a=Integer.parseInt(args[0]);
int b=21/a;
System.out.println(b);
nestry(a);
}
catch(Exception e)
{
System.out.println("divide by zero"+e);
}

}

}

array index

class exe3
{
public static void main(String args[])
{
try
{
int[] a=new int[100];
a[100]=100;
System.out.println("this wont come");
}
catch(Exception e)
{
System.out.println("array index out of range"+e);
}
System.out.println("after catch");
}
}

length of a string name

class string1
{
public static void main(String args[])
{
String s=new String("ramesh kumar");
String s1="ramesh";
String s5="ramesh";
String s2=new String("RAMESH");
String s3=new String("ramesh");
String s4=new String("ramesh");

System.out.println(s);
System.out.println(s.length());
System.out.println(s.charAt(0));
System.out.println(s.charAt(1));
System.out.println(s.toUpperCase());
System.out.println(s.toLowerCase());
System.out.println(s.startsWith("r"));
System.out.println(s.endsWith("r"));
System.out.println(s1.equals(s2));
System.out.println(s1.equalsIgnoreCase(s2));
System.out.println(s1);
System.out.println(s1.length());
System.out.println(s1.substring(2));
System.out.println(s1+" "+s1);


if(s1==s5)
{
System.out.println("true");
}
else
{
System.out.println("false");

}
}
}

no parameters to string

package mypack;

class condisp1
{
condisp1()
{
System.out.println("no parameters");
}
condisp1(int i)
{
System.out.println("value of i is"+i);
}
condisp1(int k,int j)
{
System.out.println("value of k is"+k);
System.out.println("value of j is"+j);
}
}
class classpack1
{
public static void main(String args[])
{
condisp1 a=new condisp1(5);
condisp1 b=new condisp1(2,1);
condisp1 c=new condisp1();
}
}

print two parameters

class A
{
void show()
{
System.out.println("hai");
}
}
class B extends A
{
void show()
{
System.out.println("friend");
}

}
class samp1
{
public static void main(String args[])
{
/*A oa1;
oa1=new A();
oa1.show();

A oa2;
oa2=new B();
oa2.show();
*/
B oa3;
oa3=new B();
oa3.show();


}
}

abstract class in core java

abstract class A
{
int x1=10;
int x2=20;
static int x3=30;
static void disp()
{
System.out.println(x3);
}
void set(int p1,int p2)
{
x1=p1;
x2=p2;
}
abstract void show();
}
class B extends A
{
int x4=40;
void show()
{
System.out.println(x1);
System.out.println(x2);
System.out.println(x3);
System.out.println(x4);

}
}
class C extends A
{
int x5=50;
int x6=60;
void set(int p1)
{
x1=p1;
}
void set(int p1,int p2)
{
x5=p1;
x6=p2;
}
void show()
{
System.out.println(x1);
System.out.println(x2);
System.out.println(x3);
System.out.println(x6);
}
}
class abs1
{
public static void main(String args[])
{
A.disp();

B ob=new B();
ob.show();

C oc=new C();
oc.show();
oc.set(32);
oc.set(9,5);
oc.show();

A oa1;
oa1=new B();
oa1.set(4,3);
oa1.show();

A oa2;
oa2=new C();
oa2.set(2,5);
oa2.show();

}
}

inside area of rectangle

abstract class figure
{
double dim1;
double dim2;

abstract double area();

void figure1(double a,double b)
{
dim1=a;
dim2=b;
}
}
class rectangle extends figure
{
rectangle(double a,double b)
{
super.figure1(a,b);
}
double area()
{
System.out.println("inside area of rectangle");
return dim1*dim2;
}
}
class triangle extends figure
{
triangle(double a,double b)
{
super.figure1(a,b);
}
double area()
{
System.out.println("inside area of triangle");
return (dim1*dim2)/2;
}
}
class absfig4
{
public static void main(String args[])
{
rectangle r=new rectangle(10,30);
triangle t=new triangle(10,50);

System.out.println("area of rectangle "+r.area());

System.out.println("area of triangle "+t.area());
}
}

area of rectangle

abstract class figure
{
double dim1;
double dim2;

abstract double area();

void figure1(double a,double b)
{
dim1=a;
dim2=b;
}
}
class rectangle extends figure
{
rectangle(double a,double b)
{
super.figure1(a,b);
}
double area()
{
System.out.println("inside area of rectangle");
return dim1*dim2;
}
}
class triangle extends figure
{
triangle(double a,double b)
{
super.figure1(a,b);
}
double area()
{
System.out.println("inside area of triangle");
return (dim1*dim2)/2;
}
}
class absfig2
{
public static void main(String args[])
{
rectangle r=new rectangle(10,30);
triangle t=new triangle(10,50);

System.out.println("area of rectangle "+r.area());

System.out.println("area of triangle "+t.area());
}
}

three string parameters

class con1
{
con1()
{
System.out.println("no parameters");
}
con1(int a)
{
System.out.println("single parameter"+a);
}
con1(int a,double b)
{
System.out.println("two parameters"+a+" "+b);
}
con1(int a,float b)
{
System.out.println("three parameters"+a+" "+b);
}
}
class co
{
public static void main(String args[])
{
con1 x=new con1();
con1 x1=new con1(5);
con1 x2=new con1(5,6.5);
con1 x3=new con1(55,7.5);
}
}

class b extends a

class A
{
void showA()
{
System.out.println("class showA");
}
}

class B extends A
{
void showB()
{
System.out.println("class showB");
}
}

class show
{
public static void main(String args[])
{
A a=new A();
B b=new B();
a.showA();
b.showB();
// a.showB();
b.showA();
}
}

incremeting numbers

class test
{
int a;
test(int i)
{
a=i;
}
test inc()
{
test temp=new test(a+10);
return temp;
}
}
class retobj1
{
public static void main(String args[])
{
test obj1=new test(2);
test obj2=new test(10);
test obj3;
obj3=obj1.inc();
obj3=obj2.inc();
System.out.println("obj1 value is"+obj1.a);
System.out.println("obj2 value is"+obj2.a);
System.out.println("obj3 value is"+obj3.a);
obj2=obj2.inc();
obj3=obj3.inc();

System.out.println("objafter incrementing"+obj2.a);
System.out.println("objafter incrementing"+obj3.a);

}
}

sum of three parameters

class con1
{
con1()
{
System.out.println("no parameters");
}
con1(int a)
{
System.out.println("single parameter"+a);
}
con1(int a,double b)
{
System.out.println("two parameters"+a+" "+b);
}
con1(int a,float b)
{
System.out.println("three parameters"+a+" "+b);
}
}
class co
{
public static void main(String args[])
{
con1 x=new con1();
con1 x1=new con1(5);
con1 x2=new con1(5,6.5);
con1 x3=new con1(55,7.5);
}
}

box program

class box
{
double width;
double height;
double depth;

box(box o)
{
width=o.width;
height=o.height;
depth=o.depth;
}

box(double w,double h,double d)
{
width=w;
height=h;
depth=d;
}
box()
{
width=20;
height=40;
depth=50;
}
box(double x)
{
width=height=depth=x;
}
double volume()
{
return width*height*depth;
}
}

class conoap
{
public static void main(String args[])
{
box mybox1=new box(10,40,20);
box mybox2=new box();
box mycube=new box(8);
box myref=new box(mybox1);
double vol;
vol=mybox1.volume();
System.out.println(vol);

vol=mybox2.volume();
System.out.println(vol);

vol=mycube.volume();
System.out.println(vol);

vol=myref.volume();
System.out.println(vol);
}
}

strings

class test
{
int a,b;
test(int i,int j)
{
a=i;
b=j;
}
void math(test o)
{
o.a*=2;
o.b/=2;
}
}
class oap1
{
public static void main(String args[])
{
test x=new test(10,20);
System.out.println("before call"+x.a+" "+x.b);
x.math(x);
System.out.println("before call"+x.a+" "+x.b);

}
}