SQL Programming Query1: Add a Staff table to the database with the following col
ID: 3572800 • Letter: S
Question
SQL Programming
Query1: Add a Staff table to the database with the following columns: staff_id, staff_first, staff_last, staff_phone, staff_position, hotel_id. hotel_id should be designated as a foreign key referencing the hotel table. Hint: use integer as the data type for hotelno. Make sure to designate the primary key.
Query 2: Add a column for whether the room is wheelchair accessible to the room table
Query 3: Increase prices of all rooms in hotel 4 by 10%.
Query 4: Delete guest 14 from the database.
Query 5: Add the following employee to the database: staff id 45301, John Watkins (jwatkins@holidayinn.com) holds the position of manager at hotel 1.
Query 6:
Create a query that shows all of the rooms and bookings of those rooms. The result should include all rooms, even those rooms that have not been booked. Show the hotelNo, guestNo, roomNo and datefrom in the result. Remember from homework 2 that room has a composite primary key and you must modify the join for this.
booking hotelNo guestNo dateFrom dateTo roomNo 1 1 2/28/2015 3/1/2015 101 1 2 3/5/2015 3/7/2015 206 1 2 3/10/2015 3/15/2015 206 1 2 3/4/2016 3/6/2016 206 1 2 3/9/2016 3/14/2016 206 1 5 2/1/2015 2/2/2015 200 1 6 2/4/2015 2/6/2015 500 1 7 2/3/2015 2/8/2015 206 1 7 4/1/2015 4/4/2015 212 1 8 2/1/2015 2/7/2015 200 1 9 2/1/2015 2/6/2015 101 2 3 3/22/2016 3/25/2016 410 2 4 2/6/2015 2/10/2015 411 2 8 4/1/2016 4/4/2016 200 2 8 4/15/2016 4/18/2016 200 2 9 2/5/2015 2/6/2015 412 3 1 3/13/2016 3/15/2016 212 3 3 1/31/2014 2/4/2014 308 3 3 3/26/2016 3/31/2016 210 3 4 3/11/2016 3/15/2016 214 3 7 2/3/2015 2/4/2015 212 3 7 2/5/2015 2/8/2015 308 4 9 4/15/2016 4/18/2016 321 4 10 3/12/2014 3/13/2014 312 4 10 2/22/2015 2/24/2015 312 5 2 5/18/2015 5/23/2015 219 5 11 5/11/2014 5/12/2014 308 5 13 5/12/2014 5/13/2014 421 6 9 8/3/2016 8/4/2016 401 6 10 8/9/2014 8/10/2014 222 6 10 9/8/2015 9/15/2015 401 6 11 5/31/2014 6/4/2014 222 6 12 7/3/2016 7/5/2016 222Explanation / Answer
Query1: Add a Staff table to the database with the following columns: staff_id, staff_first, staff_last, staff_phone, staff_position, hotel_id. hotel_id should be designated as a foreign key referencing the hotel table. Hint: use integer as the data type for hotelno. Make sure to designate the primary key.
//Considering hotelno to be hotel_id, Please let me know if i'm correct as all the solutions are considered based on that.
CREATE TABLE Staff
(
staff_id int NOT NULL,
staff_first varchar2(40) NOT NULL,
staff_last varchar2(40),
staff_phone varchar2(20),
staff_position varchar2(20),
hotel_id int,
PRIMARY KEY (staff_id),
FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
)
Query 2: Add a column for whether the room is wheelchair accessible to the room table
//Using Varchar2 as the data type for the column wheelchair_accessible
ALTER TABLE room ADD column_name wheelchair_accessible varchar2(10);
Query 3: Increase prices of all rooms in hotel 4 by 10%.
//Using hotel_id instead of hotelno, price*1.10 means increased by 10%
UPDATE room SET price = (price * 1.10) WHERE hotel_id=4;
Query 4: Delete guest 14 from the database.
//Assuming guestNo to be PRIMARY KEY, hence it will directly remove the record without any issue
delete from guest where guestno=14;
Query 5: Add the following employee to the database: staff id 45301, John Watkins (jwatkins@holidayinn.com) holds the position of manager at hotel 1.
//Assuming staff_phone to be jwatkins@holidayinn.com, hence the data type is varchar2(20)
insert into staff values (45301,John,Watkins,jwatkins@holidayinn.com,manager,1)
Query 6:
Create a query that shows all of the rooms and bookings of those rooms. The result should include all rooms, even those rooms that have not been booked. Show the hotelNo, guestNo, roomNo and datefrom in the result. Remember from homework 2 that room has a composite primary key and you must modify the join for this.
//used concat function of sql as the room table is having composite primary key (Two are more columns combined together to form a primary key) as mentioned in the question.
select B.hotel_id,B.guestNo,R.roomNo,B.datefrom from room R JOIN booking B where concat(B.hotel_id,B.roomNo)=concat(R.hotel_id,R.roomNo);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.