Que 5

Q.6)    Consider the following Entities and Relationships                [30 Marks]
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
Create a Database in 3NF & write queries for following.
•    Display area wise property details.
•    Display property owned by 'Mr.Patil' having minimum rate.
•    Display all properties with owner name that having highest rate of  properties located in Chinchwad area.
•    Display owner wise property detail.
•    Display owner name having maximum no. of properties.
Ans.
create table owner1(
oname varchar2(20) primary key,
addr varchar2(10),
phone int
);
create table property1(
oname varchar2(20) references owner1(oname),
pno int primary key,
desc1 varchar2(10),
area varchar2(10),
rate int
);
insert into owner1 values('Mukesh Ambani','Gujarat',123496867);
insert into owner1 values('Dilip Shanghvi','Gujarat',123496862);
insert into owner1 values('Lakshmi Mittal','Parsi',123496863);
insert into owner1 values('Azim Premji','mumbai',123496865);
insert into owner1 values('Shiv Nadar','mumbai',123496866);
insert into owner1 values('S P Hinduja','mumbai',123496868);
insert into owner1 values('patil','mumbai',123496868);

insert into  property1 values('Mukesh Ambani',111,'1 plot','deccan',1000);
insert into  property1 values('Dilip Shanghvi',222,'2 plot','baramati',20000);
insert into  property1 values('Lakshmi Mittal',333,'3 plot','faltan',3000);
insert into  property1 values('Azim Premji',444,'plot','chinchwad',40000);
insert into  property1 values('S P Hinduja',555,'banglow','chinchwad',6000);
insert into  property1 values('patil',666,'banglow','pune',6000);
insert into  property1 values('patil',888,'plot','pune',9000);

select *from owner1;
select *from property1;

1]Display area wise property details.
select

2]Display property owned by 'Mr.Patil' having minimum rate.

select oname,min(rate) from property1 group by oname having oname='patil';

3]Display all properties with owner name that having highest rate of  properties located in Chinchwad area.

select oname from property1 where area =

(select max(rate) from  property1  group by area having area='chinchwad');
4]Display owner wise property detail.


5]Display owner name having maximum no. of properties.
//////select oname from property1 where    ;

*********************************************
Q. 7)    Consider the following Entities and Relationships                [30 Marks]
Employee (emp_no, name, skill, payrate)
Position (posting_no, skill)
Relation between Employee and Position is Many to Many with day and shift as descriptive attribute.   
      
    Constraint: Primary key, payrate should be > 0.
       Create a Database in 3NF & write queries for following.
•    Find the names and rate of pay all employees who allocated a duty.
•    Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.
•    Display a list of names of employees who have skill of chef and who has assigned a duty.
•    Display emp_no and dates for all employees who are working on Tuesday and at least one other day.
•    Display shiftwise employee details

Ans
drop table emp_position;
drop table emp;
drop table position;

Create table emp(
eno int primary key,
ename varchar2(20),
skill varchar2(10),
payrate int check(payrate>0)
);
create table position(
pno int primary key,
skill varchar2(10)
);
create table emp_position(
eno int references emp(eno),
pno int references position(pno),
day1 varchar(7),
shift char(10) check(shift in('morning','evening')),
primary key(eno,pno)
);
insert into emp values(101,'sachin','watchman',2000);
insert into emp values(102,'pravin','manager',3000);
insert into emp values(103,'prasad','waiter',4000);
insert into emp values(104,'kiran','waiter',5000);
insert into emp values(105,'kartik','chef',7000);

insert into position values(1000,'manager');
insert into position values(1001,'waiter');
insert into position values(1002,'chef');
insert into position values(1003,'watchman');
insert into position values(1004,'account');

insert into emp_position values(101,1003,'Monday','morning');
insert into emp_position values(102,1000,'Tuesday','evening');
insert into emp_position values(103,1001,'Tuesday','morning');
insert into emp_position values(104,1001,'Monday','evening');
insert into emp_position values(105,1002,'Monday','morning');

select *from emp;
select *from position;
select *from emp_position;

1]Find the names and rate of pay all employees who allocated a duty.
select name,rate  from
2]Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.

3]Display a list of names of employees who have skill of chef and who has assigned a duty.

4]Display emp_no and dates for all employees who are working on Tuesday and at least one other day.

5]Display shiftwise employee details