SQL Interview Preparation (in the context of MS SQL Server)

1. Inner join on multiple columns

Suppose we have two tables in place as below:

Joining them on F1 and F4 will give you:

And join them on both F1=F4 and F2=F5, you will get:

Refer to: http://sqlfiddle.com/#!3/cec20/2

2. The anwsers to the questions from Jitbit:

• List employees (names) who have a bigger salary than their boss

  SELECT subordinates.name
  FROM [dbo].[employees] subordinates INNER JOIN [dbo].[employees] boss
  ON subordinates.bossid = boss.id
  WHERE subordinates.salary > boss.salary

• List employees who have the biggest salary in their departments

  SELECT name
  FROM emps AS emp
  INNER JOIN (
  SELECT department_id, MAX(salary) AS salary
  FROM emps GROUP BY department_id
  ) AS max_salaries
  ON emp.department_id = max_salaries.department_id AND emp.salary = max_salaries.salary;

  Correlated Query(faster)

  SELECT empy1.name
  FROM employees AS empy1
  WHERE empy1.salary = (
  	SELECT MAX(empy2.salary) 
  	FROM employees AS empy2
  	WHERE empy2.department_id = empy1.department_id )

• List departments that have less than 3 people in it

  SELECT name AS DepName FROM [dbo].[departments]
  WHERE [dbo].[departments].id IN (
  	SELECT d.id 
  	FROM [dbo].[employees] e INNER JOIN [dbo].[departments] d 
  	ON e.department_id = d.id
  	GROUP BY d.id
  	HAVING COUNT(e.id) < 3
  )

• List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

  SELECT d.name AS DepName, COUNT(e.id) AS HeadCount 
  FROM departments d LEFT JOIN employees e ON d.id = e.department_id
  GROUP BY d.name

• List employees that don't have a boss in the same department

  SELECT subordinates.name
  FROM employees subordinates LEFT JOIN employees boss
  ON subordinates.bossid = boss.id
  WHERE subordinates.department_id != boss.department_id OR subordinates.bossid IS NULL

  Using EXISTS(faster)

  SELECT subordinates.name
  FROM employees AS subordinates
  WHERE NOT EXISTS (
  	SELECT boss.id  
  	FROM employees AS boss
  	WHERE subordinates.department_id = boss.department_id 
  	AND subordinates.bossid = boss.id
  )

• List all departments along with the total salary there

  SELECT d.name, 
  CASE 
  	WHEN SUM(e.salary) IS NULL THEN 0 
  	ELSE SUM(e.salary) 
  END AS TotSal
  FROM departments d LEFT JOIN employees e
  ON d.id = e.department_id
  GROUP BY d.name