Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Stored Procedures and views

    Hi,
    Im working on a school assignment based on the AdventureWorksLT database in sql server 2008

    The assignment asks me to do this
    1. Write SQL code that finds customers who have bought a product of color red.

    2. Write SQL code that creates a view RedSpending on the Customer table with the following specifications.
    a. The fields in the view are Customer_ID, which is the primary key, FirstName, LastName, EmailAddress, HighestPrice
    b. The customers in the view should be all and only those who have bought a product of color Red.
    c. HighestPrice should contain the highest unit price that they have paid for some red product (see SalesOrderDetail; ignore UnitPriceDiscount

    3. Write a small application that does the following when run.
    i. Display on the screen all and only colors of products in the Product table (including “Multi” but excluding Null). They should be sorted in alphabetical order.
    ii. Accept a single string input <color> from the keyboard. This will be one of the colours.

    iii. Compute a view table for that <color>, where the view tables meets the criteria a,b,c for that colour. The application should write the result to the screen as plain comma separated text. The table should be sorted in ascending order by the primary key Customer_ID.
    Basically Ive done all the bolded part.
    Im wondering how to tackle the last part
    My plan is to return the view into a ResultSet by calling a stored procedure
    but I dont know what the assignment means exactly by "compute a view table"
    Should I drop the existing RedSpending view and make a new one inside my stored procedure based on the <color> parameter and then return the new view. Something like this
    Code:
    CREATE PROCEDURE P
    PARAMCOLOR
    DROP EXISTING RedSpending
    CREATE VIEW
    ...
    SELECT * FROM CREATEDVIEW
    Should I update the existing RedSpending view based on the <color> parameter (is this even possible?)
    Code:
    CREATE PROCEDURE
    PARAMCOLOR
    UPDATE VIEW based on PARAM COLOR
    SELECT * FROM VIEW
    Should I make one giant view for all the colors and just select the tuples matching <color>
    Code:
    CREATE PROCEDURE
    PARAMCOLOR
    SELECT * FROM VIEW WHERE Color = PARAMCOLOR
    For this last method I would need an extra column for Color in the view which doesnt match the specifications in the question..
    Last edited by asuchar; 07-19-11 at 03:02.

  2. #2
    Join Date
    Jul 2011
    Posts
    14
    I don't understand 3iii. I've never heard of a "view table" nor the creation of either a view or table being called a computation. The wording seems to imply that you should create a view based on the color selected by the user, but that doesn't make any sense: what if two clients ran that code around the same time? Not to mention modifying the database structure to serve up such a simple query..

    If the user is asking for information about products sold in a particular color, I'd just tailor the query to that, something like your third example. That seems to be the most logical way to serve up the request.. I have no idea why the wording in the instructions is so weird. Maybe you can contact your professor to get clarification on that?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The "View Table" is probably some sort of construct in the application language you are using to build the "small application". I think what they want is for that application to show items that have the color that was entered in item 2.

  4. #4
    Join Date
    Jul 2011
    Posts
    2
    Thanks for the replies guys,
    @MCrowley the language is weird but the instructor wanted us to practice views in sql
    @Brian I ended up doing it the 3rd way because of the situation that you described

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •