Create a view named ReservationCustomer_VW. It consists of the reservation ID, trip ID, trip name, trip...

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.

Solutions

Expert Solution

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.

venereology answered 11 months ago

Next > < Previous

Related Solutions

Oracle - Create a procedure that accepts product ID as a parameter and returns the name...

Oracle - Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. Table to use: CREATE TABLE ProductTable( ProductID INTEGER NOT NULL primary key, ProductName VARCHAR(50) NOT NULL, ListPrice NUMBER(10,2), Category INTEGER NOT NULL ); / INSERT INTO ProductTable VALUES(299,'Chest',99.99,10); INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11); INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11); INSERT INTO...

Create a class named Salesperson. Data fields for Salesperson include an integer ID number and a...

Create a class named Salesperson. Data fields for Salesperson include an integer ID number and a doubleannual sales amount. Methods include a constructor that requires values for both data fields, as well as get and set methods for each of the data fields. Write an application named DemoSalesperson that declares an array of 10 Salesperson objects. Set each ID number to 9999 and each sales value to zero. Display the 10 Salesperson objects. public class DemoSalesperson { public static void...

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth...

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth Create a table ‘ClassInfo’ table: ID Class Name Class Description Create a table ‘RegisteredClasses’ table: StudentID ClassID The RegisteredClasses table should have a foreign key relationship to StudentInfo and ClassInfo tables for the respective IDs. Also the IDs in StudentInfo and ClassInfo need to be primary keys. When you submit the file your email should also contain the following SQL Queries: Query to show...

Create a view named customer_addresses that shows the shipping and billing addresses for each customer. This...

Create a view named customer_addresses that shows the shipping and billing addresses for each customer. This view should return these columns from the Customers table: customer_id, email_address, last_name and first_name. This view should return these columns from the Addresses table: bill_line1, bill_line2, bill_city, bill_state, bill_zip, ship_line1, ship_line2, ship_city, ship_state, and ship_zip. I have this CREATE VIEW customer_addresses AS SELECT customer_id, email_address, last_name, first_name, bill_line1, bill_line2, bill_city, bill_state, bill_zip, ship_line1, ship_line2, ship_city, ship_state, ship_zip FROM Customers, Addresses WHERE Customers.customer_id = Addresses.customer_id...

Create a class named Student. Student has fields for an ID number, number of credit hours...

Create a class named Student. Student has fields for an ID number, number of credit hours earned, and number of points earned. (For example, many schools compute grade point averages based on a scale of 4, so a three-credit-hour class in which a student earns an A is worth 12 points.) Include methods to assign values to all fields. Student also has a field for grade point average. Include a method to compute the grade point average field by dividing...

(SQL Coding) Create a view based on the Job History table. Name the view: view_job_history. Select...

(SQL Coding) Create a view based on the Job History table. Name the view: view_job_history. Select all columns to be included in the view. Add a WHERE clause to restrict the employee_id to be greater than 150. Add the WITH READ ONLY option. Show me the code used to create the view and the results of a select statement on the view.

THIS IS JAVA PROGRAMMING 1. Create a class named Name that contains the following: • A...

THIS IS JAVA PROGRAMMING 1. Create a class named Name that contains the following: • A private String to represent the first name. • A private String to represent the last name. • A public constructor that accepts two values and assigns them to the above properties. • Public methods named getProperty (e.g. getFirstName) to return the value of the property. • Public methods named setProperty ( e.g. setFirstName)to assign values to each property by using a single argument passed...

Product(p-id, p-name, weight) Retailer(r-id, r-name, city) Sells(r-id, p-id, price)

Product(p-id, p-name, weight)Retailer(r-id, r-name, city)Sells(r-id, p-id, price) r-id is a foreign key referencing Retailerp-id is a foreign key referencing Product Give a Relational Algebra expression : (IT IS NOT SQL) (a) Find the names of the retailers who are selling the products that have a weight greater than 10 kg.(b) Find the names of the retailers who have never sold a product that has a weight greater than 10 kg.(c) Get the price of the products that have a...

SUBJECT: PROGRAMMING IN SQL 1. Create a view named vDepartmentInstructors that returns these columns: the DepartmentName...

SUBJECT: PROGRAMMING IN SQL 1. Create a view named vDepartmentInstructors that returns these columns: the DepartmentName column from the Departments table the LastName, FirstName, Status, and AnnualSalary columns from the Instructors table. 2. Write a SELECT statement that returns all the columns from the vDepartmentInstructors view that you created in question 1. Return one row for each fulltime instructor in the English department. 3. Write an UPDATE statement that updates the vDepartmentInstructors view you created in question 1 so it...

Create a query that shows the last name, employee ID, hours worked, and overtime amount owed...

Create a query that shows the last name, employee ID, hours worked, and overtime amount owed for hourly employees who earned overtime during week 2. Overtime is paid at 1.5 times the normal hourly rate for all hours worked over 40. Note that the amount shown in the query should be just the overtime portion of the wages paid. Also, this is not a totals query- amounts should be shown for individual workers. QBE grid template. This is the exact...

Subjects