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
CREATE PROCEDURE P
DROP EXISTING RedSpending
SELECT * FROM CREATEDVIEW
Should I update the existing RedSpending view based on the <color> parameter (is this even possible?)
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>
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..
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?
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.