DBMS-Slip6

Slip6
Consider the following Entities and Relationships                                      
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
Relation between owner and Property is One to Many.
Constraint: Primary key, rate should be > 0

Solution:-
Create a Database in 3NF & write queries for following.
•Display area wise property details.
SQL>Select area, descrip from  Property order by area;

•Display property owned by 'Mr.Patil' having minimum rate.
SQL>Select min(rate) from Property, Owner where  Property.Pno= Owner. Pno  and owner_name ='Mr.Patil’;

•Display all properties with owner name that having highest rate of  properties located in Chinchwad area.
SQL>select max(Property.rate),Owner.owner_name  from Property, Owner where  Property.Pno= Owner.Pno  and Property.area='chinchwad' group by Owner.owner_name ;

•Display owner wise property detail.
SQL>select owner_name from  Property, Owner where  Property.Pno= Owner. Pno order by Owner.owner_name asc;

•Display owner name having maximum no. of properties.
SQL>select owner_name  from Property, Owner where  Property.Pno= Owner.Pno and Property.rate = (select max(Property.rate) from Property);
or select owner_name  from Property, Owner where  Property.Pno= Owner.Pno and Property.Pno = (select max(Property.Pno) from Property);