--- title: "SQL: customer, branch, account details" subject: "Database Management System" chapter: 3 key: "semester_6/database_management_system:sql-natural-join-details" source: https://www.bctengineer.com/q/semester_6/database_management_system/sql-natural-join-details site: BCT Engineering --- # SQL: customer, branch, account details Database Management System · Chapter 3: Relational Languages & Relational Model - Asked in: year not given - 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-natural-join-details - 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 all customer details, branch details and acc details according to account number. ## Short answer Join Account, Branch, Depositor, and Customer using natural or explicit joins, then order by acc_no. This gives customer, branch, and account details for each account. ## Answer The details are spread over four tables: Account (acc_no, branch_name, balance), Branch (branch_name, branch_city, assets), Depositor (customer_name, acc_no) and Customer. Depositor links a customer to an account, so all four must be joined. #### Query using NATURAL JOIN ```sql SELECT * FROM Account NATURAL JOIN Branch -- on branch_name NATURAL JOIN Depositor -- on acc_no NATURAL JOIN Customer -- on customer_name ORDER BY acc_no; ``` #### Same query with explicit JOIN conditions ```sql SELECT a.acc_no, a.balance, b.branch_name, b.branch_city, b.assets, c.customer_name, c.customer_street, c.customer_city FROM Account a JOIN Branch b ON a.branch_name = b.branch_name JOIN Depositor d ON a.acc_no = d.acc_no JOIN Customer c ON d.customer_name = c.customer_name ORDER BY a.acc_no; ``` #### Sample output ```text acc_no | branch_name | city | balance | customer | street 101 | Putalisadak | Kathmandu | 25000 | Ram | Baneshwor 102 | Putalisadak | Kathmandu | 8000 | Ram | Baneshwor 103 | Lakeside | Pokhara | 51000 | Sita | Lakeside-6 ``` #### Explanation - Each join matches on the common column, so a row is produced only when the account has a branch, a depositor and a customer. - ORDER BY acc_no arranges the result "according to account number". - Column names branch_city and customer_city are different, so NATURAL JOIN does not join on them by mistake. (Tested in SQLite.) ## Check yourself The answer's key points as multiple-choice questions, with the correct option and why. 1. Why are four tables joined? A. Account has all details B. Only two are needed C. Depositor links customer to account, and Branch and Customer hold details D. Loan is needed Answer: C. Details are spread across tables. (Key point: Why four tables) 2. On which common columns do the natural joins match? A. branch_name, acc_no, customer_name B. branch_city, customer_city C. assets, balance D. loan_no Answer: A. They match same-named columns. (Key point: Natural join columns) 3. What does ORDER BY acc_no do? A. Filters accounts B. Joins C. Sorts the result by account number D. Groups Answer: C. Answers 'according to account number'. (Key point: Order by) ## 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: 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: branch city with assets over 1000000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-branch-assets-million) — 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: 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 - [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: 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: 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: 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