Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu04:aufgaben:a02 [2024/09/03 14:35] – vdemir
+++ modul:m290:learningunits:lu04:aufgaben:a02 [2024/10/17 12:35] (aktuell) – vdemir
@@ Zeile 1: / Zeile 1: @@
-====== LU04.A02 - SQL-DQL: Select from multiple tables======
+====== LU06.A02 - SQL-DQL: Select from multiple tables======
 ===== Requirements  =====
@@ Zeile 8: / Zeile 8: @@
       * [[https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_columns | W3Schools | SQL Editor]]
   * Expected result: Semantically and syntactically correct SQL statements according to the requirements of the case studies.
 ===== Case studies / Assignments =====
@@ Zeile 16: / Zeile 15: @@
 ===== Assignments =====
-You 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:
-  * a) Supplier and PrWe need a list with product details as following:
+==== Assignment A ====
-    * Supplier name
+We need a list with product details as following:
-    * Supplier phone
+  * Name of the supplier
-    * ProductID
+  * Supplier phone
-    * ProductName
+  * Product ID
-    * Units
+  * Name of the product
-    * Price
+  * Units
-    * Ordered by SupplierName descending
+  * Price
+The list must be be sorted by the suppliername in descending order.
-  SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
-  FROM Suppliers s, Products p
-  WHERE p.SupplierID = s.SupplierID
-  ORDER BY s.SupplierName DESC;
-  * b) Extend the select, so that the list contains only have the producs of the supplier "Leka Trading" on our list.
+==== Assignment B ====
-    * There are two possible solutions.
+Extend the the result of the Select statement from assignment a), so that it must contain only produtcs of the supplier "Leka Trading".
-    * b1) The first one is to to search for the ID (SupplierID = 20) of the supplier and take that ID into our SELECT as follows:
-  SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
+==== Assignment C ====
-  FROM Suppliers s, Products p
+We would like to know what products the customer "Hanari Carnes" has ordered in the past. Sort the list by the quantity. detail we require the following data:
-  WHERE p.SupplierID = s.SupplierID
+  * CustomerID
-  AND s.SupplierID = 20;
+  * CustomerName
+  * OrderID
+  * OrderDate
+  * Quantity
+  * ProductName
+  * CategoryName
-    * b2) The second solution take the name directly into the SELECT statemeng:
-  SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
-  FROM Suppliers s, Products p
-  WHERE p.SupplierID = s.SupplierID
-  AND s.SupplierName = 'Leka Trading';
-The result set is in both cases, as the figure below shows:
-{{:modul:m290:learningunits:lu04:aufgaben:screenshot_2024-09-03_134335.png?600|}}
-  * c) We would like to know what products the customer "Hanari Carnes" has ordered in the past. Sort the list by the quantity.
-    * In detail we require the following data: CustomerID, CustomerName, OrderID, OorderDate, Quantity, ProductName, CategoryName
-    * c1) Like in assignment b) here a two approaches possible: 1. find the customerID (34) and select according to the customer id.
-  select c.CustomerID, c.CustomerName , o.OrderID, o.orderDate, od.Quantity, p.ProductName, ct.CategoryName
-  FROM customers c, orders o, OrderDetails od, Products p, Categories ct
-  WHERE c.CustomerID = o.CustomerID
-  AND o.OrderID = od.OrderID
-  AND od.ProductID = p.ProductID
-  AND p.CategoryID = ct.CategoryID
-  AND c.CustomerID = 34
-  ORDER BY od.Quantity;
-  * C1) The second approach, as in b2), is to filter the result set directly by customer name.
-  select c.CustomerID, c.CustomerName , o.OrderID, o.orderDate, od.Quantity, p.ProductName, ct.CategoryName
-  FROM customers c, orders o, OrderDetails od, Products p, Categories ct
-  WHERE c.CustomerID = o.CustomerID
-  AND o.OrderID = od.OrderID
-  AND od.ProductID = p.ProductID
-  AND p.CategoryID = ct.CategoryID
-  AND c.CustomerName = "Hanari Kanes"
-  ORDER BY od.Quantity;
-  The result set is in both cases as shown in the following figure.
-  {{:modul:m290:learningunits:lu04:aufgaben:screenshot_2024-09-03_143510.png?600|}}
 ===== Solution =====
 [[modul:m290:learningunits:lu04:loesungen:l02|Lösung]]
 ===== Vocabulary =====
 ^ English ^ German ^
-| respectively | beziehungsweise|
+| ... | ...|
-| assignment | Auftrag |
 ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir