connect 'jdbc:derby://localhost:1527/toyBankDB;create=true;user=manager;password=toyManPWD;';

-- Some query examples on the information stored in the ToyBank Relational Database.
-- by Dr. Alvaro Monge
-- amonge AT csulb DOT edu

-- Customers who are depositors... i.e. who own some account
SELECT ID, Fname, Lname, cid, acct_number
  FROM Customers C INNER JOIN Depositors D ON C.id=D.cid;


-- An additional join with Accounts can provide information about the account such as the balance
SELECT ID, Fname, Lname, cid, A.acct_number, balance, date_opened
  FROM Customers C 
       INNER JOIN Depositors D ON C.id=D.cid
       INNER JOIN Accounts A ON D.acct_number=A.acct_number;


-- Customers who have loans...
SELECT ID, fname, lname, owner_id, loan_number
  FROM Customers C INNER JOIN Loans L ON C.id=L.owner_id;


-- Note that no additional joins are required to get information about the loan, 
-- since unlike accounts, only one person can own a loan -- i.e. it's a 1-to-many relationship

SELECT ID, fname, lname, owner_id, loan_number, amount
  FROM Customers C INNER JOIN Loans L ON C.id=L.owner_id;



-- ID's of customers who do not own any loans

SELECT id
  FROM Customers
EXCEPT
SELECT owner_id AS id
  FROM loans;


  
-- ID and names of Customers who do not own any loans

SELECT id, fName, lName
  FROM Customers
EXCEPT
SELECT owner_id as id, fName, lName
  FROM loans INNER JOIN customers ON owner_id=id;

disconnect;
exit;