Question

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 to the appropriate table to record the state the hotel is in.

Query 3: change the name of the Holiday Inn in Tulsa to Holiday Inn Supreme

query 4: delete room 212 in hotel 1 from the database

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	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
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	222

guest

guestNo	guestFirst	guestLast	guestStreet	guestCity	guestState	guestPhone	guestEmail
1	Robert	Croft	123 main	Boise	ID	(208) 564-1456	[email protected]
2	Howard	Smith	234 main	Tulsa	OK	(405) 744-1144	[email protected]
3	Mike	Rose	345 main	Hays	KS	(785) 624-5511	[email protected]
4	Matt	Smith	234 main	Monroe	LA	(318) 597-8417	[email protected]
5	Jennifer	Bratton	2526 Chatham Way	Stillwater	OK	(405) 777-5413	[email protected]
6	Bryan	Hedrick	35 West Liberty	Meridian	ID	(208) 555-3262	[email protected]
7	Raj	Ramanathan	145 E. Highland	Fargo	ND	(210) 555-7487	[email protected]
8	Marie	James	35 N. Perkins Road	Kansas City	KS	(913) 732-5541	[email protected]
9	Tony	Chang	2545 Brighton Lane	Shreveport	LA	(318) 267-4419	[email protected]
10	John	Jones	345 Apple St	Boise	ID	(208) 662-4517	[email protected]
11	Jason	Williamson	1123 15th Street	Stillwater	OK	(405) 238-5959	[email protected]
12	Martin	Johnson	3478 Trenton St.	Ruston	LA	(318) 233-9084	[email protected]
13	Susan	Ellis	1211 West Oak	Oklahoma City	OK	(405) 332-1167	[email protected]

hotel

hotelNo	hotelName	hotelCity	hotelPhone
1	Holiday Inn	Tulsa	(405) 741-5555
2	Hampton Inn	Stillwater	(405) 874-9963
3	Atherton	Stillwater	(405) 365-4412
4	Marriott-OKC	Oklahoma City	(405) 774-8514
5	Marriott-Tulsa	Tulsa	(405) 714-5562
6	Hyatt	Oklahoma City	(405) 732-5412

room

roomNo	hotelNo	type	price
101	1	single	90
123	6	double	125
200	1	double	120
200	2	double	160
206	1	double	120
210	3	double	150
210	6	single	100
212	1	family	160
212	3	single	100
212	4	single	200
214	3	double	160
219	5	single	130
222	6	single	100
308	3	single	100
308	5	double	160
312	4	single	200
315	4	double	225
321	4	single	190
333	1	single	95
401	6	single	100
410	2	family	175
411	2	family	180
412	2	family	175
421	5	single	130
500	1	single	100
521	5	single	130
999	1	family	160

Answer 1

Query 1:-

CREATE TABLE Staff (
    staff_id int NOT NULL PRIMARY KEY,
    staff_first varchar(255) ,
    staff_last varchar(255),
    staff_phone int, staff_position int, hotel_id int,

hotel_id int FOREIGN KEY REFERENCES hotel (hotel_id )

);

Query 3:-

Update hotel set hotelName= 'Holiday Inn Supreme' where hotelName='Holiday Inn' and hotelCity='Tulsa'

Query 4:-

delete from room where roomNo=212 and hotelNo=1