Jitbit's SQL interview questions

1 - sql_interview_questions.md

Jitbit's SQL interview questions

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

Schema

employees

• employee_id
• department_id
• boss_id
• name
• salary

departments

• department_id
• name

Questions

1. List employees (names) who have a bigger salary than their boss
2. List employees who have the biggest salary in their departments
3. List departments that have less than 3 people in it
4. List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
5. List employees that don't have a boss in the same department
6. List all departments along with the total salary there

2 - create_schema.sql

CREATE TABLE `employees` (
  `employee_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `department_id` int(11) DEFAULT NULL,
  `boss_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `salary` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
);
 
CREATE TABLE `departments` (
  `department_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`department_id`)
);

3 - insert_data.sql

INSERT INTO `employees` (`employee_id`, `department_id`, `boss_id`, `name`, `salary`)
VALUES
    	(1,1,0,'manager','80000'),
  	(2,1,1,'emp1','60000'),
	(3,1,1,'emp2','50000'),
	(4,1,1,'emp3','95000'),
	(5,1,1,'emp4','75000');
 
INSERT INTO `departments` (`department_id`, `name`)
VALUES
	(1,'IT'),
	(2,'HR'),
	(3,'Sales'),
	(4,'Marketing');

4 - answers.md

Answers

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

SELECT e.name AS 'Employee Name', e2.name AS 'Boss', 
e.salary AS 'Employee salary', e2.salary AS 'Boss salary'
FROM employees e
JOIN employees e2 ON e.boss_id = e2.employee_id
WHERE e2.salary < e.salary;

List employees who have the biggest salary IN their departments

SELECT e.name AS 'Employee Name', d.Name AS 'Department', e.Salary AS 'Salary'
FROM departments d JOIN employees e 
ON e.department_id = d.department_id
GROUP BY d.department_id
ORDER BY e.salary DESC

List departments that have less than 3 people IN it

SELECT d.Name AS 'Department'
FROM departments d JOIN employees e 
ON e.department_id = d.Department_id
GROUP BY d.department_id
HAVING COUNT(e.employee_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 'Department', COUNT(e.employee_id) AS '# of Employees'
FROM departments d LEFT OUTER JOIN employees e 
ON e.department_id = d.department_id
GROUP BY d.Department_id

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

SELECT e.name AS 'Employee Name', e2.name AS 'Boss', d.name AS "Employee's Department", d2.name AS "Boss' Department"
FROM employees e
JOIN employees e2 ON e.boss_id = e2.employee_id
JOIN departments d ON e.department_id = d.department_id
JOIN departments d2 ON e2.department_id = d2.department_id
WHERE e.boss_id != 0 
AND d.department_id != d2.department_id

List all departments along with the total salary there

SELECT d.name AS 'Department', SUM(e.salary) AS 'Total Salary'
FROM departments d LEFT OUTER JOIN employees e 
ON d.department_id = e.department_id
GROUP BY d.department_id

Also #4 -- your solution will show counts of 1 for empty departments. Here is a working solution that shows zeros:

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

#5 is unnecessarily complex.

SELECT e.name 
FROM employees e JOIN employees b ON e.boss_id=b.id
WHERE e.department_id != b.department_id
OR b.boss_id is NULL

The solutions given for problem #2 contain data inconsistencies. The correct answer is: IT,emp3,95000

In the original posted answer, the query will return the wrong salary and wrong employee. The group by is executed before the order by clause and returns the first row that matches department_id. As a result, the salary is not really the greatest per department. Returns: IT,manager,80000. incorrect

In skandocious answer, this query will fail completely for other databases like MS SQL and Oracle, because all non-aggregated columns must be specified in the group by clause for consistency purposes. The correct salary is returned, but with the wrong associated employee. The max(salary) returns the max salary per department ID; however, since we didn't specify the column names in the group by clause, the data is no longer consistent. We now return the first possible employee name for the matching department courtesy of the join clause, but with the maximum salary per department. Returns: IT, manager, 95000. incorrect

Below are two possible solutions:

First solution - Returns only one person for each department with the highest salary:

SELECT * 
FROM ( SELECT dept.Name Department, emp.Name Employee, emp.Salary Salary
       FROM Departments dept 
       JOIN Employees emp ON emp.department_id = dept.department_id
       ORDER BY salary desc ) result 
GROUP BY Department;

We first get the departments, employees and their relative salaries, and organize salaries in descending order. The outer query uses a group by, grabbing the first row of each record associated with their department. Since the first result set is ordered by salary in descending order, the outer group by will choose the first record, which will be the highest salary. One issue with this solution is we will not return each employee that shares the highest salary.

Second solution - Returns one or more people for each department with the highest salary:

SELECT result.Name Department, emp2.Name Employee, result.salary Salary 
FROM ( SELECT dept.name, dept.department_id, max(emp1.salary) salary 
       FROM Departments dept 
       JOIN Employees emp1 ON emp1.department_id = dept.department_id 
       GROUP BY dept.name, dept.department_id ) result 
JOIN Employees emp2 ON emp2.department_id = result.department_id 
WHERE emp2.salary = result.salary;

This one will return each employee that shares the highest salary.

Solution to the 5th can be better written as follows
select e2.*, e1.department_ID from #employees e1 inner join #employees e2 on e1.employee_id = e2.boss_id where e1.department_ID <> e2.department_ID

Solution to the 2nd is wrong.
kalypzo your query is fine but it gives the department not the employee
Question asked to find out the employee not the department. Therefore why do we need to join department table in first place.
Please comment if i'm wrong.
correct solution:
Select A.Employee_id,A.name from (Select employee_id,name,DENSE_RANK() OVER (PARTITION BY department_id ORDER BY Salary DESC) AS Rnk from Employees) A where A.Rnk=1

Thank you so much for the Table Creation/Populating code. I want to practise these questions but do not want the answers. I could have whipped them up myself but.... this makes things so much more streamlined. thank you!