I need the SQL statements for these. If you aren\'t sure of your answers its oka

Question

I need the SQL statements for these. If you aren't sure of your answers its okay, they dont have to be 100% right. I would just like them all answered, and as correct as possible. Thank you.

tables for #6

table for #7:

6 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. a) Write what the actual VIEW command would be. b) Write 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 c) Write the query that the DBMS actually executes 7) Define a view named TRIP INVENTORY. It consists of the state and the total number of trips for each state. Use Units as the column name for the total number of trips for each state. Group and Order the rows by state a Write what the actual VIEW command would be. b) Execute the SELECT portion of the VIEW command. c) Write the command to retrieve the state and units for each state having more than 10 trips. d Write the query that the DBMS actually executes.

Explanation / Answer

6) This is the view command

CREATE VIEW [RESERVATION_CUSTOMER] AS
SELECT RESERVATION.RESERVATION_ID, RESERVATION.TRIP_ID, RESERVATION.TRIP_DATE, CUSTOMER.CUSTOMER_NUM, CUSTOMER.LAST_NAME, CUSTOMER.FIRST_NAME
FROM RESERVATION, CUSTOMER
(WHERE RESERVATION.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM)

Below is the query to retrieve the values from view

SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE, LAST_NAME FROM RESERVATION_CUSTOMER WHERE TRIP_DATE = '9/11/2016'

7)

This is the view command

CREATE VIEW [TRIP_INVENTORY] AS
SELECT STATE, COUNT(STATE) AS UNITS
FROM TRIP
GROUP BY STATE
ORDER BY STATE

This is the select command from the view

SELECT STATE, COUNT(STATE) AS UNITS
FROM TRIP
GROUP BY STATE
ORDER BY STATE

Command to retrieve trips greater than 10

SELECT * FROM TRIP_INVENTORY WHERE UNITS > 10

NOTE: Please use the exact table names because that information is not available int he question. I have highlighted the table names I have used.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---