http://www.jitbit.com/news/181-jitbits-sql-interview-questions/

# DATABASE

CREATE TABLE `departments` (
  `DepartmentID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY (`DepartmentID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `departments` VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three');

CREATE TABLE `employees2` (
  `EmployeeID` int(11) NOT NULL AUTO_INCREMENT,
  `DepartmentID` int(11) NOT NULL,
  `BossID` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Salary` int(11) NOT NULL,
  PRIMARY KEY (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `employees2` VALUES
(1, 1, 0, 'Person1', 150),
(2, 1, 1, 'Person2', 100),
(3, 1, 1, 'Person3', 100),
(4, 2, 2, 'Person4', 65),
(5, 2, 2, 'Person5', 60),
(6, 2, 2, 'Person6', 60),
(7, 2, 2, 'Person7', 55),
(8, 3, 3, 'Person8', 50),
(9, 3, 3, 'Person9', 160);


# ANSWERS

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

SELECT e2.Name AS 'Employee', e.Name AS 'Boss', e.Salary AS 'Employee salary', e2.Salary AS 'Boss salary'
FROM Employees e
INNER JOIN Employees e2 ON e.BossID = e2.EmployeeID
WHERE e2.Salary < e.Salary

List employees who have the biggest salary in their departments

SELECT e.Name AS 'Employee', d.Name AS 'Department', e.Salary AS 'Salary'
FROM Departments d
INNER JOIN Employees e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID
ORDER BY e.Salary DESC

List departments that have less than 3 people in it

SELECT d.Name AS 'Department'
FROM Departments d
INNER JOIN Employees e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID
HAVING COUNT(e.EmployeeId) < 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 'Department', COUNT(e.EmployeeId) AS 'Employee count'
FROM Departments d
LEFT OUTER JOIN Employees e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID

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

SELECT e.Name AS 'Employee', e2.Name AS 'Boss', d.Name AS "Employee's department", d2.Name AS "Boss' department"
FROM Employees e
INNER JOIN Employees e2 ON e.BossiD = e2.EmployeeID
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
INNER JOIN Departments d2 ON e2.DepartmentID = d2.DepartmentID
WHERE e.BossID != 0 AND d.DepartmentID != d2.DepartmentID

List all departments along with the total salary there

SELECT d.Name AS 'Department', SUM(e.Salary) AS 'Total salary'
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID


By lucb1e / https://lucb1e.com/