Question
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started