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

本文深入探讨了SQL中内连接操作的多种应用，包括不同字段的连接查询，并提供了解决复杂查询问题的实例。同时，文章详细解答了如何通过SQL筛选特定员工、部门，以及进行薪酬比较、部门人数统计等关键业务需求的实现方法。

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