1. Create the following 5 tables using CREATE TABLE statements and make a spools
ID: 3816240 • Letter: 1
Question
1. Create the following 5 tables using CREATE TABLE statements and make a spoolscript in a file named colonial.sql
GUIDE(Guide_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone_Num, Hire_Date)
Primary Key: Guide_Num
CUSTOMERS(Customer_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone)
Primary Key: Customer_Num
TRIP(Trip_Id, Trip_Name, Start_Location, State, Distance, Max_Grp_Size, Type, Season)
Primary Key: Trip_Id
RESERVATION(Reservation_Id, Trip_Id, Trip_Date, Num_Persons, Trip_Price, Other_Fees, Customer_Num)
Primary Key: Reservation_Id (also a Surrogate Key)
Alternate Key: Trip_Id + Trip_Date + Customer_Num
Foreign Key: Trip_Id References TRIP
Foreign Key: Customer_Num References CUSTOMERS
TRIP_GUIDES(Trip_Id, Guide_Num)
Primary Key: Trip_Id + Guide_Num
Foreign Key: Trip_Id References TRIP
Foreign Key: Guide_Num References GUIDE
2. Create a spool script named colonialinput.sql which uses INSERT INTO commands to insert the following data to the 5 tables:
GUIDE CITY STATE POSTAL CODE PHONE NUM HIRE DATE GUIDE NUM LAST NAME FIRST NAME ADDRESS Abrams Williamsburg MA 617-555-6032 6/3/2012 AM01 54 Quest Ave 01096 140 Oakton Rd. Jaffrey BR01 AV4/2012 Devon Harley 25 Old Ranch Rd. Sunderland MA 01375 781-555-7767 1/s/2012 DH01 Gzol Gregory Zach 7 Moose Head Rd. Dummer NH 035ss 603-555-8765 11/4/2012 KS01 Ekiley Susan 943 Oakton Rd. Jaffrey NII 452 4/8/2013 603-555-1230 9 Congaree Ave. Fraconia. 603-555-0003 6/10/2013 Ray 24 Shenandoah Rd. Springfield MA 01101 MR01 Marston 781-555-2323 9/14/2015 RI101 Rowan Ilal 04660 12 Heather Rd Mount Desert ME 207-555-9009 6/2/2014 Lori 15 Riverton Rd Coventry DVT S02-555-3339 SL01 Stevens 05825 9/5/2014 Unser Glory 342 Pineview St Danbury, CT 06s10 2/2/2015 UG01 203-555-8534Explanation / Answer
colonial.sql
--GUIDE(Guide_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone_Num, Hire_Date)
--Primary Key: Guide_Num
--Drop Table GUIDE
Create Table GUIDE
(
Guide_Num Varchar(5) Primary Key,
Last_Name Varchar(50),
First_Name Varchar(50),
Address Varchar(50),
City Varchar(50),
State Varchar(50),
Postal_Code Varchar(50),
Phone_Num Varchar(50),
Hire_Date DateTime
)
--CUSTOMERS(Customer_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone)
--Primary Key: Customer_Num
--Drop Table CUSTOMERS
Create Table CUSTOMERS
(
Customer_Num int Primary Key,
Last_Name Varchar(50),
First_Name Varchar(50),
Address Varchar(50),
City Varchar(50),
State Varchar(50),
Postal_Code Varchar(50),
Phone Varchar(50)
)
--TRIP(Trip_Id, Trip_Name, Start_Location, State, Distance, Max_Grp_Size, Type, Season)
--Primary Key: Trip_Id
--Drop Table Trip
Create Table Trip
(
Trip_Id int Primary Key,
Trip_Name Varchar(50),
Start_Location Varchar(50),
State Varchar(50),
Distance int,
Max_Grp_Size int,
Type Varchar(50),
Season Varchar(50)
)
--RESERVATION(Reservation_Id, Trip_Id, Trip_Date, Num_Persons, Trip_Price, Other_Fees, Customer_Num)
--Primary Key: Reservation_Id (also a Surrogate Key)
--Alternate Key: Trip_Id + Trip_Date + Customer_Num
--Foreign Key: Trip_Id References TRIP
--Foreign Key: Customer_Num References CUSTOMERS
--Drop Table RESERVATION
Create Table RESERVATION
(
Reservation_Id Varchar(50) Primary Key,
Trip_Id int foreign key references TRIP(Trip_Id),
Trip_Date DateTime,
Num_Persons int ,
Trip_Price int ,
Other_Fees int ,
Customer_Num int foreign key references CUSTOMERS(Customer_Num)
)
--TRIP_GUIDES(Trip_Id, Guide_Num)
--Primary Key: Trip_Id + Guide_Num
--Foreign Key: Trip_Id References TRIP
--Foreign Key: Guide_Num References GUIDE
--Drop Table TRIP_GUIDES
Create Table TRIP_GUIDES
(
Trip_Id int foreign key references TRIP(Trip_Id),
Guide_Num Varchar(5) foreign key references GUIDE(Guide_Num),
PRIMARY KEY (Trip_Id, Guide_Num)
)
colonialinput.sql
--Guide Table
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 Oakum 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','Ilal','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')
--Customer Table
Insert Into Customers Values(101,'NorthfoId','Liam','9 Old Mill Rd.','Londonderry','NH','03053','603-555-7563')
Insert Into Customers Values(102,'Ocean','Arnold','2332 South St. Apt 3','Springfield','MA','01101','413-555-3212')
Insert Into Customers Values(103,'Kasuma','Sujata','132 Main St. #1','East Hartford','CI','06108','860-555-0703')
Insert Into Customers Values(104,'Golf','Ryan','164A South Bend Rd.','Lowell','MA','01854','781-555-8423')
Insert Into Customers Values(105,'McLean','Kyle','345 Lower Ave.','Wolcott','NY','14590','585-555-5321')
Insert Into Customers Values(106,'Morontoia','Joseph','156 Scholar St.','Johnston','RI','02919','401-555-4848')
Insert Into Customers Values(107,'Marehand','Quinn','76 Cross ltd.','Bath','NH','03740','603-555-0456')
Insert Into Customers Values(108,'Rulf','Uschi','32 Sheep Stop St.','Edinboro','PA','16412','814-555-5521')
Insert Into Customers Values(109,'Caron','Jean Lue','10 Greenfield St.','Rome','ME','04963','207-555-9643')
Insert Into Customers Values(110,'Bers','Martha','65 Granite St.','York','NY','14592','585-555-0111')
Insert Into Customers Values(112,'Jones','Laura','373 Highland Ave.','Somerville','MA','02143','857-555-6258')
Insert Into Customers Values(115,'Vaccari','Adam','1282 Ocean Walk','Ocean City','NJ','08226','609-555-5231')
Insert Into Customers Values(116,'Murakami','Iris','7 Cherry Blossom St.','Weymouth','MA','02188','617-555-6665')
Insert Into Customers Values(119,'Chau','Clement','18 Ark Ledge Ln.','Londonderry','VT','05148','802-555-3096')
Insert Into Customers Values(120,'Gernowski','Sadie','24 Stump Rd.','Athens','ME','04912','207-555-4507')
Insert Into Customers Values(121,'Bretton-Borak','Siam','10 Old Main St.','Cambridge','VT','05444','802-555-3443')
Insert Into Customers Values(122,'Hefferson','Orlauh','132 Smith St. Apt 27','Manchester','NH','03101','603-555-3476')
Insert Into Customers Values(123,'Barnett','Larry','25 Stag Rd.','Fairfield','CT','06824','860-555-9876')
Insert Into Customers Values(124,'Busa','Karen','12 Foster St.','South Windsor','CT','06074','857-555-5532')
Insert Into Customers Values(125,'Peterson','Becca','51 Fredrick St.','Albion','NY','14411','585-555-0900')
Insert Into Customers Values(126,'Brown','Brianne','154 Central St.','Vernon','CT','06066','S60-555-3234')
--Trip Table
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','Weatlietiakld','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','NIE',6,10,'Hiking','Late Spring')
Insert Into Trip Values(6,'Blueberry Mountain','Batchelders Grant','ME',8,6,'Hiking','Early Fall')
Insert Into Trip Values(7,'Bloomfield - MaidNoine','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 Spnng')
Insert Into Trip Values(24,'Mt Adams','Randolph','NH',9,6,'Hiking','Sumner')
Insert Into Trip Values(25,'Mount Baltic 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')
--Reservation
Insert Into Reservation Values(1600001,40,'3/26/2016',2,55,0,101)
Insert Into Reservation Values(1600002,21,'6/8/2016',2,95,0,101)
Insert Into Reservation Values(1600003,28,'9/12/2016',1,35,0,103)
Insert Into Reservation Values(1600004,26,'10/16/2016',4,45,15,104)
Insert Into Reservation Values(1600005,39,'6/25/2016',5,55,0,105)
Insert Into Reservation Values(1600006,32,'6/18/2016',1,80,20,106)
Insert Into Reservation Values(1600007,22,'7/9/2016',8,75,10,107)
Insert Into Reservation Values(1600008,28,'9/12/2016',2,35,0,108)
Insert Into Reservation Values(1600009,38,'9/11/2016',2,90,40,109)
Insert Into Reservation Values(1600010,2,'5/14/2016',3,25,0,102)
Insert Into Reservation Values(1600011,3,'9/15/2016',3,25,0,102)
Insert Into Reservation Values(1600012,1,'6/12/2016',4,15,0,115)
Insert Into Reservation Values(1600013,8,'7/9/2016',1,20,5,116)
Insert Into Reservation Values(1600014,12,'10/1/2016',2,40,5,119)
Insert Into Reservation Values(1600015,10,'7/23/2016',1,20,0,120)
Insert Into Reservation Values(1600016,11,'7/23/2016',6,75,15,121)
Insert Into Reservation Values(1600017,39,'6/18/2016',3,20,5,122)
Insert Into Reservation Values(1600018,38,'9/18/2016',4,85,15,126)
Insert Into Reservation Values(1600019,25,'8/29/2016',2,110,25,124)
Insert Into Reservation Values(1600020,28,'8/27/2016',2,35,10,124)
Insert Into Reservation Values(1600021,32,'6/11/2016',3,90,20,112)
Insert Into Reservation Values(1600022,21,'6/8/2016',1,95,25,119)
Insert Into Reservation Values(1600024,38,'9/11/2016',1,70,30,121)
Insert Into Reservation Values(1600025,38,'9/11/2016',2,70,45,125)
Insert Into Reservation Values(1600026,12,'10/1/2016',2,40,0,126)
Insert Into Reservation Values(1600029,4,'9/19/2016',4,105,25,120)
Insert Into Reservation Values(1600030,15,'7/25/2016',6,60,15,104)
Insert Into TRIP_GUIDES Values(1,'GZ01')
Insert Into TRIP_GUIDES Values(1,'RH01')
Insert Into TRIP_GUIDES Values(2,'AM01')
Insert Into TRIP_GUIDES Values(2,'SL01')
Insert Into TRIP_GUIDES Values(3,'SL01')
Insert Into TRIP_GUIDES Values(4,'BR01')
Insert Into TRIP_GUIDES Values(4,'GZ01')
Insert Into TRIP_GUIDES Values(5,'KS01')
Insert Into TRIP_GUIDES Values(5,'UG01')
Insert Into TRIP_GUIDES Values(6,'RH01')
Insert Into TRIP_GUIDES Values(7,'SL01')
Insert Into TRIP_GUIDES Values(8,'BR01')
Insert Into TRIP_GUIDES Values(9,'BR01')
Insert Into TRIP_GUIDES Values(10,'GZ01')
Insert Into TRIP_GUIDES Values(11,'DH01')
Insert Into TRIP_GUIDES Values(11,'KS01')
Insert Into TRIP_GUIDES Values(11,'UG01')
Insert Into TRIP_GUIDES Values(12,'BR01')
Insert Into TRIP_GUIDES Values(13,'RH01')
Insert Into TRIP_GUIDES Values(14,'KS02')
Insert Into TRIP_GUIDES Values(15,'GZ01')
Insert Into TRIP_GUIDES Values(16,'KS02')
Insert Into TRIP_GUIDES Values(17,'RH01')
Insert Into TRIP_GUIDES Values(18,'KS02')
Insert Into TRIP_GUIDES Values(19,'DH01')
Insert Into TRIP_GUIDES Values(20,'SL01')
Insert Into TRIP_GUIDES Values(21,'AM01')
Insert Into TRIP_GUIDES Values(22,'UG01')
Insert Into TRIP_GUIDES Values(23,'DH01')
Insert Into TRIP_GUIDES Values(24,'SL01')
Insert Into TRIP_GUIDES Values(25,'BR01')
Insert Into TRIP_GUIDES Values(26,'BR01')
Insert Into TRIP_GUIDES Values(27,'GZ01')
Insert Into TRIP_GUIDES Values(28,'GZ01')
Insert Into TRIP_GUIDES Values(29,'BR01')
Insert Into TRIP_GUIDES Values(30,'DH01')
Insert Into TRIP_GUIDES Values(31,'AM01')
Insert Into TRIP_GUIDES Values(32,'SL01')
Insert Into TRIP_GUIDES Values(33,'KS01')
Insert Into TRIP_GUIDES Values(34,'UG01')
Insert Into TRIP_GUIDES Values(35,'KS01')
Insert Into TRIP_GUIDES Values(36,'GZ01')
Insert Into TRIP_GUIDES Values(37,'KS02')
Insert Into TRIP_GUIDES Values(38,'RH01')
Insert Into TRIP_GUIDES Values(39,'BR01')
Insert Into TRIP_GUIDES Values(40,'DH01')
Insert Into TRIP_GUIDES Values(41,'BR01')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.