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

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!