Task 4: Construct and run the code to sum the account totals for each branch. Co

Question

Task 4: Construct and run the code to sum the account totals for each branch.

Construct your own database for this and enter the data.

AccountNr, BranchNr, ClientNr PlaceCode, are all Int. MoneyAmout is Decimal with 7 digits total with 2 digits to the right of the decimal. CityName and ClientName are Varchar(15)

My code for the tables and the above DML commands

/*A12.6DB assignment and also used for final quiz

*

* Author: rob r

* Created: Nov 27, 2016

* An extension of Fig 12.22 plus another branch from the

* branch office, see A12.6DBAssignment.doc in IFT 200 folder

*/

drop table AccountState;

drop table AccountUserState;

drop table BranchProperty;

drop table ClientProperty;

create table AccountState(

branchNr Int not null,

accountNr Int not null,

balance Decimal(7,2) not null

);

insert into AccountState values

(10, 54, 3000.00),

(10, 77, 500.55),

(23, 88, 2000.00),

(23, 54, 1000.00);

create table AccountUserState(

branchNr Int not null,

accountNr Int not null,

clientNr Int not null

);

insert into AccountUserState values

(10, 54,1001),

(10, 54,1002),

(10, 77,2013),

(23, 54,7654);

create table BranchProperty(

branchNr Int not null Primary Key,

placeCode Int not null,

cityName Varchar(15)not null

);

insert into BranchProperty values

(10, 228, 'Heber'),

(23, 357, 'Cornville'),

(15, 210, 'HorseGulch');

create table ClientProperty(

clientNr Int not null Primary Key,

clientName Varchar(15) not null

);

insert into CLientProperty values(1001 , 'Jones, ME'),

(1002, 'Jones,TA'),

(2013, 'Jones,TA'),

(7654, 'Seldon, H'),

(8005, 'Shankara, TA');

--select *

--from AccountState natural join AccountUserState;

select *

Client ClientName inr has Account N is for is used by Account Money Amount (usd:) Branch (inn) L has balance Place Description has property name near referenced By A12.6DBAssignment questions on various SQL commands 2016rr: A client has a bank account this is identified by n account number and a branch office An account has a balance. A branch office has a location that is near a city this could be a rural branch office That location has a escription. I

Explanation / Answer

package pkg;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcEg {
   public static void main(String args[]) {
       try {
           Class.forName("oracle.jdbc.driver.OracleDriver");

           Connection con = DriverManager
                   .getConnection("jdbc:oracle:thin:@localhost:1521:orcl",
                           "snop17", "snop17");

           Statement stmt = con.createStatement();

           System.out.println("sum(balance) branchnr placecode cityname");
           System.out
                   .println("----------------------------------------------------------");
           ResultSet rs = stmt
                   .executeQuery("select sum(balance), branchnr,placecode, cityname from accountstate natural left outer join branchproperty group by branchnr,placecode,cityname");
           while (rs.next()) {
               System.out.println(rs.getDouble(1) + " " + rs.getInt(2)
                       + " " + rs.getInt(3) + " " + rs.getString(4));
           }
           con.close();

       } catch (Exception e) {
           System.out.println(e);
       }

   }
}

--------------------------------------

output:

sum(balance)   branchnr   placecode   cityname
----------------------------------------------------------
3500.55       10       228       Heber
3000.0       23       357       Cornville

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.