DBMS Practical Slips

13.Person_Area

create table area
(
area_name varchar(20)primary key,
area_type varchar(20)
);

create table person
(
p_no number(5)primary key,
p_name varchar(20),
birthdate number(5),
income number(10)check(income>0),
area_name varchar(20) references area11(area_name)
);

insert into area values('wadgaon','rural');
insert into area values('dhayari','urban');
insert into area values('narhe','rural');
insert into area values('katraj','urban');

insert into person values(1,'vivek',28-06-1998,50000,'wadgaon');
insert into person values(2,'rohit',25-10-1997,100000,'dhayari');
insert into person values(3,'hitesh',20-12-1996,150000,'narhe');
insert into person values(4,'ankit',15-05-1997,90000,'katraj');

1. select person.income from person,area
where area.area_name='katraj' AND
area.area_type='urban' AND
person.income<=100000;

    INCOME
----------------
     50000
    100000
     90000

2. select COUNT(person.p_no) from area,person group by area_name;

COUNT(PERSON.P_NO)
-------------------------------------
                   8
                   8


3. select person.* from area,person
where area.area_name=person.area_name AND
area11.area_type='urban';

      P_NO P_NAME                BIRTHDATE     INCOME AREA_NAME
---------- -------------------- ---------- ---------- ---------------------------------------
         2 rohit                     -1982     100000 dhayari
         4 ankit                     -1987      90000 katraj