Handy little Transact-SQL queries for employee data

Employee-Transact-SQL.sql

    -- http://sqlfiddle.com/#!18/f85ba/44

	CREATE TABLE Department(
	  ID INT PRIMARY KEY CLUSTERED,
	  Name NVARCHAR (256) NOT NULL
	);
	CREATE TABLE Employee(
	  ID INT PRIMARY KEY CLUSTERED,
	  Department_ID INT,
	  Chief_ID INT,
	  Name NVARCHAR (256) NOT NULL,
	  Salary INT NOT NULL
	);

	INSERT INTO Department (ID, Name)
	VALUES (1, 'First Dept'), (2, 'Second Dept'), (3, 'Third Dept'), (4, 'Forth Dept')
	;
	INSERT INTO Employee 
		   (ID, Department_ID, Chief_ID, Name, Salary)
	VALUES 
	(1, 1, NULL, NEWID(), FLOOR(RAND() * 1000000 * 10)),
	(2, 1, 1,    NEWID(), FLOOR(RAND() * 1000000 * 10)),

	(3, 2, 1,    NEWID(), FLOOR(RAND() * 1000000)),
	(4, 2, 3, NEWID(), FLOOR(RAND() * 1000000)),
	(5, 2, 4, NEWID(), FLOOR(RAND() * 1000000)),
	(6, 2, 5,    NEWID(), FLOOR(RAND() * 1000000)),
	(7, 2, 5,    NEWID(), FLOOR(RAND() * 1000000)),
	(8, 2, 7,    NEWID(), FLOOR(RAND() * 1000000)),
	(9, 2, 7,    NEWID(), FLOOR(RAND() * 1000000)),

	(10, 3, 1, NEWID(), FLOOR(RAND() * 1000000)),
	(11, 3, 10, NEWID(), FLOOR(RAND() * 1000000)),
	(12, 3, 10, NEWID(), FLOOR(RAND() * 1000000)),
	(13, 3, 10, NEWID(), FLOOR(RAND() * 1000000)),
	(14, 3, 13, NEWID(), FLOOR(RAND() * 1000000)),
	(15, 3, 13, NEWID(), FLOOR(RAND() * 1000000)),
	(16, 3, 13, NEWID(), FLOOR(RAND() * 1000000)),
	(17, 3, 16, NEWID(), FLOOR(RAND() * 1000000)),
	(18, 3, 16, NEWID(), FLOOR(RAND() * 1000000)),
	(19, 3, 16, NEWID(), FLOOR(RAND() * 1000000)),
	(20, 3, 16, NEWID(), FLOOR(RAND() * 1000000))
	;
	INSERT INTO Employee 
		   (ID, Department_ID, Chief_ID, Name, Salary)
	VALUES 
	(21, 4, 1, NEWID(), (SELECT Salary FROM Employee WHERE ID = 1)),
	(22, 4, 21, NEWID(), (SELECT Salary FROM Employee WHERE ID = 2))
	;


-- 1. An employee gets paid more than his manager.
SELECT e.* /* , '-', e2.ID, e2.Salary, e2.Name */ FROM Employee e
INNER JOIN Employee e2 ON e2.ID = e.Chief_ID
WHERE e2.Salary < e.Salary
;

--2. Max salary in every department.
SELECT * FROM Employee e
WHERE e.Salary = (
  SELECT MAX(Salary) FROM Employee WHERE Department_ID = e.Department_ID)
;

--3. Department ID-s with 3 or fewer employees.
SELECT Department_ID FROM Employee
GROUP BY Department_ID
HAVING COUNT(Department_ID) <= 3
;

-- 4.1. Employees without a line manager in the same department.
SELECT e.* FROM Employee e
LEFT OUTER JOIN Employee e2 ON e2.ID = e.Chief_ID
  -- I don't like this syntax when we have more than one conditions in the ON.
  -- Greetings from ORACLE :)
  AND e2.Department_ID = e.Department_ID
WHERE e2.ID IS NULL
;
-- 4.2. Employees without a line manager in the same department.
SELECT * FROM Employee e
WHERE NOT EXISTS (
	SELECT 1 FROM Employee e2
	WHERE e2.ID = e.Chief_ID AND e2.Department_ID = e.Department_ID)
;

-- 5.1. Department ID-s with the maximum total salary.
SELECT Department_ID FROM Employee e
GROUP BY e.Department_ID
HAVING SUM(e.Salary) = (
  SELECT TOP 1  SUM(Salary) AS Total FROM Employee
  GROUP BY Department_ID
  ORDER BY Total DESC)
;
-- 5.2. (Based on CTE)
;WITH totals as (
  SELECT SUM(Salary) AS Total FROM Employee
  GROUP BY Department_ID
)
SELECT Department_ID FROM Employee e
GROUP BY e.Department_ID
HAVING SUM(e.Salary) = (SELECT MAX(Total) FROM totals)
;