Question

For every part with a supplier, find its PID, PName, average cost, maximum cost, and minimum cost. Which SQL query should be used to obtain this information?

A. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts GROUP BY Supplier
B. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts WHERE Supplier IS NOT NULL
C. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Suppliers GROUP BY Part
D. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts WHERE Supplier IS NOT NULL GROUP BY PID, PName

Answer 1

D. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts WHERE Supplier IS NOT NULL GROUP BY PID, PName, To find the PID, PName, average cost, maximum cost, and minimum cost for parts with a supplier, SQL query D should be used, which groups the results by PID and PName.

Step-by-step explanation:

The SQL query that should be used to obtain the PID, PName, average cost, maximum cost, and minimum cost for every part with a supplier is:

D. SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts WHERE Supplier IS NOT NULL GROUP BY PID, PName

This query ensures that only parts with an associated supplier are selected by checking that the Supplier field is not null. It groups the results by Part ID and Name, allowing the average, maximum, and minimum costs to be calculated for each part.

The correct SQL query for obtaining the information about each part with a supplier, including PID, PName, average cost, maximum cost, and minimum cost, is option D: "SELECT PID, PName, AVG(Cost), MAX(Cost), MIN(Cost) FROM Parts WHERE Supplier IS NOT NULL GROUP BY PID, PName."

This query filters out parts without a supplier, calculates the average, maximum, and minimum costs for each part, and groups the results by the unique combination of Part ID (PID) and Part Name (PName). This ensures accurate aggregation of cost-related data for each distinct part in the presence of multiple suppliers in the Parts table.