DBMS-Slip6

Slip6

Questions

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);