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 Kanes" has ordered in the past. Sort the list by the quantity.
  * 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
FROM Customer c
  

Lösung

Volkan Demir