solutionOfassignment3(2010).doc

5.7 List full details of all hotels.select *from Hotel5.8 List full details of all hotels in London.select * from Hotelwhere city=London5.9 List the names and addresses of all guests living in London, alphabetically ordered by name. select guestName ,guestAddress from Guestwhere guestAddress like London%order by gusetName asc5.10 List all double or family rooms with a price below $40.00 per night, in ascending order of price.select * from Roomwhere type in (double ,family) and price40.00order by price select * from Roomwhere (type=double or type =family)and price 40order by price5.11 List the bookings for which no dataTo has been specified.select * from Bookingwhere dataTo is nullAggregate functions5.12 How many hotels are there?select count(*) from Hotel5.13 What is the average price of a room?select avg(price) from Room5.14 What is the total revenue per night from all double rooms?select sum(price) from Roomwhere type=double5.15 How many different guests have made booking for August?select count(distinct guestNo)from Bookingwhere dateFrom between 08-01-2010and 08-31-2010 or dateTo between 08-02-2010 and 08-31-2010select count(distince guestNo)from Bookingwhere month(dateFrom) =8or month(dateTo) =85.16 List the price and type of all rooms at the Grosvenor Hotel.select price , type from Room join Hotel on Hotel.hotelNo=Room.hotelNowhere hotelName=Grosvenor HotelSelect price , typefrom Roomwhere hotelNo=(select hotelNo from Hotel where hotelName=Grosvenor Hotel) Select price , typefrom Roomwhere exists(select * from Hotelwhere Hotel.hotelNo=Room.hotelNoand hotelName=Grosvenor Hotel)5.17 List all guests currently staying at the Grosvenor Hotel.select guestNo ,gusetName ,guestAddressfrom Guestwhere guestNo in(select guestNo from Booking where dateFromgetdate() and hotelNo in (select hotelNo from Hotel where hotelName=Grosvenor Hotel)5.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.select Room.roomNo,type,price,guestNamefrom (Room join Hotel on Room.hotelNo=Hotel.hotelNo) left Join (Booking join Guest on Guest.guestNo=Booking.guestNo) on Room.roomNo=Booking.roomNo and Hotel.hotelNo=Booking.hotelNowhere DateFromgetdate() and hotelName=Grosvenor Hotel)5.19 What is the total income from Bookings for the Grosvenor Hotel todayselect sum(Price) as TotalIncomefrom Room where exists( select * from Booking where Room.hotelNo=Booking.hotelNo and Room.roomNo=Booking.roomNo and DateFromgetdate() and HotelNo=(select HotelNofrom Hotelwhere HotelName=Grosvenor)5.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.select * from Room where not exists( select * from Booking where Room.hotelNo=Booking.hotelNo and Room.roomNo=Booking.roomNo and DateFromgetdate()and HotelNo=(select HotelNofrom Hotelwhere HotelName=Grosvenor)5.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?select sum(price)from Room where not exists( select * from Booking where Room.hotelNo=Booking.hotelNo and Room.roomNo=Booking.roomNo and DateFromgetdate()and HotelNo=(select HotelNofrom Hotelwhere HotelName=Grosvenor) 5.22 list the number of rooms in each hotel Select hotelNo, count(roomNo) From room Group by hotelNo 5.23 list the number of rooms in each hotel in LondonSelect room.hotelNo, hotelName, count(roomNo) From room join Hotel on room.hotelNo=Hotel.hotelNo Where city=London Group by room .hotelNo,hotelName5.24 What is the average number of bookings for each hotel in August?、select r.hotelNo,avg(r.sumroom) as avgbookingfrom(select hotelNo,dateFrom, count(*) as sumroomfrom Bookingwhere dateFrom between 2008-08-01 and 2008-08-31 or dateTo between 2008-08-01 and 2008-08-31group by hotelNo, ,dateFrom) rgroup by r.hotelNo5.25 What is the most commonly booked room type for each hotel in London?Create view type_roomNumber(hotelNo, type, booknum) AsSelect hotelNo,type, count(*)From Booking join Room on Booking.hotelNo=Room.hotelNo and Booking.roomNo=Room.roomNoGroup by hotelNo,typeSelect hotelNo,typeFrom type_roomNumber T1Where booknum= (select max(booknum) From type_roomNumber T2 Where T2.hotelNo=T1.hotelNo)5.26 what is the lost income from unoccupied rooms at each hotel today select hotelNo,sum(price)from Room where not exists( select * from Booking where Room.hotelNo=Booking.hotelNo and Room.roomNo=Booking.roomNo and DateFromgetdate()group by hotelNo5.27 insert rows into each of these tablesFor example:Insert into Hotle(hotelNo, hotelName,city)Values (001, Grosvenor,Londan)Insert into Room(RoomNo, hotelNo,type,price)Values (1001, 001,family,55)Insert into Booking(hotelNo, guestNo,dateFrom,dateTo,roomNo)Values (001, 100, 8-1, 8-26,1001)Insert into Guest(guestNo,guestlName,guestAddress)Values (100, Tom,LondanStress)5.28 update the price of all rooms by 5%update Roomset price=price*1.056.10 Create the Hotel table using the integrity enhancement features of SQL create table Hotel (hotelNo char(8) not null unique, hotelName char(35), city char(20), constraint PK_Hotel Primary key(hotelNo) )6.11 Now create the Room,Booking,and Guest tables using the integrity enhancement features of SQL with the following constraints: (a) type must be one of Single,Double,or Family. (b) price must be between $10 and $100. (c) roomNo must be between 1 and 100. (d) dateFrom and dateTo must be greater than todays date. (e) the same room cannot be double-booked. (f) the same guest cannot have overlapping bookings. create table Room (roomNo char(8) not null, hotelNo char(8) not null, type char(10), price money, constraint PK_Room Primary key(roomNo,hotelNo), constraint FK_Room foreign key(hotelNo) references Hotel(hotelNo), constraint chk_roomNo check(roomNo between 1 and 100), constraint chk_type check(type in(Single,Double,Family), constraint chk_price check(price between 10 and 100) )create table Guest(guestNo char(8) not null guestName char(15), guestAddress char(35), constraint PK_Guest Primary key(guestNo)create table Booking(hotelNo char(8) not null, roomNo char(9) not null, guestNo char(10) not null, dateFrom datetime not null, dateTo datetime,constraint PK_Booking Primary key(hotelNo,guestNo, dateFrom) constraint FK_Booking1 foreign key(hotelNo,roomNo) references Room(hotelNo，roomNo),constraint FK_Booking2 foreign key(guestNo) references Guest(guestNo),constraint chk_dateFrom check(dateFrom=getdate(), constraint chk_dateTo check(dateTogetdate() )6.12 Create a separate table with the same structure as the Booking table to hold archive records.Using the INSERT statement, copy the records from the Booking table to the archive table relating to bookings before 1 January 2003.Delete all bookings before 1 January 2003 from the Booking table.create table Booking_archive(hotelNo char(8) not null, roomNo char(9) not null, guestNo char(10) not null, dateFrom datetime not null, dateTo datetime,constraint PK_Booking Primary key(hotelNo,guestNo, dateFrom) constraint FK_Booking1 foreign key(hotelNo,roomNo) references Room(hotelNo，roomNo),constraint FK_Booking2 foreign key(guestNo) references Guest(guestNo),constraint chk_dateFrom check(dateFrom=getdate(), constraint chk_dateTo check(dateTogetdate() insert into Booking_archive (hotelNo,guestNo,dateFrom,dateTo,roomNo) select hotelNo,guestNo,dateFrom,dateTo,roomNo from Booking where dateFrom2003-01-01 delete from Bookingwhere dateFrom2003-01-016.13 Create a view containing the hotel name and the names of the guests staying at the hotel.create view Staying_guest(hotelName,guestName)as select guestName, hotelName from (Guest G join Bookings B on G.guestNo=B.guestNo) join Hotel H on H.hotelNo=B.hotelNo where dateFrom =getdate() or dateTo is null) 6.14 Create a view containing the account for each guest at the Grosvenor Hotel Create view guestAccount(guestNo,guestName,guestAddress,Accout) As Select Guest.guestNo, guestName, guestAddress, (getdate()-dateFrom)*price From (Guest join Booking on Booking.guestNo=Guest.guestNo) join Room on Room.hotelNo=Booking.hotelNo and Room.roomNo=Booking.roomNoWhere Hotel.hotelNo= (Select hotelNo From Hotel Where hotelName=Grosvenor Hotel)and dateFrom =getdate() or dateTo is null)6.15 Give the users Manager and Director full access to these views，with the privilege to pass the access on to other users.Grant all privileges On guestAccount, Staying_guestTo Manager, DirectorWith grant option6.16 Give the user Accounts select access to these views. Now revoke the access from this user.Grant