50 SQL Queries for Interviews on Tables Company, Employee, Department
1. How many employees are there in the company?
SQL
Query:
SELECT COUNT(*) AS total_employees FROM
Employee;
Explanation:
This query counts the total number of rows
in the Employee table, which represents the total number of employees in the
company.
2. List all employees and their departments.
SQL
Query:
SELECT e.name AS employee_name, d.name AS
department_name FROM Employee e JOIN Department d ON e.department_id =
d.department_id;
Explanation:
This query retrieves the names of employees
along with their respective department names by performing an INNER JOIN
between the Employee and Department tables using the department_id as the
common key.
3. Retrieve all departments in the company.
SQL
Query:
SELECT name FROM Department;
Explanation:
This query selects all department names
from the Department table, listing every department available in the company.
4. Show the details of all employees who work in a
specific department.
SQL
Query:
SELECT * FROM Employee WHERE department_id
= (SELECT department_id FROM Department WHERE name = 'Engineering');
Explanation:
This query first finds the department_id of
the 'Engineering' department using a subquery, then retrieves all employees who
belong to that department.
5. Get the names of all employees who work in a specific
company.
SQL
Query:
SELECT e.name FROM Employee e JOIN
Department d ON e.department_id = d.department_id JOIN Company c ON
d.company_id = c.company_id WHERE c.name = 'Tech Corp';
Explanation:
This query joins the Employee, Department,
and Company tables to find employees working in a specified company. It first
links employees to their departments, then links departments to the company
based on the company name.
6. List employees along with their department names.
SQL
Query:
SELECT e.name AS employee_name, d.name AS
department_name FROM Employee e INNER JOIN Department d ON e.department_id =
d.department_id;
Explanation:
This query uses an INNER JOIN to retrieve
all employees and their corresponding department names by matching
department_id in both the Employee and Department tables.
7. Retrieve all employees along with their company names.
SQL
Query:
SELECT e.name AS employee_name, c.name AS
company_name FROM Employee e INNER JOIN Department d ON e.department_id =
d.department_id INNER JOIN Company c ON d.company_id = c.company_id;
Explanation:
This query joins the Employee, Department,
and Company tables to fetch each employee along with the name of the company
they work for by linking through department_id and company_id.
8. Find employees who don’t belong to any department.
SQL
Query:
SELECT e.* FROM Employee e LEFT JOIN
Department d ON e.department_id = d.department_id WHERE d.department_id IS
NULL;
Explanation:
This query uses a LEFT JOIN between
Employee and Department, then filters for employees where department_id is
NULL, meaning they are not assigned to any department.
9. Get all departments and their employees (including
departments with no employees).
SQL
Query:
SELECT d.name AS department_name, e.name AS
employee_name FROM Department d LEFT JOIN Employee e ON d.department_id =
e.department_id;
Explanation:
This query retrieves all departments and
their employees using a LEFT JOIN, ensuring that departments without employees
are also included, displaying NULL in the employee_name column where no
employees exist.
10. Show the count of employees in each department.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS employee_count FROM Department d LEFT JOIN Employee e
ON d.department_id = e.department_id GROUP BY d.name;
Explanation:
This query counts the number of employees
in each department by performing a LEFT JOIN between Department and Employee,
then grouping by department name.
11. Find employees who work in a department that belongs
to a specific company.
SQL
Query:
SELECT e.name AS employee_name, d.name AS
department_name, c.name AS company_name FROM Employee e INNER JOIN Department d
ON e.department_id = d.department_id INNER JOIN Company c ON d.company_id =
c.company_id WHERE c.name = 'Tech Corp';
Explanation:
This query joins the Employee, Department,
and Company tables to find employees who work in a department belonging to the
specified company ('Tech Corp'). It filters results based on the company name.
12. List companies along with the number of departments
they have.
SQL
Query:
SELECT c.name AS company_name,
COUNT(d.department_id) AS department_count FROM Company c LEFT JOIN Department
d ON c.company_id = d.company_id GROUP BY c.name;
Explanation:
This query counts the number of departments
within each company using a LEFT JOIN between Company and Department, ensuring
that companies with no departments are also included.
13. Find the total number of employees in each department.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS total_employees FROM Department d LEFT JOIN Employee e
ON d.department_id = e.department_id GROUP BY d.name;
Explanation:
This query counts the number of employees
in each department using a LEFT JOIN between Department and Employee, ensuring
departments with no employees are also included.
14. Get the average salary of employees in each
department.
SQL
Query:
SELECT d.name AS department_name,
AVG(e.salary) AS average_salary FROM Department d LEFT JOIN Employee e ON
d.department_id = e.department_id GROUP BY d.name;
Explanation:
This query calculates the average salary
for employees in each department by grouping employees based on their
department_id and applying the AVG() function.
15. Find the department with the highest number of
employees.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS employee_count FROM Department d LEFT JOIN Employee e
ON d.department_id = e.department_id GROUP BY d.name ORDER BY employee_count
DESC LIMIT 1;
Explanation:
This query counts the employees in each
department, orders the results in descending order, and returns only the
department with the highest number of employees.
16. Retrieve the company that has the highest number of
employees.
SQL
Query:
SELECT c.name AS company_name,
COUNT(e.employee_id) AS employee_count FROM Company c JOIN Department d ON
c.company_id = d.company_id JOIN Employee e ON d.department_id =
e.department_id GROUP BY c.name ORDER BY employee_count DESC LIMIT 1;
Explanation:
This query counts the number of employees
in each company by joining the Employee, Department, and Company tables, then
orders the results in descending order to get the company with the highest
employee count.
17. Find the minimum and maximum salaries in each
department.
SQL
Query:
SELECT d.name AS department_name,
MIN(e.salary) AS min_salary, MAX(e.salary) AS max_salary FROM Department d LEFT
JOIN Employee e ON d.department_id = e.department_id GROUP BY d.name;
Explanation:
This query retrieves the minimum and
maximum salaries for each department by grouping employees based on their
department and applying the MIN() and MAX() functions.
18. Show the total salary expenditure for each company.
SQL
Query:
SELECT c.name AS company_name,
SUM(e.salary) AS total_salary_expenditure FROM Company c JOIN Department d ON
c.company_id = d.company_id JOIN Employee e ON d.department_id =
e.department_id GROUP BY c.name;
Explanation:
This query calculates the total salary
expenditure for each company by summing up employee salaries after joining the
Employee, Department, and Company tables.
19. Get the number of departments per company.
SQL
Query:
SELECT c.name AS company_name,
COUNT(d.department_id) AS department_count FROM Company c LEFT JOIN Department
d ON c.company_id = d.company_id GROUP BY c.name;
Explanation:
This query counts the number of departments
in each company using a LEFT JOIN between Company and Department, ensuring
companies without departments are also included.
20. Find the department with the least employees.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS employee_count FROM Department d LEFT JOIN Employee e
ON d.department_id = e.department_id GROUP BY d.name ORDER BY employee_count
ASC LIMIT 1;
Explanation:
This query counts employees in each
department, orders by ascending count, and returns the department with the
fewest employees.
21. Show the total, average, and maximum salary for
employees in each company.
SQL
Query:
SELECT c.name AS company_name,
SUM(e.salary) AS total_salary, AVG(e.salary) AS average_salary, MAX(e.salary)
AS max_salary FROM Company c JOIN Department d ON c.company_id = d.company_id
JOIN Employee e ON d.department_id = e.department_id GROUP BY c.name;
Explanation:
This query calculates the total, average,
and maximum salaries for each company by grouping employees based on their
company.
22. Count the number of employees whose salary is above
the average salary of their department.
SQL
Query:
SELECT COUNT(e1.employee_id) AS
above_average_salary_count FROM Employee e1 WHERE e1.salary > (SELECT
AVG(e2.salary) FROM Employee e2 WHERE e2.department_id = e1.department_id);
Explanation:
This query counts employees whose salary is
greater than the average salary of their department using a correlated subquery
that calculates the department’s average salary for each employee.
23. Get the names of employees earning more than a
specific amount.
SQL
Query:
SELECT name FROM Employee WHERE salary >
50000;
Explanation:
This query selects employee names where the
salary is greater than 50,000. You can replace 50,000 with any specific salary
threshold.
24. Find employees who were hired before a certain date.
SQL
Query:
SELECT name, hire_date FROM Employee WHERE
hire_date < '2018-01-01';
Explanation:
This query retrieves employees who were
hired before January 1, 2018. The date can be changed to any specific hiring
cutoff.
25. Show employees whose names start with 'A'.
SQL
Query:
SELECT * FROM Employee WHERE name LIKE
'A%';
Explanation:
This query selects all employees whose
names start with the letter 'A' using the SQL LIKE operator with a wildcard
'%'.
26. List all employees who don’t belong to any department.
SQL
Query:
SELECT * FROM Employee WHERE department_id
IS NULL;
Explanation:
This query selects employees whose
department_id is NULL, meaning they are not assigned to any department.
27. Retrieve employees working in departments located in a
specific city.
SQL
Query:
SELECT e.name FROM Employee e JOIN
Department d ON e.department_id = d.department_id WHERE d.city = 'New York';
Explanation:
This query joins the Employee and
Department tables to find employees who work in departments located in 'New
York'. You can change 'New York' to any other city.
28. Find employees who have been in the company for more
than 5 years.
SQL
Query:
SELECT name, hire_date FROM Employee WHERE
hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
Explanation:
This query retrieves employees whose hire
date is at least 5 years before the current date by subtracting 5 years from
the current date using DATE_SUB().
29. Show departments that have more than 10 employees.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS employee_count FROM Department d LEFT JOIN Employee e
ON d.department_id = e.department_id GROUP BY d.name HAVING employee_count >
10;
Explanation:
This query groups employees by department
and filters departments that have more than 10 employees using the HAVING clause.
30. Find the employee with the highest salary.
SQL
Query:
SELECT * FROM Employee ORDER BY salary DESC
LIMIT 1;
Explanation:
This query sorts employees in descending
order based on salary and returns only the top result, which corresponds to the
employee with the highest salary.
31. Retrieve employees who earn more than the average
salary of the company.
SQL
Query:
SELECT * FROM Employee WHERE salary >
(SELECT AVG(salary) FROM Employee);
Explanation:
This query uses a subquery to calculate the
average salary of all employees and then selects employees whose salary is
greater than that average.
32. Find departments that have at least one employee
earning more than $100,000.
SQL
Query:
SELECT DISTINCT d.name FROM Department d
JOIN Employee e ON d.department_id = e.department_id WHERE e.salary >
100000;
Explanation:
This query joins the Employee and
Department tables and selects unique department names where at least one
employee has a salary greater than $100,000.
33. List employees who work in the largest department.
SQL
Query:
SELECT e.name FROM Employee e WHERE
e.department_id = (SELECT d.department_id FROM Department d LEFT JOIN Employee
e ON d.department_id = e.department_id GROUP BY d.department_id ORDER BY
COUNT(e.employee_id) DESC LIMIT 1);
Explanation:
This query first determines the department
with the highest number of employees, then retrieves all employees who work in
that department.
34. Find employees who are the only ones in their
department.
SQL
Query:
SELECT e.name FROM Employee e WHERE
e.department_id IN (SELECT department_id FROM Employee GROUP BY department_id
HAVING COUNT(employee_id) = 1);
Explanation:
This query finds departments that have
exactly one employee using a subquery with GROUP BY and HAVING COUNT() = 1,
then retrieves those employees.
35. Retrieve employees whose salaries are in the top 10%
of the company.
SQL
Query:
SELECT * FROM Employee WHERE salary >=
(SELECT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY salary) FROM Employee);
Explanation:
This query retrieves employees whose
salaries are in the top 10% by using the PERCENTILE_CONT(0.90) function to
compute the salary threshold for the top 10% of earners.
36. Rank employees based on their salaries within their
departments.
SQL
Query:
SELECT name, department_id, salary, RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM
Employee;
Explanation:
This query assigns a ranking to employees
based on their salary within each department. The RANK() function partitions
the ranking by department_id and orders salaries in descending order.
37. Assign row numbers to employees in each department.
SQL
Query:
SELECT name, department_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY name) AS row_num FROM Employee;
Explanation:
This query assigns a unique row number to
each employee within their department using ROW_NUMBER(), which restarts
numbering for each department.
38. Get the cumulative salary sum for each department.
SQL
Query:
SELECT name, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS
cumulative_salary FROM Employee;
Explanation:
This query calculates the cumulative sum of
salaries within each department using SUM() as a window function, ordered by
salary.
39. Find the difference between an employee’s salary and
the department’s average salary.
SQL
Query:
SELECT name, department_id, salary, salary
- AVG(salary) OVER (PARTITION BY department_id) AS salary_difference FROM
Employee;
Explanation:
This query calculates the difference
between each employee’s salary and the average salary of their department using
the AVG() window function.
40. Show the previous and next employee's salary for each
employee.
SQL
Query:
SELECT name, salary, LAG(salary) OVER (ORDER
BY salary) AS previous_salary, LEAD(salary) OVER (ORDER BY salary) AS
next_salary FROM Employee;
Explanation:
This query uses LAG() and LEAD() window
functions to retrieve the previous and next employee's salary for each employee
based on salary ordering.
41. Add a new employee to a department.
SQL
Query:
INSERT INTO Employee (name, salary,
hire_date, department_id) VALUES ('John Doe', 60000, '2023-03-01', 2);
Explanation:
This query inserts a new employee named
'John Doe' with a salary of 60,000 and a hire date of March 1, 2023, into
department 2. The department_id should be replaced with the actual department
ID.
42. Update the salary of employees in a specific
department.
SQL
Query:
UPDATE Employee SET salary = salary + 5000
WHERE department_id = 3;
Explanation:
This query increases the salary of all
employees in department 3 by 5,000. The department_id should be changed to the
relevant department.
43. Delete employees who left the company before a certain
date.
SQL
Query:
DELETE FROM Employee WHERE last_working_day
< '2020-01-01';
Explanation:
This query deletes employees whose last
working day was before January 1, 2020. The column last_working_day must exist
in the Employee table.
44. Insert a new department into the company.
SQL
Query:
INSERT INTO Department (name, company_id,
city) VALUES ('AI Research', 1, 'San Francisco');
Explanation:
This query creates a new department named
'AI Research' under company ID 1, located in San Francisco. The company_id
should be replaced with the actual company ID.
45. Move all employees from one department to another.
SQL
Query:
UPDATE Employee SET department_id = 5 WHERE
department_id = 3;
Explanation:
This query reassigns all employees from
department 3 to department 5. The department IDs should be updated based on the
actual data.
46. Delete all employees from a specific company.
SQL
Query:
DELETE FROM Employee WHERE department_id IN
(SELECT department_id FROM Department WHERE company_id = 2);
Explanation:
This query deletes all employees who work
in departments belonging to company ID 2. The company_id should be replaced
with the actual company ID.
47. Increase salaries by 10% for employees in a specific
department.
SQL
Query:
UPDATE Employee SET salary = salary * 1.10
WHERE department_id = 4;
Explanation:
This query increases salaries by 10% for
employees in department 4 by multiplying their salary by 1.10. The
department_id should be updated based on the target department.
48. Assign all employees without a department to a default
department.
SQL
Query:
UPDATE Employee SET department_id = 1 WHERE
department_id IS NULL;
Explanation:
This query assigns all employees who
currently have no department (department_id IS NULL) to the default department
with ID 1. The department ID should be adjusted based on the organization's
structure.
49. Find the second-highest salary in the company.
SQL
Query:
SELECT MAX(salary) AS second_highest_salary
FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);
Explanation:
This query finds the second-highest salary
by first identifying the maximum salary and then selecting the highest salary
that is less than the maximum salary.
50. Retrieve the name of the company with the highest
average salary.
SQL
Query:
SELECT c.name FROM Company c JOIN
Department d ON c.company_id = d.company_id JOIN Employee e ON d.department_id
= e.department_id GROUP BY c.name ORDER BY AVG(e.salary) DESC LIMIT 1;
Explanation:
This query calculates the average salary
for each company and orders the companies in descending order by average
salary. It then retrieves the company with the highest average salary.
51. List employees who joined in the last 6 months.
SQL
Query:
SELECT * FROM Employee WHERE hire_date >
DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
Explanation:
This query selects employees who have
joined within the last 6 months by comparing their hire_date with the current
date minus 6 months.
52. Get the name of the department where the employee
count is closest to the company average.
SQL
Query:
SELECT d.name FROM Department d JOIN
(SELECT department_id, COUNT(*) AS dept_count FROM Employee GROUP BY
department_id) e_count ON d.department_id = e_count.department_id WHERE
ABS(e_count.dept_count - (SELECT AVG(dept_count) FROM (SELECT department_id,
COUNT(*) AS dept_count FROM Employee GROUP BY department_id) AS avg_count)) =
(SELECT MIN(ABS(e_count.dept_count - (SELECT AVG(dept_count) FROM (SELECT
department_id, COUNT(*) AS dept_count FROM Employee GROUP BY department_id) AS
avg_count))) FROM (SELECT department_id, COUNT(*) AS dept_count FROM Employee
GROUP BY department_id) e_count);
Explanation:
This query identifies the department where
the employee count is closest to the average employee count across all
departments. It uses subqueries to calculate the department sizes and find the
closest value.
53. Show a list of departments along with their headcount,
sorted in descending order.
SQL
Query:
SELECT d.name AS department_name,
COUNT(e.employee_id) AS headcount FROM Department d LEFT JOIN Employee e ON
d.department_id = e.department_id GROUP BY d.name ORDER BY headcount DESC;
Explanation:
This query calculates the number of
employees in each department (headcount) and orders the result in descending
order by headcount, using a LEFT JOIN to include departments with no employees.
54. Retrieve employees who work in the same department as
a specific employee.
SQL
Query:
SELECT e.name FROM Employee e WHERE
e.department_id = (SELECT department_id FROM Employee WHERE name = 'John Doe');
Explanation:
This query finds all employees working in
the same department as 'John Doe' by using a subquery to find John Doe's
department_id and matching that with other employees.
55. Find the top 3 departments with the highest average
salaries.
SQL
Query:
SELECT d.name, AVG(e.salary) AS avg_salary
FROM Department d JOIN Employee e ON d.department_id = e.department_id GROUP BY
d.name ORDER BY avg_salary DESC LIMIT 3;
Explanation:
This query calculates the average salary
for each department and retrieves the top 3 departments with the highest
average salary by ordering the results in descending order and limiting the
output.