#Adventureworkslt database full#

Some customers have never placed orders, and some products have never been ordered. SELECT c.CompanyName, c.FirstName, c.LastName, c.Phoneĥ) Retrieve a list of customers and products without orders You must write a query that returns a list of customer IDs, company names, contact names (first name and last name), and phone numbers for customers with no address stored in the database. Select CompanyName,FirstName,LastName,SalesOrderID,TotalDueĤ) Retrieve a list of customers with no addressĪ sales employee has noticed that Adventure Works does not have address information for all customers.

Customers who have not placed any orders should be included at the bottom of the list with NULL values for the order ID and total due. The sales manager wants a list of all customer companies and their contacts (first name and last name), showing the sales order ID and total due for each order they have placed. ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'ģ)Retrieve a list of all customers and their orders SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,Ī.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue Your query will need to include both of these tables, and should filter the join to SalesLT.CustomerAddress so that only Main Office addresses are included. Note that each customer can have multiple addressees in the SalesLT.Address table, so the database developer has created the SalesLT.CustomerAddress table to enable a many-to-many relationship between customers and addresses. State or province, postal code, and country or region

#Adventureworkslt database full#

SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDueĢ) Retrieve customer orders with addressesĮxtend your customer orders query to include the Main Office address for each customer, including the full street address, city, We are back & continuing with the 3rd part of the series.Īs an initial step towards generating the invoice report, write a query that returns the company name from the SalesLT.Customer table, and the sales order ID and total due from the SalesLT.SalesOrderHeader table.