Most important SQL queries for interview – part 2

SQL skills are the indispensable in most of the job roles, be it database developer, database admin or a full stack developer. Everyone working in Software development might come across SQL at some time in their career. As recently many software companies looking for full stack skill set instead of prowess in any single technology. So SQL is ever demanding and good to have skill in the resume.

Here I prepared a second set of SQL queries, which are common in many interviews. There is SQL queries for interview – part 1 so before going through below I recommend you to go through part -1 first.

First lets prepare schema so that understanding the queries will be more easy.

employees
employee_id
department_id
boss_id
name
salary
departments
department_id
department_name

Q.1) List employees who have the biggest salary in their departments

SELECT d.department_name AS department, 
       e.NAME            AS max_earner, 
       Max(e.salary)     AS salary 
FROM   employees e 
       JOIN departments d 
         ON e.department_id = d.department_id 
GROUP  BY e.department_id; 

Q.2) List departments that have less than 5 people in it

SELECT d.department_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) < 5 

Q.3) List all departments along with the total salary

SELECT d.department_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 

Q.4) List employees that don’t have a boss in the same department

SELECT e.NAME 
FROM   employees e 
       JOIN employees b 
         ON e.boss_id = b.employee_id 
WHERE  e.department_id != b.department_id 
        OR b.boss_id IS NULL 

Q.5) List all departments along with the number of people there

SELECT d.department_name, 
       Count(e.department_id) AS num_employees 
FROM   departments d 
       LEFT JOIN employees e 
              ON d.department_id = e.department_id 
GROUP  BY d.department_id 

Q.6) Returns only one person for each department with the highest salary

SELECT * 
FROM   (SELECT dept.department_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; 

Q.7) List 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;