I'm practicing writing SQL queries, I just wanted to check that my answer would give me the desired result.
I'm given a few tables of a database, I need to retrieve "the customer IDs of customers that have ordered any product from a supplier based in Canberra"
The tables:
- Suppliers (SupplierID, CompanyName, ContactName,ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage)
- Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
- OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount)
- Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
- Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
- Employees (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)
- Shippers (ShipperID, CompanyName, Phone)
The SQL query I've written:
SELECT Customer.CustomerID
FROM Customers, Orders
WHERE (Customers.CustomerID = Orders.CustomerID)
AND (Orders.ProductID IN (SELECT Products.ProductID
FROM Suppliers, Products
WHERE (Products.SupplierID = Suppliers.SupplierID)
AND (Suppliers.City = 'Canberra'))
The query should retrieve a list of customer numbers that match customers that have ever ordered a product that a Canberra supplier has made.
JOIN
syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged