---
title: "SQL: branch city with assets over 1000000"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:sql-branch-assets-million"
source: https://www.bctengineer.com/q/semester_6/database_management_system/sql-branch-assets-million
site: BCT Engineering
---

# SQL: branch city with assets over 1000000

Database Management System · Chapter 3: Relational Languages & Relational Model

- Asked in: year not given
- Video: [DBMS - Basic Structure of SQL](https://www.youtube.com/watch?v=kh3a--gzIPg) — TutorialsPoint
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/sql-branch-assets-million
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-3

## Question

Consider database schema:
Account (acc_no, branch_name, balance)
Branch (branch_name, branch_city, assets)
Customer (customer_name, customer_street, customer_city)
Loan (loan_no, branch_name, amount)
Depositor (customer_name, acc_no)
Borrower (customer_name, loan_no)
List branch city and total assets where asset > 1000000.

## Short answer

Use SELECT branch_city, SUM(assets) FROM Branch WHERE assets > 1000000 GROUP BY branch_city. This sums assets per city for branches with assets over one million.

## Answer

Branch city and assets are both in the `Branch` table. Since "total assets" is asked per city, group by city and use `SUM`; the assets > 1000000 condition is on individual branches, so it goes in `WHERE`.

```sql
SELECT   branch_city, SUM(assets) AS total_assets
FROM     Branch
WHERE    assets > 1000000
GROUP BY branch_city;
```

**Sample (tested):** Branches Dharan (2000000) and Dh2 (3000000) are in Dharan city, Itahari (1500000) is in Itahari, and Pokhara (500000) is excluded. Output: (Dharan, 5000000), (Itahari, 1500000).

If the question only wants each branch listed with its own assets (no adding up), use:

```sql
SELECT branch_city, assets
FROM   Branch
WHERE  assets > 1000000;
```

To filter on the *city total* instead of each branch, the condition would move to `HAVING SUM(assets) > 1000000`.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. Why is assets > 1000000 in WHERE?
   A. It is aggregate
   B. HAVING is invalid
   C. WHERE after GROUP BY
   D. It is a condition on individual branches before grouping
   Answer: D. Row filter goes in WHERE. (Key point: WHERE)

2. What does SUM(assets) GROUP BY branch_city give?
   A. Per branch
   B. Total assets per city
   C. Overall
   D. Count
   Answer: B. Grouping by city sums within each. (Key point: SUM group)

3. Dharan 2000000 + Dh2 3000000: total for Dharan?
   A. 2000000
   B. 3000000
   C. 1000000
   D. 5000000
   Answer: D. 2000000+3000000 = 5000000. (Key point: Sample)

4. When would HAVING SUM(assets) > 1000000 be used?
   A. Row filter
   B. To filter on the city total rather than each branch
   C. Never
   D. For sorting
   Answer: B. HAVING filters groups. (Key point: HAVING)

## Close variants set in other exams

- [SQL: customer name and address](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-address) — Database Management System, chapter 3
- [SQL: branches with avg balance over 50000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-avg-balance-branch) — Database Management System, chapter 3
- [SQL: number of accounts per branch](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-count-accounts-branch) — Database Management System, chapter 3
- [SQL: customer names beginning with su](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-like-su) — Database Management System, chapter 3
- [SQL: customer, branch, account details](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-natural-join-details) — Database Management System, chapter 3
- [SQL: customers with account in Dharan](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customers-dharan-branch) — Database Management System, chapter 3

## Related in Database Management System

- [SQL: create tables for bank schema](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-create-tables-bank) — Database Management System, chapter 3
- [SQL: create and use bank database](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-create-database-bank) — Database Management System, chapter 3
- [SQL: increase balances by 100% and 50%](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-update-balance-rates) — Database Management System, chapter 3
- [RA: increase loans over 10000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-increase-loan-10) — Database Management System, chapter 3
- [RA: count accounts in each branch](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-count-accounts-branch) — Database Management System, chapter 3
- [RA: delete loans under 1000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-delete-small-loans) — Database Management System, chapter 3