SQL for Business

posted by .

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.

  • SQL for Business -

    Which part or concept or keyword are you having problems with?

  • SQL for Business -

    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.

  • SQL for Business -

    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.

Respond to this Question

First Name
School Subject
Your Answer

Similar Questions

  1. Java

    I am supposed to convert a program i already made into a Java console application that uses the Scanner class to read the user's input. This is the code i have that i need to convert. I don't know where to start. Here is the code import …
  2. sql

    By using this table below I need to 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 …
  3. statistics

    Use the given information to find the P-value. Also, use a 0.05 significance level and state the conclusion about the null hypothesis (reject the null hypothesis or fail to reject the null hypothesis). 1) Vvith H1: p * 0.733, the test …
  4. SQL

    Due today Submit a text file (extension .txt). 1- Write a statement that creates a table named Part, with an Id field as an int idendity PK, a SupplierId int field, a Description string field of size 25, a Count int field, a Class …
  5. SQL

    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 …
  6. SQL server help!

    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 …
  7. SQL for business help

    I have no idea where to begin with this. 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 …
  8. computer science

    i have two numbers... 1564656 1981676 i want to check if the first and the last number from a string of these numbers are the same or not...how would i do that. how would i just compare the first and a last number and ignore the other …
  9. c++ program

    how can a modfiy this source code to a menu. #include <stdio.h> #include <stdlib.h> #include <time.h> main() { char cYesNo = '\0'; int iResp1 = 0; int iResp2 = 0; int iResp3 = 0; int iElaspedTime = 0; int iCurrentTime …
  10. computer science

    int *reverse(const int *, int); int *temp; temp = reverse(arr1,N); int *reverse(const int *arr1, int N) { int *arr2; arr2 = new int[N]; for(int count = 0; count < N; count++) { for(int index = (N-1); index <= 0; index--) arr2[count] …

More Similar Questions