Question

"5 USE Createguitarshop DATABASE

Write a SELECT statement that returns the
ProductName and ListPrice columns from the
Products table.
Return one row for each product that has
the same list price as another product.
Hint: Use a self-join to check that the ProductID columns
aren't equal but the ListPrice column is equal.
Sort the result set by ProductName."

Answer 1

To return products with the same list price as another product, you can use a self-join in your SQL query. Use the SELECT statement with the JOIN keyword to compare the ListPrice column of different rows in the same Products table. Sort the result set by ProductName.

Step-by-step explanation:

To write a SELECT statement that returns the ProductName and ListPrice columns from the Products table and shows the products with the same list price as another product, you need to use a self-join. The self-join is used to compare the ListPrice column of different rows in the same table. Here is the SQL query:

SELECT p1.ProductName, p1.ListPrice
FROM Products p1
JOIN Products p2 ON p1.ListPrice = p2.ListPrice
WHERE p1.ProductID < p2.ProductID
ORDER BY p1.ProductName;

In this query, we join the Products table with itself using a self-join. We compare the ListPrice column of p1 with the ListPrice column of p2. The WHERE clause ensures that we only retrieve rows where p1.ProductID is less than p2.ProductID, which guarantees that we get each pair of products only once. Finally, we sort the result set by ProductName.