Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu04:theorie:c_multipletables [2024/09/03 11:09] – vdemir
+++ modul:m290:learningunits:lu04:theorie:c_multipletables [2024/10/17 12:34] (aktuell) – vdemir
@@ Zeile 1: / Zeile 1: @@
-====== LU04c - Multiple table ======
+====== LU06c - SQL-DQL: Select from multiple tables ======
 Source: [[https://www.w3schools.com/sql/sql_where.asp|W3Schools.co: SELECT with WHERE]]
-As we know, a RDB essentially consists of tables which are linked together to increase quality and performance and reduce unintended redundancies and inconsistencies. If we want to know which orders were placed by which customer we have to deal with the two tables **CUSTOMERS** and **ORDERS**, and the key that connects the two is logically the CustomerID, as shown in the figure below.
+As we know, a RDB essentially consists of tables which are linked together to increase quality and performance and reduce unintended redundancies and inconsistencies. The following ERD shows such a data structure of a shop including some details.
-{{:modul:m290:learningunits:lu04:theorie:erd_customer.png?900}}
+{{:modul:m290:learningunits:lu04:theorie:erd_customer_orders.drawio.png?900| ERD of a Webshop}}
-In real live we would like to know which one particular customer, e.g. with the ID =3,  had ordered, the subsequent SQL statement would be as following:
+If we want to know which orders were placed by which customer we have to deal with the two tables **CUSTOMERS** and **ORDERS**, and the key that connects the two is logically the CustomerID.
+===== Select on two tables =====
+In practice, we would like to know which customer, e.g. with ID =3, has ordered which product. The following SQL statement would read as follows:
   SELECT c.*, o.orderdate
@@ Zeile 19: / Zeile 23: @@
 {{:modul:m290:learningunits:lu04:theorie:screenshot_2024-09-03_110719.png|}}
+===== Select on three or more tables =====
+The basic idea how to receive data from two table shows us the way how we can retrieve data from three or more tables, as the approach is similar. In that case the three or more tables require a common ID, that linkes the tables involved. The requirements could contain some order details, e.g. which product and the which quantity the customers had ordered. The according SQL select statement would be like:
-===== ... ====
+  SELECT c.CustomerID, c.CustomerName, o.Orderdate, od.ProductID, od.Quantity
+  FROM Customers c, Orders o, OrderDetails od
+  WHERE o.orderid = od.orderID
+  AND c.CustomerID = o.CustomerID
+  AND c.CustomerID = 2;
+The result is shown in the figure below:
+{{:modul:m290:learningunits:lu04:theorie:screenshot_2024-09-03_113230.png|}}
 ===== Vocabulary =====
 ^ English ^ German ^
 | essentially | im Grunde genommen, hauptsächlich |
+| requirement | Anforderung |