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

• Work type: individual
• Timeframe: 20 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) We 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 „Karrki OY“ on our list.

There are two possible solutions. 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;

Lösung

Volkan Demir