Just a general explanation owuld be fine as to the possible reasoning this is oc
ID: 3841468 • Letter: J
Question
Just a general explanation owuld be fine as to the possible reasoning this is occuring and some possible solutions.
SQL> select * from product;
PRODUCT_CD NAME PRODUCT_TY
---------- -------------------------------------------------- ----------
DATE_OFFE DATE_RETI
--------- ---------
CHK checking account ACCOUNT
01-JAN-00 01-JAN-30
SAV savings account ACCOUNT
01-JAN-00 01-JAN-30
MM money market account ACCOUNT
01-JAN-00 01-JAN-30
PRODUCT_CD NAME PRODUCT_TY
---------- -------------------------------------------------- ----------
DATE_OFFE DATE_RETI
--------- ---------
CD certificate of deposit ACCOUNT
01-JAN-00 01-JAN-30
MRT home mortgage LOAN
01-JAN-00
AUT auto loan LOAN
01-JAN-00
PRODUCT_CD NAME PRODUCT_TY
---------- -------------------------------------------------- ----------
DATE_OFFE DATE_RETI
--------- ---------
BUS business line of credit LOAN
01-JAN-00
SBL small business loan LOAN
01-JAN-00
8 rows selected.
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
1 CHK 1 15-JAN-00 04-JAN-05 ACTIVE
2 10 1057.75 1057.75
2 SAV 1 15-JAN-00 19-DEC-04 ACTIVE
2 10 500 500
3 CD 1 30-JUN-04 30-JUN-04 ACTIVE
2 10 3000 3000
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
4 CHK 2 12-MAR-01 27-DEC-04 ACTIVE
2 10 2258.02 2258.02
5 SAV 2 12-MAR-01 11-DEC-04 ACTIVE
2 10 200 200
6 CHK 3 23-NOV-02 30-NOV-04 ACTIVE
3 13 1057.75 1057.75
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
7 MM 3 15-DEC-02 05-DEC-04 ACTIVE
3 13 2212.5 2212.5
8 CHK 4 12-SEP-03 03-JAN-05 ACTIVE
1 1 534.12 534.12
9 SAV 4 15-JAN-00 24-OCT-04 ACTIVE
1 1 767.77 767.77
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
10 MM 4 30-SEP-04 11-NOV-04 ACTIVE
1 1 5487.09 5487.09
11 CHK 5 27-JAN-04 05-JAN-05 ACTIVE
4 16 2237.97 2897.97
12 CHK 6 24-AUG-02 29-NOV-04 ACTIVE
1 1 122.37 122.37
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
13 CD 6 28-DEC-04 28-DEC-04 ACTIVE
1 1 10000 10000
14 CD 7 12-JAN-04 12-JAN-04 ACTIVE
2 10 5000 5000
15 CHK 8 23-MAY-01 03-JAN-05 ACTIVE
4 16 3487.19 3487.19
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
16 SAV 8 23-MAY-01 12-OCT-04 ACTIVE
4 16 387.99 387.99
17 CHK 9 30-JUL-03 15-DEC-04 ACTIVE
1 1 125.67 125.67
18 MM 9 28-OCT-04 28-OCT-04 ACTIVE
1 1 9345.55 9845.55
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
19 MM 9 30-JUN-04 30-JUN-04 ACTIVE
1 1 1500 1500
20 MM 10 30-SEP-02 15-DEC-04 ACTIVE
4 16 23575.12 23575.12
21 MM 10 01-OCT-02 28-AUG-04 ACTIVE
4 16 0 0
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
22 MM 11 22-MAR-04 14-NOV-04 ACTIVE
2 10 9345.55 9345.55
23 MM 12 30-JUL-03 15-DEC-04 ACTIVE
4 16 38552.05 38552.05
24 CHK 13 22-FEB-04 17-DEC-04 ACTIVE
3 3 50000 50000
ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS
---------- ---------- ---------- --------- --------- --------- ----------
OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE
-------------- ----------- ------------- ---------------
35 SBL 1 23-APR-17
55 SBL 9 23-APR-12
4
56 LOAN 8 23-APR-12
SQL> SELECT * FROM BRANCH;
BRANCH_ID NAME ADDRESS
---------- -------------------- ------------------------------
CITY ST ZIP
-------------------- -- ------------
1 Headquarters 3882 Main St.
Waltham MA 02451
2 Woburn Branch 422 Maple St.
Woburn MA 01801
3 Quincy Branch 125 Presidential Way
Quincy MA 02169
BRANCH_ID NAME ADDRESS
---------- -------------------- ------------------------------
CITY ST ZIP
-------------------- -- ------------
4 So. NH Branch 378 Maynard Ln.
Salem NH 03079
SELECT b.branch_id, loan.s LOAN, acc.s ACCOUNT
FROM branch b LEFT OUTER JOIN (SELECT a.open_branch_id, sum(a.avail_balance) s
FROM account a INNER JOIN product p
ON a.product_cd = p.product_cd
WHERE p.product_type_cd='LOAN'
GROUP BY a.open_branch_id) loan
ON b.branch_id = loan.open_branch_id
LEFT OUTER JOIN (SELECT a.open_branch_id, sum(a.avail_balance) s
FROM account a INNER JOIN product p
ON a.product_cd = p.product_cd
WHERE p.product_type_cd='ACCOUNT'
GROUP BY a.open_branch_id) acc
ON b.branch_id = acc.open_branch_id
ORDER BY b.branch_id;
The output has only NULLs in the LOAN column. This seems inconsistent with “LEFT OUTER JOIN.” Provide a coherent explanation, including insertion of some data that would show in some row of the LOAN column.
Explanation / Answer
import java.util.*;
import java.io.*;
import java.util.ArrayList;
//CLASS Bank
class Bank
{
private ArrayList<BankAccount> bankaccountList=new ArrayList<BankAccount>();
private ArrayList<Customer> cust_customerList=new ArrayList<Customer>();
//ADDS NEW Customer
public void addCustomer(Customer newcust12)
{
cust_customerList.add(newcust12);
}
//ADDS NEW BankAccount
public void addAccount(BankAccount newacc12)
{
bankaccountList.add(newacc12);
}
}
/****************************
CLASS Customer
****************************/
class Customer extends Bank
{
int cust_id;
String cust_name;
ArrayList<SavingsAccount> savingAccnt=new ArrayList<SavingsAccount>();
ArrayList<CheckingAccount> checkingAcct=new ArrayList<CheckingAccount>();
public Customer(int a_id, String a_name) {
this.cust_id = a_id;
this.cust_name = a_name;
}
//ADDS new CheckingAccount
public void addCheckingAccount(CheckingAccount chk_accnt)
{
checkingAcct.add(chk_accnt);
}
//ADDS new SavingAccount
public void addSavingAccount(SavingsAccount sav_accnt)
{
savingAccnt.add(sav_accnt);
}
//BOOLEAN METHOD WITHDRAW MONEY FROM SavingsAccount FOR THE CheckingAccount
public boolean transferMoney(CheckingAccount chk_accnt,int a_amt)
{
boolean isMoneyTransfer=false;
for(SavingsAccount sa:savingAccnt)
{
if(sa.getAccnt_id()==chk_accnt.getAccnt_id())
{
sa.withdraw(a_amt);
isMoneyTransfer=true;
}
}
return isMoneyTransfer;
}
}
/**********************
CLASS CheckingAccount
**********************/
class CheckingAccount extends BankAccount
{
Customer a_cust;
public CheckingAccount(int account_No, String account_name,double accnt_balance)
{
super(account_No, account_name, accnt_balance);
}
public void withdraw(int a_amt)
{
if(a_amt<=accnt_balance)
{
accnt_balance=accnt_balance-a_amt;
System.out.println("SUCESSFULLY WITHDRAWN!!");
}
else
{
boolean flag12=a_cust.transferMoney(this,a_amt);
if(!flag12)
System.out.println("INSUFFICIENT BALANCE");
}
}
public void display()
{
System.out.println("ACCOUNT BALANCE is $"+accnt_balance);
}
}
/************************
CLASS SavingsAccount
*************************
*/
class SavingsAccount extends BankAccount
{
public SavingsAccount(int account_No1, String account_name1, double accnt_balance1)
{
super(account_No1, account_name1, accnt_balance1);
}
public void withdraw(int a_amt)
{
if(a_amt<=accnt_balance)
{
accnt_balance=accnt_balance-a_amt;
System.out.println("SUCESSFULLY WITHDRAWN!");
}
else
System.out.println("INSUFFICIENT BALANCE");
}
public void display()
{
System.out.println("Balance is $"+accnt_balance);
}
}
//ABSTRACT CLASS BankAccount
abstract class BankAccount
{
int account_No;
String account_name;
double accnt_balance;
public BankAccount(int account_No1, String account_name1,double accnt_balance1)
{
this.account_No = account_No1;
this.account_name = account_name1;
this.accnt_balance=accnt_balance1;
}
public int getAccnt_id()
{
return account_No;
}
public String getAccount_Name()
{
return account_name;
}
public void deposit(int a_amt)
{
accnt_balance= accnt_balance+a_amt;
System.out.println("Account deposited with $"+a_amt);
}
public abstract void withdraw(int a_amt);
}
// CLASS BankImplementation
public class BankImplementation {
//main method
public static void main(String[] args)
{
int a_id=110;
String a_name="Arlen";
double a_bal=5000;
//CREATING SAVINGSACCOUNT
SavingsAccount newsaving=new SavingsAccount(a_id, a_name, a_bal);
//CREATING A CUSTOMER
Customer newCust123=new Customer(a_id, a_name);
newCust123.addSavingAccount(newsaving);
//CREATING CHECKINGACCOUNT
CheckingAccount newCheckingAccount=new CheckingAccount(a_id,a_name,1000);
newCust123.addCheckingAccount(newCheckingAccount);
newsaving.display();
newsaving.deposit(5000);
newsaving.display();
newsaving.withdraw(9000);
newsaving.display();
newCheckingAccount.display();
newCheckingAccount.deposit(2000);
newCheckingAccount.display();
newCheckingAccount.withdraw(3500);
newCust123.transferMoney(newCheckingAccount, 2000);
newCheckingAccount.display();
newCheckingAccount.display();
}//MAIN ENDS
}//CLASS ENDS HERE
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.