LU05.S02 - SQL-DQL: Select from multiple tables

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

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

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.

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;

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.

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
FROM Suppliers s, Products p
WHERE p.SupplierID = s.SupplierID
AND s.SupplierID = 20;

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

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;

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
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 Carnes"
ORDER BY od.Quantity;    

The result set is in both cases as shown in the following figure.

Lösung

Volkan Demir