Account: AccountType Name Rate Terms A Private 10.00% 30 B Corporate 7.00% 60 C
ID: 3764329 • Letter: A
Question
Account:
AccountType Name Rate Terms
A Private 10.00% 30
B Corporate 7.00% 60
C Preferred 5.00% 90
Customer:
CustomerID FirstName LastName AccountType DateAcquired
1123 Barney Rubble A 4/1/2000
1234 Bugs Bunny B 4/4/2000
2134 Fred Flintstone B 12/1/1999
2233 Herman Munster A 3/5/1990
3324 George Jetson C 3/30/1993
3333 Jane HisWife A 3/31/1993
Loan:
LoanID DistributionDate CustomerID LoanAmount
120 3/10/00 1234 $500.00
121 4/4/00 2233 $10,000.00
122 3/14/00 1234 $500.00
123 3/15/00 1123 $1,000.00
For each of the following questions, write the complete SQL syntax that will produce the results specified. Do not include any extra SQL clauses that are not required by the question (for example, do not sort your results unless this has been specifically stated or implied by the requested data). Make sure any calculated results are labeled appropriately.
Display the loan ID, customer name (first and last), and loan amount for all loans over $5,000.00.
Provide a list of customers (first and last name) who have at least one loan. Do not show duplicate customer names.
For each customer with loans, show the customer ID, last name, and the total amount of loans that customer has.
For every loan, show the loan ID number, the original loan amount, and the total amount to be repaid (loan amount + appropriate interest).
Explanation / Answer
Query#1: Display the loan ID, customer name (first and last), and loan amount for all loans over $5,000.00.
SELECT LoanID,LoanAmount FROM Loan L AND FirstName, LastName FROM Customer C
WHERE C.customerID = L.customerID AND L.LoanAmount>5000;
Query#2: Provide a list of customers (first and last name) who have at least one loan. Do not show duplicate customer names.
SELECT FirstName, LastName FROM Customer C, Loan L
WHERE C.customerID = L.customerID;
Query#3: For each customer with loans, show the customer ID, last name, and the total amount of loans that customer has.
SELECT CustomerID, LastName FROM Customer C AND count.CustomerID FROM Loan L
WHERE IF(C.customerID = L.customerID) THEN count.customerID = count.customerID+1;
Query#4: For every loan, show the loan ID number, the original loan amount, and the total amount to be repaid (loan amount + appropriate interest).
SELECT LoanID,LoanAmount,sum.LoanAmount FROM Loan L, Customer C, Account A
WHERE L.customerID = C.customerID AND C.AccountType = A.AccountType
AND sum.LoanAmount = L.LoanAmount - (L.LoanAmount*((A.rate/100)*A.terms));
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.