I need help with this SQL query. Here are the tables: customer( CID: int, Name:
ID: 3822466 • Letter: I
Question
I need help with this SQL query.
Here are the tables:
customer( CID: int,
Name: varchar(32),
Address: varchar(32),
Phone_no: char(16),
Email: varchar(32),
primary key(CID));
reservation( InvoiceNo: int,
ResDate: date,
TotalAmt: decimal(5,2)
CID: int,
Room_no: int,
HotelID:int,
InDate: date,
OutDate: date,
NoOfDays: int,
Cnumber: varchar(16),
bType: varchar(32),
sType: varchar(32),
primary key(InvoiceNo),
foreign key(CID) references customer(CID),
foreign key(Cnumber) references creditcard (Cnumber),
foreign key(HotelID) references hotel (HotelID),
foreign key(bType) refercens breakfast (bType),
foreign key(sType) referecnes service (sType),
foreign key(Room_no) referecnes room (Room_no));
Query:
For a given time period(begin date and end date) compute the 5 best customers (in terms of money spent in reservations).
Explanation / Answer
SELECT *
FROM customer
WHERE
CID IN
( SELECT CID,SUM(TotalAmt) AS totalAmount
FROM reservation group by CID
ORDER BY totalAmount limit 5).CID ;