title: Finding the highest average salary of a department using joins in SQL?
body: <p>I have two tables with the below schema:</p>
  
  <pre><code>Table 1
  -------
  empID
  empName
  
  
  Table 2
  -------
  empID 
  department  
  salary
  </code></pre>
  
  <p>Assuming the tables are:
  Table1:</p>
  
  <pre><code>empID|empName
  1    A
  2    B
  3    C
  4    D
  5    E
  6    F
  7    G
  8    H
  9    I
  10   J
  </code></pre>
  
  <p>Table 2:</p>
  
  <pre><code>empID|department|salary
  1    X    10
  2    X    10
  3    X    10
  4    Y    5
  5    Y    5
  6    Y    5
  7    Y    5
  8    Y    5
  9    Z    3
  10   Z    3
  </code></pre>
  
  <p>I need to find the department name with the highest average salary and display them along with the employee names. </p>
  
  <p>The output I am expecting is:</p>
  
  <pre><code>empName|department|salary
  A         10        
  B    X    10
  C         10
  </code></pre>
  
  <p>This was an interview question, and I am recreating this from memory so it might not be perfect. I am also picking up SQL after a gap of more than 2 years. Please suggest if I am missing something. </p>
  
  <p>The query that I have formed is:</p>
  
  <pre><code>SELECT
      table1.empName,
      TOP(1) AVG(table2.salary),
      table2.department
  FROM
      table1
  INNER JOIN
      table2
      ON table1.empID = table2.empID
  GROUP BY
      table2.department
  </code></pre>