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
Exercise instructions
- Define
PartID
asPRIMARY KEY
of typeINT
. - Define
Cost
of typeINT
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;