Consider the following tables which are a part of a hotel management database: Table Name: ROOMDETAIL...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the following tables which are a part of a hotel management database: Table Name: ROOMDETAIL DetailID RD01 RD02 RD03 Description 2 single beds 1 king bed 1 super king bed, 1 king bed Table Name: ROOM RoomID R1001 R2002 R2005 R3006 R5005 C104 C105 C106 Name Superior Twin Deluxe Twin Superior King Deluxe King Deluxe Suite Table Name: GUEST GuestID C101 C102 C103 GuestName J.Alan P.Amos S.John K.William J.Martin K.Oscar Table Name: RESERVATION ReservationID R$1001 RS1193 RS1208 RS1245 RS2501 RS2601 RS3002 RoomID R1001 R2005 R3006 R5005 R2002 R2005 R2005 DetailID RD01 RD01 RD02 RD02 RD03 Contact Number 011-6738495 018-3927648 019-3442486 017-9387652 011-5326780 016-2230087 MaxCapacity 2 2 4 GuestID C102 C105 C103 C101 C102 C103 C102 Price 199 250 550 CheckInDate 2019-12-02 2019-12-05 2020-01-15 2021-05-09 2021-08-10 2022-01-15 2022-04-08 CheckOutDate 2019-12-05 2019-12-06 2020-01-16 2021-05-12 2021-08-12 2022-01-17 2022-04-10 Based on the tables above, write SQL statements for the following queries: a. Provide the list of guests who made reservation more than 2 times. (4 marks) b. Provide the list of guest names and contact numbers who stayed in the hotel for year 2019. (4 marks) c. Provide the list of guest names, contact numbers, check-in and check-out dates who made reservation for room "Deluxe Suite". (4 marks) d. Write the DDL statement to create the RESERVATION table shown above. Assign suitable datatype for each attribute and include appropriate constraints in the table. (4 marks) e. During the peak season, the price of all rooms was increased by 20%. After the peak season is over, the price of all rooms was dropped by 10%. Write queries to update the rooms' price for different seasons. (4 marks) Consider the following tables which are a part of a hotel management database: Table Name: ROOMDETAIL DetailID RD01 RD02 RD03 Description 2 single beds 1 king bed 1 super king bed, 1 king bed Table Name: ROOM RoomID R1001 R2002 R2005 R3006 R5005 C104 C105 C106 Name Superior Twin Deluxe Twin Superior King Deluxe King Deluxe Suite Table Name: GUEST GuestID C101 C102 C103 GuestName J.Alan P.Amos S.John K.William J.Martin K.Oscar Table Name: RESERVATION ReservationID R$1001 RS1193 RS1208 RS1245 RS2501 RS2601 RS3002 RoomID R1001 R2005 R3006 R5005 R2002 R2005 R2005 DetailID RD01 RD01 RD02 RD02 RD03 Contact Number 011-6738495 018-3927648 019-3442486 017-9387652 011-5326780 016-2230087 MaxCapacity 2 2 4 GuestID C102 C105 C103 C101 C102 C103 C102 Price 199 250 550 CheckInDate 2019-12-02 2019-12-05 2020-01-15 2021-05-09 2021-08-10 2022-01-15 2022-04-08 CheckOutDate 2019-12-05 2019-12-06 2020-01-16 2021-05-12 2021-08-12 2022-01-17 2022-04-10 Based on the tables above, write SQL statements for the following queries: a. Provide the list of guests who made reservation more than 2 times. (4 marks) b. Provide the list of guest names and contact numbers who stayed in the hotel for year 2019. (4 marks) c. Provide the list of guest names, contact numbers, check-in and check-out dates who made reservation for room "Deluxe Suite". (4 marks) d. Write the DDL statement to create the RESERVATION table shown above. Assign suitable datatype for each attribute and include appropriate constraints in the table. (4 marks) e. During the peak season, the price of all rooms was increased by 20%. After the peak season is over, the price of all rooms was dropped by 10%. Write queries to update the rooms' price for different seasons. (4 marks)
Expert Answer:
Related Book For
Organic Chemistry A Short Course
ISBN: 978-1111425562
13th edition
Authors: Harold Hart, Christopher M. Hadad, Leslie E. Craine, David J. Hart
Posted Date:
Students also viewed these databases questions
-
Consider a binomial distribution with 10 trials. Look at Table 2 in the Appendix showing binomial probabilities for various values of p, the probability of success on a single trial. (a) For what...
-
How are financial statements adjusted for exchange rates?
-
A sample consists of the following n = 6 scores: 2, 7, 4, 6, 4, and 7. a. Compute the mean and standard deviation for the sample. b. Find the z-score for each score in the sample. c. Transform the...
-
A two-dimensional Rankine half body, 8 cm thick, is placed in a water tunnel at 20°C. The water pressure far upstream along the body centerline is 120 kPa. What is the nose radius of the...
-
Explain why tension (in ropes, etc.) and the contact forces between surfaces are not fundamental forces.
-
Marjorie Jorgenson, CPA, is verifying the accuracy of outstanding accounts payable for Marygold Hardware, a large, single-location retail hardware store. There are 650 vendors listed on the...
-
Please provide the answers and formulas, preferably through excel. Question 1 Consider the following three stocks: a) Stock A is expected to provide a dividend of $10 a share forever (starting next...
-
What are the Partner Deployment options in Central Partner Dashboard?
-
Suppose a test for hypoglycemia is given. If a person has hypoglycemia, the test will detect it in \(90 \%\) of the cases; if the person does not have hypoglycemia, the test will show a positive...
-
In the game described in Problem 57, what is the probability that there will be an odd man out in a particular play if there are four players? Data from Problem 57 In a certain office, three men...
-
Two cards are drawn in succession from a deck of 52 cards (without replacement). Let \(D_{1}=\{\) diamond is drawn on the first draw\} and \(D_{2}=\) \{diamond is drawn on the second draw\}. Find the...
-
The Weather Channel makes accurate predictions about \(85 \%\) of the time. Use this information to answer the questions in Problems 12-13. What is the probability that they are accurate exactly 4 of...
-
A researcher chooses three leaves from a target environment and classifies each sample as fungus free or contaminated. Suppose that a leaf has a probability of 0.2 of being infected. In Problems...
-
phincipal moments g principal. inestia and of anis fon Find divection the quien cube Isy- Iyz =Inz -1 Ma?.
-
Discuss the information available from the following techniques in the analysis of inorganic pigments used in antique oil paintings: (i) Powder X-ray diffraction, (ii) Infrared and Raman...
-
(5R,6S)-6-Acetoxy-5-hexadecanolide is a pheromone that attracts certain disease-carrying mosquitoes to sites where they like to lay their eggs. Such compounds might be used to lure these insects away...
-
As discussed in "A Word About . . . Degradable Polymers" (p. 426), some polymers can be naturally recycled in the environment. One can also imagine an application in drug delivery by the slow...
-
Draw structural formulas for the three possible isomers of C3H8O.
-
Is there any information that Shuang could get more easily by interview than by researching secondary sources? If so, what is this information and who would you recommend that she contact?
-
Kaplan gives several examples of the need to clearly explain the cafs purpose and goals. Identify specific assumptions or misconceptions that their audience may have. Why is managing or countering...
-
Do you have any additional tips and tricks you use to organize research results? If so, explain how they would be useful for this project.
Study smarter with the SolutionInn App