CREATE TABLE Manufacturers (

Code INTEGER,

Name VARCHAR(255) NOT NULL,

PRIMARY KEY (Code)

);

CREATE TABLE Products (

Code INTEGER,

Name VARCHAR(255) NOT NULL ,

Price DECIMAL NOT NULL ,

Manufacturer INTEGER NOT NULL,

PRIMARY KEY (Code),

FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)

) ;

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');

INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');

INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');

INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');

INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');

INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);

Answer the following questions:

1. Explain why and when stored procedures are useful?

2. Working on HW3 database, perform following tasks:

a. Retrieve all columns for Sony manufacture (code=1) from Manufacture table.

b. Create a SQL stored procedure and call it SP_HW4SP1. Copy and paste the query you wrote in a inside the body of SP_HW4SP1.

c. Call the SP_HW4SP1 using EXEC function.

d. Create a SQL stored procedure and call it SP_HW4SP2 with a manufacture code argument (e.g., @Manu_Code).

e. Call the SP_HW4SP2

3. Create a new database call it HW3_TR and perform the following task:

a. Create a student table with two columns (Student_id, Student_name)

b. Create another table call it student_BACKUP table with two columns (Student_id, Student_name).

c. Create a trigger call it TR_AfterInsert_Student. This trigger will be fired if a new record gets inserted on the student table and then store the new inserted a record on the student backup table. Hint: use the following query inside the body of the trigger:

BEGIN

INSERT INTO STUDENT_BACKUP

SELECT * FROM INSERTED

END