Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints.
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
The relationship between owner and Property
is One to Many.
Constraint: Primary key, rate should be
> 0
Consider the above tables and execute the
following queries:
1. Display area of property whose rate is
less than 100000.
2. Give the details of owner whose property
is at “Pune” .
Solution:-
SQL>Create table Property (Pno int
primary key, desc varchar(10),area varchar(10),rate int check(rate>0));
SQL>Insert into Property values(1 ,’R Zone’,'Rastapeth',2000);
SQL>Select * from Property;
SQL>COMMIT;
SQL>Create table owner(Ono int primary
key ,oname varchar(15),addr varchar(10),phno int ,Pno int);
SQL>Insert into owner
values(101,’Asha’,'pune',9877765435,1);
SQL>Select * from Owner;
SQL>COMMIT;
Note:- HOW TO JOIN TWO TABLES ?
Select ( Field names) from First Table , Second Table where First Table.primary key = Second Table.Firsttables -primary key
1. Display area of property whose rate is
less than 100000.
Select area from Property where
rate<100000;
2. Give the details of owner whose property
is at “Pune” .
Select * from Property, Owner where Property.Pno= Owner. Pno and addr='pune';
Q4. Consider the above tables and execute
the following queries: [25 Marks]
1.Display area wise property details.
Select area, descrip from Property order by area;
2.Display property owned by 'Mr.Patil' having minimum rate.
Select min(rate) from Property, Owner
where Property.Pno= Owner. Pno and owner_name ='Mr.Patil’;
3.Delete all properties from “pune” owned by “Mr. Joshi”.
Delete from owner where addr='pune' and
oname='joshi';
4. Update the phone Number of “Mr. Joshi” to 9922112233 who is having property at “Uruli Kanchan”.
Update owner set phno = 9922112233 where
addr = ‘UruliKanchan’ and oname = ‘joshi’;
5. Delete column address from Owner table.
ALTER TABLE Owner DROP COLUMN addr;
