Loop in a sql query

Question

Hi,

In my Sql database, I have tables

1 Account_Customer -AccountID -CustomerID -Account_CustomerID

2 Accounts -AccountID -Balance

3 Customers -CustomerID -Name -Sex -Age

4 Loans -LoanID -Amount -BranchName

5 Loan_Customer -Loan_CustomerID -LoanID -CustomerID

and I want to write a stored procedure for Listing customers together with their number of accounts, total account balances, number of loans, and total loan amounts, living in a given city, having given sex and age, and having accounts and/or loans in a given branch.

I can do the number of accounts and total account balances in my program but I need a stored procedure for my assignment.

Any help would be greatly appreciated.

Answer 1

There isn't enough information to relate the Loans table to the other tables.

Answer 2

Okay, lets give this a bash (although I still think we're missing a few pieces)

CREATE PROCEDURE SelectCustomerDetailsBySex
  @Sex <your data type here>
AS
BEGIN
  SELECT cus.CustomerID,
    cus.Name,
    COUNT(acc.AccountID) AS AccountCount,
    SUM(acc.Balance) AS AccountBalance,
    COUNT(loa.LoanID) AS LoanCount,
    SUM(loa.Amount) AS LoanTotal
  FROM Customers cus
  LEFT OUTER JOIN Account_Customer ac ON cus.CustomerID = ac.CustomerID
  LEFT OUTER JOIN Accounts acc ON ac.AccountID = acc.AccountID
  LEFT OUTER JOIN Loan_Customer lc ON cus.CustomerID = lc.CustomerID
  LEFT OUTER JOIN Loans loa ON lc.LoanID = loa.LoanID
  WHERE cus.Sex = @Sex
  GROUP BY cus.CustomerID,
    cus.Name;
END

Will that do as an example, or would you like me to do another?