Examples are based on AdventureWorks DB. You can find that from MSDN and install that with your express version of sql server 2008. I will post links about how to setup that.
I like the "Cross Apply" command in MS SQL. It gives you access to left side of the join and you can perform operations for each row from left side.
Normally cross join operator performs cartesion product if you don't specify where statement in your query.
Select p.SalesPerson, st.Name as Territory
from Sales.SalesPerson p
cross join Sales.SalesTerritory st
This query will produce total of nxm rows. You don't have access to table p inside table st.
You can do much better with Cross Apply.
1- This will calculate avg qty in orders and total number of orders for each product.
select pt.ProductID, pt.Name, pt.StandardCost, A.num_orders, A.avg_order
from Production.Product pt
select count(distinct sd.SalesOrderID) as num_orders, avg(sd.OrderQty) as avg_order
from Sales.SalesOrderDetail as sd
where sd.ProductID = pt.ProductID
) as A
order by num_orders desc;
2-Latest 3 orders per customer
Select c.CustomerID, A.SalesOrderID, A.orderdate
from Sales.Customer as c
select top(3) O.SalesOrderID, O.SalesPersonID, O.OrderDate,O.DueDate
from sales.SalesOrderHeader as O
where O.CustomerID = c.CustomerID
order by orderdate desc, SalesOrderID desc
) as A;