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

My Solution:

select a.employee_id, salary, department_id
from employees a
where salary = (select max(salary) from employees where department_id = a.department_id)

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