Describe SQL injection. How can it be prevented?

Here are the SQL queries for the given database schema:

(i) A query to prepare a list of all customers who have an account at all the branches located in Dhaka

This query identifies customers who have accounts linked to every branch situated in Dhaka. It uses a double NOT EXISTS clause, which is a standard technique for solving relational division problems in SQL. The outer NOT EXISTS checks if there is any Dhaka branch for which the current customer does not have an account. If no such branch exists (meaning the inner NOT EXISTS condition is never met for any Dhaka branch), then the customer has an account at all Dhaka branches.

SELECT DISTINCT C.customer_name
FROM Customer C
WHERE NOT EXISTS (
    SELECT *
    FROM Branch B
    WHERE B.branch_city = 'Dhaka'
    AND NOT EXISTS (
        SELECT *
        FROM Depositor D
        JOIN Account A ON D.account_number = A.account_number
        WHERE D.customer_name = C.customer_name
        AND A.branch_name = B.branch_name
    )
);

(ii) A query to find out the total loan amount of a branch

This query calculates the sum of all loan amounts for each branch. It uses the SUM() aggregate function and the GROUP BY clause to group the loans by their respective branch names, providing the total loan amount for each branch.

SELECT branch_name, SUM(amount) AS total_loan_amount
FROM Loan
GROUP BY branch_name;

(iii) A query to prepare a list of all branches that have assets greater than the asset of at least one branch located in Dhaka

This query lists branches whose assets are greater than the assets of any single branch in Dhaka. The SOME keyword (or ANY) is used with a subquery that retrieves the assets of all branches in Dhaka. This means it returns true if the current branch's assets are greater than at least one of the asset values returned by the subquery, effectively identifying branches with assets surpassing the lowest asset value of a Dhaka branch.

SELECT B1.branch_name
FROM Branch B1
WHERE B1.assets > SOME (
    SELECT B2.assets
    FROM Branch B2
    WHERE B2.branch_city = 'Dhaka'
);

(iv) A query to find out the total loan amount of a customer

This query calculates the total loan amount for each customer. It joins the Customer, Borrower, and Loan tables to establish the links between customers and their loans. The SUM() aggregate function is then used in conjunction with GROUP BY customer_name to sum up the loan amounts for each distinct customer, providing their total borrowing.

SELECT C.customer_name, SUM(L.amount) AS total_loan_amount
FROM Customer C
JOIN Borrower B ON C.customer_name = B.customer_name
JOIN Loan L ON B.loan_number = L.loan_number
GROUP BY C.customer_name;