database:

CREATE TABLE GUIDE (GUIDE_NUM CHAR(4) PRIMARY KEY, LAST_NAME CHAR(15), FIRST_NAME CHAR(15), ADDRESS CHAR(25), CITY CHAR(25), STATE CHAR(2), POSTAL_CODE CHAR(5), PHONE_NUM CHAR(12), HIRE_DATE DATE ); CREATE TABLE CUSTOMER (CUSTOMER_NUM CHAR(4) PRIMARY KEY, LAST_NAME CHAR(30) NOT NULL, FIRST_NAME CHAR (30), ADDRESS CHAR(35), CITY CHAR(35), STATE CHAR(2), POSTAL_CODE CHAR(5), PHONE CHAR(12) ); CREATE TABLE RESERVATION (RESERVATION_ID CHAR(7) PRIMARY KEY, TRIP_ID DECIMAL(3,0), TRIP_DATE DATE, NUM_PERSONS DECIMAL(3,0), TRIP_PRICE DECIMAL(6,2), OTHER_FEES DECIMAL(6,2), CUSTOMER_NUM CHAR(4) ); CREATE TABLE TRIP (TRIP_ID DECIMAL(3,0) PRIMARY KEY, TRIP_NAME CHAR(75), START_LOCATION CHAR(50), STATE CHAR(2), DISTANCE DECIMAL(4,0), MAX_GRP_SIZE DECIMAL(4,0), TYPE CHAR(20), SEASON CHAR(20) ); CREATE TABLE TRIP_GUIDES (TRIP_ID DECIMAL(3,0), GUIDE_NUM CHAR(4), ASSIGN_DATE DATE, PRIMARY KEY (TRIP_ID, GUIDE_NUM) ); INSERT INTO GUIDE VALUES ('AM01','Abrams','Miles','54 Quest Ave.','Williamsburg','MA','01096','617-555- 6032','6-3-2012'); INSERT INTO GUIDE VALUES ('BR01','Boyers','Rita','140 Oakton Rd.','Jaffrey','NH','03452','603-555-2134','3- 4-2012'); INSERT INTO GUIDE VALUES ('DH01','Devon','Harley','25 Old Ranch Rd.','Sunderland','MA','01375','781-555- 7767','1-8-2012'); INSERT INTO GUIDE VALUES ('GZ01','Gregory','Zach','7 Moose Head Rd.','Dummer','NH','03588','603-555- 8765','11-4-2012'); INSERT INTO GUIDE VALUES ('KS01','Kiley','Susan','943 Oakton Rd.','Jaffrey','NH','03452','603-555-1230','4- 8-2013'); INSERT INTO GUIDE VALUES ('KS02','Kelly','Sam','9 Congaree Ave.','Fraconia','NH','03580','603-555-0003','6- 10-2013'); INSERT INTO GUIDE VALUES ('MR01','Marston','Ray','24 Shenandoah Rd.','Springfield','MA','01101','781-555- 2323','9-14-2015'); INSERT INTO GUIDE VALUES ('RH01','Rowan','Hal','12 Heather Rd.','Mount Desert','ME','04660','207-555- 9009','6-2-2014'); INSERT INTO GUIDE VALUES ('SL01','Stevens','Lori','15 Riverton Rd.','Coventry','VT','05825','802-555- 3339','9-5-2014'); INSERT INTO GUIDE VALUES ('UG01','Unser','Glory','342 Pineview St.','Danbury','CT','06810','203-555- 8534','2-2-2015'); INSERT INTO CUSTOMER VALUES ('101','Northfold','Liam','9 Old Mill Rd.','Londonderry','NH','03053','603-555- 7563'); INSERT INTO CUSTOMER VALUES ('102','Ocean','Arnold','2332 South St. Apt 3','Springfield','MA','01101','413-555- 3212'); INSERT INTO CUSTOMER VALUES ('103','Kasuma','Sujata','132 Main St. #1','East Hartford','CT','06108','860-555- 0703'); INSERT INTO CUSTOMER VALUES ('104','Goff','Ryan','164A South Bend Rd.','Lowell','MA','01854','781-555-8423'); INSERT INTO CUSTOMER VALUES ('105','McLean','Kyle','345 Lower Ave.','Wolcott','NY','14590','585-555-5321'); INSERT INTO CUSTOMER VALUES ('106','Morontoia','Joseph','156 Scholar St.','Johnston','RI','02919','401-555- 4848'); INSERT INTO CUSTOMER VALUES ('107','Marchand','Quinn','76 Cross Rd.','Bath','NH','03740','603-555-0456'); INSERT INTO CUSTOMER VALUES ('108','Rulf','Uschi','32 Sheep Stop St.','Edinboro','PA','16412','814-555-5521'); INSERT INTO CUSTOMER VALUES ('109','Caron','Jean Luc','10 Greenfield St.','Rome','ME','04963','207-555-9643'); INSERT INTO CUSTOMER VALUES ('110','Bers','Martha','65 Granite St.','York','NY','14592','585-555-0111'); INSERT INTO CUSTOMER VALUES ('112','Jones','Laura','373 Highland Ave.','Somerville','MA','02143','857-555- 6258'); INSERT INTO CUSTOMER VALUES ('115','Vaccari','Adam','1282 Ocean Walk','Ocean CITY','NJ','08226','609-555- 5231'); INSERT INTO CUSTOMER VALUES ('116','Murakami','Iris','7 Cherry Blossom St.','Weymouth','MA','02188','617-555- 6665'); INSERT INTO CUSTOMER VALUES ('119','Chau','Clement','18 Ark Ledge Ln.','Londonderry','VT','05148','802-555- 3096'); INSERT INTO CUSTOMER VALUES ('120','Gernowski','Sadie','24 Stump Rd.','Athens','ME','04912','207-555-4507'); INSERT INTO CUSTOMER VALUES ('121','Bretton-Borak','Siam','10 Old Main St.','Cambridge','VT','05444','802-555- 3443'); INSERT INTO CUSTOMER VALUES ('122','Hefferson','Orlagh','132 South St. Apt 27','Manchester','NH','03101','603- 555-3476'); INSERT INTO CUSTOMER VALUES ('123','Barnett','Larry','25 Stag Rd.','Fairfield','CT','06824','860-555-9876'); INSERT INTO CUSTOMER VALUES ('124','Busa','Karen','12 Foster St.','South Windsor','CT','06074','857-555-5532'); INSERT INTO CUSTOMER VALUES ('125','Peterson','Becca','51 Fredrick St.','Albion','NY','14411','585-555-0900'); INSERT INTO CUSTOMER VALUES ('126','Brown','Brianne','154 Central St.','Vernon','CT','06066','860-555-3234'); INSERT INTO RESERVATION VALUES ('1600001',40,'3-26-2016',2,55.00,0.00,'101'); INSERT INTO RESERVATION VALUES ('1600002',21,'6-8-2016',2,95.00,0.00,'101'); INSERT INTO RESERVATION VALUES ('1600003',28,'9-12-2016',1,35.00,0.00,'103'); INSERT INTO RESERVATION VALUES ('1600004',26,'10-16-2016',4,45.00,15.00,'104'); INSERT INTO RESERVATION VALUES ('1600005',39,'6-25-2016',5,55.00,0.00,'105'); INSERT INTO RESERVATION VALUES ('1600006',32,'6-18-2016',1,80.00,20.00,'106'); INSERT INTO RESERVATION VALUES ('1600007',22,'7-9-2016',8,75.00,10.00,'107'); INSERT INTO RESERVATION VALUES ('1600008',28,'9-12-2016',2,35.00,0.00,'108'); INSERT INTO RESERVATION VALUES ('1600009',38,'9-11-2016',2,90.00,40.00,'109'); INSERT INTO RESERVATION VALUES ('1600010',2,'5-14-2016',3,25.00,0.00,'102'); INSERT INTO RESERVATION VALUES ('1600011',3,'9-15-2016',3,25.00,0.00,'102'); INSERT INTO RESERVATION VALUES ('1600012',1,'6-12-2016',4,15.00,0.00,'115'); INSERT INTO RESERVATION VALUES ('1600013',8,'7-9-2016',1,20.00,5.00,'116'); INSERT INTO RESERVATION VALUES ('1600014',12,'10-1-2016',2,40.00,5.00,'119'); INSERT INTO RESERVATION VALUES ('1600015',10,'7-23-2016',1,20.00,0.00,'120'); INSERT INTO RESERVATION VALUES ('1600016',11,'7-23-2016',6,75.00,15.00,'121'); INSERT INTO RESERVATION VALUES ('1600017',39,'6-18-2016',3,20.00,5.00,'122'); INSERT INTO RESERVATION VALUES ('1600018',38,'9-18-2016',4,85.00,15.00,'126'); INSERT INTO RESERVATION VALUES ('1600019',25,'8-29-2016',2,110.00,25.00,'124'); INSERT INTO RESERVATION VALUES ('1600020',28,'8-27-2016',2,35.00,10.00,'124'); INSERT INTO RESERVATION VALUES ('1600021',32,'6-11-2016',3,90.00,20.00,'112'); INSERT INTO RESERVATION VALUES ('1600022',21,'6-8-2016',1,95.00,25.00,'119'); INSERT INTO RESERVATION VALUES ('1600024',38,'9-11-2016',1,70.00,30.00,'121'); INSERT INTO RESERVATION VALUES ('1600025',38,'9-11-2016',2,70.00,45.00,'125'); INSERT INTO RESERVATION VALUES ('1600026',12,'10-1-2016',2,40.00,0.00,'126'); INSERT INTO RESERVATION VALUES ('1600029',4,'9-19-2016',4,105.00,25.00,'120'); INSERT INTO RESERVATION VALUES ('1600030',15,'7-25-2016',6,60.00,15.00,'104'); INSERT INTO TRIP VALUES (1,'Arethusa Falls ','Harts Location','NH',5,10,'Hiking','Summer'); INSERT INTO TRIP VALUES (2,'Mt Ascutney - North Peak','Weathersfield','VT',5,6,'Hiking','Late Spring'); INSERT INTO TRIP VALUES (3,'Mt Ascutney - West Peak','Weathersfield','VT',6,10,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (4,'Bradbury Mountain Ride','Lewiston-Auburn','ME',25,8,'Biking','Early Fall'); INSERT INTO TRIP VALUES (5,'Baldpate Mountain ','North Newry','ME',6,10,'Hiking','Late Spring'); INSERT INTO TRIP VALUES (6,'Blueberry Mountain','Batchelders Grant','ME',8,8,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (7,'Bloomfield - Maidstone','Bloomfield','CT',10,6,'Paddling','Late Spring'); INSERT INTO TRIP VALUES (8,'Black Pond','Lincoln','NH',8,12,'Hiking','Summer'); INSERT INTO TRIP VALUES (9,'Big Rock Cave','Tamworth','NH',6,10,'Hiking','Summer'); INSERT INTO TRIP VALUES (10,'Mt. Cardigan - Firescrew','Orange','NH',7,8,'Hiking','Summer'); INSERT INTO TRIP VALUES (11,'Chocorua Lake Tour','Tamworth','NH',12,15,'Paddling','Summer'); INSERT INTO TRIP VALUES (12,'Cadillac Mountain Ride','Bar Harbor','ME',8,16,'Biking','Early Fall'); INSERT INTO TRIP VALUES (13,'Cadillac Mountain','Bar Harbor','ME',7,8,'Hiking','Late Spring'); INSERT INTO TRIP VALUES (14,'Cannon Mtn','Franconia','NH',6,6,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (15,'Crawford Path Presidentials Hike','Crawford Notch','NH',16,4,'Hiking','Summer'); INSERT INTO TRIP VALUES (16,'Cherry Pond','Whitefield','NH',6,16,'Hiking','Spring'); INSERT INTO TRIP VALUES (17,'Huguenot Head Hike','Bar Harbor','ME',5,10,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (18,'Low Bald Spot Hike','Pinkam Notch','NH',8,6,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (19,'Mason''s Farm ','North Stratford','CT',12,7,'Paddling','Late Spring'); INSERT INTO TRIP VALUES (20,'Lake Mephremagog Tour','Newport','VT',8,15,'Paddling','Late Spring'); INSERT INTO TRIP VALUES (21,'Long Pond','Rutland','MA',8,12,'Hiking','Summer'); INSERT INTO TRIP VALUES (22,'Long Pond Tour','Greenville','ME',12,10,'Paddling','Summer'); INSERT INTO TRIP VALUES (23,'Lower Pond Tour','Poland','ME',8,15,'Paddling','Late Spring'); INSERT INTO TRIP VALUES (24,'Mt Adams ','Randolph','NH',9,6,'Hiking','Summer'); INSERT INTO TRIP VALUES (25,'Mount Battie Ride','Camden','ME',20,8,'Biking','Early Fall'); INSERT INTO TRIP VALUES (26,'Mount Cardigan Hike','Cardigan','NH',4,16,'Hiking','Late Fall'); INSERT INTO TRIP VALUES (27,'Mt. Chocorua','Albany','NH',6,10,'Hiking','Spring'); INSERT INTO TRIP VALUES (28,'Mount Garfield Hike','Woodstock','NH',5,10,'Hiking','Early Fall'); INSERT INTO TRIP VALUES (29,'Metacomet-Monadnock Trail Hike','Pelham','MA',10,12,'Hiking','Late Spring'); INSERT INTO TRIP VALUES (30,'McLennan Reservation Hike','Tyringham','MA',6,16,'Hiking','Summer'); INSERT INTO TRIP VALUES (31,'Missisquoi River - VT','Lowell','VT',12,10,'Paddling','Summer'); INSERT INTO TRIP VALUES (32,'Northern Forest Canoe Trail','Stark','NH',15,10,'Paddling','Summer'); INSERT INTO TRIP VALUES (33,'Park Loop Ride','Mount Desert Island','ME',27,8,'Biking','Late Spring'); INSERT INTO TRIP VALUES (34,'Pontook Reservoir Tour','Dummer','NH',15,14,'Paddling','Late Spring'); INSERT INTO TRIP VALUES (35,'Pisgah STATE Park Ride','Northborough','NH',12,10,'Biking','Summer'); INSERT INTO TRIP VALUES (36,'Pondicherry Trail Ride','White Mountains','NH',15,16,'Biking','Late Spring'); INSERT INTO TRIP VALUES (37,'Seal Beach Harbor','Bar Harbor','ME',5,16,'Hiking','Early Spring'); INSERT INTO TRIP VALUES (38,'Sawyer River Ride','Mount Carrigain','NH',10,18,'Biking','Early Fall'); INSERT INTO TRIP VALUES (39,'Welch and Dickey Mountains Hike','Thorton','NH',5,10,'Hiking','Summer'); INSERT INTO TRIP VALUES (40,'Wachusett Mountain','Princeton','MA',8,8,'Hiking','Early Spring'); INSERT INTO TRIP VALUES (41,'Westfield River Loop','Fort Fairfield','ME',20,10,'Biking','Late Spring'); INSERT INTO TRIP_GUIDES VALUES (1,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (1,'RH01', '7-2-2014'); INSERT INTO TRIP_GUIDES VALUES (2,'AM01', '7-3-2012'); INSERT INTO TRIP_GUIDES VALUES (2,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (3,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (4,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (4,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (5,'KS01', '5-8-2013'); INSERT INTO TRIP_GUIDES VALUES (5,'UG01', '3-2-2015'); INSERT INTO TRIP_GUIDES VALUES (6,'RH01', '7-2-2014'); INSERT INTO TRIP_GUIDES VALUES (7,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (8,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (9,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (10,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (11,'DH01', '2-8-2012'); INSERT INTO TRIP_GUIDES VALUES (11,'KS01', '5-8-2013'); INSERT INTO TRIP_GUIDES VALUES (11,'UG01', '3-2-2015'); INSERT INTO TRIP_GUIDES VALUES (12,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (13,'RH01', '7-2-2014'); INSERT INTO TRIP_GUIDES VALUES (14,'KS02', '7-10-2013'); INSERT INTO TRIP_GUIDES VALUES (15,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (16,'KS02', '7-10-2013'); INSERT INTO TRIP_GUIDES VALUES (17,'RH01', '7-2-2014'); INSERT INTO TRIP_GUIDES VALUES (18,'KS02', '7-10-2013'); INSERT INTO TRIP_GUIDES VALUES (19,'DH01', '2-8-2012'); INSERT INTO TRIP_GUIDES VALUES (20,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (21,'AM01', '7-3-2012'); INSERT INTO TRIP_GUIDES VALUES (22,'UG01', '3-2-2015'); INSERT INTO TRIP_GUIDES VALUES (23,'DH01', '2-8-2012'); INSERT INTO TRIP_GUIDES VALUES (23,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (24,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (25,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (26,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (27,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (28,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (29,'DH01', '2-8-2012'); INSERT INTO TRIP_GUIDES VALUES (30,'AM01', '7-3-2012'); INSERT INTO TRIP_GUIDES VALUES (31,'SL01', '10-5-2014'); INSERT INTO TRIP_GUIDES VALUES (32,'KS01', '5-8-2013'); INSERT INTO TRIP_GUIDES VALUES (33,'UG01', '3-2-2015'); INSERT INTO TRIP_GUIDES VALUES (34,'KS01', '5-8-2013'); INSERT INTO TRIP_GUIDES VALUES (35,'GZ01', '12-4-2012'); INSERT INTO TRIP_GUIDES VALUES (36,'KS02', '7-10-2013'); INSERT INTO TRIP_GUIDES VALUES (37,'RH01', '7-2-2014'); INSERT INTO TRIP_GUIDES VALUES (38,'KS02', '7-10-2013'); INSERT INTO TRIP_GUIDES VALUES (39,'BR01', '4-4-2012'); INSERT INTO TRIP_GUIDES VALUES (40,'DH01', '2-8-2012'); INSERT INTO TRIP_GUIDES VALUES (41,'BR01', '4-4-2012');

1. Use Exists to find reservation ID, trip ID, and trip date for reservations for a trip in New Hampshire (NH). 2. Find the guide last name and guide first name of all guides who can lead a paddling trip. 3. Find the trip ID and trip name for each trip whose maximum group size is greater than the maximum group size of every trip that has the type Hiking. 4. Find the trip ID and trip name for each trip whose maximum group size is greater than the maximum group size of at least one trip that has the type Biking. 5. Calculate the total revenue for each trip. Total revenue is calculated as number of persons times trip prices plus other fees. 6. Use the formula in question 5 to find total revenue and average revenue for each guide. 7. Who are the customers participated in a summer trip with a guide Called Zach Gregory