LU04.A02 - SQL-DQL: Select from multiple tables

• Work type: individual
• Timeframe: 30 Minutes
• Means of aid:
  • only teaching materials, no websearch, no use of ai.
  • W3Schools | SQL Editor
• Expected result: Semantically and syntactically correct SQL statements according to the requirements of the case studies.

The following ERD describes a order database for a a shop.

You general assignment is to develop DQL commands that matches the requirements below:

• a) Supplier and PrWe need a list with product details as following:
  • Supplier name
  • 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
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.
  • There are two possible solutions.
  • 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
FROM Suppliers s, Products p
WHERE p.SupplierID = s.SupplierID
AND s.SupplierID = 20;

• 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:

* 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|}}

Lösung

Volkan Demir