If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Another thing I am concerned about is that in the line INNER JOIN Order ON Customer.customer_id = Order.customer_id , I have written Customer.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign?

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

Everything is wrong! A table is a set, so the _name is a plural or collective noun. I used a credit card number for the customer_id. I follow USPS rules for the address data. I included some of the constraints needed, so this is a minimal skeleton.

Did you know that DATE is a reserved word in SQL? And it is too generic to be used anyway!
What is the vague adjective “active”? Surely you did not use a bit flag in SQL! What is discount? A rate? an amount? A discount class? Until we have specs, I am dropping them:

This is a common idiom in SQL and you should have seen it in your data modeling class. Putting the total in the Orders table is called a “non-normal form redundancy” and it is common among noobs with a COBOL background. Since you put one column per line, like a punch card, I will guess you are an old COBOL programmer or were trained by one.

>> Could you tell me if my query is correct from efficiency point of view: <<

Correct and effective are not the same thing. Your design is wrong. Your query did no totals.

SELECT C.first_name, C.last_name,
SUM(D.unit_price * order_qty) AS cusomer_order_tot
FROM Customers AS C, Order_Details AS
WHERE C.customer_id = D.customer_id
AND C.customer_id = '4857'
GROUP BY C.first_name, C.last_name;

>> INNER JOIN Order ON C.customer_id = D.customer_id*, I have written C.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign? <<

There is no difference. Notice that I did not use the infixed inner join syntax; there is also no difference with it. However, the infixed syntax shows a linear mindset rather than a declarative programmer's mindset. That ids a bit harder to understand but it is a guide I uzse to look for errors when I am correcting SQL.

the SQL engine will not care which side your column is on in an inner join with regards to performance.

If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

Celko is correct that you need a GROUP BY to aggregate your totals.
The only other thing I would suggest is that your query will return no records at all if the customer has no orders, when perhaps returning $0 would be better.
You can do this by left outer joining to your Orders table and using coalesce(SUM(Order.total_price), 0) for your calculation.