SQL Exercise 5

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises

Consider a relation REPAYMENT with the following schema;

REPAYMENT(BORROWER_ID, NAME, ADDRESS, LOANAMOUNT, REQUESTDATE, REPAYMENT_DATE, REPAYMENT_AMOUNT)

Assume that this table records the repayment of loans by the borrowers. A borrower may have multiple entries if he/she has paid multiple installments.

Write SQL statements (queries) to achieve the following;

Question (a)

Find all the records with information on repayments from the borrower with id equal to 42, and where the lent amount exceeds 1000.

Answer (a)

SELECT *

FROM Repayment

WHERE borrower_id=42 AND loanamount>1000;

Question (b)

Find the total amount repaid for every address in the repayment table.

Answer (b)

SELECT address, SUM(repayment_amount)

FROM Repayment

GROUP BY address;

Question (c)

Delete all information on the completed loans. (Note: you can find the status of the loan by summing the total repaid amount. If the total repaid amount is equal to the loan amount, then you would say that the loan is ended.)

Answer (c)

DELETE FROM Repayment A

WHERE loanamount=

(SELECT SUM(repayment_amount)

FROM Repayment B

WHERE B.borrower_id=A.borrower_id AND B.requestdate=A.requestdate);

Question (d)

Find all the borrower names who has unique address. (ie., you should not count the borrowers who are from the same address)

Answer (d)

SELECT name

FROM Repayment A

WHERE 1=

(SELECT COUNT(DISTINCT name)

FROM Repayment B

WHERE A.address=B.address);

Question (e)

Find the total number of repayments made by every borrower.

Answer (e)

SELECT borrower_id, count(*)

FROM repayment

GROUP BY borrower_id;

*******************