Create the parts list

The first step in creating a bill of material is to define a hierarchical data model. To do this, you have to create a table BillOfMaterial with the following fields:

The BillOfMaterial table describes the following hierarchy:

Your task is to define the field PartID as primary key, to define the field Cost, and to insert the following to records into the table:

Component: SUV, Title: BMW X1, Vendor: BMW, ProductKey: F48, Cost: 50000, Quantity: 1
Component: Wheels, Title: M-Performance 19/255, Vendor: BMW, ProductKey: MKQ134098URZ, Cost: 400, Quantity: 4

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

Define PartID as PRIMARY KEY of type INT.
Define Cost of type INT and not to be NULL.
Insert the root element SUV as described in the context section.
Insert the entry Wheels as described in the context section.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

CREATE TABLE Bill_Of_Material (
	-- Define PartID as primary key of type INT
  	___ ___ NOT NULL ___ KEY,
	SubPartID INT,
	Component VARCHAR(255) NOT NULL,
	Title  VARCHAR(255) NOT NULL,
	Vendor VARCHAR(255) NOT NULL,
  	ProductKey CHAR(32) NOT NULL,
  	-- Define Cost of type INT and NOT NULL
  	___ ___ ___ ___,
	Quantity INT NOT NULL);

-- Insert the root element SUV as described
INSERT INTO Bill_Of_Material
VALUES (1,NULL,'___','BMW X1','___','___',___,1);
INSERT INTO Bill_Of_Material
VALUES (2,1,'Engine','V6BiTurbro','BMW','EV3891ASF',3000,1);
INSERT INTO Bill_Of_Material
VALUES (3,1,'Body','AL_Race_Body','BMW','BD39281PUO',5000,1);
INSERT INTO Bill_Of_Material
VALUES (4,1,'Interior Decoration','All_Leather_Brown','BMW','ZEU198292',2500,1);
-- Insert the entry Wheels as described
INSERT INTO Bill_Of_Material
VALUES (___,___,'Wheels','M-Performance 19/255',___,___,___,___);

SELECT * 
FROM Bill_Of_Material;

Edit and Run Code