SQL: customer, branch, account details

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

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

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

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.)