By using this table:
CREATE TABLE Part (
Partid int IDENTITY PRIMARY KEY,
Supplierid int NOT NULL
REFERENCES Part (PartID),
Description VARCHAR (25) NOT NULL,
Count int NOT NULL,
Class VARCHAR (5) 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.
Which part or concept or keyword are you having problems with?
For the first one I know that I have to the keyword Group By. I just don't know how to set it up. I been trying to find tutorials and examples to help me but they are even more confusing.
OK. Do you have a table to test on? Here's a statement that might get you going:
SELECT Supplierid, SUM(Count)
FROM Part
WHERE Count > PARAMETER
GROUP BY Supplierid
If you just run this against your table, substituting some number in for PARAMETER for now, you should get the expected result.
When you've got comfortable with that, you can code it into a stored procedure.
1- To create a stored procedure that selects the supplierId field and the total count for each group of supplierId's from the Part table, and only returns rows with a count total greater than a specified parameter value, you can use the following SQL statement:
```
CREATE PROCEDURE GetSuppliersWithTotalCountGreaterThan
@countThreshold INT
AS
BEGIN
SELECT Supplierid, SUM(Count) AS TotalCount
FROM Part
GROUP BY Supplierid
HAVING SUM(Count) > @countThreshold
END
```
2- To create a stored procedure that adds a new record to the Part table and returns the newly created PartID value in an output parameter, you can use the following SQL statement:
```
CREATE PROCEDURE AddNewPart
@Supplierid INT,
@Description VARCHAR(25),
@Count INT,
@Class VARCHAR(5),
@Inspection SMALLDATETIME,
@NewPartID INT OUTPUT
AS
BEGIN
INSERT INTO Part (Supplierid, Description, Count, Class, Inspection)
VALUES (@Supplierid, @Description, @Count, @Class, @Inspection)
SET @NewPartID = SCOPE_IDENTITY()
END
```
3- To create a user-defined function that returns the difference between the highest and lowest count field values from the Part table, you can use the following SQL statement:
```
CREATE FUNCTION CalculateCountDifference()
RETURNS INT
AS
BEGIN
DECLARE @MaxCount INT
DECLARE @MinCount INT
SELECT @MaxCount = MAX(Count), @MinCount = MIN(Count)
FROM Part
RETURN @MaxCount - @MinCount
END
```
4- To create an update trigger on the Part table that raises an error if the Count field of the updated row has a higher value than the previous value, and rolls back the update operation, you can use the following SQL statement:
```
CREATE TRIGGER UpdateCountTrigger
ON Part
AFTER UPDATE
AS
BEGIN
IF UPDATE(Count) -- Only trigger if the Count field is updated
BEGIN
IF EXISTS (
SELECT 1
FROM inserted i
INNER JOIN deleted d ON i.Partid = d.Partid
WHERE i.Count > d.Count
)
BEGIN
RAISERROR('The new Count value cannot be larger than the old value.', 16, 1)
ROLLBACK TRANSACTION
END
END
END
```
Please note that these SQL statements are examples and may need to be modified based on your specific requirements and database schema.