You can use the following queries to view purchasing and vendor data and to become familiar with the purchasing and vendor table relationships.
A. Viewing vendors by location
The following example lists the vendors and their address.
USE AdventureWorks;
GO
SELECT V.VendorID, V.Name AS Vendor, A.AddressLine1, A.AddressLine2, A.City, SP.Name AS State, CR.Name AS Country
FROM Purchasing.Vendor AS V
JOIN Purchasing.VendorAddress AS VA ON VA.VendorID = V.VendorID
JOIN Person.Address AS A on A.AddressID = VA.AddressID
JOIN Person.StateProvince AS SP on SP.StateProvinceID = A.StateProvinceID
JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
GROUP BY V.VendorID, V.Name, A.AddressLine1, A.AddressLine2, A.City, SP.Name, CR.Name
ORDER BY V.VendorID;
GO
B. Viewing products supplied by vendors
The following example lists the products that the vendors supply to Adventure Works Cycles.
USE AdventureWorks;
GO
SELECT P.ProductNumber, P.Name AS Product, V.Name AS Vendor, PV.LastReceiptCost
FROM Production.Product AS P
JOIN Purchasing.ProductVendor AS PV ON P.ProductID = PV.ProductID
JOIN Purchasing.Vendor AS V ON V.VendorID = PV.VendorID
ORDER BY P.Name ;
GO
C. Viewing vendor contacts by vendor
The following example lists vendor contacts. Vendor contacts are employees of the vendor with whom employees of the Adventure Works Cycles purchasing department interact to order parts and products.
GO
SELECT V.Name as Vendor, C.FirstName, C.LastName, CT.Name AS Title
FROM Person.Contact AS C
JOIN Purchasing.VendorContact VC ON C.ContactID = VC.ContactID
JOIN Person.ContactType CT ON CT.ContactTypeID = VC.ContactTypeID
JOIN Purchasing.Vendor V ON V.VendorID = VC.VendorID
ORDER BY V.Name;
GO
D. Viewing purchases by vendor
The following example displays the vendors and their associated purchase orders.
USE AdventureWorks;
GO
SELECT V.Name AS Vendor, SUM(PH.TotalDue)AS [Total Purchase],
AVG(PH.TotalDue)AS [Average Purchase], MIN(PH.TotalDue)
AS [Minimum Purchase], MAX(PH.TotalDue)AS [Maximum Purchase]
FROM Purchasing.Vendor AS V
JOIN Purchasing.PurchaseOrderHeader AS PH ON V.VendorID = PH.VendorID
GROUP BY V.Name
ORDER BY V.Name;
GO