modul:m290:learningunits:lu04:loesungen:l03

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu04:loesungen:l03 [2024/09/27 12:45] – vdemirmodul:m290:learningunits:lu04:loesungen:l03 [2024/10/17 12:35] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU05.S03 - SQL-DQL: Selects with Aggregate Functions ======+====== LU06.S03 - SQL-DQL: Selects with Aggregate Functions ======
  
 ==== A: MIN ==== ==== A: MIN ====
-We want to know which of our products actually the most expensive is.+We want to know which of our products actually the cheapest is.
  
   SELECT min(price)   SELECT min(price)
   FROM Products;   FROM Products;
 +  
 +**Result from DB:**
 +2.5 
 +
 +
  
 ==== B: MAX ==== ==== B: MAX ====
-What is the lowest price for the products of the supplier with id = 12?+What is the highest price for the products of the supplier with id = 12?
  
   SELECT max(price)    SELECT max(price) 
   FROM Products   FROM Products
   WHERE SupplierID = 12;   WHERE SupplierID = 12;
 +  
 +**Result from the DB:** 123.79 
  
 ==== c: AVG ==== ==== c: AVG ====
Zeile 20: Zeile 27:
   FROM Products   FROM Products
   WHERE SupplierID = 3;   WHERE SupplierID = 3;
 +  
 +**Result from the db:** 31.6667 
  
 ==== D: COUNT ==== ==== D: COUNT ====
Zeile 28: Zeile 37:
   where customerID = 5;   where customerID = 5;
      
 +**Result from the db:** 3
 ==== E: SUM ==== ==== E: SUM ====
 What is the worth of the order 10255? Please note, that there are two tables involved in this select statement. What is the worth of the order 10255? Please note, that there are two tables involved in this select statement.
Zeile 35: Zeile 45:
   WHERE orderDetails.OrderID = 10255   WHERE orderDetails.OrderID = 10255
   AND OrderDetails.ProductID = Products.ProductID;   AND OrderDetails.ProductID = Products.ProductID;
 +  
 +**Result from the db:** 3115.75 
  
 ==== F: GROUP BY ==== ==== F: GROUP BY ====
 For our anual report we need list of the orders, and the value of each, grouped by the OrderID. For our anual report we need list of the orders, and the value of each, grouped by the OrderID.
  
-  SELECT sum(price*quantity)+  SELECT OrderDetails.orderID, sum(price*quantity)
   FROM OrderDetails, Products   FROM OrderDetails, Products
-  WHERE orderDetails.OrderID = 10255 +  WHERE OrderDetails.ProductID = Products.ProductID 
-  AND OrderDetails.ProductID = Products.ProductID;+  GROUP BY OrderDetails.orderid; 
 +   
 +*** Result from the db:**
  
 +{{:modul:m290:learningunits:lu04:loesungen:lusdfas..png?600|}}
 +  
      
 ===== Vocabulary ===== ===== Vocabulary =====
  • modul/m290/learningunits/lu04/loesungen/l03.1727433921.txt.gz
  • Zuletzt geändert: 2024/09/27 12:45
  • von vdemir