CREATE TABLE Manufacturers ( Code INTEGER, Name VARCHAR(255) NOT NULL, PRIMARY K

Question

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

CREATE TABLE Manufacturers (

Explanation / Answer

--1. Explain why and when stored procedures are useful?
Helps in faster execution(Pre executed query),reduce network traffic.
--2. Working on HW3 database, perform following tasks:

/*a. Retrieve all columns for Sony manufacture (code=1) from Manufacture table.*/
SELECT * FROM Manufacturers WHERE Code=1;

/*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.*/
CREATE PROCEDURE SP_HW4SP1
AS
BEGIN
SELECT * FROM Manufacturers WHERE Code=1;
END

--c. Call the SP_HW4SP1 using EXEC function.
EXEC SP_HW4SP1

--d. Create a SQL stored procedure and call it SP_HW4SP2 with a manufacture code argument (e.g., @Manu_Code).
Create Procedure SP_HW4SP2
@Manu_Code INT
AS
BEGIN
SELECT * FROM Manufacturers WHERE Code=@Manu_Code;
END

--e. Call the SP_HW4SP2

EXEC SP_HW4SP2 @Manu_Code =1;

--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)
CREATE TABLE Student(Student_id INTEGER NOT NULL, Student_name VARCHAR(20));
--b. Create another table call it student_BACKUP table with two columns (Student_id, Student_name).
SELECT * INTO student_BACKUP FROM Student;

/*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: */
CREATE TRIGGER TR_AfterInsert_Student ON student_BACKUP
FOR INSERT AS
BEGIN

INSERT INTO STUDENT_BACKUP(Student_id,Student_name) SELECT (Student_id,Student_name) FROM INSERTED

END

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.