title: SQL query: Get the most expensive product ordered in each month of 2011
body: <p>I don't understand how to "combine" two tables so that I can generate an answer to this.</p>
  
  <p>Basically I have a CAP database and I'm only concerned with two tables:</p>
  
  <pre><code>ORDERS (ordno, month, year, cid, aid, pid, qty, dollars)
  PRODUCTS (pid, pname, city, quantity, price)
  </code></pre>
  
  <p>I know that i need to generate a new table like the following to get the answer:</p>
  
  <pre><code>Temp (month, year, pname, price)
  </code></pre>
  
  <p>The way I tried to do it is:</p>
  
  <pre><code>Temp = Select * from (
  
  (Select month, year from Orders)
  
  join
  
  (Select price, pname from Products)
  
  on
  
  orders.pid = products.pid )
  </code></pre>
  
  <p>The above query gives me an error that I don't understand: Every derived table must have its own alias.</p>
  
  <p>The next step i'm pretty sure is:</p>
  
  <pre><code>Answer = select max(price), pname, month from Temp where year = 2011 group by month;
  </code></pre>
  
  <p>I am very new to SQL. If you guys could help me that would be great. If there is a website that can help me learn SQL better please let me know. Thanks!</p>
  
  <p>EDIT: also if there is more than one product that has max price then you need to list all of them for that month.</p>
  
  <p>EDIT 2: SOLVED THANKS!!</p>