Implement DML commands SELECT, INSERT, UPDATE and DELETE
create table DEPOSIT(ACTNO VARCHAR(10),CNAME VARCHAR(20),BNAME VARCHAR(20),AMOUNT NUMERIC(8,2),ADATE DATE);
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| ACTNO | varchar(10) | YES | | NULL | |
| CNAME | varchar(20) | YES | | NULL | |
| BNAME | varchar(20) | YES | | NULL | |
| AMOUNT | decimal(8,2) | YES | | NULL | |
| ADATE | date | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
create table BRANCH(BNAME VARCHAR(20), CITY VARCHAR(20));
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| BNAME | varchar(20) | YES | | NULL | |
| CITY | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
create table CUSTOMERS(CNAME VARCHAR(20),CITY VARCHAR(20));
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| CNAME | varchar(20) | YES | | NULL | |
| CITY | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
create table BORROW(LOANNO VARCHAR(20),CNAME VARCHAR(20),BNAME VARCHAR(20),AMOUNT NUMERIC(8,2));
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| LOANNO | varchar(20) | YES | | NULL | |
| CNAME | varchar(20) | YES | | NULL | |
| BNAME | varchar(20) | YES | | NULL | |
| AMOUNT | decimal(8,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
INSERT INTO DEPOSIT VALUES ('100', 'ANIL', 'VREC', 1000.00, '1996-03-01');
INSERT INTO DEPOSIT VALUES ('101', 'SUNIL', 'AJNI', 5000.00, '1996-01-04');
INSERT INTO DEPOSIT VALUES ('102', 'MEHUL', 'KAROBAGH', 3500.00,'1995-11-17');
INSERT INTO DEPOSIT VALUES ('104', 'MADHURI', 'CHANDNI', 1200.00, '1995-12-17');
INSERT INTO DEPOSIT VALUES ('105', 'PRAMOD', 'M.G.ROAD', 30000.00,'1996-03-27');
INSERT INTO DEPOSIT VALUES ('106', 'SANDIP', 'ANDHERI',2000.00,'1996-03-31');
INSERT INTO DEPOSIT VALUES ('107', 'SHIVANI', 'VIRAR', 1000.00,'1995-09-05');
INSERT INTO DEPOSIT VALUES ('108', 'KRANTI', 'NEHRU PLACE', 5000.00,'1995-07-02');
INSERT INTO DEPOSIT VALUES ('109', 'MINU', 'POWAI', 7000.00,'1995-08-10');
+-------+---------+-------------+---------+------------+
| ACTNO | CNAME | BNAME | AMOUNT | ADATE |
+-------+---------+-------------+---------+------------+
| 100 | Anil | VREC | 1000.00 | 1995-03-01 |
| 101 | Sunil | AJNI | 500.00 | 1996-01-04 |
| 102 | Mehul | KAROLBAGH | 3500.00 | 1995-11-17 |
| 104 | Madhuri | CHANDN | 1200.00 | 1995-12-17 |
| 105 | Pramod | M.G.ROAD | 3000.00 | 1996-03-27 |
| 106 | Sandeep | ANDHERI | 2000.00 | 1996-03-31 |
| 107 | Shivani | VIRAR | 1000.00 | 1995-09-05 |
| 108 | Kranti | NEHRU PLACE | 5000.00 | 1995-07-02 |
| 109 | Minu | POWAI | 7000.00 | 1995-08-10 |
+-------+---------+-------------+---------+------------+
INSERT INTO BRANCH VALUES ('VRCE', "NAGPUR");
INSERT INTO BRANCH VALUES ('AJNI', 'NAGPUR');
INSERT INTO BRANCH VALUES ('KAROLBAGH', 'DELHI');
INSERT INTO BRANCH VALUES ('CHANDNI', 'DELHI');
INSERT INTO BRANCH VALUES ('DHARAMPETH', 'NAGPUR');
ISERT INTO BRANCH VALUES ('M.G.ROAD', 'BANGALORE');
INSERT INTO BRANCH VALUES ('ANDHERI', 'BOMBAY');
INSERT INTO BRANCH VALUES ('VIRAR', 'BOMBAY');
INSERT INTO BRANCH VALUES ('NEHRU PLACE', 'DELHI');
INSERT INTO BRANCH VALUES ('POWAI', 'BOMBAY');
+-------------+----------+
| BNAME | CITY |
+-------------+----------+
| VRCE | NAGPUR |
| AJNI | NAGPUR |
| KAROLBAGH | DELHI |
| CHANDINI | DELHI |
| DHARAMPEETH | NAGPUR |
| M.G.ROAD | BANGLORE |
| ANDHERI | BOMBAY |
| VIRAR | BOMBAY |
| NEHRU PLACE | DELHI |
| POWAI | BOMBAY |
+-------------+----------+
insert into CUSTOMERS values('ANIL','CULCUTTA');
insert into CUSTOMERS values('MEHUL','BARODA');
insert into CUSTOMERS values('MANDAR','PATNA');
insert into CUSTOMERS values('MADHURI','NAGPUR');
insert into CUSTOMERS values('PRAMOD','NAGPUR');
insert into CUSTOMERS values('SANDIP','SURAT');
insert into CUSTOMERS values('SHIVANI','BOMBAY');
insert into CUSTOMERS values('KRANTI','BOMBAY');
insert into CUSTOMERS values('NAREN','BOMBAY');
+---------+----------+
| CNAME | CITY |
+---------+----------+
| ANIL | CULCUTTA |
| MEHUL | BARODA |
| MANDAR | PATNA |
| MADHURI | NAGPUR |
| PRAMOD | NAGPUR |
| SANDIP | SURAT |
| SHIVANI | BOMBAY |
| KRANTI | BOMBAY |
| NAREN | BOMBAY |
+---------+----------+
insert into BORROW values('201','AJNI','VRCE',1000.00);
insert into BORROW values('206','MEHUL','AJNI',5000.00);
insert into BORROW values('311','SUNIL','DHARAMPEETH',3000.00);
insert into BORROW values('321','MADHURI','ANDHERI',2000.00);
insert into BORROW values('375','RAMOD','VIHAR',8000.00);
insert into BORROW values('481','KRANTI','NEHRU PLACE',3000.00);
+--------+---------+-------------+---------+
| LOANNO | CNAME | BNAME | AMOUNT |
+--------+---------+-------------+---------+
| 201 | AJNI | VRCE | 1000.00 |
| 206 | MEHUL | AJNI | 5000.00 |
| 311 | SUNIL | DHARAMPEETH | 3000.00 |
| 321 | MADHURI | ANDHERI | 2000.00 |
| 375 | RAMOD | VIHAR | 8000.00 |
| 481 | KRANTI | NEHRU PLACE | 3000.00 |
+--------+---------+-------------+---------+
List all data from table DEPOSIT
select * from DEPOSIT;
+-------+---------+-------------+---------+------------+
| ACTNO | CNAME | BNAME | AMOUNT | ADATE |
+-------+---------+-------------+---------+------------+
| 100 | Anil | VREC | 1000.00 | 1995-03-01 |
| 101 | Sunil | AJNI | 500.00 | 1996-01-04 |
| 102 | Mehul | KAROLBAGH | 3500.00 | 1995-11-17 |
| 104 | Madhuri | CHANDN | 1200.00 | 1995-12-17 |
| 105 | Pramod | M.G.ROAD | 3000.00 | 1996-03-27 |
| 106 | Sandeep | ANDHERI | 2000.00 | 1996-03-31 |
| 107 | Shivani | VIRAR | 1000.00 | 1995-09-05 |
| 108 | Kranti | NEHRU PLACE | 5000.00 | 1995-07-02 |
| 109 | Minu | POWAI | 7000.00 | 1995-08-10 |
+-------+---------+-------------+---------+------------+
List all data from table BORROW
select * from BORROW;
+--------+---------+-------------+---------+
| LOANNO | CNAME | BNAME | AMOUNT |
+--------+---------+-------------+---------+
| 201 | AJNI | VRCE | 1000.00 |
| 206 | MEHUL | AJNI | 5000.00 |
| 311 | SUNIL | DHARAMPEETH | 3000.00 |
| 321 | MADHURI | ANDHERI | 2000.00 |
| 375 | RAMOD | VIHAR | 8000.00 |
| 481 | KRANTI | NEHRU PLACE | 3000.00 |
+--------+---------+-------------+---------+
List all data from table CUSTOMERS
select * from CUSTOMERS;
+---------+----------+
| CNAME | CITY |
+---------+----------+
| ANIL | CULCUTTA |
| MEHUL | BARODA |
| MANDAR | PATNA |
| MADHURI | NAGPUR |
| PRAMOD | NAGPUR |
| SANDIP | SURAT |
| SHIVANI | BOMBAY |
| KRANTI | BOMBAY |
| NAREN | BOMBAY |
+---------+----------+
List all data from table BRANCH
select * from BRANCH;
+-------------+----------+
| BNAME | CITY |
+-------------+----------+
| VRCE | NAGPUR |
| AJNI | NAGPUR |
| KAROLBAGH | DELHI |
| CHANDINI | DELHI |
| DHARAMPEETH | NAGPUR |
| M.G.ROAD | BANGLORE |
| ANDHERI | BOMBAY |
| VIRAR | BOMBAY |
| NEHRU PLACE | DELHI |
| POWAI | BOMBAY |
+-------------+----------+
Give account no. and amount of depositors
Select ACTNO, AMOUNT from DEPOSIT;
Give cname and account no. of depositors
Select CNAME, ACTNO from DEPOSIT;
Give names of customers.
select CNAME from CUSTOMERS;
Give names of branches
select BNAME from BRANCH;
Give names of borrowers
select CNAME from BORROW;
Give names of customers living in city NAGPUR
select * from CUSTOMERS where city = "NAGPUR";
Give 10% interest to all depositors
Update DEPOSIT set AMOUNT = (110*AMOUNT)/100;
Give 10% interest to all depositors having branch VRCE
Update DEPOSIT set AMOUNT = (110*AMOUNT)/100 where BRANCH = "VRCE";
Give 10% interest to all depositors living in NAGPUR
Update DEPOSIT set AMOUNT = (110*AMOUNT)/100 where;
Give 10% interest to all depositors living in NAGPUR having branch in BOMBAY
Add hundered rupees to the deposit of Anil and assign it to Sunil
Change the deposit of VRCE to 1000 and change the branch VRCE as AMBAZAR
Assign to the deposit of Anil the maximum deposit from the VRCE bank
Change the living City of VRCE branch borrowers to NAGPUR
Update DEPOSIT of Anil, give him the maximum deposit from depositors living in coty NAGPUR
Deposit the sum of deposits of Sunil and Vijay in the account of Anil