MS SQL query problem, PLEASE HELP ME, Thanks!!

A

auerbach

New Member
I have the following questions that i need the help of you guys to code in MS SQL:

Table Name: ABC

Order No. Product Quantity Price Date-Sold
A123 C0001 100 50.50 13-May-2008
A123 C0002 30 20.00 13-May-2008
A123 C0006 20 10.80 13-May-2008
A124 C0002 101 100.20 19-Jul-2008
A124 C0006 200 9.00 19-Jul-2008
A333 C0006 500 8.00 20-Aug-2008

(a) Create the above table defining the data type and length for each attribute.

(b) Add a new row of record for new order, product number C0002 and order number A124 and quantity 300 and date 30-Jan-2010.

(c) There is a change in quantity from 101 to 108 pieces and date sold from 19-Jul-2008 to 31-Dec-2010 for product number C0002 and order number A124.

(d) Retrieve all ABC table details where the quantity is over 100 and sort the records in descending order of quantity ordered.

Your help is greatly appreciated.
 
A

auerbach

New Member
thanks for those links.

as for (a), I am still unclear how one can integrate create table and insert into a single query.

As for (c), do i use "SET quantity=quantity+7, date-sold='......' WHERE Order_No='A124' AND Product='C0002" or "SET quantity=(quantity+7), date-sold='....' WHERE Order_No='A124' AND Product='C0002" or do i just include the the changed value like"SET quantity=108, date-sold='....' WHERE Order_No='A124' AND Product='C0002?

Sorry to ask such basic questions as I have just started learning SQL for less than a week.
 
Last edited:
A

auerbach

New Member
For question (a) is if i have to input many values to create the table, how do i do so in one query without the need to make several inserts?
 
strollin

strollin

Well-Known Member
Typically you create the table and once it's created you do a dataload using whatever dataload utility that came with the your db system. The data is usually in a text or CSV file that may have been exported from another system. Here's a link that shows an example of creating a table and doing a BULK INSERT from a CSV file: sql-server-import-csv-file-into-sql-server-using-bulk

As for (c), do i use "SET quantity=quantity+7, date-sold='......' WHERE Order_No='A124' AND Product='C0002" or "SET quantity=(quantity+7), date-sold='....' WHERE Order_No='A124' AND Product='C0002" or do i just include the the changed value like"SET quantity=108, date-sold='....' WHERE Order_No='A124' AND Product='C0002?
Click to expand...

It's really up to you whether you directly set the value or do some math function to arrive at the same quantity. For simple queries like these where you are just updating a couple of values, it's easier to just say "SET quantity=108,....".
 
Last edited:
B

Blandnuts

New Member
When is this due? From looking over it I think I can help you out...but I have other things I need to get to first.
 
strollin

strollin

Well-Known Member
Not sure what additional help the OP might need other than for someone to actually write the queries.
 
You must log in or register to reply here.
Top