Day 1
create database practical1;
create table branch(branch_name varchar(255) primary key, branch_city varchar(255) ,assets int);
create table customer (cust_name varchar(255) primary key, cust_street varchar(255), cust_city
varchar(255));
create table account(Acc_no int primary key ,branch_name varchar(255) ,balance int, foreign
key(branch_name) references branch(branch_name));
create table depositor(cust_name varchar(255), Acc_no int, foreign key(cust_name) references
customer(cust_name), foreign key(Acc_no) references account(Acc_no));
create table loan (loan_no int primary key, branch_name varchar(255), amount int, foreign
key(branch_name) references branch(branch_name));
create table borrower( cust_name varchar(255), loan_no int, foreign key (cust_name) references
customer(cust_name), foreign key(loan_no) references loan(loan_no));
insert into branch values ('Akurdi', 'Pune', 155000);
insert into branch values ('Raver', 'Jalgaon', 534450);
insert into branch values ('Nashik', 'Nashik', 525);
insert into customer values ('shubham', 'pimpri street', 'pune');
insert into customer values ('manish', 'nidgi street', 'pune');
insert into customer values ('hitesh', 'vidhyanagar street', 'raver');
insert into account values (1, 'Akurdi', 111111);
insert into account values (2, 'Raver', 1534);
insert into account values (3, 'Nashik', 1456);
Day 2
show tables;
Tables_in_practical1
account
borrower
branch
customer
depositor
loan
SQL > select branch_name from loan;
+-------------+
| branch_name |
+-------------+
| Akurdi |
| Nashik |
| Raver |
+-------------+
3 rows in set (0.0241 sec)
SQL > select loan_no from loan where branch_name = 'Akurdi' and amount <= 1200;
+---------+
| loan_no |
+---------+
| 1|
+---------+
1 row in set (0.0038 sec)
SQL > select distinct branch_name from loan;
+-------------+
| branch_name |
+-------------+
| Akurdi |
| Nashik |
| Raver |
+-------------+
3 rows in set (0.0032 sec)
SQL > select borrower.cust_name, borrower.loan_no, loan.amount from borrower Inner join loan
on borrower.loan_no = loan.loan_no ;
+-----------+---------+--------+
| cust_name | loan_no | amount |
+-----------+---------+--------+
| shubham | 1 | 223 |
| hitesh | 2 | 323 |
| manish | 3 | 553 |
+-----------+---------+--------+
SQL > select borrower.cust_name from borrower INNER JOIN loan on borrower.loan_no =
loan.loan_no;
+-----------+
| cust_name |
+-----------+
| shubham |
| hitesh |
| manish |
+-----------+
3 rows in set (0.0006 sec)
MySQL localhost:33060+ ssl practical1 SQL > select borrower.cust_name from borrower INNER
JOIN loan on borrower.loan_no = loan.loan_no where loan.branch_name = 'Raver';
+-----------+
| cust_name |
+-----------+
| hitesh |
+-----------+
1 row in set (0.0024 sec)
SQL > select borrower.cust_name from borrower INNER JOIN loan on borrower.loan_no =
loan.loan_no where loan.branch_name = 'Akurdi';
+-----------+
| cust_name |
+-----------+
| shubham |
+-----------+
SQL > select avg(balance) from account where branch_name = 'Raver';
+--------------+
| avg(balance) |
+--------------+
| 1453445.0000 |
+--------------+
SQL > select avg(balance) from account where branch_name = 'Raver';
+--------------+
| avg(balance) |
+--------------+
| 1453445.0000 |
+--------------+
1 row in set (0.0006 sec)
SQL > select sum(amount) from loan where branch_name = 'Raver';
+-------------+
| sum(amount) |
+-------------+
| 323 |
+-------------+
1 row in set (0.0005 sec)
MySQL localhost:33060+ ssl practical1 SQL > select * from depositor;
+-----------+--------+
| cust_name | acc_no |
+-----------+--------+
| shubham | 1|
| hitesh | 2|
| manish | 3|
+-----------+--------+
3 rows in set (0.0100 sec)
SQL > select customer.cust_name from customer INNER join depositor on depositor.cust_name =
customer.cust_name Inner join borrower on borrower.cust_name = customer.cust_name;
+-----------+
| cust_name |
+-----------+
| hitesh |
| manish |
| shubham |
+-----------+
SQL > select count(depositor.cust_name) from depositor inner join account on account.acc_no =
depositor.acc_no where account.branch_name = 'Raver';
+----------------------------+
| count(depositor.cust_name) |
+----------------------------+
| 1|
+----------------------------+
Assignment 3
create view View1 as select borrower.cust_name from borrower inner join loan on
borrower.loan_no = loan.loan_no where loan.branch_name = 'Pune_Station';
create view View1 as select borrower.cust_name from borrower inner join loan on
borrower.loan_no = loan.loan_no where loan.branch_name = 'Pune_Station' order by
borrower.cust_name ASC ;
select * from View1;
create view View2 as select branch_name, assets from branch;
insert into View2 values('Delhi', 222222);
update View2 set assets = 33333 where branch_name = 'Delhi';
insert into View2 values('Assam', 434342);
delete from View2 where branch_name = 'Assam';
create view View3 as select borrower.loan_no, depositor.cust_name from borrower , depositor;
create index city on branch (branch_name);
create unique index loan_branch on loan (branch_name, loan_no);
SHOW INDEX FROM branch;
SHOW INDEX FROM loan;
TRUNCATE TABLE custormer;
create companies ( comp_id int primary key, name varchar(25), cost int ,year int );
create table orders (comp_id int, domain varchar(255), quantity int, foreign key (comp_id)
references companies(comp_id) );
select companies.name, companies.cost, orders.domain, orders.quantity from companies inner join
orders on companies.comp_id = orders.comp_id; (edited)
select companies.name, companies.cost, orders.domain, orders.quantity from companies left join
orders on companies.comp_id = orders.comp_id;
select companies.name, companies.cost, orders.domain, orders.quantity from companies right join
orders on companies.comp_id = orders.comp_id;
select name, cost from companies union select domain, quantity from orders;
create view view1 as select companies.name, orders.quantity from companies, orders;
select * from view1;
