MSAccess - Assignment 4 - Chapter 7 Worksheet 2.docx

Put your sql command and results here create view

This preview shows page 3 - 7 out of 8 pages.

Put your SQL command and results here:
Image of page 3

Subscribe to view the full document.

CREATE VIEW RESERVATION_CUSTOMER AS SELECT RESERVATION.RESERVATION_ID AS "RESERVATION_ID",RESERVATION.TRIP_ID AS "TRIP_ID",RESERVATION.TRIP_DATE AS "TRIP_DATE",RESERVATION.CUSTOMER_NUM,CUSTOMER.LAST_NAME AS "LAST_NAME",CUSTOMER.FIRST_NAME,CUSTOMER.PHONE FROM RESERVATION JOIN CUSTOMER ON CUSTOMER.CUSTOMER_NUM = RESERVATION.CUSTOMER_NUM ; 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 with a trip date of September 11, 2016. Put your SQL command and results here: SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE ,LAST_NAME FROM RESERVATION_CUSTOMER WHERE TRIP_DATE = '9-11-2016'; c. (5 pts) Write and execute the query that the MSAccess DBMS actually executes.
Image of page 4
Put your SQL command and results here: SELECT RESERVATION.RESERVATION_ID, RESERVATION.TRIP_ID, RESERVATION.TRIP_DATE ,CUSTOMER.LAST_NAME FROM CUSTOMER, RESERVATION WHERE RESERVATION.TRIP_DATE = '9-11-2016'; 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 TRIP TO RODRIQUEZ; 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 RESERVATION TO GOMEZ ,LISTON; GRANT INSERT ON CUSTOMER TO GOMEZ , LISION; c. (4 pts) Users Andrews and Zimmer must be able to change the price of any trip. Complete the following SQL command:
Image of page 5

Subscribe to view the full document.

GRANT UPDATE (PRIZE) ON RESERVATION TO ANDREWS,ZIMMER; 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 (TRIP_NAME,START_LOCATION,DISTANCE,TYPE) ON TRIP TO PUBLIC; e. (4 pts) User Golden must be able to add and delete guides. Complete the following two
Image of page 6
Image of page 7
  • Fall '18
  • Wanda Burwick

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern