Problem Statement:
Bank keeps the following information about the account holders in a table Account: Account Number, Balance, and Customer Name. Bank also keeps the information about people who have availed loan from the Bank, in a table Loan Loan No, Amount and Customer name. Write a program in Java which provides the following provisions:
a) to add new tuple in both the tables
b) To retrieve information such as
i) The maximum loan amount sanctioned.
ii) The Names of customers who are having an account and availed a loan.
Input and Output Requirements:
According to users choice tuples are entered into the account and loan table using insert query. Also information like maximum loan amount and the customers having both account and loan is calculated and retrieved using select query.
Algorithm:
Begin
Create two tables account and loan.
Accept users choice
Write sql queries according to the choice of the user to insert values to the tables or retrieve the required information.
End
Program:
import java.io.*; import java.awt.*; import java.awt.event.*; import java.sql.*; import java.util.Scanner; class bankopn { public static void main(String args[]) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn=DriverManager.getConnection( "jdbc:odbc:mydb","scott","tiger"); Statement stmt1=conn.createStatement(); Scanner input=new Scanner(System.in); int cont=1,cc,choice,bal; String nam,acno; do { System.out.println("Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop"); choice=input.nextInt(); switch(choice) { case 1: System.out.println("Enter values Customer Name,Account Number,Balance"); nam=input.next(); System.out.println(nam); acno=input.next(); System.out.println(acno); bal=input.nextInt(); System.out.println(bal); String query1="insert into account1(name,accountno,balance)values('"+nam+"','"+acno+"',"+bal+")"; System.out.println(query1); cc=stmt1.executeUpdate(query1); break; case 2: System.out.println("Enter values Customer Name,Loan Number,Loan Amount"); nam=input.next(); acno=input.next(); bal=input.nextInt(); String query2="insert into loan1 (name,loanno,loanamt)values('"+nam+"','"+acno+"',"+bal+")"; System.out.println(query2); cc=stmt1.executeUpdate(query2); break; case 3: int lmt=0; String query="select loanamt from loan1"; ResultSet rs=stmt1.executeQuery(query); System.out.println(query); int maxloan=0,mx; System.out.println("Loans Sanctioned : "); while(rs.next()) { lmt=rs.getInt(1); System.out.println(lmt); if(maxloan<lmt) maxloan=lmt; } System.out.println("Maximum Loan Sanctioned is: "+maxloan); break; case 4: String query6="select name from loan1 where name in(select name from account1)"; System.out.println(query6); ResultSet rs5=stmt1.executeQuery(query6); while(rs5.next()) { System.out.println(rs5.getString(1)); } break; case 5: String querya="select * from account1"; ResultSet rsa=stmt1.executeQuery(querya); ResultSetMetaData rmeta=rsa.getMetaData(); int nCols=rmeta.getColumnCount(); System.out.println("ACCOUNT TABLE"); for (int i=1;i<=nCols; i++) System.out.print(rmeta.getColumnName(i)+" "); System.out.println(); while(rsa.next()) { for (int j=1; j<=nCols; ++j) { System.out.print(rsa.getString(j)+" "); } System.out.println(); } break; case 6: String queryl="select * from loan1"; ResultSet rsl=stmt1.executeQuery(queryl); ResultSetMetaData rmetl=rsl.getMetaData(); int nColsl=rmetl.getColumnCount(); System.out.println("LOAN TABLE"); for (int i=1;i<=nColsl; i++) System.out.print(rmetl.getColumnName(i)+" "); System.out.println(); while(rsl.next()) { for (int j=1; j<=nColsl; ++j) { System.out.print(rsl.getString(j)+" "); } System.out.println(); } break; case 7: cont=0; } }while(cont==1); } catch (Exception ex) { System.out.println(ex); } } } |
Output:
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
1
Enter values Customer Name,Account Number,Balance
Anna
a7890
4500
insert into account1(name,accountno,balance)values(‘Anna’,’7890′,4500)
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
5
ACCOUNT TABLE
NAME ACCOUNTNO BALANCE
nisha s100 1890
vani s109 2134
vinil k567 2345
Anna a7890 4500
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
2
Enter values Customer Name,Loan Number,Loan Amount
Anna
a7890
5432
insert into loan1 (name,loanno,loanamt)values(‘Anna’,’7890′,5432)
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
6
LOAN TABLE
NAME LOANNO LOANAMT
anu s123 2000
vani s109 1500
vinil k567 8890
Anna a7890 5432
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
4
select name from loan1 where name in(select name from account1)
Anna
vani
vinod
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
3
select loanamt from loan1
Loans Sanctioned :
2000
1500
8890
5432
Maximum Loan Sanctioned is: 8890
Enter choice 1.New Account 2.New Loan 3.Maximum Loan 4.customers with Loan and Account 5.View Accounts 6.View Loans 7.Stop
7