1) For a given time period(begin date and end date) compute 5 best customers(in

Question

1) For a given time period(begin date and end date) compute 5 best customers(in terms of money spend in reservation)

2) ) For a given time period(begin date and end date) compute the highest rated room type for each hotel

3) ) For a given time period(begin date and end date) compute the highest rated breakfast type across all hotels

4) ) For a given time period(begin date and end date) compute the highest rated service type across all hotels

Schema:

CREATE TABLE Customer (CID int NOT NULL, Email varchar (50) NOT NULL, Address varchar(10000) NOT NULL, Phone_no varchar(12) NOT NULL, Name varchar(50) NOT NULL, primary key(CID));

CREATE TABLE CreditCard (Cnumber varchar(25), ExpDate Date, Type varchar(15), SecCode varchar(10), Name varchar(50), BillingAddr varchar(10000), primary key (Cnumber));

CREATE TABLE Pay_For_Reservation (InvoiceNo int, Cnumber varchar(25) NOT NULL, CID int, ResDate DATE, TotalAmt REAL DEFAULT NULL, primary key (InvoiceNo, Cnumber, CID), foreign key (Cnumber) references CreditCard (Cnumber), foreign key(CID) references Customer(CID));

CREATE TABLE Hotel (HotelID int, Street varchar(50), City varchar(50), State varchar(50), Country varchar(50), ZIP varchar(20), primary key (HotelID));

CREATE TABLE Hotel_Phone_no (HotelID int, phone_no varchar(25), primary key (HotelID, phone_no), foreign key (HotelID) references Hotel (HotelID));

CREATE TABLE Room_AND_OfferRoom (Room_no int, HotelID int, price real, capacity int, Floor_no int, Description varchar(10000), Type varchar(50), SDate DATE, Discount real, EDate DATE, primary key (Room_no, HotelID), foreign key (HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE Service (sType varchar(50), HotelID int, sCost real, primary key (sType, HotelID), foreign key (HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE Breakfast (bType varchar(50), HotelID int, Description varchar(10000), bPrice real, primary key (bType, HotelID), foreign key (HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE RoomReview (ReviewID int, CID int NOT NULL, Room_no int NOT NULL, HotelID int NOT NULL, Rating int, TextComment varchar(10000), primary key(ReviewID), foreign key(CID) references Customer (CID), foreign key(Room_no) references Room_AND_OfferRoom (Room_no), foreign key(HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE ServiceReview (ReviewID int, CID int NOT NULL, sType varchar (50) NOT NULL, HotelID int NOT NULL, Rating int, TestComment varchar(10000), primary key(ReviewID), foreign key(CID) references Customer (CID), foreign Key(sType) references Service (sType), foreign Key(HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE BreakfastReview (ReviewID int, CID int NOT NULL, bType varchar(50) NOT NULL, HotelID int NOT NULL, Rating int, TextComment varchar(10000), primary key(ReviewID), foreign key(CID) references Customer (CID), foreign Key(btype) references Breakfast (btype), foreign key (HotelID) references Hotel (HotelID) on DELETE CASCADE);

CREATE TABLE Includes (InvoiceNo int, bType varchar(50), Room_no int, HotelID int, primary key (Room_no, InvoiceNo, bType, HotelID), foreign key (InvoiceNo) references Pay_For_Reservation (InvoiceNo), foreign key (bType) references Breakfast (bType), foreign key (HotelID) references Hotel (HotelID), foreign key(Room_no) references Room_AND_OfferRoom (Room_no));

CREATE TABLE Contains (Room_no int, InvoiceNo int, sType varchar(50), HotelID int, primary key (Room_no, InvoiceNo, sType, HotelID), foreign key (InvoiceNo) references Pay_For_Reservation (InvoiceNo), foreign key (sType) references Service (sType), foreign key (HotelID) references Hotel (HotelID), foreign key(Room_no) references Room_AND_OfferRoom (Room_no));

CREATE TABLE Reserves (InvoiceNo int, Room_no int, HotelID int, InDate Date, OutDate Date, NoOfDays int DEFAULT NULL, primary key (InvoiceNo, Room_no, HotelID), foreign key (InvoiceNo) references Pay_For_Reservation (InvoiceNo), foreign key (HotelID) references Hotel (HotelID));

Explanation / Answer

1)
select c.CID,c.Name,sum(p.TotalAmt) from Customer c,Pay_For_Reservation p where c.CID=p.CID and p.ResDate BETWEEN date1AND date2 and ROWNUM <=5 group by c.CID order by sum(p.TotalAmt) asc ;

(here in above query you hve to give the dates then results the query between those dates)

2)
select h.HoteNo,r.Room_no,r.rating from hotel h, RoomReview r,Reserves re where h.HotelID=r.HotelID and re.Room_no=r.Room_no and re.outdate BETWEEN date1AND date2 and ROWNUM <=5 group by r.rating order by h.hotelID asc ;

3)
select b.btype from BreakfastReview where b.Rating>=any(select rating from BreakfastReview) ;

4)
select s.stype from ServiceReview where s.Rating>=any(select Rating from ServiceReview) ;

for above quiries begin date and end date must be given as your wish..............

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.