Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Rasbin Rijal
PLUS
Rasbin Rijal
Courses Plus Student 10,864 Points
Posted by Rasbin Rijal
Rasbin Rijal
Courses Plus Student 10,864 Points

SQL Query questions (Database)

  1. Find the total no. of loans given by each branch SELECT COUNT (amount) FROM branch, loan GROUP BY branch

  2. Find the name of branch that have maximum loans located in Nepal. SELECT MAX(loans) From branch, loan.

Please suggest me how to correct.

Database name : mydb Table names : branch, loan, assets. Inside branch there are these field names : branch_id, branch_name, assets, loan_amount. Inside loan there are these field names : id, loan_amount, branch_id Inside assets there are these field name : assets_id, amount, branch_id

[ NOTE : You may suggest if I should change / edit the tables or fields for these 2 questions. ]

Bob McCarty
Bob McCarty
Courses Plus Student 16,618 Points
Bob McCarty
Bob McCarty
Courses Plus Student 16,618 Points

Rasbin,

Can you include the table description in your question? By this I mean, please provide the table name and its column names?

1 Answer

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

This is a very strange database - are you sure that loan_amount appears in both the branch and loan tables?

If so, your first query for "Find the total no. of loans given by each branch" could be written as (you were close):

SELECT branch_name, count(*) AS loans
  FROM branch
  GROUP BY branch

But I wouldn't expect to see any loan information in a "branch" table. So if that was a mistake, then the query would be:

SELECT branch_name, count(loan_amount) AS loans
  FROM branch B
  LEFT JOIN loan L ON B.branch_id = L.branch_id
  GROUP BY branch

The bad news is that your second query cannot be done using the tables given, as none of them contain a column for the branch location.

Is any of this from a Treehouse course? Which course?

Rasbin Rijal
Rasbin Rijal
Courses Plus Student 10,864 Points
Rasbin Rijal
Rasbin Rijal
Courses Plus Student 10,864 Points

There is a branch_name. Second question is meant to ask the branch_name with the maximum loan amount.