DBMS-Slip20

Slip20

Consider the following Entities and Relationships                                   
Person (pno, person_name, birthdate, income)
Area (area_name, area_type)
Relation between Person and area is Many to One.
Constraint: Primary key, income should be > 0, area_type should be rural or urban.

Solution:-

Create a Database in 3NF & write queries for following.

•Display persons having income less than 1 lakhs in PCMC Area.
SQL> select pname from persons,area where persons.ano= area.ano and  aname=’pcmc’
and income <100000;

•Display population of each area.
SQL>select population,aname from persons, area where  persons. ano= area. ano group by aname,population;

•Display persons details from 'Urban' area.
SQL> select pname from persons,area where person.s ano= area. ano and atype='urban';

•Display the details of area having population greater than that of in Pune.
SQL>select max(population),aname  from area where  aname='pune' group by aname;

•Display details of person from each area having minimum income.
SQL> select pname,birthdate,income  from persons where income=(select min(income) from persons);