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;

3]    Find no. of AC rooms.

select desc1,count(*) from room group by desc1 having desc1='Ac';

4]    Display total amount for NON-AC rooms.

select desc1,sum(rate) from room group by desc1 having desc1='Non-Ac';

5]    Find names of guest with maximum room charges.


Q. 5)    Consider the following Entities and Relationships                [30 Marks]
        Book (Book_no, title, author, price, year_published)
       Customer (cid, cname, addr)
       Relation between Book and Customer is Many to Many with quantity as  
       descriptive attribute.
            Constraint: Primary key, price should be >0.
      Create a Database in 3NF & write queries for following.
•    Display customer details from 'Mumbai'.
•    Display author wise details of book.
•    Display all customers who have purchased the books published in the year 2013.
•    Display customer name that has purchased more than 3 books.
•    Display book names having price between 100 and 200 and published in the year 2013.
;

Ans.

drop table book cascade constraints;

create table book(
bno int primary key,
title varchar2(20),
author varchar2(20),
price int check(price>0),
yearp date
);

create table customer3(
cid int primary key,
cname varchar2(20),
addr varchar2(20)
);

create table book_cus(
bno int references book(bno),
cid int references customer3(cid),
qunt int,
primary key(bno,cid)
);

insert into book values(17,'Wings of Fire','A.P.J. Abdul Kalam',900,'1-jan-2013');
insert into book values(16,'Turning Point','A.P.J. Abdul Kalam',1000,'1-jeb-2013');
insert into book values(13,'End of the Era ','C.S. Pandit',700,'1-mar-2017');
insert into book values(14,'My Struggle ','E K Nayanar',800,'1-jun-2017');
insert into book values(15,'My Truth','Indira Gandhi ',400,'1-jan-2017');

insert into customer3 values(101,'Ram','Pune');
insert into customer3 values(102,'Sam','Pune');
insert into customer3 values(103,'Lata','Satara');
insert into customer3 values(104,'Aasha','Nashik');
insert into customer3 values(105,'Usha','Satara');

insert into book_cus values(11,101,3);
insert into book_cus values(12,102,5);
insert into book_cus values(11,102,2);
insert into book_cus values(13,104,1);
insert into book_cus values(14,105,10);
insert into book_cus values(17,105,10);


select *from book;
select *from customer3;
select *from book_cus;

1]Display customer details from 'Mumbai'.

select *from customer3 where addr='Mumbai';
3]Display all customers who have purchased the books published in the year 2013.

select *from customer3 where cid in(select cid from book_cus where bno in(select bno from book where extract(year from yearp)=2013 ));

4]Display customer name that has purchased more than 3 books.

select cname from customer3
where cid in
      (select cid from book_cus group by cid having count(*)>1);


5]Display book names having price between 100 and 200 and published in the year 2013.
select title,price from book where price between 800 and 900;

select title ,price,yearp
from book
where price>=400
      and price<=700
      and  extract(year from yearp)=2017;