Question

Create a view named ReservationCustomer_VW. It consists of the reservation ID, trip ID, trip name, trip date, customer number, customer last name, customer first name, and phone number for trips whose guide is Glory Unser or Susan Kiley. Show the SQL Server code for this view.

Answer 1

Assuming Trip table has all the data then SQL query to create view would be as below:

Create View ReservationCustomer_VW as

Select reservationID, tripId, tripname, tripdate, customernumber, customerlastname, customerfirstname, phonenumber from trips

where guide in ('Glory Unser','Susan Kiley');

e.g. Data in Trip table

Run the command:

Verify that view is created as below:

If your customer table is separated from trip and customer table contains the trip Id from the Trip table your query to create view would be as follow:

Create View ReservationCustomer_VW as

Select a.reservationID, a.tripId, a.tripname, a.tripdate,

b.customernumber, b.customerlastname, b.customerfirstname, b.phonenumbers from trips a

join [dbo].[TripCustomers] b on a.tripid = b.tripid

where guide in ('Glory Unser','Susan Kiley');

Trip table in this scenario:

Trip customer table named as TripCustomer:

Run the create view command:

Below is the data from newly created view

SELECT * FROM ReservationCustomer_VW

Please rate your answer Thanks.