New to SQL - Please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New to SQL - Please help

Author

Topic

kc212
Starting Member

4 Posts

Posted - 2010-03-10 : 12:55:26

I'm having trouble writing SQL queries relating to this image (ERD).

http://www.FreeImageHut.com/view.php?file=23450-ivrgjbit.jpg

For example:

"Write an SQL Command to display each item ordered for order number 1001, its standard price, and the total price for each item ordered."

There is no "total price" attribute associated with product though?
Nor is there an "item ordered" attribute. Additionally, how am I suppose to extract data from order and product table with one SQL command?

For instance if I answer:

SELECT ORDER_ID, STANDARD_PRICE, TOTAL_PRICE
FROM PRODUCT_t
WHERE ORDER_ID = 1001

I'm only referencing PRODUCT_t and not ORDER_t

I hope this is clear - I'm very confused. Please help!

Thank you...

Katrina **

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 12:59:47

You have to JOIN Order_line_t via Product_ID.
Is this a homework?
Do you know anything about joining tables?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:02:20

its pretty obvious that this is homework

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kc212
Starting Member

4 Posts

Posted - 2010-03-10 : 13:08:02

Yes it is homework but I don't require an exact answer, just a better understanding.

We have not learned JOIN yet but only the basics... select, update, delete, insert

Is the JOIN command the only option here?

kc212
Starting Member

4 Posts

Posted - 2010-03-10 : 13:44:38

Are you unwilling to help me if it's homework?
I just need some guidance, please!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 14:55:19

No - not unwilling.
Wait a moment and I will come up with 2 different solutions.
One solution using JOIN and another one using subselect.

But I don't understand how this homework comes up without giving you the needed "how to" at a first step.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 15:12:50

et voilá:

-- Here begins the solution using JOIN
select p.Product_id,
       p.standard_Price,
       p.Standard_Price * o.ordered_Quantity as TotalPrice
from Order_line_t as o
join PRODUCT_t as p on p.Product_id=o.Product_id
where o.Order_id=1001

-- Here begins the solution using subselect
select o.Product_id,
       (select p.Standard_Price from PRODUCT_t as p where p.Product_id=o.Product_id) as Standard_Price,
       o.ordered_Quantity * (select p.Standard_Price from PRODUCT_t as p where p.Product_id=o.Product_id) as TotalPrice
from Order_line_t as o
where o.Order_id=1001

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 15:24:14

If your teacher comes up with a different solution then please show us!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources