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.
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;