===================================================
CASE STUDY 4 : REAL ESTATE
===================================================
/**** CREATING DATABASE ****/
mysql> create database ganesh_realES;
Query OK, 1 row affected (0.00 sec)
mysql> use ganesh_realES;
Database changed
/**** CREATING TABLE : AGENT ****/
mysql> create table agent(aid int,aname varchar(20),address varchar(20),contact varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from agent;
+------+---------+------------+------------+
| aid | aname | address | contact |
+------+---------+------------+------------+
| 1 | vinod | warje | 9088231455 |
| 2 | mahesh | karvenagar | 9028445811 |
| 3 | amir | kothrud | 8711372010 |
| 4 | amit | poud road | 9028550411 |
| 5 | sharman | tilak road | 8727584458 |
+------+---------+------------+------------+
5 rows in set (0.00 sec)
/**** CREATING TABLE : ESTATE****/
mysql> create table estate(eno int,type varchar(20),location varchar(20),price int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from estate;
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
+------+------+--------------+--------+
5 rows in set (0.00 sec)
/**** CREATING TABLE : CUSTOMER****/
mysql> create table customer(custID int,cname varchar(20),c_contact varchar(10),c_address varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from customer;
+--------+---------------+------------+--------------+
| custID | cname | c_contact | c_address |
+--------+---------------+------------+--------------+
| 1 | Ashok Khade | 9028552365 | Kothrud |
| 2 | Uday Mane | 9020555065 | Karvenagar |
| 3 | Mahesh Sharma | 9077555065 | Shivajinagar |
| 4 | Anand Joshi | 9080545165 | Warje |
| 5 | Abdul Khan | 9088545155 | Pashan |
+--------+---------------+------------+--------------+
5 rows in set (0.01 sec)
/**** CREATING TABLE : TRANSACTION****/
mysql> create table transaction(aid int references agent(aid),eno int references estate(eno),custID int references customer(custID),pur_date date,commission int);
Query OK, 0 rows affected (0.07 sec)
mysql> select * from transaction;
+------+------+--------+------------+------------+
| aid | eno | custID | pur_date | commission |
+------+------+--------+------------+------------+
| 1 | 4 | 5 | 2011-04-03 | 25000 |
| 3 | 2 | 4 | 2009-05-03 | 20000 |
| 4 | 3 | 2 | 2008-06-08 | 50000 |
| 2 | 5 | 1 | 2008-07-09 | 53000 |
| 5 | 1 | 3 | 2012-12-10 | 20000 |
+------+------+--------+------------+------------+
5 rows in set (0.00 sec)
//*********QUERIES********//
Q1.Find the name of agent with their contact number who have sold land in Baner area location.
mysql> select aname,contact from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and type="Land"and location="Baner";
+--------+------------+
| aname | contact |
+--------+------------+
| mahesh | 9028445811 |
+--------+------------+
1 row in set (0.00 sec)
Q2.Give the agent wise list of customers.
mysql> select agent.aid,agent.aname,customer.custID,customer.cname from agent,customer,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID group by(agent.aid);
+------+---------+--------+---------------+
| aid | aname | custID | cname |
+------+---------+--------+---------------+
| 1 | vinod | 5 | Abdul Khan |
| 2 | mahesh | 1 | Ashok Khade |
| 3 | amir | 4 | Anand Joshi |
| 4 | amit | 2 | Uday Mane |
| 5 | sharman | 3 | Mahesh Sharma |
+------+---------+--------+---------------+
5 rows in set (0.00 sec)
Q3.Find total commission earned by amir by selling 2BHK flat.
mysql> select sum(commission) as total_commission from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and agent.aname="amir" and estate.type="2BHK";
+------------------+
| total_commission |
+------------------+
| 20000 |
+------------------+
1 row in set (0.00 sec)
Q4.Give the name of customers who have purchased an estate in the same area in which they are living
mysql> select customer.cname from customer,agent,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID and customer.c_address="Warje";
+-------------+
| cname |
+-------------+
| Anand Joshi |
+-------------+
1 row in set (0.00 sec)
//////*********** VIEWS ************//////
V.1 : Create a view of all real estate which are in "malvadi" area and which are not sold.
mysql> create view vb as select e.eno,e.type,e.location,e.price from estate as e where e.location="Malvadi";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from vb;
+------+------+----------+--------+
| eno | type | location | price |
+------+------+----------+--------+
| 3 | Land | Malvadi | 120000 |
+------+------+----------+--------+
1 row in set (0.00 sec)
V2 : Create view which contain details of all estate sold by vinod and also details of all customers who have bought the estate.
mysql> create view vc as select estate.eno,aname,location,price,customer.cname,address,agent.contact from estate,transaction,agent,customer where estate.eno=transaction.eno and agent.aid=transaction.aid and customer.custID=transaction.custID and aname="vinod";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from vc
-> ;
+------+-------+-------------+--------+------------+---------+------------+
| eno | aname | location | price | cname | address | contact |
+------+-------+-------------+--------+------------+---------+------------+
| 4 | vinod | Pashan Area | 115000 | Abdul Khan | warje | 9088231455 |
+------+-------+-------------+--------+------------+---------+------------+
1 row in set (0.00 sec)
//////*********** FUNCTIONS ************//////
F1.Write a function to calculate the total price of estates the perticular agent had sold.
mysql> delimiter //
mysql> create function f1(p text)
-> returns text
-> deterministic
-> begin
-> declare a int;
-> select sum(estate.price) into a from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and aname=p and agent.aid in (agent.aid,transaction.aid);
-> return a;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> select f1('vinod');
-> //
+-------------+
| fs('vinod') |
+-------------+
| 115000 |
+-------------+
1 row in set (0.00 sec)
F2.Write a function which will the location from which maz estate are sold.
mysql> delimiter //
mysql> create function f2()
-> returns text
-> deterministic
-> begin
-> declare a text;
-> select estate.location into a from estate,transaction where estate.eno=transaction.eno and transaction.eno in(estate.eno,transaction.eno) group by location limit 0,1;
-> return a;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select f2();
-> //
+-------+
| f2() |
+-------+
| Baner |
+-------+
1 row in set (0.00 sec)
//////*********** PROCEDURE ************//////
P.1 : Write a procedure which will list all estate type with agent name on perticular location(accept location as an input parameter) .
mysql> delimiter //
mysql> create procedure pro1(in a text,out b text,out c text)
-> begin
-> select agent.aname,estate.type from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and estate.location=a;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> call pro1("F.C Road",@b,@c);
-> //
+---------+------+
| aname | type |
+---------+------+
| sharman | 1BHK |
+---------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
P.2:Write a procedure which will find the estate having maximum price among each agent type.
mysql> delimiter //
mysql> create procedure pp(in a int,out b int,out c int,out d int)
-> begin
-> select estate.eno,agent.aid,estate.price from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a and estate.price=(select max(price) from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a group by transaction.aid);
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call pp(2,@b,@c,@d);
-> //
+------+------+--------+
| eno | aid | price |
+------+------+--------+
| 5 | 2 | 130000 |
+------+------+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
//////*********** CURSOR ************//////
C.1: Write a procedure which will calculate the total commission earned by each agent.
mysql> delimiter //
mysql> create procedure pro1(out a text,out b float)
-> begin
-> declare a1 text;
-> declare done int default 0;
-> declare a2 float;
-> declare com cursor for select aname,sum(transaction.commission) from agent,transaction where agent.aid=transaction.aid group by (aname);
-> declare continue handler for SQLSTATE'02000'
-> set done=1;
-> open com;
-> repeat
-> fetch com into a1,a2;
-> if not done then
-> select a1,a2;
-> end if;
-> until done end repeat;
-> close com;
-> end
-> //
mysql> call pro1(@a,@b);
-> //
+------+-------+
| a1 | a2 |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| a1 | a2 |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| a1 | a2 |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| a1 | a2 |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)
+-------+-------+
| a1 | a2 |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
C.2: Write a pro which will increase the commission by 5% of all agent who have sold Land.
mysql> delimiter //
mysql> create procedure ab()
-> begin
-> declare agt text;
-> declare done int default 0;
-> declare o_com,n_com float;
-> declare com2 cursor for select distinct aname,commission from agent,transaction,estate where agent.aid=transaction.aid and estate.eno=estate.eno and estate.type='Land';
-> declare continue handler for SQLSTATE'02000'
-> set done=1;
-> open com2;
-> repeat
-> fetch com2 into agt,o_com;
-> if not done then
-> select agt,o_com;
-> end if;
-> until done end repeat;
-> close com2;
-> set done=0;
-> update transaction,agent,estate set commission=commission+(commission*0.5) where type='1BHK';
-> open com2;
-> repeat
-> fetch com2 into agt,n_com;
-> if not done then
-> select agt,n_com;
-> end if;
-> until done end repeat;
-> close com2;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call ab;
-> //
+-------+-------+
| agt | o_com |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | o_com |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | o_com |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| agt | o_com |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| agt | o_com |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)
+-------+-------+
| agt | n_com |
+-------+-------+
| vinod | 37500 |
+-------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | n_com |
+------+-------+
| amir | 30000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | n_com |
+------+-------+
| amit | 75000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| agt | n_com |
+--------+-------+
| mahesh | 79500 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| agt | n_com |
+---------+-------+
| sharman | 30000 |
+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
//////*********** TRIGGER ************//////
T.1: First alter table estate by adding a column status varchar(20) (Initially all status are null).Write a trigger which will fire after insertion on transaction table which will change the status as sold.
mysql> delimiter //
mysql> create trigger bob after insert on transaction for each row
-> begin
-> declare a int;
-> set a=new.eno;
-> update estate set status='sold' where estate.aid=a;
-> end
-> //
mysql> insert into transaction values(2,13,13,'2008-01-01',263.5);
-> //
mysql> select * from estate;
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
| 6 | Land | Pimpari | 1000000|
+------+------+--------------+--------+
5 rows in set (0.02 sec)
T.2: Write a trigger which will fire after any estate is deleted from the transaction table it will also delete from the estate table.
mysql> create trigger k1 after delete on transaction for each row
-> begin
-> delete from estate where eno=pld.eno;
-> end
-> //
mysql> delete from transaction where eno=3;
-> //
Query OK, 1 rows affected (0.00 sec)
mysql> select * from estate;
-> //
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
+------+------+--------------+--------+
5 rows in set (0.01 sec)
END CASE STUDY : 4
================================================
CASE STUDY 4 : REAL ESTATE
===================================================
/**** CREATING DATABASE ****/
mysql> create database ganesh_realES;
Query OK, 1 row affected (0.00 sec)
mysql> use ganesh_realES;
Database changed
/**** CREATING TABLE : AGENT ****/
mysql> create table agent(aid int,aname varchar(20),address varchar(20),contact varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from agent;
+------+---------+------------+------------+
| aid | aname | address | contact |
+------+---------+------------+------------+
| 1 | vinod | warje | 9088231455 |
| 2 | mahesh | karvenagar | 9028445811 |
| 3 | amir | kothrud | 8711372010 |
| 4 | amit | poud road | 9028550411 |
| 5 | sharman | tilak road | 8727584458 |
+------+---------+------------+------------+
5 rows in set (0.00 sec)
/**** CREATING TABLE : ESTATE****/
mysql> create table estate(eno int,type varchar(20),location varchar(20),price int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from estate;
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
+------+------+--------------+--------+
5 rows in set (0.00 sec)
/**** CREATING TABLE : CUSTOMER****/
mysql> create table customer(custID int,cname varchar(20),c_contact varchar(10),c_address varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from customer;
+--------+---------------+------------+--------------+
| custID | cname | c_contact | c_address |
+--------+---------------+------------+--------------+
| 1 | Ashok Khade | 9028552365 | Kothrud |
| 2 | Uday Mane | 9020555065 | Karvenagar |
| 3 | Mahesh Sharma | 9077555065 | Shivajinagar |
| 4 | Anand Joshi | 9080545165 | Warje |
| 5 | Abdul Khan | 9088545155 | Pashan |
+--------+---------------+------------+--------------+
5 rows in set (0.01 sec)
/**** CREATING TABLE : TRANSACTION****/
mysql> create table transaction(aid int references agent(aid),eno int references estate(eno),custID int references customer(custID),pur_date date,commission int);
Query OK, 0 rows affected (0.07 sec)
mysql> select * from transaction;
+------+------+--------+------------+------------+
| aid | eno | custID | pur_date | commission |
+------+------+--------+------------+------------+
| 1 | 4 | 5 | 2011-04-03 | 25000 |
| 3 | 2 | 4 | 2009-05-03 | 20000 |
| 4 | 3 | 2 | 2008-06-08 | 50000 |
| 2 | 5 | 1 | 2008-07-09 | 53000 |
| 5 | 1 | 3 | 2012-12-10 | 20000 |
+------+------+--------+------------+------------+
5 rows in set (0.00 sec)
//*********QUERIES********//
Q1.Find the name of agent with their contact number who have sold land in Baner area location.
mysql> select aname,contact from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and type="Land"and location="Baner";
+--------+------------+
| aname | contact |
+--------+------------+
| mahesh | 9028445811 |
+--------+------------+
1 row in set (0.00 sec)
Q2.Give the agent wise list of customers.
mysql> select agent.aid,agent.aname,customer.custID,customer.cname from agent,customer,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID group by(agent.aid);
+------+---------+--------+---------------+
| aid | aname | custID | cname |
+------+---------+--------+---------------+
| 1 | vinod | 5 | Abdul Khan |
| 2 | mahesh | 1 | Ashok Khade |
| 3 | amir | 4 | Anand Joshi |
| 4 | amit | 2 | Uday Mane |
| 5 | sharman | 3 | Mahesh Sharma |
+------+---------+--------+---------------+
5 rows in set (0.00 sec)
Q3.Find total commission earned by amir by selling 2BHK flat.
mysql> select sum(commission) as total_commission from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and agent.aname="amir" and estate.type="2BHK";
+------------------+
| total_commission |
+------------------+
| 20000 |
+------------------+
1 row in set (0.00 sec)
Q4.Give the name of customers who have purchased an estate in the same area in which they are living
mysql> select customer.cname from customer,agent,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID and customer.c_address="Warje";
+-------------+
| cname |
+-------------+
| Anand Joshi |
+-------------+
1 row in set (0.00 sec)
//////*********** VIEWS ************//////
V.1 : Create a view of all real estate which are in "malvadi" area and which are not sold.
mysql> create view vb as select e.eno,e.type,e.location,e.price from estate as e where e.location="Malvadi";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from vb;
+------+------+----------+--------+
| eno | type | location | price |
+------+------+----------+--------+
| 3 | Land | Malvadi | 120000 |
+------+------+----------+--------+
1 row in set (0.00 sec)
V2 : Create view which contain details of all estate sold by vinod and also details of all customers who have bought the estate.
mysql> create view vc as select estate.eno,aname,location,price,customer.cname,address,agent.contact from estate,transaction,agent,customer where estate.eno=transaction.eno and agent.aid=transaction.aid and customer.custID=transaction.custID and aname="vinod";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from vc
-> ;
+------+-------+-------------+--------+------------+---------+------------+
| eno | aname | location | price | cname | address | contact |
+------+-------+-------------+--------+------------+---------+------------+
| 4 | vinod | Pashan Area | 115000 | Abdul Khan | warje | 9088231455 |
+------+-------+-------------+--------+------------+---------+------------+
1 row in set (0.00 sec)
//////*********** FUNCTIONS ************//////
F1.Write a function to calculate the total price of estates the perticular agent had sold.
mysql> delimiter //
mysql> create function f1(p text)
-> returns text
-> deterministic
-> begin
-> declare a int;
-> select sum(estate.price) into a from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and aname=p and agent.aid in (agent.aid,transaction.aid);
-> return a;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> select f1('vinod');
-> //
+-------------+
| fs('vinod') |
+-------------+
| 115000 |
+-------------+
1 row in set (0.00 sec)
F2.Write a function which will the location from which maz estate are sold.
mysql> delimiter //
mysql> create function f2()
-> returns text
-> deterministic
-> begin
-> declare a text;
-> select estate.location into a from estate,transaction where estate.eno=transaction.eno and transaction.eno in(estate.eno,transaction.eno) group by location limit 0,1;
-> return a;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select f2();
-> //
+-------+
| f2() |
+-------+
| Baner |
+-------+
1 row in set (0.00 sec)
//////*********** PROCEDURE ************//////
P.1 : Write a procedure which will list all estate type with agent name on perticular location(accept location as an input parameter) .
mysql> delimiter //
mysql> create procedure pro1(in a text,out b text,out c text)
-> begin
-> select agent.aname,estate.type from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and estate.location=a;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> call pro1("F.C Road",@b,@c);
-> //
+---------+------+
| aname | type |
+---------+------+
| sharman | 1BHK |
+---------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
P.2:Write a procedure which will find the estate having maximum price among each agent type.
mysql> delimiter //
mysql> create procedure pp(in a int,out b int,out c int,out d int)
-> begin
-> select estate.eno,agent.aid,estate.price from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a and estate.price=(select max(price) from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a group by transaction.aid);
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call pp(2,@b,@c,@d);
-> //
+------+------+--------+
| eno | aid | price |
+------+------+--------+
| 5 | 2 | 130000 |
+------+------+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
//////*********** CURSOR ************//////
C.1: Write a procedure which will calculate the total commission earned by each agent.
mysql> delimiter //
mysql> create procedure pro1(out a text,out b float)
-> begin
-> declare a1 text;
-> declare done int default 0;
-> declare a2 float;
-> declare com cursor for select aname,sum(transaction.commission) from agent,transaction where agent.aid=transaction.aid group by (aname);
-> declare continue handler for SQLSTATE'02000'
-> set done=1;
-> open com;
-> repeat
-> fetch com into a1,a2;
-> if not done then
-> select a1,a2;
-> end if;
-> until done end repeat;
-> close com;
-> end
-> //
mysql> call pro1(@a,@b);
-> //
+------+-------+
| a1 | a2 |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| a1 | a2 |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| a1 | a2 |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| a1 | a2 |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)
+-------+-------+
| a1 | a2 |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
C.2: Write a pro which will increase the commission by 5% of all agent who have sold Land.
mysql> delimiter //
mysql> create procedure ab()
-> begin
-> declare agt text;
-> declare done int default 0;
-> declare o_com,n_com float;
-> declare com2 cursor for select distinct aname,commission from agent,transaction,estate where agent.aid=transaction.aid and estate.eno=estate.eno and estate.type='Land';
-> declare continue handler for SQLSTATE'02000'
-> set done=1;
-> open com2;
-> repeat
-> fetch com2 into agt,o_com;
-> if not done then
-> select agt,o_com;
-> end if;
-> until done end repeat;
-> close com2;
-> set done=0;
-> update transaction,agent,estate set commission=commission+(commission*0.5) where type='1BHK';
-> open com2;
-> repeat
-> fetch com2 into agt,n_com;
-> if not done then
-> select agt,n_com;
-> end if;
-> until done end repeat;
-> close com2;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call ab;
-> //
+-------+-------+
| agt | o_com |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | o_com |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | o_com |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| agt | o_com |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| agt | o_com |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)
+-------+-------+
| agt | n_com |
+-------+-------+
| vinod | 37500 |
+-------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | n_com |
+------+-------+
| amir | 30000 |
+------+-------+
1 row in set (0.00 sec)
+------+-------+
| agt | n_com |
+------+-------+
| amit | 75000 |
+------+-------+
1 row in set (0.00 sec)
+--------+-------+
| agt | n_com |
+--------+-------+
| mahesh | 79500 |
+--------+-------+
1 row in set (0.00 sec)
+---------+-------+
| agt | n_com |
+---------+-------+
| sharman | 30000 |
+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
//////*********** TRIGGER ************//////
T.1: First alter table estate by adding a column status varchar(20) (Initially all status are null).Write a trigger which will fire after insertion on transaction table which will change the status as sold.
mysql> delimiter //
mysql> create trigger bob after insert on transaction for each row
-> begin
-> declare a int;
-> set a=new.eno;
-> update estate set status='sold' where estate.aid=a;
-> end
-> //
mysql> insert into transaction values(2,13,13,'2008-01-01',263.5);
-> //
mysql> select * from estate;
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
| 6 | Land | Pimpari | 1000000|
+------+------+--------------+--------+
5 rows in set (0.02 sec)
T.2: Write a trigger which will fire after any estate is deleted from the transaction table it will also delete from the estate table.
mysql> create trigger k1 after delete on transaction for each row
-> begin
-> delete from estate where eno=pld.eno;
-> end
-> //
mysql> delete from transaction where eno=3;
-> //
Query OK, 1 rows affected (0.00 sec)
mysql> select * from estate;
-> //
+------+------+--------------+--------+
| eno | type | location | price |
+------+------+--------------+--------+
| 1 | 1BHK | F.C Road | 120000 |
| 2 | 2BHK | Sinhgad Road | 80000 |
| 3 | Land | Malvadi | 120000 |
| 4 | 3BHK | Pashan Area | 115000 |
| 5 | Land | Baner | 130000 |
+------+------+--------------+--------+
5 rows in set (0.01 sec)
END CASE STUDY : 4
================================================
Post a Comment