Name: Amaan Shaikh TCOD-38
ASSINMENT NO:2
Q1. Find the names of all branches in loan relation
mysql> SELECT DISTINCT branch_name FROM loan;
+ + ss
| branch_name |
+ +
| Akurdi |
| Nigdi |
| Pune |
+ +
3 rows in set (0.00 sec)
Q2. Find all loan numbers for loans made at Akurdi Branch with loan amount >
12000. mysql> SELECT loan_no FROM loan WHERE branch_name = 'Akurdi' AND amount >
12000;
+ +
| loan_no |
+ +
| L-101 |
| L-105 |
+ +
2 rows in set (0.01 sec)
Q3. Find all customers who have a loan from bank. Find their names, loan_no and loan
amount.
mysql> SELECT borrower.customer_name, loan.loan_no, loan.amount
FROM borrower
INNER JOIN loan ON borrower.loan_no = loan.loan_no;
+ + + +
| customer_name | loan_no | amount |
+ + + +
| Vaibhav | L-101 | 15000 |
| Prasanna | L-102 | 12000 |
| Ajit | L-103 | 13000 |
+ + +
+
Q4. List all customers in alphabetical order who have loan from Akurdi branch.
mysql> SELECT borrower.customer_name
FROM borrower
JOIN loan ON borrower.loan_no = loan.loan_no
WHERE loan.branch_name = 'Akurdi'
ORDER BY borrower.customer_name;
+ +
| customer_name |
+ +
| Ajit |
| Vaibhav |
+ +
Q5. Find all customers who have an account or loan or both at bank.
mysql> SELECT customer_name FROM depositor
UNION
SELECT customer_name FROM borrower;
Name: Amaan Shaikh TCOD-38
+ +
| customer_name |
+ +
| Vaibhav |
| Prasanna |
| Ajit |
| Ankit |
+ --------------- +
Q6. Find all customers who have both account and loan at bank.
mysql> SELECT customer_name FROM depositor
INTERSECT
SELECT customer_name FROM borrower;
+ +
| customer_name |
+ +
| Vaibhav |
| Prasanna |
+ +
Q7. Find all customers who have account but no loan at the bank.
mysql> SELECT customer_name FROM depositor
EXCEPT
SELECT customer_name FROM borrower;
+ +
| customer_name |
+ +
| Ankit |
+ +
Q8. Find average account balance at Akurdi branch.
mysql> SELECT AVG(balance) AS avg_balance FROM account WHERE branch_name = 'Akurdi';
+ +
| avg_balance |
+ +
| 13500.00 |
+ +
Q9. Find the average account balance at each branch.
mysql> SELECT branch_name, AVG(balance) AS avg_balance FROM account GROUP BY
branch_name;
+ + +
| branch_name | avg_balance |
+ + +
| Akurdi | 13500.00 |
| Nigdi | 11000.00 |
| Pune | 15000.00 |
+ + +
Q10. Find no. of depositors at each branch.
mysql> SELECT a.branch_name, COUNT(DISTINCT d.customer_name) AS num_depositors
Name: Amaan Shaikh TCOD-38
FROM account a
JOIN depositor d ON a.account_no = d.account_no
GROUP BY a.branch_name;
+ + +
| branch_name | num_depositors |
+ + +
| Akurdi | 2 |
| Nigdi | 1 |
+ + +
Q11. Find the branches where average account balance > 12000.
mysql> SELECT branch_name FROM account
GROUP BY branch_name
HAVING AVG(balance) > 12000;
+ +
| branch_name |
+ +
| Akurdi | | Pune
|
+ +
2 rows in set (0.00 sec)
Q12. Find number of tuples in customer relation. mysql>
SELECT COUNT(*) AS total_customers FROM customer;
+ +
| total_customers |
+ +
| 4 |
+ +
1 row in set (0.00 sec)
Q13. Calculate total loan amount given by bank. mysql>
SELECT SUM(amount) AS total_loan_amount FROM loan;
+ +
total_loan_a |
mount
4000
0
r s (0.
ow i e 00
n t
|
+ + ||
+ +
1 sec)
Q14. Delete all loans with loan amount between 1300 and 1500.
mysql> DELETE FROM loan WHERE amount BETWEEN 1300 AND 1500;
Q15. Delete all tuples at every branch located in Nigdi.
mysql> DELETE FROM account WHERE branch_name = 'Nigdi';
Name: Amaan Shaikh TCOD-38
Q17. Create sequence roll_seq and use in student table for roll_no column.
mysql> CREATE TABLE student (
-> roll_no INT AUTO_INCREMENT,
-> name VARCHAR(50),
-> PRIMARY KEY (roll_no)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO student(name) VALUES ('Vaibhav'),
('Prasanna'); Query OK, 2 rows affected (0.00 sec) mysql>
SELECT * FROM student;
+ + +
| roll_no | name |
Name: Amaan Shaikh TCOD-38
+ + +
| 1 | Vaibhav | | 2 |
Prasanna |
+ + +
2 rows in set (0.00 sec)
