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.
If you are working on PL/SQL (from Oracle), you can specify it.
In addition, you need to specify what your proposed solution is, and what difficulties you are encountering.
To be able to help you, we also need the specifications of the Part Table, name/type of the fields, size, etc.
MrMath, please do not post any answers to this question. My daughter, thinking she would help me with homework posted this. I appreciate your time but do not need any assistance as I have a good understanding of the material. I have asked the site to delete this post. Sorry for wasting your time.
ok, no problem.
Thank you sir:)
1- To create a stored procedure with one input parameter of data type int that selects the supplierId field and the sum of the Count field values for each group of supplierId fields from the Part table, you can use the following SQL statement:
```sql
CREATE PROCEDURE GetSupplierCount
@Threshold INT
AS
BEGIN
SELECT supplierId, SUM(Count) AS TotalCount
FROM Part
GROUP BY supplierId
HAVING SUM(Count) > @Threshold
END
```
This statement creates a stored procedure named "GetSupplierCount" with an input parameter "@Threshold" of type int. Inside the stored procedure, it selects the supplierId field and the sum of the Count field values for each group of supplierId fields from the Part table. The HAVING clause ensures that only groups with a total count greater than the input parameter value are returned.
2- To create a stored procedure that adds a new record to the Part table and returns the value of the PartID PK of the record just added, you can use the following SQL statement:
```sql
CREATE PROCEDURE AddPart
@Field1 VARCHAR(50),
@Field2 VARCHAR(50),
@PartId INT OUTPUT
AS
BEGIN
INSERT INTO Part (Field1, Field2)
VALUES (@Field1, @Field2)
SELECT @PartId = SCOPE_IDENTITY()
END
```
In this statement, a stored procedure named "AddPart" is created with three input parameters: @Field1 and @Field2 to pass the field values of the record to be added, and @PartId as an output parameter to store the generated PartID value. Inside the stored procedure, an INSERT statement is used to add a new record to the Part table. The SELECT statement with SCOPE_IDENTITY() assigns the generated PartID value to the @PartId output parameter.
3- To create a user-defined function (UDF) that returns an int value by deducting the lowest Count field value from the highest Count field value of the Part table, you can use the following SQL statement:
```sql
CREATE FUNCTION CalculateCountDifference()
RETURNS INT
AS
BEGIN
DECLARE @MaxCount INT, @MinCount INT, @Difference INT
SELECT @MaxCount = MAX(Count), @MinCount = MIN(Count)
FROM Part
SET @Difference = @MaxCount - @MinCount
RETURN @Difference
END
```
This statement creates a UDF named "CalculateCountDifference" that returns an int value. Inside the UDF, two variables (@MaxCount and @MinCount) are declared to store the maximum and minimum Count field values from the Part table. The SELECT statement retrieves these values using the MAX() and MIN() aggregate functions. The @Difference variable is then calculated by subtracting the @MinCount value from the @MaxCount value. Finally, the function returns the @Difference value.
4- To 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, and rolls back the update operation, you can use the following SQL statement:
```sql
CREATE TRIGGER CheckCountUpdate
ON Part
AFTER UPDATE
AS
BEGIN
IF UPDATE(Count)
BEGIN
IF EXISTS (
SELECT *
FROM inserted i
INNER JOIN deleted d ON i.PartID = d.PartID
WHERE i.Count > d.Count
)
BEGIN
RAISERROR ('Count cannot be increased.', 16, 1)
ROLLBACK TRANSACTION
END
END
END
```
In this statement, a trigger named "CheckCountUpdate" is created on the Part table, to be executed after an update operation. The trigger checks if the Count field is being updated by using the UPDATE() function. If it is, the trigger compares the Count values of the inserted (new) and deleted (old) rows. If any inserted Count value is higher than the corresponding deleted Count value, it raises an error message using the RAISERROR function and rolls back the transaction using ROLLBACK TRANSACTION.