B.E.
(E&TC) – 2019 Pattern Database Management Lab
Group: A
Experiment No.5
Design and develop at least 5 SQL queries for suitable database application using
SQL DML statement: all types of join and Sub-Query.
Roll Number
Date of Performance
AIM: Design and develop at least 5 SQL queries for suitable database application using SQL
DML statement: all types of join and Sub-Query.
OBJECTIVES: Develop problem solving ability of the students using advanced Database
techniques.
SOFTWARE/TOOLS: MySQL Server, MySQL Workbench, and Command Line Client.
THEORY: -
JOINS: Joins are used to retrieve the data from multiple tables.
Types of Joins
1. EQUI_JOIN
2. NON EQUI_JOIN
3. SELF JOIN
4. OUTER JOIN
4.1 Right outer join
4.2 Left outer join
4.3 Full outer join
1. Create following Tables
cust_mstr(cust_no,fname, Iname)
add_dets(code_no, add1, add2, state, city, pincode)
Retrieve the address of customer Frame as 'xyz' and Loames 'pr'
2. Create following Tables
cust_mstr(custno, fname, Iname)
21
B.E. (E&TC) – 2019 Pattern Database Management Lab
acc_fd_cust_dets(codeno, acc_fd_no)
fd_dets(fd_sr_no, amt)
List the customer holding fixed deposit of amount more than 5000
3. Create following Tables
emp mstre mpno.f name, l_name, m_name.dept, desg, branch_no)
branch mstr(name, b_no)
List the employee details along with branch names to which they belong
4. Create following Tables
emp_mstr(emp_no, f_name, l_name, m_name,dept)
cntc_dets(code_no.cntc_type,cntc_data)
List the employee details along with contact details using left outer join & right join
5. Create following Tables
cust_mstr(cust_no, fname, Iname)
add_dets(code_no, pincode)
List the customer who do not have bank branches in their vicinity.
6. a) Create View on borrower table by selecting any two columns and perform inser update delete
operations
b) Create view on borrower and depositor table by selecting any one column from each table perform insert
update delete operations
c) create updateable view on borrower table by selecting any two columns and perform insert, update and
delete operations.
Solutions:
1. Create following Tables
cust_mstr(cust_no, fname, Iname)
add_dets(code_no, add1, add2, state, city, pincode)
Retrieve the address of customer Fname as 'Rutuja' and Lname as 'Deshmane’
SQL> select * from cust_mstr;
CUSTNO FNAME LNAME
------------ -------------- -----------------
C101 Rutuja Deshmane
C102 Trupti Bargaje
C103 Samiksha Dharmadhikari
C104 Mahima Khandelwal
22
B.E. (E&TC) – 2019 Pattern Database Management Lab
SQL> select add1, add2 from add_dets where code_no in(select custno from cust_mstr where fname=’Rutuja’
and lname=’Deshmane’);
ADD1 ADD2
----------------------------------------
Venu nagar dange chowk
2. Create following Tables
cust_mstr(custno, fname, Iname)
acc_fd_cust_dets(codeno, acc_fd_no)
fd_dets(fd_sr_no,amt)
List the customer holding fixed deposit of amount more than 5000
SQL> select fname, Iname from cust mstr where custno in(select codeno from acc_fd_cust_dets where acc
_fd_no in(select fd_sr_no from fd_dets where amt>5000));
FNAME LNAME
-----------------------------------------
Rutuja Deshmane
Samiksha Dharmadhikari
3. Create following Tables
emp_mstr(e_mpno, f_name, l_name, m_name, dept, desg, branch_no)
branch_mstr(name, b_no)
List the employee details along with branch names to which they belong
SQL> select emp_no, fname, Iname, mname, dept, desg, branch_no, b.name from emp_mstr e inner join
branch_tb b on e.branch_no=b.b_no;
EMP_NO FNAME LNAME MNAME DEPT DESG BRANCH_NO NAME
1011 Samarth Deshmane Suryakant sports trainer 2011 Akurdi
1012 Alka Choudhari Rohitash comp tester 2012 Nigdi
1013 Shriyash Shingare Santosh comp coder 2013 chinchwad
4. Create following Tables
emp_mstr(emp_no, f_name, l_name, m_name,dept)
cntc_dets(code_no, cntc_type, cntc_data)
List the employee details along with contact details using left outer join & right join
SQL> select emp_no, fname, Iname, mname, dept, c.code_no, c.cntc_type, c.cntc_data from emp_mstr e left
outer join cntc_dets c on e.emp_no=c.code_no;
23
B.E. (E&TC) – 2019 Pattern Database Management Lab
EMP_NO FNAME LNAME MNAME DEPT CODE_NO CNTC_TYPE
CONTC_DATA
------------------------------------------------------------------------------------------------------------------------------------
1011 Samarth Deshmane Suryakant sports 1011 phno
9689349523
1012 Alka Choudhari Rohitash comp 1012 email
Rutu2gmail.com
1013 Shriyash Shingare Santosh comp
SQL> select emp_no, fname, lname, mname, dept, c.code_no, c.cntc_type, c.cntc_data from emp_mstr e right
outer join cntc_dets c on e.emp_no=c.code_no;
EMP_NO FNAME LNAME MNAME DEPT CODE_NO CNTC_TYPE
CNTC_DATA
-------------------------------------------------------------------------------------------------------------------------------------------
1011 Samarth Deshmane Suryakant sports 1011 phno
9689349523
1012 Alka Choudhari Rohitash comp 1012 email
rutu@gmail.com
1014
shrink@gmail.com
5. Create following Tables
cust_mstr(cust_no, fname, Iname)
add_dets(code_no, pincode)
List the customer who do not have bank branches in their vicinity.
SQL> select * from cust_mstr where cust_no in (select code_no from add_dets where code_no like ‘C%’ and
pincode not in (select pincode from add_dets where code_no like ‘B%’));
CUST_NO FNAME LNAME
C102 Trupti Bargaje
6. A) Create View on borrower table by selecting any two columns and perform insert update delete
operations
SOL > select * from borrower;
ACC_NO NAME AMOUNT
------------------------------------------------------------
101 Aish 10000
102 Adi 10000
103 Swati 45216
24
B.E. (E&TC) – 2019 Pattern Database Management Lab
SQL> create view bl as select name, amount from borrower;
View created.
SQL> select * from bl;
NAME AMOUNT
----------------------------
Aish 10000
Adi 10000
Swati 45216
SQL> update b1 set amount=7845 where name=’swati’;
1 row updated.
SOL > select * from borrower;
ACC_NO NAME AMOUNT
------------------------------------------------------------
101 Aish 10000
102 Adi 10000
103 Swati 7845
SQL> delete from b1 where name=’swati’;
1 row deleted.
SQL> select * from borrower;
ACC_NO NAME AMOUNT
--------------------------------------------------------------
101 Aish 10000
B) Create view on borrower and depositor table by selecting any one column from each table perform insert
update delete operations.
SQL> select * from borrower;
ACC_NO NAME AMOUNT
----------------------------------------------------------------
101 Aish 10000
102 Adi 10000
SQL> select * from depositor;
DACC_NO DNAME DAMOUNT
------------------------------------------------------------------------
102 Adi 45789
104 Sneha 7895
103 Swati 79854
SQL> create view b3 as select amount loan, damount deposit from borrower, depositor;
25
B.E. (E&TC) – 2019 Pattern Database Management Lab
View created.
SQL> select * from b3;
LOAN DEPOSIT
---------------------------------------
10000 45789
10000 7895
10000 79854
C) create updateable view on borrower table by selecting any two columns and perform insert, Update and
delete operations.
SQL> create table borrower(acc_no number(10) primary key, name varchar(10), amount number(10));
Table created.
SQL> insert into borrower values(&acc,'&name',&amount);
Enter value for acc: 101
Enter value for name: Aish
Enter value for amount: 10000
old 1: insert into borrower values(&acc,'&name', &amount)
new 1: insert into borrower values(101, 'aish', 10000)
1 row created.
SQL>/
Enter value for acc: 102
Enter value for name: Adi
Inter value for amount: 4500
old 1: insert into borrower values(&ace, ‘&name', &amount)
new 1: insert into borrower values(102,'adi.4500)
1 row created.
SQL>/
Enter value for ace: 103
Enter value for name: Swati
Enter value for amount: 45216.
old 1: insert into borrower values(&acc, ‘&name', &amount)
new 1: insert into borrower values(103,'swati', 45216)
26
B.E. (E&TC) – 2019 Pattern Database Management Lab
I row created.
SQL> create view bview as select acc_no, amount from borrower;
View created.
SQL> insert into bview values(&acc, &amount);
Enter value for acc: 104
Enter value for amount: 58901
old 1: insert into bview values(&acc, &amount)
new 1: insert into bview values(104, 58901)
1 row created.
SQL> select * from borrower;
ACC_NO NAME AMOUNT
-------------------------------------------------------------
101 aish 10000
102 adi 4500
103 swati 45216
104 58901
SQL> update bview set amount=45000 where acc_no=104;
1 row updated.
SQL> select * from borrower;
ACC NO NAME AMOUNT
----------------------------------------------------------------
101 aish 10000
102 adi 4500
103 swati 45216
104 45000
SQL> delete from bview where acc_no=104;
1 row deleted.
SQL> select * from bview;
ACC_NO AMOUNT
--------------------------------------
101 10000
102 4500
103 45216
CONCLUSION:
27
B.E. (E&TC) – 2019 Pattern Database Management Lab
QUESTIONS:
1. What is need of sub query?
2. Explain self-join with example?
REFERENCES:
[1] Database System Concepts 7th Edition Avi Silberschatz, Henry F. Korth, S Sudarshan. Mc Graw Hill
Publication. Chapter- 04, Page No- 125-164
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
Understanding
28
