Objectives This assignment focuses on how to create and use views and indexes Th
ID: 3906403 • Letter: O
Question
Objectives This assignment focuses on how to create and use views and indexes This assignment uses the tables associated with the bookstore database. Write SQL statements to perform the following queries: Query 1:Create a view named customer_address that shows the shipping for each customer. This view should return these columns from the CUSTOMER table: customer_id, email_address, last_name, and first_name. This view should return these columns from the ADDRESS table: ship_linel, ship_line2, ship_city, ship state, and ship zip. Write a SELECT statement that returns these columns from the customer adress view: customer_id, last name, first_name, ship_city and ship_state. Query 2:Write an SQL statement that updates the customer table using the customer_address view you created in query 1. Set the first line of the shipping address to "1990 Westwood Blvd." for the customer with an ID of 11119. Query 3:Create a view named product summary. This view should return summary information about each product. Each row should include product_id, order_count (the number of times the product has been ordered) and order_total (the total sales for the product). Write a SELECT statement that returns all the columns from the product summary view Query 4: Creates a view named cheap_products whose subquery retrieves products only where the price is less than $50. Add a CHECK OPTION constraint. Wica SELECT salmentExplanation / Answer
Please Note: I have answered the First two Questions. Pelase Re-Post for Separate Questions.
Query 1) View customer_address shows shipping for each customer.
Answer)
Assume that customer_id is the primary key in CUSTOMER table and is the foreign key in the ADDRESS table.
CREATE VIEW customer_address AS
select CUSTOMER.customer_id, CUSTOMER .email_address, CUSTOMER.last_name, CUSTOMER.first_name, ADDRESS.ship_line1, ADDRESS.ship_line2, ADDRESS.ship_city, ADDRESS.ship_state, ADDRESS.ship_zip from CUSTOMER inner join ADDRESS on CUSTOMER.customer_id = ADDRESS.customer_id;
select customer_id, last_name, first_name, ship_city, ship_state from customer_address;
Query 2) Update customer_address.
Answer)
UPDATE customer_address
SET ship_line1 = ‘1990 Westwood Blvd’
WHERE customer_id = 11119;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.