The following (simple) database has been designed to keep track of customers and
ID: 3936502 • Letter: T
Question
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"Explanation / Answer
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;