B.E.
(E&TC) – 2019 Pattern Database Management Lab
Group: A
Experiment No.4
Design and develop at least 5 SQL queries for suitable database application using
SQL DML statements: Update and delete with operators and functions.
Roll Number
Date of Performance
AIM: Design and develop at least 5 SQL queries for suitable database application using SQL
DML statements: Update and delete with operators and functions.
OBJECTIVES: Develop problem solving ability of the students using advanced Database techniques.
SOFTWARE/TOOLS: MySQL Server, MySQL Workbench, and Command Line Client.
THEORY: -
Set Operators:
The set operations union, intersect, and except operate on relations and correspond to the relational algebra
operations ∪, ∩, -.
Each of the above operations automatically eliminates duplicates; to retain all duplicates use the
corresponding multiset versions union all, intersect all and except all.
Suppose a tuple occurs m times in r and n times in s, then, it occurs:
m+n times in r union all s
min(m,n) times in r intersect all s
max(0, m-n) times in r except all s
Aggregate Functions:
These functions operate on the multiset of values of a column of a relation, and return a value
Avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
14
B.E. (E&TC) – 2019 Pattern Database Management Lab
Solve following queries:
Q1. Find the names of all branches in loan relation.
Q2. Find all loan numbers for loans made at Akurdi Branch with loan amount > 12000.
Q3. Find all customers who have a loan from bank. Find their names.loan_no and loan amount.
Q4. List all customers in alphabetical order who have loan from Akurdi branch.
Q5. Find all customers who have an account or loan or both at bank.
Q6. Find all customers who have both account and loan at bank.
Q7. Find all customer who have account but no loan at the bank.
Q8. Find average account balance at Akurdi branch.
Q9. Find the average account balance at each branch
Q10. Find no. of depositors at each branch.
Q11. Find the branches where average account balance > 12000.
Q12. Find number of tuples in customer relation.
Q13. Calculate total loan amount given by bank.
Q14. Delete all loans with loan amount between 1300 and 1500.
Q15. Delete all tuples at every branch located in Nigdi.
Q.16. Create synonym for customer table as cust.
Q.17, Create sequence roll_seq and use in student table for roll_no column.
Create above tables with appropriate constraints like primary key, foreign key, check constrains, not null etc.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * Table Structure * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Create table Account(Acc_no, branch_name, balance);
SQL>select * from account:
ACC_NO BRANCH_NAME BALANCE
--------------- --------------------------- --------------------
1001 Akurdi 15000
1002 Nigdi 11000
1003 Chinchwad 20000
1004 Wakad 10000
1005 Akurdi 14000
1006 Nigdi 17000
15
B.E. (E&TC) – 2019 Pattern Database Management Lab
6 rows selected.
Create table branch (branch_name, branch_city, assets):
SQL> select * from branch;
BRANCH_NAME BRANCH_CITY ASSETS
----------------------- ------------------------- -----------------------
Akurdi Pune 200000
Nigdi Pimpari_chinchwad 300000
Wakad Pune 100000
Chinchwad Pimpari_chinchwad 400000
Sangvi Pune 230000
Create table customer(cust_name, cust_street, cust_city) :
SQL> select * from customer1;
CUST_NAME CUST_STREET CUST_CITY
--------------------- --------------------- ---------------------
Rutuja JM road Pune
Alka Senapati road Pune
Samiksha Savedi road Pimpri_chinchwad
Trupti Lakshmi road Pune
Mahima Pipeline road Pimpari_chinchwad
Ayushi FC road Pune
Priti Camp road Pimpri_chinchwad
7 rows selected.
Create table Depositor(cust_name, acc_no):
SQL>select * from depositer;
CUST_NAME ACC_NO
----------------- --------------
Rutuja 1005
Trupti 1002
Samiksha 1004
16
B.E. (E&TC) – 2019 Pattern Database Management Lab
Loan(loan_no, branch_name, amount):
SQL>select * from loan;
LOAN_NO BRANCH_NAME AMMOUNT
-------------- ----------------------- ----------------
2001 Akurdi 2000
2002 Nigdi 1200
2003 Akurdi 1400
2004 Wakkad 1350
2005 Chinchwad 1490
2006 Akurdi 12300
2007 Akurdi 14000
7 rows selected.
Create table borrower(cust_name, loan_no):
SQL>select * from borrower;
CUST_NAME LOAN_NO
----------------- ---------------
Mahima 2005
Trupti 2002
Rutuja 2004
Ayushi 2006
Priti 2007
* * * * * * * * * * * * * * * * * * * * *Problem Statements* * * * * * * * * * * * * * * * * * * * * * * * * * *
Q1. Find the name of all branches in loan relation.
SQL>select branch_name from loan;
BRANCH_NAME
----------------------
Akurdi
Nigdi
Akurdi
Wakad
Chinchwad
Akurdi
7 rows selected.
Q2. Find all loan numbers for loans made at Akurdi Branch with loan amount>12000.
SQL> select loan no from loan where branch name='Akurdi' and amount>12000;
17
B.E. (E&TC) – 2019 Pattern Database Management Lab
LOAN_NO
--------------
2006
2007
Q3. Find all customers who have a loan from bank. Find their names, loan_no and loan
amount.
SQL> select b.cust_name,b.loan_no, l.amount from borrower b inner join loan 1 on
b.loan_no=l.loan_no;
CUST_NAME LOAN_NO AMOUNT
----------------- --------------- --------------
Trupti 2002 1200
Rutuja 2004 1350
Mahima 2005 1490
Ayushi 2006 12300
Priti 2007 14000
Q4. List all customers in alphabetical order who have loan from Akurdi branch.
SQL> select b.cust_name from borrower b inner join loan 1 on b.loan_no=l.loan_no
where 1.branch_name 'Akurdi' order by b.cust name;
CUST_NAME
------------------
Ayushi
Priti
Q5. Find all customers who have an account or loan or both at bank.
SQL>select cust_name from depositer union select cust_name from borrower,
CUST_NAME
-----------------
Ayushi
MahimaPriti
Rutuja
Samiksha
Trupti
6 rows selected.
Q6. Find all customers who have both account and loan at bank.
SQL> select cust_name from depositer intersect select cust_name from borrower;
CUST_NAME
-----------------
Rutuja
Trupti
Q7. Find all customer who have account but no loan at the bank.
SQL> select cust_name from depositer minus select cust_name from borrower;
18
B.E. (E&TC) – 2019 Pattern Database Management Lab
CUST_NAME
------------------
Samiksha
Q8. Find average account balance at Akurdi branch.
SQL> select avg(balance) from account where branch_name='Akurdi';
AVG(BALANCE)
-------------------
14500
Q9. Find the average account balance at each branch
SOI select branch name.avghalance) from account group by branch name;
BRANCH NAME AVG(BALANCE)
Chinchwad 20000
Nigdi 14000
Wakad 10000
Akurdi 14500
Q10. Find no. of depositors at each branch.
SQL> select branch name.count(branch name) from account a inner join depositer d on
a.acc_no=d.acc_no group by branch_name;
BRANCH_NAME COUNT(BRANCH_NAME)
--------------------------------------------------------------------
Nigdi 1
Wakad 1
Akurdi 1
Q11. Find the branches where average account balance > 12000.
SQL> select branch_name from account group by branch_name having avg(balance)>1200;
BRANCH ΝΑΜΕ
----------------------
Chinchwad
Nigdi
Wakad
Akurdi
Q12. Find number of tuples in customer relation.
SQL> select count(cust_name) no_of_tuples from customerl;
NO_OF_TUPLES
---------------------
7
Q13. Calculate total loan amount given by bank.
SQL seleet sum(amount) total loan amount from loan;
19
B.E. (E&TC) – 2019 Pattern Database Management Lab
TOTAL LOAN AMOUNT
------------------------------
33740
Q14. Delete all loans with loan amount between 1300 and 1500.
SQL> delete from loan where amount>1300 and amount<1500;
LOAN_NO BRANCH_NAME AMOUNT
2001 Akurdi 2000
2002 Nigdi 1200
2006 Akurdi 12300
2007 Akurdi 14000
Q15. Delete all tuples at every branch located in Nigdi.
SQL>delete from branch where branch_name='Nigdi';
Q.16. Create synonym for customer table as cust.
SQL> create public synonym cust2 for customer1; Synonym created.
Q.17. Create sequence roll_seq and use in student table for roll_no column.
CONCLUSION:
QUESTIONS:
1. Explain the differences between truncate, drop and delete?
2. Explain function MIN(), GROUP_CONCAT(), AVG()
3. What is the need of view?
REFERENCES:
[1] Database System Concepts 7th Edition Avi Silberschatz, Henry F. Korth, S Sudarshan. Mc Graw
Hill Publication. Chapter- 03, Page No-65-111
Marks (Out of 20) Signature of Faculty with Date
MR (6) MP (6) MU (8) Total (20)
MR – Marks for Regularity, MP – Marks for Presentation, MU – Marks for
20
Understanding
