Numbers 89 and 10 Number 8 has been changed to Create a tabl

Numbers 8,9 and 10

Number 8 has been changed to

Create a table HW1_8_xxxx showing the most commonly booked room type for each hotel in London. You need to show hotelname, (NOT hotelno), room type, and the count.

CPS3740 Spring 2017 Homework Na a1. Page 1 of 1 Dr. Huang Due date: 11:59pm on February 16, 2017 Please implement your answers in the database CPS3740 201.7 on server innc kean edu. You have to refer to the Room, Booking, Guest in database dreamhome on imc kean edu Your view name must be EXACTLY the same as the requirement. xxxx is your Kean ID. Please view your answers online at http://i keanedu/students Email 1. (10 pts Create a view vHwI 1 xxxx for implementing the following relational algebra operations (oprice 40 (Room)) 2. ao pts) Create a view vHw1 2 xxxx for implementing the following relational algebra operations: hotel Name (Hotel HotelhoterNo-Room.hotelNo price 60 (Room))) 3. (10 pts) Create a view vHw1 3 xxxx sting the names and addresses of all guests in London, alphabetically ordered by name. 4. (0 pts) Create a view vHwi 4 xxxx showing the hotel name, room type and the price for the hotel that has the most expensive room. 5. a0 pts) Create a view vHwi 5xxxx sting all double or family rooms with a price below 50.00 per night, in ascending order of price. 6. (0 pts) Create a view vHw1 6 xxxx listing all guests staying at the Grosvenor Hotel from May 1st t July 31st, 2004. 7. (10 pts) Create a view vHwi-7-xxxx sting the number of rooms in each hotel. You need to show 8. (10 pts) Create a view vHwi 8 Kxx showing the most commonly booked room type for each hot London. need show hotelname, 9. (0 pts) Create a table Customers xxxx with the following fields and constraints id: int type, name: varchar type, balance: float type, zipcode: varchar type All fields cannot be NULL. id: the primary key CSP3740.Zipcode table. zipcode is a foreign key references to the zipcode field in

Solution

8) You can use the SELECT ...INTO query to create a new table and insert data into it from the result of a SELECT query as follows:-

SELECT Hotel.Name, Booking.RoomType, MAX(COUNT(Booking.RoomType)) AS Booked_Count

INTO HW1_8_xxxx

FROM Hotel, Booking

GROUP BY Hotel.Name ORDER BY COUNT(Booking.RoomType)

WHERE Hotel.id=Booking.HotelID;

Here, it is assumed that HotelID is a foreign key in the table Booking referencing \'id\' of the table Hotel and that the Booking table has a field name \'RoomType\'. Please make appropriate changes to the queries if this is not so, for example including the Room table for getting the Room Type if required, using the correct field names and so on.

9)CREATE TABLE Customers_xxxx(id INT NOT NULL, name VARCHAR NOT NULL, balance FLOAT NOT NULL, zipcode VARCHAR NOT NULL REFERENCES CSP3740.Zipcode(zipcode), PRIMARY KEY (id));

10)There are two ways of inserting records into a table:-

i) The field names need to be mentioned before the data. The data has to be in the same order as the field name, i.e- the data mentioned in the first place will be inserted into the field name mentioned first in the query, the data mentioned second will be inserted into the field mentioned second and so on.

If any field is not mentioned in the query with its corresponding data, then that field remais empty. But that is not allowed in this case as all fields of the Customers_xxxx table have been specified as NOT NULL.

If you want to use this method of inserting data, please write a query like:-

INSERT INTO Customers_xxxx (id, name, balance, zipcode)

VALUES (1, \'John Smith\', 500.75, \'22345\');

ii) The second way of inserting records into a table is by mentioning only the data values that you want to enter without mentioning the field names. Data values for all the fields of the record must be must be added and they must be in the same order as the field names in the table. To use this method, a query like the following must be entered:-

INSERT INTO Customers_xxxx VALUES (1, \'John Smith\', 500.75, \'22345\');

You can use either one or both the above methods to enter four individual records into the Customers_xxxx table by using appropriate data for vlaues.

Hope this helps!