1. Write a SELECT statement that returns the same result set as this SELECT stat

Question

1. Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.
SELECT DISTINCT CategoryName
FROM Categories c JOIN Products p
ON c.CategoryID = p.CategoryID
ORDER BY CategoryName
2. Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products?
Return the ProductName and ListPrice columns for each product.
Sort the results by the ListPrice column in descending sequence.
3. Write a SELECT statement that returns the CategoryName column from the Categories table.
Return one row for each category that has never been assigned to any product in the Products table. To do that, use a subquery introduced with the NOT EXISTS operator.
4. Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table.
Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.
5. Write a SELECT statement that returns the name and discount percent of each product that has a unique discount percent. In other words, don’t include products that have the same discount percent as another product.
Sort the results by the ProductName column.
6. Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: EmailAddress, OrderID, and OrderDate.

Explanation / Answer

1)

SELECT DISTINCT CategoryName FROM Categories WHERE CategoryID IN (SELECT CategoryID FROM Products) ORDER BY CategoryName

2)

SELECT ProductName, ListPrice FROM Products WHERE ListPrice > (SELECT AVG(ListPrice) FROM Products) ORDER BY ListPrice DESC

3)

SELECT DISTINCT CategoryName FROM Categories WHERE CategoryID NOT EXISTS (SELECT CategoryID FROM Products)

Too many parts in one question!!

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.