Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
| modul:m290:learningunits:lu04:aufgaben:a04 [2024/09/04 15:33] – vdemir | modul:m290:learningunits:lu04:aufgaben:a04 [2024/10/17 12:35] (aktuell) – vdemir | ||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| - | ====== | + | ====== |
| ===== Requirements | ===== Requirements | ||
| * Work type: individual | * Work type: individual | ||
| - | * Timeframe: | + | * Timeframe: |
| * Means of aid: | * Means of aid: | ||
| * only teaching materials, no websearch, no use of ai. | * only teaching materials, no websearch, no use of ai. | ||
| Zeile 15: | Zeile 15: | ||
| The general assignment is to develop DQL commands that matches the requirements below: | The general assignment is to develop DQL commands that matches the requirements below: | ||
| - | ==== Assignment a: MIN ==== | + | ==== A: MIN ==== |
| - | We want to know which of our products | + | We want to know which of our products actually the cheapest is. |
| - | SELECT max(price) | + | ==== B: MAX ==== |
| - | FROM Products; | + | What is the highest price for the products of the supplier with id = 12? |
| - | ==== Assignment b: MAX ==== | + | ==== C: AVG ==== |
| - | What is the lowest price for the products of the supplier with id = 12? | + | |
| - | + | ||
| - | SELECT max(price) | + | |
| - | FROM Products | + | |
| - | WHERE SupplierID = 12; | + | |
| - | + | ||
| - | ==== Assignment c: AVG ==== | + | |
| What is the average price for products of supplier 3? | What is the average price for products of supplier 3? | ||
| - | SELECT AVG(price) | + | ==== D: COUNT ==== |
| - | FROM Products | + | |
| - | WHERE SupplierID | + | |
| - | + | ||
| - | ==== Assignment d: AVG ==== | + | |
| How many orders do we currently have in our data-base system from the customer with id = 5? | 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; | ||
| | | ||
| - | ==== Assignment 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. | ||
| - | SELECT sum(price*quantity) | + | ==== F: GROUP BY ==== |
| - | FROM OrderDetails, | + | |
| - | WHERE orderDetails.OrderID | + | |
| - | AND OrderDetails.ProductID | + | |
| - | + | ||
| - | ==== Assignment 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) | ||
| - | FROM OrderDetails, | ||
| - | WHERE orderDetails.OrderID = 10255 | ||
| - | AND OrderDetails.ProductID = Products.ProductID; | ||
| - | |||
| - | |||
| - | |||
| - | |||
| ===== Solution ===== | ===== Solution ===== | ||
| - | [[modul: | + | [[modul: |
| | | ||
| ===== Vocabulary ===== | ===== Vocabulary ===== | ||