Que 4

4]
Consider the following Entities and Relationships                [30 Marks]
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
Relation between Room and Guest is One to One.
         Constraint: Primary key, no of days should be > 0.
Create a Database in 3NF & write queries for following.
•    Display room details according to its rates  in ascending order.
•    Find the names of guest who has allocated room for more than 3 days.
•    Find no. of AC rooms.
•    Display total amount for NON-AC rooms.
•    Find names of guest with maximum room charges.
Ans.

create table room(
roomno int primary key,
desc1 varchar2(10),
rate int
);

create table guest(
gno int primary key,
roomno int unique references room (roomno),
gname varchar2(20),
nday int
);

insert into room values(101,'Ac',2000);
insert into room values(102,'Non-Ac',3000);
insert into room values(103,'Ac',4000);
insert into room values(104,'Ac',3000);
insert into room values(105,'Ac',7000);
insert into room values(106,'Ac',3000);
insert into room values(107,'Ac',4000);
insert into room values(108,'Ac',5000);
insert into room values(109,'Non-Ac',5000);

insert into guest values(21,101,'Yuvraj-Singh',5);
insert into guest values(22,102,'Yusuf-Pathan',13);
insert into guest values(23,103,'Gautam-Gambhir',10);
insert into guest values(24,104,'Raina',10);
insert into guest values(25,105,'Sachin-Tendulkar',11);
insert into guest values(26,106,'Praveen-Kumar',11);
insert into guest values(27,107,'Ravindra-Jadeja',14);

select *from room;
select *from guest;

1]Display room details according to its rates  in ascending order.

select desc1,rate from room order by  rate asc ;

2]    Find the names of guest who has allocated room for more than 3 days.

select gname,nday from guest where nday>3;

select gname,nday from guest where nday>10;