The following simple database has been designed to keep trac

The following (simple) database has been designed to keep track of customers and outstanding loans.Some sample data has been entered into the database. · Account: Accountlype Name Rate Terms Private Corporate Preferred 10.00% 7.00% 5.00% 30 60 90 Customer: CustomeriDFirstName 1123 1234 2134 2233 3324 LastName AccountType DateAcquired Barney Bugs Fred Herman George Jane Rubble Bunny Flintstone Munster Jetson HisWife 4/1/00 6 8 4/4/00 12/1/99 3/5/90 3/30/93 3/31/93 Loan: LoanlD 120 121 122 123 DistributionDate CustomerlD LoanAmount S500.00 S10,000.00 $500.00 $1,000.00 3/10/00 1234 4/4/00 2233 3/14/00 1234 3/15/00 1123 1. Using the above tables, show exactly what would be produced by the following SQL statement: SELECT LastName, DateAcquired FROM Customer WHERE Accounthpe = \"A\" ORDER BY LastNme; 2. Using the above tables, show exactly what would be produced by the following SQL statement: SELECT LastName, DateAcquired FROM Customer INNER JOIN Loan ON Customer·CustomerID = Loan·CustomerID WHERE AccountType = \"A\"

Solution

8)select L.LoanID,C.FirstName||\',\'||C.LastName,L.LoanAmount from Customer C,Loan L where C.CustomerID=L.CustomerID and L.LoanAmount>5000;

9)select distinct FirstName||\',\'||LastName from Customer where CustomerID in(select CustomerID from Loan);

10)select C.CustomerID,C.LastName,sum(L.LoanAmount) from Customer C,Loan L where C.CustomerID in(select CustomerID from Loan) group by L.CustomerID;

11)select L.LoanID,L.LoanAmount,
(COALESCE(L.LoanAmount, 0) + COALESCE(L.LoanAmount*MONTHS_BETWEEN(sysdate,L.DistributionDate)*(A.Rate), 0))
from Loan L,Account A,Customer C
where L.CustomerID=C.CustomerID and C.AccountType=A.AccountType;