WEEK4NEW.docx - Assignment 4 – Chapter 7(90 pts...

This preview shows page 1 - 4 out of 9 pages.

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. 1. Create a view 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). a. (4 pts) Write and execute the CREATE VIEW command to create the MAINE_TRIPS view. Put your SQL command and results here: CREATE VIEW MAINE_TRIPS AS SELECT TRIP_ID, TRIP_NAME, START_LOCATION, DISTANCE, MAX_GRP_SIZE, TYPE, SEASON FROM TRIP WHERE STATE = 'ME'; b. (4 pts) 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
Image of page 1

Subscribe to view the full document.

FROM MAINE_TRIPS WHERE TYPE = 'Biking'; c. (4 pts) Write and execute the query that the DBMS actually executes. Put your SQL command and results here: SELECT TRIP_ID, TRIP_NAME, DISTANCE FROM TRIP WHERE STATE ='ME' AND TYPE = 'Biking'; 2. Create a view named RESERVATION_CUSTOMER. It 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 CREATE VIEW command to create the RESERVATION_CUSTOMER view. Put your SQL command and results here: CREATE VIEW RESERVATION_CUSTOMER AS SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE, RESERVATION.CUSTOMER_NUM, LAST_NAME, FIRST_NAME, PHONE FROM RESERVATION, CUSTOMER WHERE RESERVATION.CUSTOMER_NUM-CUSTOMER.CUSTOMER_NUM;
Image of page 2
b. (4 pts) Write and execute the command to retrieve the reservation ID, trip ID, trip date, and customer last name for every reservation in the RESERVATION_CUSTOMER view 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 = '2016/09/11' c. (4 pts) Write and execute the query that the DBMS actually executes.
Image of page 3

Subscribe to view the full document.

Image of page 4
  • Fall '18
  • Mrs. Kim

{[ snackBarMessage ]}

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