Please implement your answers in the database CPS3740_2017S on server imc.kean.e
ID: 3795097 • Letter: P
Question
Please implement your answers in the database CPS3740_2017S on server imc.kean.edu.
You have to refer to the tables – Hotel, Room, Booking, Guest in database dreamhome on imc.kean.edu
server.
Your view name must be EXACTLY the same as the requirement. xxxx is your Kean Email ID.
Please view your answers online at http://imc.kean.edu/students
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.
and example is create or replace view vHW1_1_xxxx as select hotelNo from dreamhome.Room where price >40
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
zipcode is a foreign key references to the zipcode field in CPS3740.Zipcode table.
10)Please insert 4 records into your Customers_xxxx table.
Explanation / Answer
create or replace VHW1_8_XXX as
SELECT MAX(X)
FROM ( SELECT type, COUNT(type) AS X
FROM dreamhome.Booking b, dreamhome.Hotel h, dreamhome.Room r
WHERE r.roomNo = b.roomNo AND b.hotelNo = h.hotelNo AND
city = ‘London’
GROUP BY type);
create table dreamhome.HW1_8_xxxx as select * from VHW1_8_xxxx;
10.
CREATE TABLE Customers_xxxx
(
Id number NOT NULL,
Name varchar2(30) NOT NULL,
Balance number(7,2) NOT NULL,
Zipcode varchr2(10) NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Zipcode) REFERENCES
CPS340.Zipcode(zipcode);
10.before inserting values into customers_XXX tale make sure you have value sin zipcode table.
insert into customers_xxxx values (102,'John',1200.30,'NY023');insert into customers_xxxx values (104,'Peter hon',1300.30,'CF023');
insert into customers_xxxx values (104,'Jessy',1600.50,'NY023');
insert into customers_xxxx values (105,'Albert',1000.30,'NJ023');