Lab03-Database

1.A. Find the name and phone number of each vendor who has a cooperating vendor. Hint: the vendors for whom the field Cooperating_Vendor_id is not empty B. Now find additionally the name and phone number of the cooperating vendor. Present these two columns as: ‘cooperating vendor’ and ‘cooperating vendor phone’. [login to view URL] the above query to report all vendors. If they have a cooperating vendor, report their name if not report ‘N/A’. 3.A. Find the order date for all the orders of customers who leave in Los Angeles using joins B. Repeat the same query this time using non-correlated subqueries C. Repeat the same query this time using correlated subqueries with the IN statement D. Repeat the same query this time using WHERE EXISTS [login to view URL] the UNION, UNION ALL, INTERSECT and MINUS statements find: The order_id of the orders that include a product from artist 'Burt Ruggles' and do not include products from 'Jess & Odie’. The order_id of the orders that include a product from artist 'Burt Ruggles' and include products from 'Jess & Odie’. The order_id of the orders that include a product from artist 'Burt Ruggles' or include products from 'Jess & Odie’. The order_id of the orders that include a product from artist 'Burt Ruggles' or include products from 'Jess & Odie’ Desk’, listing this time all the results, even if the same salesperson is repeated. [login to view URL] the customer id and the total amount of dollars he has ever spent in orders. Name your amount: total_amount Hint: To do that you need to join customers, orders order_details and items and calculate the total (sum) of order_qty*unit_price per customer. [login to view URL] use the previous query as a subquery in the FROM statement (it will be used in place of a table). From this new “table” select the customer_id, the total_amount and a new column, named, customer_status, that has the following values: very bad: when total_amount <= 50 bad: when total_amount > 50 and total_amount <=100 ok: when total_amount > 100 and total_amount <=150 good: when total_amount > 150 and total_amount <=200 very good: when total_amount > 200 SCREEN SHOTS OF EVERY INQUIRY MUST BE INCLUDED