Question

Write a SELECT statement that returns one row for each category that has products with these columns: The CategoryName column from the Categories table The count of the products in the Products table The list price of the most expensive product in the Products table Sort the result set so the category with the most products appears first.

Answer 1

A SQL SELECT statement is required to join Categories and Products tables, count the products per category, find the maximum price of products in each category, and sort by the number of products, with the highest number first.

Step-by-step explanation:

The student has asked for a SELECT statement that retrieves data from two tables, namely Categories and Products. The query should result in each category listed along with the count of products in that category and the price of the most expensive product within that category. Lastly, the result should be sorted so that the category with the highest number of products appears first.

To fulfill these requirements, a SQL statement that joins the two tables on their respective category identifiers, counts the number of products, finds the maximum list price, and sorts the results by the count of products in descending order is needed.

The SELECT statement will look like this:

SELECT
Categories.CategoryName,
COUNT(Products.ProductID) AS ProductCount,
MAX(Products.ListPrice) AS MostExpensiveProduct
FROM
Categories
JOIN
Products ON Categories.CategoryID = Products.CategoryID
GROUP BY
Categories.CategoryName
ORDER BY
ProductCount DESC;

Answer 2

SELECT C.CategoryName,COUNT(P.CategoryID) AS NumberOfProducts, MAX(P.ListPrice) AS

MostExpensiveProduct

FROM Categories C, Products P

WHERE P.CategoryID=C.CategoryID

GROUP BY C.CategoryName ORDER BY COUNT(*) desc;

Step-by-step explanation:

This program uses a SELECT statement to arrive at its outout or perform its function.

The SELECT statement helps to return one row for each category that has products.

This is done by Sorting the result set so the category with the most products appears first.