In this puzzle, we're going to learn how to work with the AdventureWorks Bill of Materials table. Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product.
Solving puzzles is a great way to learn SQL. Nothing beats practicing what you've learned. Once you have figured out the puzzle, post your answer in the comments so we all can learn from one another. We also discuss the puzzle and more in Essential SQL Learning Group on Facebook. Be sure to find us there!
Using the AdventureWorks
database and diagram above, provide queries for the following:
- Write a query to count the number of products not listed in
BillOfMaterials
- List Product IDs, including
Names
and ProductNumbers
, that have no subcomponents - List Product IDs, including
Names
and ProductNumbers
, that aren’t in a subcomponent - List Product IDs, including
Names
and ProductNumbers
, that are a subcomponent
If you need help understanding how a BOM works, please check out the following diagram:
- The items in yellow are products that aren't in a subcomponent.
- The items in green are sub components of the
Bike
product. - The items in green are also product assemblies, as there are other components that are used to make them.
- The items in blue are parts that don't have any subcomponents.
Note: If you're wondering how to work with the AdventureWorks
database, then check out my Getting Started with SQL guide. It helps you get a free version of SQL Server all setup with AdventureWorks
. You'll be ready to learn SQL Server in no time!
Write a Query to Count the Number of Products not Listed in BillOfMaterials
To find out the number of products not listed in the bill of materials, we need to find out how many products are in the aren't listed in the BOM as a product assembly or component.
One way to do this is to use a subquery in the where
clause and look for ProductID
references that don't exist in either the BillofMaterial
table ProductAssemblyID
or ComponentID
columns (green text).
You'll see the results are also restricted to products current being sold and not discontinued (blue text).
The query to do so is:
SELECT Count(1)
FROM Production.Product P
WHERE P.SellEndDate is NULL
AND p.DiscontinuedDate is NULL
AND NOT EXISTS
(SELECT 1
FROM Production.BillOfMaterials BOM
WHERE BOM.ProductAssemblyID = p.ProductID
OR BOM.ComponentID = p.ProductID
)
This returns the scalar value 157
.
The subquery is correlated with the main query via ProductID
. Meaning, for each Product
, a query is run to find BOM entries whose ComponentID
or ProductAssemblyID
matches the Product.ProductID
. You can learn more about these subqueries in my article Subqueries in the Where Clause.
List Product IDs, including Names and ProductNumbers, that have no subcomponents
To answer this question, we take our query from the previous answer, alter it to display product column values, rather than a row count.
Since a subcomponent is any product used within another, we can easily infer that a product doesn't have subcomponents if the product isn't listed in the BOM
table as an assembly. You'll see we used a subquery (green text) to search for product assemblies. The NOT EXISTS
returns TRUE
if no ProductAssemblyID
s are found.
SELECT P.ProductID,
P.Name,
P.ProductNumber,
P.FinishedGoodsFlag,
P.ListPrice
FROM Production.Product P
WHERE P.SellEndDate is NULL
AND p.DiscontinuedDate is NULL
AND NOT EXISTS (SELECT 1
FROM Production.BillOfMaterials BOM
WHERE P.ProductID = BOM.ProductAssemblyID)
List Product IDs, including Names and ProductNumbers, that aren’t in a subcomponent
The main difference in answering this question versus the previous lies in the subquery used to probe the BillOfMaterials
table.
For this question, we're concerned whether it is not a subcomponent. To do so, we alter our subquery to search for BOM entries whose ComponentID
matched the ProductID
. Finding a match here signifies our product is a subcomponent. Of course, we're looking for the opposite, so we use the NOT EXISTS
qualifier to make it so.
SELECT P.ProductID,
P.Name,
P.ProductNumber,
P.FinishedGoodsFlag,
P.ListPrice
FROM Production.Product P
WHERE P.SellEndDate is NULL
AND p.DiscontinuedDate is NULL
AND NOT EXISTS (SELECT 1
FROM Production.BillOfMaterials BOM
WHERE P.ProductID = BOM.ComponentID)
List Product IDs, including Names and ProductNumbers, that are a subcomponent
The answer to this question is just the opposite to that of the previous one. So, in this case, the subquery searches for BOM entries whose ComponentID
matched the ProductID
. When found, the EXISTS
returns TRUE
and the product is included in the result.
SELECT P.ProductID,
P.Name,
P.ProductNumber,
P.FinishedGoodsFlag,
P.ListPrice
FROM Production.Product P
WHERE P.SellEndDate is NULL
AND p.DiscontinuedDate is NULL
AND EXISTS (SELECT 1
FROM Production.BillOfMaterials BOM
WHERE P.ProductID = BOM.ComponentID
)
Final Comments on AdventureWorks Bill of Materials
You may be wondering why I use subqueries in my answers rather than joins. I did so since I think in this sort of problem, the subquery is easier to read. Since we're testing for existence, the EXISTS
clause lends nicely to this, and may be easier to interpret.
Also, by using subqueries, I'm also including products that don't have any entries in the BOM
table. If my answer used INNER JOINS, then by virtue they only include matching rows, these products which don't have bill of materials would have been excluded.
Of course, I could have used Outer JOIN
S and tested for NULL
to skirt the issue, but for some reason I try to avoid outer join
s when I can. That's just my preference, and not necessarily a best practice.
The post Work with the AdventureWorks Bill of Materials using Subqueries appeared first on Essential SQL.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/