use the colonial tours page to write SQL commands and take a screenshot of results and copy and paste your SQL commands on paper
MSAccess - Assignment 4 – Chapter 7 (90 pts) Instructions: Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question. The Entity Relationship Diagram (ERD) for the Colonial Adventure Tours database is shown below. You need this diagram to know the table and attribute names. Take advantage of the “Hints” that accompany each question. 1.Create a query named MAINE_TRIPS. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in Maine (ME). MOD0119 a.(4 pts) Write and execute the query that lists the trip ID, trip name, start location,distance, maximum group size, type, and season for every trip located in Maine (ME). This is a simple SELECT statement using one table. After your SELECT statement returns correct results save your query as MAINE_TRIPS Put your SQL command and results here: SELECT TRIP.TRIP_ID, TRIP.TRIP_NAME, TRIP.START_LOCATION, TRIP.DISTANCE, TRIP.MAX_GRP_SIZE, TRIP.TYPE, TRIP.SEASON FROM TRIP WHERE (((TRIP.[STATE])='ME')); b.(5 pts) Using the MAINE_TRIPS query write and execute the command to retrieve the trip ID, trip name, and distance for every Biking trip. Put your SQL command and results here: SELECT TRIP_ID, TRIP_NAME, DISTANCE FROM MAINE_TRIPS WHERE TYPE = 'Biking'; c.(5 pts) Write and execute the query that the MSAccess DBMS actually executes. Put your SQL command and results here: 2.Create a query named RESERVATION_CUSTOMER that consists of the reservation ID, trip ID, trip date, customer number, customer last name, customer first name, and phone number. a.(4 pts) Write and execute the SELECT command to create the RESERVATION_CUSTOMER query. This is a multiple table query involving the RESERVATION table and the CUSTOMER table. After your SELECT statement returns correct results save your query as RESERVATION_CUSTOMER. Put your SQL command and results here: b.(5 pts) Using the RESERVATION_CUSTOMER query write and execute the command to retrieve the reservation ID, trip ID, trip date, and customer last name for every trip date equal to September 11, 2016. Put your SQL command and results here: c.(5 pts) Write and execute the query that the MSAccess DBMS actually executes. Put your SQL command and results here: 3.COMPLETE, but do not execute, the commands to grant the following privileges. The section “Security” in your eBook (Chapter 7, page 204) contains a number of related examples on the GRANT command. a.(4 pts) User Rodriquez must be able to retrieve data from the TRIP table. Complete the following SQL command: GRANT SELECT ON ???? TO ????; b.(4 pts) Users Gomez and Liston must be able to add new reservations and customers to the database. Complete the following two SQL commands: GRANT INSERT ON ???? TO ????, ????; GRANT INSERT ON ???? TO ????, ????; c.(4 pts) Users Andrews and Zimmer must be able to change the price of any trip. Complete the following SQL command: GRANT UPDATE (????) ON ???? TO ????, ????; d.(4 pts) All users must be able to retrieve the trip name, start location, distance and type for every trip. Complete the following SQL command: GRANT SELECT (????, ????, ????, ????) ON ???? TO ????; e.(4 pts) User Golden must be able to add and delete guides. Complete the following two SQL commands: GRANT INSERT ON ???? TO ????; GRANT DELETE ON ???? TO ????; f.(4 pts) User Andrews must be able to create an index for the TRIP table. Complete the following SQL command: GRANT INDEX ON ???? TO ????; g.(4 pts) Users Andrews and Golden must be able to change the structure of the CUSTOMER table. Complete the following SQL command: GRANT ALTER ON ???? TO ????, ????; h.(4 pts) User Golden must have all privileges on the TRIP, GUIDE, and TRIP_GUIDES tables. Complete the following three SQL commands: GRANT ALL ON ???? TO ????; GRANT ALL ON ???? TO ????; GRANT ALL ON ???? TO ????; 4.(3 pts) COMPLETE, but do not execute, the command to revoke all privileges from user Andrews. Complete the following SQL command: REVOKE ALL PRIVILEGES FROM ????; 5.COMPLETE, but do not execute, the commands to create the following indexes. The section “Security” in your eBook (Chapter 7, page 209) contains a number of related examples on the INDEX command. a.(3 pts) Create an index named TRIP_INDEX1 on the TRIP_NAME column in the TRIP table. Complete the following SQL command: CREATE INDEX ???? ON TRIP(????); b.(3 pts) Create an index named TRIP_INDEX2 on the TYPE column in the TRIP table. Complete the following SQL command: CREATE INDEX ???? ON TRIP(????); c.(3 pts) Create an index named TRIP_INDEX3 on the LAST_NAME and FIRST_NAME columns in the CUSTOMER table. Complete the following SQL command: CREATE INDEX ???? ON ????(????, ????); 6.(3 pts) COMPLETE, but do not execute, the commands to delete the index named BOOK_INDEX3. Complete the following SQL command: DROP INDEX ????; 7.COMPLETE, but do not execute, the commands to obtain the following information from the system catalog. The section “System Catalog” in your eBook (Chapter 7, page 212) contains a number of related examples on the SQL commands that obtain information from the system catalog. Please note that all SQL examples in the this section of the eBook obtain information from “DBA” tables. In all of the following questions “DBA” has been changed to “ALL” in order to allow your SQL commands to execute properly. a.(3 pts) List every column (i.e., COLUMN_NAME) in the GUIDE table and its associated data type (i.e., DATA_TYPE) . Put your SQL command here: Do NOT run this SQL command. b.(3 pts) List every table (i.e. TABLE_NAME) that contains a column (i.e., COLUMN_NAME) that is named TRIP_ID. Put your SQL command here: Do NOT run this SQL command. c.(3 pts) List the table name, column name, and data type for the columns named TRIP_ID and TRIP_NAME. Order the results by table name within column name. (That is, column name is the major sort key and table name is the minor sort key.) Put your SQL command here: Do NOT run this SQL command. 8.(3 pts) COMPLETE, but do not execute, the following ALTER command that adds the CUSTOMER_NUM column as a foreign key in the RESERVATION table. Complete the following SQL command: ALTER TABLE RESERVATION ADD FOREIGN KEY (CUSTOMER_NUM) REFERENCES CUSTOMER(????????_???); 9.(3 pts) COMPLETE, but do not execute, the following ALTER command that ensures that the TYPE column in the TRIP table can accept only values of Biking, Hiking or Paddling. Complete the following SQL command: ALTER TABLE ???? ADD CHECK (TYPE IN ('??????', '??????', '????????')); MSAccess - Assignment 4 – Chapter 8 (30 pts) Instructions: Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question. 1.(10 pts) List the guide number, first name, and last name for all guides. The first name should appear in lowercase letters and the last name should appear in uppercase letters. NOTE: MSAccess uses the functions LCASE and UCASE whereas Oracle uses LOWER and UPPER. MOD0119 Put your SQL command and results here: 2.(10 pts) Every guide receives their first review 6 months after their hire date. List the guide’s last name, first name, and hire date and his/her first review date. Use REVIEW_DATE as the column title. NOTE: MSAccess uses the function DATEADD whereas Oracle uses the function ADD_MONTHS. Put your SQL command and results here: 3.(10 pts) Repeat the previous question but display the guides’ names as first name followed by last name (for example, Rita Boyers). Use NAME as the column title for the guides’ names. NOTE: MSAccess uses the ‘&’ symbol instead of the two vertical bars (‘||’) that Oracle uses. Put your SQL command and results here: Colonial Adventure Tours CUSTOMER_NUMLAST_NAMEFIRST_NAMEADDRESSCITYSTATEPOSTAL_CODEPHONE 101NorthfoldLiam9 Old Mill Rd.LondonderryNH03053603-555-7563 102OceanArnold2332 South St. Apt 3SpringfieldMA01101413-555-3212 103KasumaSujata132 Main St. #1East HartfordCT06108860-555-0703 104GoffRyan164A South Bend Rd.LowellMA01854781-555-8423 105McLeanKyle345 Lower Ave.WolcottNY14590585-555-5321 106MorontoiaJoseph156 Scholar St.JohnstonRI02919401-555-4848 107MarchandQuinn76 Cross Rd.BathNH03740603-555-0456 108RulfUschi32 Sheep Stop St.EdinboroPA16412814-555-5521 109CaronJean Luc10 Greenfield St.RomeME04963207-555-9643 110BersMartha65 Granite St.YorkNY14592585-555-0111 112JonesLaura373 Highland Ave.SomervilleMA02143857-555-6258 115VaccariAdam1282 Ocean WalkOcean CityNJ08226609-555-5231 116MurakamiIris7 Cherry Blossom St.WeymouthMA02188617-555-6665 119ChauClement18 Ark Ledge Ln.LondonderryVT05148802-555-3096 120GernowskiSadie24 Stump Rd.AthensME04912207-555-4507 121Bretton-BorakSiam10 Old Main St.CambridgeVT05444802-555-3443 122HeffersonOrlagh132 South St. Apt 27ManchesterNH03101603-555-3476 123BarnettLarry25 Stag Rd.FairfieldCT06824860-555-9876 124BusaKaren12 Foster St.South WindsorCT06074857-555-5532 125PetersonBecca51 Fredrick St.AlbionNY14411585-555-0900 126BrownBrianne154 Central St.VernonCT06066860-555-3234 GUIDE_NUMLAST_NAMEFIRST_NAMEADDRESSCITYSTATEPOSTAL_CODEPHONE_NUMHIRE_DATE AM01AbramsMiles54 Quest Ave.WilliamsburgMA01096617-555-60326/3/12 BR01BoyersRita140 Oakton Rd.JaffreyNH03452603-555-21343/4/12 DH01DevonHarley25 Old Ranch Rd.SunderlandMA01375781-555-77671/8/12 GZ01GregoryZach7 Moose Head Rd.DummerNH03588603-555-876511/4/12 KS01KileySusan943 Oakton Rd.JaffreyNH03452603-555-12304/8/13 KS02KellySam9 Congaree Ave.FraconiaNH03580603-555-00036/10/13 MR01MarstonRay24 Shenandoah Rd.SpringfieldMA01101781-555-23239/14/15 RH01RowanHal12 Heather Rd.Mount DesertME04660207-555-90096/2/14 SL01StevensLori15 Riverton Rd.CoventryVT05825802-555-33399/5/14 UG01UnserGlory342 Pineview St.DanburyCT06810203-555-85342/2/15 RESERVATION_IDTRIP_IDTRIP_DATENUM_PERSONSTRIP_PRICEOTHER_FEESCUSTOMER_NUM 1600001403/26/162¤ 55.00¤ 0.00101 1600002216/8/162¤ 95.00¤ 0.00101 1600003289/12/161¤ 35.00¤ 0.00103 16000042610/16/164¤ 45.00¤ 15.00104 1600005396/25/165¤ 55.00¤ 0.00105 1600006326/18/161¤ 80.00¤ 20.00106 1600007227/9/168¤ 75.00¤ 10.00107 1600008289/12/162¤ 35.00¤ 0.00108 1600009389/11/162¤ 90.00¤ 40.00109 160001025/14/163¤ 25.00¤ 0.00102 160001139/15/163¤ 25.00¤ 0.00102 160001216/12/164¤ 15.00¤ 0.00115 160001387/9/161¤ 20.00¤ 5.00116 16000141210/1/162¤ 40.00¤ 5.00119 1600015107/23/161¤ 20.00¤ 0.00120 1600016117/23/166¤ 75.00¤ 15.00121 1600017396/18/163¤ 20.00¤ 5.00122 1600018389/18/164¤ 85.00¤ 15.00126 1600019258/29/162¤ 110.00¤ 25.00124 1600020288/27/162¤ 35.00¤ 10.00124 1600021326/11/163¤ 90.00¤ 20.00112 1600022216/8/161¤ 95.00¤ 25.00119 1600024389/11/161¤ 70.00¤ 30.00121 1600025389/11/162¤ 70.00¤ 45.00125 16000261210/1/162¤ 40.00¤ 0.00126 160002949/19/164¤ 105.00¤ 25.00120 1600030157/25/166¤ 60.00¤ 15.00104 TRIP_IDTRIP_NAMESTART_LOCATIONSTATEDISTANCEMAX_GRP_SIZETYPESEASON 1Arethusa Falls Harts LocationNH510HikingSummer 2Mt Ascutney - North PeakWeathersfieldVT56HikingLate Spring 3Mt Ascutney - West PeakWeathersfieldVT610HikingEarly Fall 4Bradbury Mountain RideLewiston-AuburnME258BikingEarly Fall 5Baldpate Mountain North NewryME610HikingLate Spring 6Blueberry MountainBatchelders GrantME88HikingEarly Fall 7Bloomfield - MaidstoneBloomfieldCT106PaddlingLate Spring 8Black PondLincolnNH812HikingSummer 9Big Rock CaveTamworthNH610HikingSummer 10Mt. Cardigan - FirescrewOrangeNH78HikingSummer 11Chocorua Lake TourTamworthNH1215PaddlingSummer 12Cadillac Mountain RideBar HarborME816BikingEarly Fall 13Cadillac MountainBar HarborME78HikingLate Spring 14Cannon MtnFranconiaNH66HikingEarly Fall 15Crawford Path Presidentials HikeCrawford NotchNH164HikingSummer 16Cherry PondWhitefieldNH616HikingSpring 17Huguenot Head HikeBar HarborME510HikingEarly Fall 18Low Bald Spot HikePinkam NotchNH86HikingEarly Fall 19Mason’s Farm North StratfordCT127PaddlingLate Spring 20Lake Mephremagog TourNewportVT815PaddlingLate Spring 21Long PondRutlandMA812HikingSummer 22Long Pond TourGreenvilleME1210PaddlingSummer 23Lower Pond TourPolandME815PaddlingLate Spring 24Mt Adams RandolphNH96HikingSummer 25Mount Battie RideCamdenME208BikingEarly Fall 26Mount Cardigan HikeCardiganNH416HikingLate Fall 27Mt. ChocoruaAlbanyNH610HikingSpring 28Mount Garfield HikeWoodstockNH510HikingEarly Fall 29Metacomet-Monadnock Trail HikePelhamMA1012HikingLate Spring 30McLennan Reservation HikeTyringhamMA616HikingSummer 31Missisquoi River - VTLowellVT1210PaddlingSummer 32Northern Forest Canoe TrailStarkNH1510PaddlingSummer 33Park Loop RideMount Desert IslandME278BikingLate Spring 34Pontook Reservoir TourDummerNH1514PaddlingLate Spring 35Pisgah State Park RideNorthboroughNH1210BikingSummer 36Pondicherry Trail RideWhite MountainsNH1516BikingLate Spring 37Seal Beach HarborBar HarborME516HikingEarly Spring 38Sawyer River RideMount CarrigainNH1018BikingEarly Fall 39Welch and Dickey Mountains HikeThortonNH510HikingSummer 40Wachusett MountainPrincetonMA88HikingEarly Spring 41Westfield River LoopFort FairfieldME2010BikingLate Spring TRIP_IDGUIDE_NUM 1GZ01 1RH01 2AM01 2SL01 3SL01 4BR01 4GZ01 5KS01 5UG01 6RH01 7SL01 8BR01