Scribd
Upload
5 views2 pages

DBMS Assignment SQL

The document outlines an assignment for a Database Management System course, requiring the creation of tables for Customers, Accounts, Transactions, Loans, and Branches with specified data. It also includes a set of SQL queries to extract specific information from the created tables. The queries cover customer details, account balances, transaction records, loan information, and branch statistics.

Uploaded by

hardik.m.makvana123
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

DBMS Assignment SQL

The document outlines an assignment for a Database Management System course, requiring the creation of tables for Customers, Accounts, Transactions, Loans, and Branches with specified data. It also includes a set of SQL queries to extract specific information from the created tables. The queries cover customer details, account balances, transaction records, loan information, and branch statistics.

Uploaded by

hardik.m.makvana123
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Government engineering college, patan

Database management system (3330703)


Assignment 2
==================================================================
Create the following table insert the provided data and solve the SQL queries.

1. Customers

customer_id name email city phone


1 Ramesh Kumar ramesh.k@[Link] Mumbai 9876543210
2 Priya Sharma priya.s@[Link] Delhi 9898989898
3 Arjun Mehta arjun.m@[Link] Bengaluru 9123456780
4 Kavita Iyer kavita.i@[Link] Chennai 9000011122
5 Suresh Patel suresh.p@[Link] Ahmedabad 9345678901

2. Accounts

account_id customer_id account_type balance branch_name


101 1 Savings 50000.00 Mumbai Main
102 2 Current 120000.00 Delhi Central
103 3 Savings 75000.00 Bengaluru One
104 4 Fixed Deposit 200000.00 Chennai South
105 5 Savings 30000.00 Ahmedabad West

3. Transactions

transaction_id account_id trans_date amount trans_type


10001 101 2025-09-01 5000.00 Debit
10002 101 2025-09-03 2000.00 Credit
10003 102 2025-09-02 15000.00 Debit
10004 103 2025-09-05 7000.00 Debit
10005 105 2025-09-06 3000.00 Credit

4. Loans

loan_id customer_id loan_type amount interest_rate


201 1 Home Loan 1500000 8.5
202 2 Car Loan 600000 9.0
203 3 Personal 200000 12.0
204 4 Education 400000 10.0
205 5 Gold Loan 100000 11.0

5. Branches

branch_id branch_name city manager_name


301 Mumbai Main Mumbai Neha Joshi
302 Delhi Central Delhi Amit Khanna
303 Bengaluru One Bengaluru Anjali Rao
304 Chennai South Chennai Rajesh Nair
305 Ahmedabad West Ahmedabad Pooja Desai

SQL Query

1. List all customers living in Ahmedabad.


2. Show account details (account_id, balance, type) for customer “Ramesh Kumar”.
3. Find the total balance of all Savings accounts.
4. Get all transactions made on or after 2025-09-03.
5. Show the names of customers who have taken a loan greater than ₹5,00,000.
6. Find the average interest rate of all loans.
7. Display customers who have both an account and a loan.
8. Show branch name and total number of customers in each branch.
9. Get the highest transaction amount and the corresponding account_id.
10. List all customers who have not made any transactions.

You might also like