Practical no 3: SQL Queries- all types of Join, Sub-Query and View:
Write at least 10 SQL queries for suitable database application using SQL DML
statements.
Note: Instructor will design the queries which demonstrate the use of concepts like
all types of
Join, Sub-Query and View
................................................................................
mysql> create database Account;
Query OK, 1 row affected (0.02 sec)
mysql> use Account;
Database changed
mysql> create table Account(Acc_no int,branch_name varchar(20), balance int);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into Account value(110,'581 bank', 20000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Account value(111,'IFFF bank',560000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Account value(111,'MAHARASHTRA bank', 2450000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Account value(112, 'KOTAK bank', 780000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Account value(114, 'ICIC bank', 78540);
Query OK, 1 row affected (0.01 se:)
mysql> SELECT*FROM Account;
Acc_no Branch Name Balance
110 SBI Bank 20,000
111 IFFF Bank 560,000
111 MAHARASHTRA Bank2,450,000
112 KOTAK Bank 780,000
114 ICIC Bank 178,540
5 rows in set (0.00 sec)
mysql> create table branch(branch_name varchar(20), branch_city varchar(20), assets
varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into branch value('SBI bank', 'pune', 'car loan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into branch value('IFFF bank, 'Akurdi', 'home loan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into branch value('MAHARASHTRA bank', 'Thhane', 'personal loan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into branch value('KOTAK bank', 'lonee', 'education loan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into branch value('ICIC', 'alndi', 'gold loan');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM branch;
branch_name branch_city assets
SBI Bank Pune Car Loan
IFFF Bank Akurdi Home Loan
MAHARASHTRA Bank Thane Personal Loan
KOTAK Bank lonee Education Loan
ICIC Bank alndi Gold Loan
5 rows in set (0.00 sec)
mysql> create table customer(cust_name varchar(20), cust_city varchar(20),
cust_street varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into customer value('swati', 'pune', 'Mudhava');
Query OK, 1 row affected (0.01 sec)
mysql> insert into customer value('jyoti', 'Akurdi', 'hadpser');
Query OK, 1 row affected (0.01 sec)
mysal> insert into customer value('sakshi', 'Thhane', 'hadevadi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into customer value('priti', 'lonee', 'keshvnher');
Query OK, 1 row affected (0.01 sec)
mysql> insert into customer value('siddhi', 'alndi', 'kharadi");
Query OK, 1 row affected (0.01 sec)
mysql> select * from customer;
cust_name cust_city cust_street
Swati Pune Mudhava
Jyoti Akurdi Hadpsar
Salaht Thane Hadevadi
Priti lonee Keshavnher
Siddhi alndi Kharadi
5 rows in set (0.00 sec)
mysql> create table depositor(cust_name varchar(20), Acc_no int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into depositor value('swati,110);
Query OK, 1 row affected (0.01 sec)
mysql> insert into depositor value('jyoti',111);
Query OK, 1 row affected (0.01 sec)
mysql> insert into depositor value('sakshi',112);
Query OK, 1 row affected (0.01 sec)
mysql> insert into depositor value('priti',113);
Query OK, 1 row affected (0.01 sec)
mysql> insert into depositor value('siddhi',114),
Query OK, 1 row affected (0.01 sec)
mysql> select * from depositor;
cust_name Acc_no
Swati 110
Jyoti 111
Isakshi 112
Priti 113
Siddhi 114
5 rows in set (0.00 sec)
mysql> create table Loan(Loan_no int,branch_name varchar(20), amount int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into Loan value(11, 'SBI bank', 1208);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan value (12, 'IFFF bank', 64833);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan value(13, 'MAHARASHTRA bank', 6372);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan value(14,'KOTAK bank', 9974);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Loan value(15, 'ICIC bank', 231214);
Query OK, 1 row affected (0.01 sec)
mysql> select * from Loan;
Loan_no branch_name amount
11 SBI Bank 1208
12 IFFF Bank 164833
13 MAHARASHTRA Bank 6372
14 KOTAK Bank 9974
15 ICIC Bank 231214
rows in set (0.00 sec)
mysql> create table borrower(Loan_no int, cust_name varchar(20));
Query OK, 0 raws affected (0.03 sec)
mysql> insert into Borrower value(11,'swati');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Borrower value(12,'jyoti');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Borrower value(13, 'sakshi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Borrower value(14,'priti');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Borrower value(15, 'siddhi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from borrower;
Loan_no cust_name
11 Swati
12 Jyoti
13 Sakshi
14 Priti
15 Siddhi
5 rows in set (0.00 sec)
mysql> create view borrower_view as select Loan_no from borrower;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from borrower;
Loan_no cust_name
11 Swati
12 Jyoti
13 Sakshi
14 Priti
15 Siddhi
5 rows in set (0.00 sec)
mysql> select * from borrower_view;
Loan_no
11
12
13
14
15
5 rows in set (0.01 sec)
mysql> update borrower set cust_name='radha' where Loan_no=12;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from borrower;
Loan_no cust_name
11 Swati
12 Radha
13 Sakshi
14 Priti
15 Siddhi
5 rows in set (0.00 sec)
mysql> alter table borrower drop column Loan_no;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from borrower;
cust_name
Swati
Radha
Isakshi
Priti
Siddhi
5 rows in set (0.00 sec)
mysql> select * from depositor;
cust_name Acc_no
Swati 110
Jyoti 111
Sakshi 112
Priti 113
Siddhi 114
5 rows in set (0.00 sec)
mysql> create view depositor_view as select cust_name, Acc_no from depositor;
Query OK, 0 rows affected (0.01 sec)
mysql> select*from depositor_view;
cust_name Acc_no
Swati 110
Jyoti 111
Sakshi 112
Priti 113
Siddhi 114
5 rows in set (0.00 sec)
mysql> select * from customer;
cust_name cust_city cust_street
Swati Pune Mudhava
Jyoti Akurdi Hadpsar
Sakshi Thane Hadevadi
Priti lonee Keshavnher
Siddhi alndi Kharadi
5 rows in set (0.00 sec)
