Skip to content

So You Want to Learn SQL… Session 3

Published by sbalkum on August 22, 2013

Homework Answers

List AOV per credit card type and country region code for July, 2008. Drop rows with statistically irrelevant results, i.e. less than 10. Sort by card type, AOV descending, then country.

SELECT cc.CardType, cr.Name AS Country, AVG(oh.TotalDue) AS AOV
	FROM Sales.SalesOrderHeader oh
	JOIN Sales.CreditCard cc ON cc.CreditCardID = oh.CreditCardID
	JOIN Sales.SalesTerritory t ON t.TerritoryID = oh.TerritoryID
	JOIN Person.CountryRegion cr ON cr.CountryRegionCode = t.CountryRegionCode
	WHERE oh.OrderDate >= '7/1/2008' AND oh.OrderDate < '8/1/2008'
 	GROUP BY cc.CardType, cr.Name
 	HAVING COUNT(*) > 10
	ORDER BY AOV DESC, Country

OUTER JOINs

LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN

All rows from one of the tables, plus the rows from the other table.  The columns will contain NULL when a matching row is not found.

LEFT

  • All rows from the existing table, plus matching rows from the joining table
  • This is by far the most common outer join in my experience
  • LEFT [OUTER] JOIN Table2 t2 ON t2.PrimaryKey = t1.ForeignKey

RIGHT

  • All rows from the joining table, plus columns from the existing table
  • This join is rather rare
  • RIGHT [OUTER] JOIN Table2 t2 ON t2.PrimaryKey = t1.ForeignKey

Compare the output, especially the row count, of these queries:

SELECT *
	FROM HumanResources.JobCandidate

SELECT *
	FROM Person.BusinessEntity

SELECT jc.JobCandidateID, be.BusinessEntityID
	FROM HumanResources.JobCandidate jc
	JOIN Person.BusinessEntity be ON be.BusinessEntityID = jc.BusinessEntityID

SELECT jc.JobCandidateID, be.BusinessEntityID
	FROM HumanResources.JobCandidate jc
	LEFT JOIN Person.BusinessEntity be ON be.BusinessEntityID = jc.BusinessEntityID

SELECT jc.JobCandidateID, be.BusinessEntityID
	FROM HumanResources.JobCandidate jc
	RIGHT JOIN Person.BusinessEntity be ON be.BusinessEntityID = jc.BusinessEntityID

FULL [OUTER] JOIN

All rows from both tables

  • Columns will contain NULL when matching row not found
  • This join is rather rare
  • FULL [OUTER] JOIN Table2 t2 ON t2.PrimaryKey = t1.ForeignKey

CROSS JOIN

All rows from existing table matched to all rows in joining table and vice versa

  • So rare I have only ever done this once for a real request in 15 years of working with SQL
  • CROSS JOIN Table2
  • No relationship specified

A contrived example: compare the row counts and data of these two queries:

SELECT *
	FROM Person.AddressType

SELECT at1.Name, at2.Name
	FROM Person.AddressType at1
	CROSS JOIN Person.AddressType at2

In the second query you can see every row in at1 is joined to every row in at2 creating all possible combinations.

LEFT OUTER JOIN in Action

Move thru this progression, notice the result set changes, and continue to note the row count stays the same.  We start with the list of all products.  Our goal is to get a list of the product names, category, subcategory, and model as a human readable phrase.  A product has a name and can have an optional subcategory and optional model.  That means of these two optional foreign keys in Product, either of them could be NULL.

SELECT p.ProductID, p.Name, p.ProductSubcategoryID, p.ProductModelID
	FROM Production.Product p

(504 rows)

Join to ProductModel to get the model name.

SELECT p.ProductID, p.Name, p.ProductSubcategoryID, pm.Name
	FROM Production.Product p
	LEFT JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID

Join to ProductSubcategory to get the subcategory name.

SELECT p.ProductID, p.Name, sc.Name, pm.Name
	FROM Production.Product p
	LEFT JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID
	LEFT JOIN Production.ProductSubcategory sc ON sc.ProductSubcategoryID = p.ProductSubcategoryID

Join to ProductCategory to get the category name. Notice we must continue to use a left join to ProductCategory even though ProductSubcategory.ProductCategoryID may not be NULL.

SELECT p.ProductID, p.Name, sc.Name, c.Name, pm.Name
	FROM Production.Product p
	LEFT JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID
	LEFT JOIN Production.ProductSubcategory sc ON sc.ProductSubcategoryID = p.ProductSubcategoryID
	LEFT JOIN Production.ProductCategory c ON c.ProductCategoryID = sc.ProductCategoryID

Now, let’s build a readable phrase.

SELECT p.ProductID, p.Name + ', a ' + pm.Name + ' model in ' + sc.Name + ' within the ' + c.Name + ' category'
	FROM Production.Product p
	LEFT JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID
	LEFT JOIN Production.ProductSubcategory sc ON sc.ProductSubcategoryID = p.ProductSubcategoryID
	LEFT JOIN Production.ProductCategory c ON c.ProductCategoryID = sc.ProductCategoryID

Hmmmm. Where we had NULL model and subcategory names, the concatenation is still NULL. Let’s use the COALESCE function to fill in the gaps.

SELECT p.ProductID, p.Name + COALESCE(', a ' + pm.Name, ', an unknown') + ' model in ' + COALESCE(sc.Name + ' within the ' + c.Name, ' no specified') + ' category'
	FROM Production.Product p
	LEFT JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID
	LEFT JOIN Production.ProductSubcategory sc ON sc.ProductSubcategoryID = p.ProductSubcategoryID
	LEFT JOIN Production.ProductCategory c ON c.ProductCategoryID = sc.ProductCategoryID

The COALESCE function can accept as many arguments as you like. It tests the first argument for a NULL value. If the it is non-NULL, it will return that value and quit. If it is NULL, it will test the second argument similarly. The last argument will be the default value.

Homework

Generate a list of products that have a bill of material, stating each subcomponent and the quantity used. You should use the Production.BillOfMaterials table and create a human readable phrase.  You might find the STR function useful.  Here are a few representative rows:

A BB Ball Bearing contains      1 Lock Ring
A BB Ball Bearing contains      2 Cone-Shaped Race
A Front Derailleur contains      1 Front Derailleur Cage
Published inUncategorized