SQL Code Questions/Iranian Visas

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

SQL Code Questions/Iranian Visas

Author

Topic

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 02:55:55

I was given some SQL Code Scenarios.....Here are some questions asked of me. Is there a quick fix/answer to this? Like just an order by clause? Can anyone provide the answer please? Need quickly if possible.

Thanks......

Example Scenario:

Requirement: Write the SQL required to show the Profit by Region for all of 2008, ordered most to least profitable. Profit is defined as total revenue minus total cost. Use the hypothetical table structure listed below. Assume that primary keys are indicated by the arrows pointing toward a column.

Example Solution:

SELECT
C.Region,
SUM((OL.Price - P.Cost) * OL.Quantity) as Profit
FROM OrderLine OL
JOIN OrderHeader O
ON O.OrderID = OL.OrderID
JOIN Customer C
ON C.CustomerID = O.CustomerID
JOIN Product P
ON P.ProductID = OL.ProductID
WHERE
O.OrderDate BETWEEN '1/1/2008' and '12/31/2008'
GROUP BY
C.Region
ORDER BY 2 DESC

--------------------------------------------
Scenario 1:
Requirement: Write the SQL required to show the total revenue by product line for Credit Card payments over the last 90 days. Credit Card Payment is defined as PaymentType = 'CC'. Order highest to lowest revenue.

--------------------------------------------
Scenario 2:
Requirement: Write the SQL required to show the total # of orders by month by Freight Carrier this year. Order the results by Month, then total orders.

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 03:42:07

You want 2 different queries for two different scenarios??

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 03:47:00

Yes if possible.....is this very hard to do?

Heather

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 03:52:05

Scenario 2
select count(orders)as OrderCount,datename(mm,orderdate)as OrderMonth from ordertable
group by datename(mm,orderdate)
order by datename(mm,orderdate),count(orders)

What about the query you posted ?Is it not working properly

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 04:00:20

That was just an example scenario given to me that I might go by. Have not tried it yet, but your solution is for the 2nd scenario, correct? What about scenario 1?

Heather

btw, where are you from?

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 04:32:32

Idera,

Any ideas to Scenario 1?

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 05:19:27

quote:
Originally posted by HeatherGIS

That was just an example scenario given to me that I might go by. Have not tried it yet, but your solution is for the 2nd scenario, correct? What about scenario 1?

Heather

Yes.
For scenario 1 can u pls post some sample data??

quote:

btw, where are you from?

Any particular reason y u want to know??

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 05:34:00

As far as where you are from, I saw Iran and I hear that riding the train into Tehran with the mtns in the background is absolutely beautiful.

Ok I have no data whatsoever. But I have just been given a table structure. This is like a test they are giving me.

Customer tbl
CustomerID
Name
Region
Industry

Order tbl
OrderID
CustomerID
OrderDate
PaymentType
FreightCarrier

OrderLine tbl
OrderLineID
OrderID
ProductID
Quantity
Price
ExtendedPrice

Product tbl
ProductID
Name
ProductLine
Cost
InventoryOnHand

Obviously joins are of same name between the 4 tables. I assume this might change up scenario 2 as well. Can you please help. I have only matter of time to finish?

Heather

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 05:56:04

Thank god atleast there is someone out there who thinks good about Iran

Scenario 1
SELECT
P.ProductLine,
SUM("I dont know how do u get total revenue") as Total revenue
FROM OrderLine OL
JOIN OrderHeader O
ON O.OrderID = OL.OrderID
JOIN Customer C
ON C.CustomerID = O.CustomerID
JOIN Product P
ON P.ProductID = OL.ProductID
WHERE
O.OrderDate BETWEEN Convert(datetime,CONVERT(varchar(50),getdate(),101) and dateadd(dd,-90,Convert(datetime,CONVERT(varchar(50),getdate(),101)))
and PaymentType='CC'
GROUP BY P.ProductLine
ORDER BY SUM("I dont know how do u get total revenue") DESC

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 06:00:26

Yes I would love to visit. All countries are thought of both good and bad in various ways. I sometimes wished I did not live in the USA. Maybe in a country that was more simple and not so fast-paced. But it could be fast-paced if I wanted it to be......if that makes a lick of sense?

Anyway, would Scenario 2 still be the same as we discussed earlier or would this change it around a bit. I would assume so.

So you never answered where you were from? The capital city or somewhere else?

Heather

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 06:11:27

For ppl from US it is quite tough to visit IR.You have to come through a recognised travle agent only.
Nope I am not from there.I am from Tabriz.Small city in North west of Iran.
Here are som images from my city

http://www.skyscrapercity.com/showthread.php?t=403242

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 06:34:43

The first panorama view of "Sktskraper Cirt" is unbelievable. I especially like the artistry from inside Sayyed Hamzeh Mosque. That is lovely. The pics at nighttime are lovely as well. Based on the altitude, I would most likely compare it to our Denver, Colorado, which is where I hope to be moving to from the dreary southeast of the USA. I love the mountains; can't help it, they are beautiful.

So anyways, should I change up scenario 2 now?

The Total Revenure from Scenario 1 I figured would be OL.Price * OL.Quantity

Thanks so much for the continued help...

Heather

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 06:50:13

Scenario 2
SELECT
P.ProductLine,
SUM(OL.Price * OL.Quantity) as Total revenue
FROM OrderLine OL
JOIN OrderHeader O
ON O.OrderID = OL.OrderID
JOIN Customer C
ON C.CustomerID = O.CustomerID
JOIN Product P
ON P.ProductID = OL.ProductID
WHERE
O.OrderDate BETWEEN Convert(datetime,CONVERT(varchar(50),getdate(),101) and dateadd(dd,-90,Convert(datetime,CONVERT(varchar(50),getdate(),101)))
and PaymentType='CC'
GROUP BY P.ProductLine
ORDER BY SUM(OL.Price * OL.Quantity) DESC

Scenario 2
SELECT
DATENAME(mm,O.OrderDate)as OrderMonth
COUNT(O.OrderID) as Total Orders
FROM OrderLine OL
JOIN OrderHeader O
ON O.OrderID = OL.OrderID
JOIN Customer C
ON C.CustomerID = O.CustomerID
JOIN Product P
ON P.ProductID = OL.ProductID
group by DATENAME(MM,O.OrderDate)
order by DATENAME(MM,O.OrderDate),COUNT(O.OrderID)

Here are some modern faces
http://www.skyscrapercity.com/showthread.php?t=403242&page=4
http://www.skyscrapercity.com/showthread.php?t=403242&page=6

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 07:09:54

the chocolate city of Iran, eh? I like the night life pics and of course of the Maghberatol Shoara. It is a true piece of architecture. Is is dangerous to travel over there as and American right now?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-18 : 07:16:12

quote:
Originally posted by Idera

Thank god atleast there is someone out there who thinks good about Iran

PBUH

I don't know of many people that think bad of Iran or it's people, just the government of Iran. (I can't think of one good government for that matter

)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 07:18:36

No not at all.
If common a person on the street knows you are an american everyone will want to come & speak to you & know more abt america.Here even the state run TV show american films & Tv shows.

I found this piece of information from here http://www.iranvisa.co.uk/#Q12.U can also have a look here http://www.irantour.org/iranvisa.html

12. Are there any specific requirements for US citizens travelling to Iran?

Visas for US passport holders are subject to particular regulations.
At present the Iranian Ministry of Foreign Affairs (MFA) does not allow US passport holders to travel to Iran independently. Americans are required to travel on escorted tours; either as part of a tour group, or on a tailor-made individual tour. An exact itinerary, to which you must adhere, must be submitted in advance.

But I guess this is not a right place to spk abt all this.It is an SQL forum.Maybe the moderators wont like it :)

PBUH

HeatherGIS
Starting Member

9 Posts

Posted - 2009-08-18 : 07:24:26

Heather

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 07:29:52

quote:
Originally posted by DonAtWork

quote:
Originally posted by Idera

Thank god atleast there is someone out there who thinks good about Iran

PBUH

I don't know of many people that think bad of Iran or it's people, just the government of Iran. (I can't think of one good government for that matter )

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Here to it is the same.We hate american government & its policies towards us.Iranians have suffered so much during the Iran Iraq war.Now also they continue to suffer.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources