November 26, 2015

Homework Help: SQL for Business

Posted by kat on Saturday, October 31, 2009 at 1:42pm.

By using this table:

Supplierid int NOT NULL
Description VARCHAR (25) NOT NULL,
Count int NOT NULL,
Inspection smalldatetime NOT NULL);

I need to write the following SQL statements: If it helps I am using MS Server 2008.

1- Write a SQL statement that creates a stored procedure with one int input parameter. The stored procedure selects the

supplierId field and the total of all Count field values for each group of supplierId's from the Part table. Only rows with their Count total, or sum, greater than the value specified in the input

parameter should be returned. Hint: Each returned record has a unique SupplierID value, since we are dealing with groups.

2- Write a SQL statement that creates a stored procedure that adds a new record to the Part table, and returns the value of

the newly created PartID PK in an out parameter. The field values of the record to be added should be passed into the

stored proc as input parameters. The assumption here is that the Part table has its PartID PK field as an idendity field,

or surrogate PK, meaning a numeric field that is auto-generated by the db system.

3- Write a SQL statement that creates a user defined function that returns an int value. This int return value is obtained

by subtracting the lowest Count field value from the highest Count field value of the Part table. You could enter dummy

test records in the Part table if you are running/testing this function in SQL Server.

4- Create an Update trigger on the Part table that raises an error string if the Count field of the updated row has a

higher value than the value prior to the Update. In other words, the new value cannot be larger than the older value. If

the trigger fires and after the error string is displayed, the Update operation should be rolled back. The exact text of

the error string is up to you to define.

Answer this Question

First Name:
School Subject:

Related Questions

More Related Questions