Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

In Northwind database 2019: If the question states to use a subquery and a subquery is not used no credit will be given for the

In Northwind database 2019: If the question states to use a subquery and a subquery is not used no credit will be given for the question.

7. List the OrderID, ShippedDate and CompanyName of any order that was shipped on the same day as any of the orders that were shipped to "Norway". Use the ANY keyword. Use a subquery in the WHERE clause. (Hint: The main query requires two tables.)

SELECT orders.OrderID, orders.ShippedDate, customers.CompanyName FROM orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.ShippedDate = ( SELECT ShippedDate FROM orders WHERE ShipCountry = 'Norway' ); *I had a error here and was wondering where my mistake was*

8. List the SupplierID, ProductID, ProductName, and UnitPrice for each product whose UnitPrice is higher than the average UnitPrice for that Supplier. Use a correlated subquery in the WHERE clause. Order the list by SupplierId. *Already answered this question, but this question applies to number 9*

SELECT Suppliers.SupplierID, Products.ProductID, Products.ProductName, Products.UnitPrice FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID WHERE Products.UnitPrice > ( SELECT AVG(UnitPrice) FROM Products as P2 WHERE Products.SupplierID = P2.SupplierID ) ORDER BY Suppliers.SupplierID;

9. Write the same query as above but this time display the average UnitPrice for the respective supplier (name this column "Supplier Average Price"). Do this by adding a subquery in the SELECT clause. Also, round this column to two decimal places.

10. Write a CTE that obtains the CompanyName (from the suppliers table)), count of products, minimum UnitPrice, and maximum UnitPrice (from the products table). Then write a query that uses the CTE that lists the CompanyName, count, minimum price, and maximum price for all products that have a count greater than 3 and a maximum price greater than 50.00. Use appropriate column names. Round the prices to two decimal places. (Hint: The CTE requires two tables.)

I just need number 9 and 10 and please see where my error was for number 7. Thanks for any help.

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 Programming With Visual Basic .NET

Authors: Carsten Thomsen

2nd Edition

1590590325, 978-1590590324

More Books

Students also viewed these Databases questions