In this blog post, I have curated a list of SQL queries asked in interviews.

Dataset

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  salary INT NOT NULL,
  department VARCHAR(50) NOT NULL
);


INSERT INTO employees (name, gender, salary, department) VALUES
('Ramesh Gupta', 'Male', 55000, 'Sales'),
('Priya Sharma', 'Female', 65000, 'Marketing'),
('Sanjay Singh', 'Male', 75000, 'Sales'),
('Anjali Verma', 'Female', 45000, 'Finance'),
('Rajesh Sharma', 'Male', 80000, 'Marketing'),
('Smita Patel', 'Female', 60000, 'HR'),
('Vikram Yadav', 'Male', 90000, 'Sales'),
('Neha Sharma', 'Female', 55000, 'Marketing'),
('Rahul Singh', 'Male', 70000, 'Finance'),
('Sonali Gupta', 'Female', 50000, 'Sales');

Queries

1. Write a query to find the average salary of male and female employees in each department.

SELECT department, gender, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, gender;

1. Write a query to find the name and salary of the employee with the highest salary in each department.

SELECT department, name, salary
FROM employees
WHERE (department, salary) IN (
  SELECT department, MAX(salary)
  FROM employees
  GROUP BY department
);

3. Write a query to find the names of employees who earn more than the average salary in their department.

SELECT name, salary, department
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees AS e2
  WHERE e2.department = employees. Department
);

1. Find the top 3 highest paid employees in each department.

SELECT e.department, e.name, e.salary
FROM employees e
WHERE (
  SELECT COUNT(*) 
  FROM employees 
  WHERE department = e.department AND salary > e.salary
) < 3;

5. Find the names of employees who have a salary greater than the average salary of their department.

SELECT e.name
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

1. Find the department(s) with the highest paid employee(s).

WITH max_salary AS (
  SELECT department, MAX(salary) AS highest_salary
  FROM employees
  GROUP BY department
)
SELECT m.department, e.name, e.salary
FROM employees e
JOIN max_salary m ON e.department = m.department AND e.salary = m.highest_salary;