Question
I need help with writing queries. My prof will give us textbook readings and a complicated assignment wherein we had no examples to learn from.
I need help with writing queries. My prof will give us textbook readings and a complicated assignment wherein we had no examples to learn from. As such, I don't know how to write queries with these requirements to answer the questions.
INSTRUCTION: Write a query in SSMS to answer the following questions. Use the NorthWind database to answer the questions.
QUESTIONS:
1. How many vendors provide products?
2. How many vendors provide products?
--Name the column that will be displayed as "Number of Suppliers".
3. How many vendors have products with price < 10?
4. How many vendors (unique vendors) have products with price < 10?
5. How many products with price < 10?
6. What is the highest unit price?
7. What is the lowest unit price?
8. How much is the total value of products in inventory?
9. What is the average product price?
10. What is the minimum price for each vendor?
11. What is the average price for each vendor?
12. How many products each supplier provides?
13: List the number of products by vendor with the average price,
--include only the rows with price below 10.00.
14: Aggregate the total cost of products by vendor,
-- Select only the rows having a total cost greater than 500, and
-- List the results in descending order by total cost.
15. Write a query to display the total, average, maximum, and the minimum unit price.
--Give relevant name for each of the columns.
16. Write a query to display the total, average, maximum, and the minimum unit price for
--items supplied by each supplier (Modify query the above query). Order the result in ascending
--order by the total unit price
--Give relevant name for each of the columns.
--USE THE SALES DATABASE TO ANSWER THE FOLLOWING QUESTIONS
17. Write a query to display the number of products within each base and type combination.
18. Write a query to display the average product price (prod_price) for each product category
--that have at least 10 product prices.
--Example 19: Write a query that lists out sales reps, the number of invoices
--they generated and the total value of the invoices for those sales reps
-- that generated more than 1000 in total invoice value with less than five invoices in 2012
--Example 20: List the details of customers (customer name, address, etc) for those customers
-- that are in New York, has more than 5 invoices and the total value of the invoice is more
--than 1000 in descending order by total invoice value.
--Hint: Join CUSTOMER and INVOICE tables
***Below is the table schema for the Northwind Database
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