Write a query that will show how may bookings have been made

Assignment Help Database Management System
Reference no: EM13902541

Background

You will be working with a set of tables for a hotel.

To access these tables in sql create the following synonyms:

SQL> CREATE SYNONYM GUEST FOR VBRUNO.GUEST; SQL> CREATE SYNONYM BOOKING FOR VBRUNO.BOOKING; SQL> CREATE SYNONYM ROOM FOR VBRUNO.ROOM;

Queries

You are to prepare 13 SQL query statements that will provide answers to the following 13 requests for information.

1. List all bookings (using only date_in) during June 2015 of guests that are between 30 and 50 years old. Show the full name, sex, room number and age.

2. Show the number of bookings, for each month of the 2015 calendar year.

3. Management want to know the usage of the hotel based on floors. Write a query that will show how may bookings have been made for each floor.

4. For rooms that have a linked room, show details of the room and the linked room together (hint: self join).

5. Give a breakdown of how many bookings each sex has made in the hotel - ie. How many bookings have been made by males and how many bookings were made by females.

6. List the details of rooms which have been booked more than 20 times.

7. Display the names of guests that have stayed at the hotel more than 10 times.

8. Display details of the room(s) with the most bedrooms in the hotel.

9. Display guests (if there are any) that have never made a booking.

10. The hotel wishes to analyse the origin of it's guests. Write a view that displays a summary of the number of guests that live in each distinct postcode.

11. Using the view in question 10, show the postcode that has the most guests.

12. Display a summary of the number of individual bookings made for each room. If there are any rooms that have never been booked, they should be included and shown with a count of zero.

13. Create your own query. It must include a nested query. Submit the following:
• question your query is answering
• the SQL query
• The mark for this question will depend on the complexity of the query.
• Higher marks will be given for queries that are more complex and/or innovative.
• If you do not provide a description of what question the query is answering, you will get zero for this query.

Table descriptions for the "hotel" database


GUEST


 

Name                  Type

--------------------- ------------

GUEST_NO              CHAR(8)

SURNAME               VARCHAR2(30)

GIVEN                 VARCHAR2(30)

DOB                   DATE

SEX                   CHAR(1)

HOME_PHONE            VARCHAR2(20)

WORK_PHONE            VARCHAR2(20)

FAX                   VARCHAR2(20)

ADDRESS               VARCHAR2(40)

SUBURB                VARCHAR2(40)

STATE                 VARCHAR2(40)

POSTCODE              NUMBER(4)


ROOM

 

 

Name                  Type

--------------------- ------------

ROOM_NO               CHAR(10)

FLOOR_NO              NUMBER(2)

SPA_BATH              CHAR(1)

NUM_BEDS              NUMBER(1)

PRICE                 NUMBER(10,2)

LINKED_ROOM_NO        CHAR(10)


BOOKING

 

 

Name                  Type

--------------------- -----------

BOOKING_NO            CHAR(10)

GUEST_NO              CHAR(8)

ROOM_NO               CHAR(10)

DATE_IN               DATE

DATE_OUT              DATE

Reference no: EM13902541

Questions Cloud

Differentiation strategy and with cost leadership strategy : What are 6 employment sources that HRM can utilize to source candidates for customer service sales position at a company with differentiation strategy and with cost leadership strategy?
When a linear programming model has equality constraint : Label each of the following statements as true or false, and then justify your answer. (a) When a linear programming model has an equality constraint, an artificial variable is introduced into this constraint in order to start the simplex method with..
Bering co. disposes of a machine costing : On January 2, 2013, Bering Co. disposes of a machine costing $ 44,000 with accumulated depreciation of $ 24,625. Prepare the entries to record the disposal under each of the following separate assumptions.
Rules of the game as component of organizational culture : Which of the following is an example of "rules of the game" as a component of organizational culture? Which of the following single components can most appropriately be considered to embody or represent the culture of any given organization? The diff..
Write a query that will show how may bookings have been made : Display the names of guests that have stayed at the hotel more than 10 times - Display details of the room(s) with the most bedrooms in the hotel.
What profit does cheapest earn per week with current system : Cheapest Car Rental rents cars at the Chicago airport. The rental market consists of two segments: the short term segment, which rents for an average of 0.5 weeks, and the medium-term segment, which rents for an average of 2 weeks. Identify throughpu..
Explain marginal product of second workers higher than first : Explain why the marginal product of the second and third workers might be higher than the first. Why might you expect the marginal product of additional workers to diminish eventually?
Objectives for a public health program : An explanation of three potential implications of having poorly articulated goals and objectives for a public health program and Description of your goal and objectives
Prepare a three-period moving-average forecast for the data : In the Atlanta area, the number of daily calls for the repair of Xerox copy machines has been recorded as follows: Day of month Calls (count) 1 92 2 127 3 106 4 165 5 125 6 111 7 178 8 97 Using Excel, perform the following calculations: Prepare a thr..

Reviews

Write a Review

 

Database Management System Questions & Answers

  Determine functional dependencies of table

Using your knowledge of Premiere Products, determine the functional dependencies that exist in the following table.

  Create a data dictionary

Construct a query that will show the number of days that exist between the first invoice and last invoice, for each month, for each employee, using the DATEDIFF function. Be sure to provide the SQL script that will carry out this function.

  Mark the words as index entries behavior and favorable

Mark the following words in the document as index entries: behavior (four instances), favorable (five instances), and impression (eight instances).

  Implement direct-address table keys of stored elements

Suggest how to implement direct-address table in which keys of stored elements don't require to be distinct and elements can have satellite data.

  Kudler fine foods it security report and presentation

Learning Team Instructions- Draft the security considerations for each phase of the systems development process.

  Create an xml file with markup tags

Create an XML file with markup tags and some sample data to represent a list of invoices. Include the XML tags for two invoices in the list. Also, assume the invoices are created from a database whose tables are shown in the following database rela..

  Describe the different operations of relational algebra

Describe relationships with the example. Also illustrate degree of relationship for that example. Describe the different operations of relational algebra with suitable example each.

  Write a two to three page paper in which youdocument the

write a two to three page paper in which youdocument the requirements based on the information provided and assumptions

  Database with data about a university

This assignment uses a database with data about a university; the database resides in our Oracle servers. The schema of the database is provided below. Keys are in bold face and underlined, field types are omitted; assume that appropriate foreign ..

  Importing and analyzing data for johnson equipment

Importing and Analyzing Data for Johnson Equipment-Johnson Equipment, the medium-sized laboratory equipment manufacturing company where you work, is in the process of acquiring Sloan Manufacturing, a smaller equipment /manufacturer in the same indu..

  Construct a b+ tree

Construct a B+ tree for the following set of key value (2,3,5,7,11,19,23,29,31) in ascending order where the pointers that will fit in one node is as follows:A. four B. six C. eight assuming the tree is initially empty and values are in ascending ..

  Write an sql query

Write an SQL query to get the author of the book "The Alchemist"

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd