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:54] – 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======
  
  
Zeile 10: Zeile 10:
 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) ====+==== Assignment A ====
 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. 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.
  
Zeile 18: Zeile 18:
   ORDER BY s.SupplierName DESC;   ORDER BY s.SupplierName DESC;
  
-==== Assignment b) ====+==== Assignment B ====
 Extend the the result of the Select statement from assignment a), so that it must contain only produtcs of the supplier "Leka Trading".  Extend the the result of the Select statement from assignment a), so that it must contain only produtcs of the supplier "Leka Trading". 
  
 There are two possible solutions.  There are two possible solutions. 
  
-**b1)** +**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:  +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 31: Zeile 31:
   AND s.SupplierID = 20;   AND s.SupplierID = 20;
  
-**b2)** +**B2)** 
 The second solution takes the name directly into the SELECT statement:   The second solution takes the name directly into the SELECT statement:  
  
Zeile 38: 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|}}
      
-==== Assigmment c) ====+==== Assigmment 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  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)**+**C1)**
 Like in assignment b) here a two approaches possible: 1. find the customerID (34) and select according to the customer id. 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 58: Zeile 58:
   ORDER BY od.Quantity;   ORDER BY od.Quantity;
  
-**C1)** +**C2)** 
-The second approach, as in b2), is to filter the result set directly by customer name.+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 67: 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.1725368057.txt.gz
  • Zuletzt geändert: 2024/09/03 14:54
  • von vdemir