Page

of 9

ZOOM

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');

Use the Colonial database to answer the following questions (5 points per question): 1. For each reservation, list the reservation ID, trip ID, customer number, and customer last name. Order the results by customer last name. 2. List the trip name of each trip that has Miles Abrams as a guide. 3. List the trip name of each trip that has the type Biking and that has Rita Boyers as a guide. 4. For each reservation that has a trip date of July 23, 2016, list the customer's last name, the trip name, and the start location. 5. How many trips do we have for each customer? 6. What is the total number of customers for each guide? 7. Which states did Karen Busa visit? 8. Find the trip names of all reservations for hiking trips and sort the results by trip name in ascending order