Exe-16

set A:

Consider the relations

  Person (pnumber, pname, birthdate, income),
  Area( aname,area_type).

An area can have one or more person living in it , but a person belongs to exactly one area. The
attribute ‘area_type’ can have values as either urban or rural.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).

Assume appropriate data types for all the attributes. Add any new attributes as required,
depending on the queries. Insert sufficient number of records in the relations / tables with
appropriate values as suggested by some of the queries.
Write select queries for following business tasks and execute them

Ans.

create table Area (
aname varchar(20) primary key,
area_type varchar check( area_type in('rural','urban'))
);

create table Person(
pnumber int Primary key,
pname varchar(20),
birthdate date,
income numeric(7,2),
aname varchar(20),
constraint fk foreign key(aname) references Area(aname) on delete cascade on update cascade
);


insert into Area values('Pune','urban');
insert into Area values('Satara','urban');
insert into Area values('Mumbai','urban');
insert into Area values('Washim','rural');
insert into Area values('Khed','rural');


insert into Person values(101,'Raj Hawaldar','08/24/1995',50000.00,'Pune');
insert into Person values(102,'Sanket Dewale','07/12/1996',20000.00,'Khed');
insert into Person values(103,'Mandar Athavle','05/02/1996',30000.00,'Washim');
insert into Person values(104,'Nilesh Bora','08/20/1995',40000,'Mumbai');
insert into Person values(105,'Prathmesh Gurav','08/12/1995',10000,'Satara');
insert into Person values(106,'Ram Sultan','05/02/1993',30000,'Pune');
insert into Person values(107,'Kiran Shinde','1/08/1994',40000,'Mumbai');
insert into Person values(108,'Swapnil Gore','2/05/1994',10000,'Satara');
insert into Person values(109,'Sunil Koli','7/04/1994',10000,'Satara');

Queries

1. List the names of all people living in ‘pune’ area.

Ans

Select * from Person where aname='Pune';

2. List details of all people whose names start with the alphabet ‘R_’ & contains
maximum 1 alphabets in it.

Ans.

Select pname,max(char_length(pname)) from Person where pname like'R%'
group by pname limit 1;

3.List the names of all people whose birthday falls in the month of 8.

Ans.

 select pname,birthdate from Person where extract (Month from birthdate)=08;

4. Give the count of people who are born on ‘08/24/1995’

Ans.

Select pname,count(*) from person where birthdate='08/24/1995';

5. Give the count of people whose income is below 40000.

Ans.

Select pname,income from person where income<40000;

6. List names of all people whose income is between 10000 and 30000;

Ans.

Select pname,income from person where income between 10000 and 30000;

7.List the names of people with average income

Ans.

Select pname,avg(income) from person group by pname;

8.List the sum of incomes of people living in ‘pune’

Ans.

select aname,sum(income) from person group by aname;//all area
//select aname,sum(income) from person group by aname having aname='Pune';//particular area

9.List the names of the areas having people with maximum income (duplicate areas must be
omitted in the result)

Ans.

select max(income),aname from person group by aname;
//select aname ,max(income) from person group by aname;

10.Give the count of people in each area

Ans.

Select aname,count(*) from person group by aname;

11.List the details of people living in ‘pune’ and having income greater than 50000;

Ans.

Select pname,income from person where aname='Pune' and income>50000;

12.List the details pf people, sorted by person number

Ans.

Select * from person order by pnumber ;


13.List the details of people, sorted by area, person name

Ans.

Select * from person order by aname,pname;

14.List the minimum income of people

Ans.

Select min(income) from person;

15.Transfer all people living in ‘pune’ to ‘mumbai’.

Ans.

Update person set aname='Mumbai' where aname='Pune';