modul:m290:learningunits:lu04:loesungen:l02

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:l02 [2024/09/03 14:36] – vdemirmodul:m290:learningunits:lu04:loesungen:l02 [2024/10/17 12:35] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU04.S02 - SQL-DQL: Select from multiple tables====== +====== LU06.S02 - SQL-DQL: Select from multiple tables======
- +
-===== Requirements  ===== +
-  * Work type: individual +
-  * Timeframe: 30 Minutes +
-  * Means of aid:  +
-      * only teaching materials, no websearch, no use of ai. +
-      * [[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. +
  
  
Zeile 16: Zeile 8:
  
 ===== Assignments ===== ===== 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: Name of the supplier, Supplier phone, Product ID, Name of the product, units, price. The list must be be sorted by SupplierName in descending order.
-    * Supplier phone  +
-    * ProductID +
-    * ProductName +
-    * Units +
-    * Price +
-    * Ordered by SupplierName descending+
  
   SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price   SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
Zeile 32: Zeile 18:
   ORDER BY s.SupplierName DESC;   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:  + 
 +There are two possible solutions.  
 + 
 +**B1)** 
 +The first assignment is 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   SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
Zeile 41: Zeile 31:
   AND s.SupplierID = 20;   AND s.SupplierID = 20;
  
-    * b2) The second solution take the name directly into the SELECT statemeng:  +**B2)**  
 +The second solution takes the name directly into the SELECT statement:  
  
   SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price   SELECT s.SupplierName, s.SupplierID, s.phone,  p.ProductID, p.ProductName, p.Unit, p.Price
Zeile 47: Zeile 38:
   WHERE p.SupplierID = s.SupplierID   WHERE p.SupplierID = s.SupplierID
   AND s.SupplierName = 'Leka Trading';   AND s.SupplierName = 'Leka Trading';
 +  
 The result set is in both cases, as the figure below shows:  The result set is in both cases, as the figure below shows: 
  
 {{:modul:m290:learningunits:lu04:aufgaben:screenshot_2024-09-03_134335.png?600|}} {{: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.  +==== Assigmment C ==== 
-    * In detail we require the following data: CustomerID, CustomerName, OrderID, OorderDate, Quantity, ProductName, CategoryName  +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.+**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+  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   FROM customers c, orders o, OrderDetails od, Products p, Categories ct
   WHERE c.CustomerID = o.CustomerID   WHERE c.CustomerID = o.CustomerID
Zeile 67: Zeile 58:
   ORDER BY od.Quantity;   ORDER BY od.Quantity;
  
-  * C1) The second approach, as in b2), is to filter the result set directly by customer name.+**C2)** 
 +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+  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   FROM customers c, orders o, OrderDetails od, Products p, Categories ct
   WHERE c.CustomerID = o.CustomerID   WHERE c.CustomerID = o.CustomerID
Zeile 75: Zeile 67:
   AND od.ProductID = p.ProductID   AND od.ProductID = p.ProductID
   AND p.CategoryID = ct.CategoryID   AND p.CategoryID = ct.CategoryID
-  AND c.CustomerName = "Hanari Kanes"+  AND c.CustomerName = "Hanari Carnes"
   ORDER BY od.Quantity;       ORDER BY od.Quantity;    
      
-  The result set is in both cases as shown in the following figure.+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|}} {{:modul:m290:learningunits:lu04:aufgaben:screenshot_2024-09-03_143510.png?600|}}
      
 ===== Solution ===== ===== Solution =====
-[[modul:m290:learningunits:lu04:loesungen:l02|Lösung]]+[[modul:m290:learningunits:lu04:loesungen:l04|Lösung]]
  
  
  • modul/m290/learningunits/lu04/loesungen/l02.1725367018.txt.gz
  • Zuletzt geändert: 2024/09/03 14:36
  • von vdemir