Transcript Lecture 3 solution

IS6126 Databases for Management
Information Systems
Lecture 3: SQL III – Solutions
Rob Gleasure
[email protected]
robgleasure.com
Exercise Solution
1.
We want to create a new table in our database called Inventory with
the following criteria
I.
Three columns for Part_ID, Part_Name, and Part_of_Product
II.
All three of these columns set to not null
III.
The Part_ID set as primary key
IV.
The Part_of_Product set as a foreign key to
Products(ProductID)
V.
The default Part_Name is ‘Phalange’

CREATE TABLE Inventory (
Part_ID int PRIMARY KEY,
Part_name varchar (50) NOT NULL DEFAULT 'Phalange',
Part_of_Product varchar(255) NOT NULL ,
CONSTRAINT fk FOREIGN KEY (Part_of_Product )
REFERENCES Products(ProductID)
);
Exercise Solution
Run the following query to see what the database looks like
INSERT INTO Inventory (Part_ID,Part_of_Product) VALUES ('1', '4');
INSERT INTO Inventory (Part_ID,Part_of_Product,Part_Name) VALUES
('3', '5', 'Cogs');
2.
We want to create an index for the Part_Name, so that this can be
searched independently?
CREATE UNIQUE INDEX PtNm_index ON Inventory(Part_Name);
3.
4.
We want to add a column to Inventory called Cost with type
varchar(20)?
ALTER TABLE Inventory ADD Cost varchar(20);
We want to create a view [Phalanges] showing where Part_ID,
Part_of_Product, and Cost where the Part_Name is ‘Phalange’?
CREATE VIEW [Phalanges] AS SELECT Part_ID,
Part_of_Product, Cost FROM Inventory WHERE
Part_name='Phalange';