Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

SQL: - List the product name and the supplier name distinctly for those products that meet the following criteria: there had to be at least

SQL:

- List the product name and the supplier name distinctly for those products that meet the following criteria: there had to be at least a quantity of 50 placed on an order, the products should be within the following categories: Beverages, Meat/Poultry, Dairy Products, Seafood. Order the list by product name.

- Create a union that combines the following result sets: The first interim result set should display the literal Discounted, product ID, name, unit price, quantity, product sale amount which is a calculated field: (UnitPrice * Quantity) * (1 - Discount) AS ProductSale. Use the UnitPrice from the Products table. Return only discounted products (located in the Order Details table where discount>0) where a quantity of 50 or more were placed for an order. The second interim result set should display the literal No Discount, product ID, name, unit price, quantity, product sale which should be setup as follows: (UnitPrice * Quantity) AS ProductSale. This should be for non discount products (discount=0) where a quantity of 50 or more were placed for an order. Sort the entire result set by Product Name, discount type, and quantity highest to lowest

- Create a query that will display an employee first name and last name uniquely for those employees that live in the USA and have sold at least one of the following products: Boston Crab Meat or Sir Rodney's Scones. Make sure you bring in the correct number of tables to perform this query, a Cross Join SHOULD NOT BE USED.

- List the cities for an employee that is common to a customer city location. YOU MUST use the Intersect operator.

- This query will involve 7 tables. Make sure a CROSS JOIN is NOT used. Display the employee first name, last name, Territory description, unit price at the time of the order, quantity ordered, product name, region description for order quantities within the range of 10 and 25, territory of Atlanta, within the Southern region, and an unit price, at the time of the order, of 20 dollars or more, and the product name that begins with the letter G.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Machine Performance Modeling Methodologies And Evaluation Strategies Lncs 257

Authors: Francesca Cesarini ,Silvio Salza

1st Edition

3540179429, 978-3540179429

More Books

Students also viewed these Databases questions

Question

What are the best practice recommendations for WLAN design?

Answered: 1 week ago

Question

10. What is meant by a feed rate?

Answered: 1 week ago