Skip to main content

Most asked SQL queries in Data Engineering Interviews

Updated 2 min readMarkdown
Most asked  SQL queries in Data Engineering Interviews

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;

About the author

  • I am a Tech Enthusiast having 13+ years of experience in 𝐈𝐓 as a 𝐂𝐨𝐧𝐬𝐮𝐥𝐭𝐚𝐧𝐭, 𝐂𝐨𝐫𝐩𝐨𝐫𝐚𝐭𝐞 𝐓𝐫𝐚𝐢𝐧𝐞𝐫, 𝐌𝐞𝐧𝐭𝐨𝐫, with 12+ years in training and mentoring in 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐓𝐞𝐬𝐭 𝐀𝐮𝐭𝐨𝐦𝐚𝐭𝐢𝐨𝐧 𝐚𝐧𝐝 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞. I have 𝒕𝒓𝒂𝒊𝒏𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 10,000+ 𝑰𝑻 𝑷𝒓𝒐𝒇𝒆𝒔𝒔𝒊𝒐𝒏𝒂𝒍𝒔 and 𝒄𝒐𝒏𝒅𝒖𝒄𝒕𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 500+ 𝒕𝒓𝒂𝒊𝒏𝒊𝒏𝒈 𝒔𝒆𝒔𝒔𝒊𝒐𝒏𝒔 in the areas of 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐃𝐞𝐯𝐞𝐥𝐨𝐩𝐦𝐞𝐧𝐭, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐂𝐥𝐨𝐮𝐝, 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐢𝐬, 𝐃𝐚𝐭𝐚 𝐕𝐢𝐬𝐮𝐚𝐥𝐢𝐳𝐚𝐭𝐢𝐨𝐧𝐬, 𝐀𝐫𝐭𝐢𝐟𝐢𝐜𝐢𝐚𝐥 𝐈𝐧𝐭𝐞𝐥𝐥𝐢𝐠𝐞𝐧𝐜𝐞 𝐚𝐧𝐝 𝐌𝐚𝐜𝐡𝐢𝐧𝐞 𝐋𝐞𝐚𝐫𝐧𝐢𝐧𝐠. I am interested in 𝐰𝐫𝐢𝐭𝐢𝐧𝐠 𝐛𝐥𝐨𝐠𝐬, 𝐬𝐡𝐚𝐫𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐤𝐧𝐨𝐰𝐥𝐞𝐝𝐠𝐞, 𝐬𝐨𝐥𝐯𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐢𝐬𝐬𝐮𝐞𝐬, 𝐫𝐞𝐚𝐝𝐢𝐧𝐠 𝐚𝐧𝐝 𝐥𝐞𝐚𝐫𝐧𝐢𝐧𝐠 new subjects.

No comments yet. Be the first to comment.

More from this blog