CREATE TABLE Manufacturers ( Code INTEGER, Name VARCHAR(255) NOT NULL, PRIMARY K
ID: 3719689 • Letter: C
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