SQL Programming Query1: Add a Staff table to the database with the following col
ID: 3573634 • 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
CREATE TABLE STAFF(STAFF_ID INT PRIMARY KEY, staff_first VARCHAR(100), staff_last VARCHAR(100), staff_phone VARCHAR(100), staff_position VARCHAR(100),HOTEL_ID INT,
Foreign Key (HOTEL_ID) REFERENCES HOTEL(HOTELNO));
ALTER TABLE BOOKING
ADD WHEELCHAIR ACCESSIBLE VARCHAR(10);
UPDATE ROOM SET PRICE = PRICE + (0.4*PRICE) WHERE PRICE>0;
DELETE FROM GUEST WHERE GUESTNO=14;
INSERT INTO STAFF VALUES(45301,"John","Watkins","12345678","MANAGER",1);
SELECT hotelNo, guestNo, roomNo and datefrom FROM HOTEL ,GUEST , ROOM , BOOKING
WHERE GUEST.GUESTNO=BOOKING.GUESTNO AND BOOKING.HOTELNO=HOTEL.HOTELNO AND ROOM.HOTELNO=HOTEL.HOTELNO ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.