LU05.S03_tmp - SQL-DQL: Selects with Aggregate Functions

We want to know which of our products is actually the most expensive.

SELECT max(price)
FROM Products;

What is the lowest price for the products of the supplier with id = 12?

SELECT max(price) 
FROM Products
WHERE SupplierID = 12;

What is the average price for products of supplier 3?

SELECT AVG(price) 
FROM Products
WHERE SupplierID = 3;

How many orders do we currently have in our data-base system from the customer with id = 5?

SELECT count(customerID)
FROM Orders
where customerID = 5;

What is the worth of the order 10255? Please note, that there are two tables involved in this select statement.

SELECT sum(price*quantity)
FROM OrderDetails, Products
WHERE orderDetails.OrderID = 10255
AND OrderDetails.ProductID = Products.ProductID;

For our anual report we need list of the orders, and the value of each, grouped by the OrderID.

SELECT sum(price*quantity)
FROM OrderDetails, Products
WHERE orderDetails.OrderID = 10255
AND OrderDetails.ProductID = Products.ProductID;

Volkan Demir