May 22, 2015

Homework Help: SQL

Posted by Robert on Friday, April 15, 2011 at 6:12pm.

1- A SQL statement is needed to create a stored procedure that has one input parameter of data type int. The stored proc selects the supplierId field and the total, or sum, of all Count field values for each group of supplierId fields from the Part table. The Part table was defined in the assignment of week 3. 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- A SQL statement is needed to create a stored procedure, which adds a new record to the Part table of week 3. The stored proc returns an out parameter that contains the value of the PartID PK of the record just added. Input parameters are needed to pass the field values of the record to be added. Keep in mind that the PK field in the Part table of week 3 is an idendity field, which SQL Server creates values for. The identity PK field is managed by SQL Server.

3- A SQL statement that creates a user defined function that returns an int value is needed. This int return value is obtained by deducting the lowest Count field value from the highest Count field value of the Part table. You should enter sample test records in the Part table for testing this function in SQL Server.

4- An Update trigger on the Part table is needed, which 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