SQLTeam.com | Weblogs | Forums

New to SQL need an assistance

image Create a query which can show the customers who have bought the most expensive product and the customers who bought the least expensive product, and returns the results in one table. (20 points)

SELECT C1.CustomerID, CustomerName, OrderedQuantity,
‘the most expensive product’ AS Quantity
FROM Customer_T C1,Order_T O1, OrderLine_T Q1
WHERE C1.CustomerID = O1.CustomerID
AND O1.OrderID = Q1.OrderID
AND PRODUCTPRICE =
(SELECT MAX(PRODUCT PRICE)
FROM OrderLine_T)
UNION
SELECT C1.CustomerID, CustomerName, OrderedQuantity,
‘the least expensive product’
FROM Customer_T C1, Order_T O1, OrderLine_T Q1
WHERE C1.CustomerID = O1.CustomerID
AND O1.OrderID = Q1.OrderID
AND PRODUCTPRICE =
(SELECT MIN(PRODUCTPRICE)
FROM OrderLine_T)
ORDER BY 3;

not sure is it correct or not

This is your code. I edited a bit for better readability.

SELECT C1.CustomerID
	, CustomerName
	, OrderedQuantity
	, 'the most expensive product' AS Quantity
FROM Customer_T C1
	,Order_T O1
	, OrderLine_T Q1
WHERE C1.CustomerID = O1.CustomerID
	AND O1.OrderID = Q1.OrderID
	AND PRODUCTPRICE = (SELECT MAX(PRODUCT PRICE) FROM OrderLine_T)
UNION
SELECT C1.CustomerID
	, CustomerName
	, OrderedQuantity
	,'the least expensive product'
FROM Customer_T C1
	, Order_T O1
	, OrderLine_T Q1
WHERE C1.CustomerID = O1.CustomerID
	AND O1.OrderID = Q1.OrderID
	AND PRODUCTPRICE = (SELECT MIN(PRODUCTPRICE) FROM OrderLine_T)
ORDER BY 3;

I think your query is right.
The best way to make sure your query works is by running it of coarse. Run it on the database your school provides. That's the preferred way.
If you have no access to that database, write a few CREATE TABLE scripts and populate those tables with some sample data.

Make sure you know beforehand what the result should be and compare that to the result of your query. If they differ, at least one of them has an error.

I notice you use the old school way of joining tables. This query shows how to achieve the above with explicit INNER JOINs:

SELECT C1.CustomerID
	, CustomerName
	, OrderedQuantity
	, 'the most expensive product' AS Quantity
FROM Customer_T C1
	INNER JOIN Order_T O1
		ON C1.CustomerID = O1.CustomerID
	INNER JOIN OrderLine_T Q1
		ON O1.OrderID = Q1.OrderID
WHERE Q1.PRODUCTPRICE = (SELECT MAX(PRODUCTPRICE) FROM OrderLine_T)

....

Now the code used to JOIN tables and the business rules are separated.