Scenario-based SQL interview questions with answer

Scenario: You have a table named “employees” with columns “employee_id” and “salary.” Find the highest salary among all employees.

SQL Query:

• SELECT MAX(salary) AS highest_salary FROM employees;

Scenario: You need to count the number of employees in each department from the “employees” table.

SQL Query:

• SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;

Scenario: You want to retrieve the top 5 highest-paid employees from the “employees” table.

SQL Query:

• SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 5;

Scenario: You have two tables, “employees” and “departments,” and you need to retrieve the names of employees along with their department names.

SQL Query:

• SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;

Scenario: You need to find employees who have the same salary and department as employee ID 101.

SQL Query:

• SELECT e2.employee_id, e2.first_name, e2.last_name FROM employees e1 INNER JOIN employees e2 ON e1.salary = e2.salary AND e1.department_id = e2.department_id WHERE e1.employee_id = 101 AND e2.employee_id != 101;

Scenario: You have a table “orders” with columns “order_id,” “customer_id,” and “order_date.” Retrieve the total number of orders placed by each customer in the year 2023.

SQL Query:

• SELECT customer_id, COUNT(*) AS num_orders FROM orders WHERE YEAR(order_date) = 2023 GROUP BY customer_id;

Scenario: You need to find the average salary of employees in each department and display it along with the department name.

SQL Query:

• SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

Scenario: You want to retrieve all employees who do not belong to any department.

SQL Query:

• SELECT employee_id, first_name, last_name FROM employees WHERE department_id IS NULL;

Scenario: You need to find the number of employees hired in each year.

SQL Query:

• SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS num_employees_hired FROM employees GROUP BY YEAR(hire_date);

Scenario: You have a table “students” with columns “student_id” and “marks.” Find the second highest marks scored by a student.

SQL Query:

• SELECT MAX(marks) AS second_highest_marks FROM students WHERE marks < (SELECT MAX(marks) FROM students);

Scenario: You want to retrieve the top 3 departments with the highest average salary of employees.

SQL Query:

• SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary DESC LIMIT 3;

Scenario: You need to find the number of orders placed by each customer and sort the result in descending order of the number of orders.

SQL Query:

• SELECT customer_id, COUNT(order_id) AS num_orders FROM orders GROUP BY customer_id ORDER BY num_orders DESC;

Scenario: You want to retrieve the names of employees who joined the company in January.

SQL Query:

• SELECT first_name, last_name FROM employees WHERE MONTH(hire_date) = 1;

Scenario: You have a table “products” with columns “product_id,” “product_name,” and “price.” Find the top 5 most expensive products.

SQL Query:

• SELECT product_id, product_name, price FROM products ORDER BY price DESC LIMIT 5;

Scenario: You need to retrieve the names of employees who have “A” in their first name and “B” in their last name.

SQL Query:

• SELECT first_name, last_name FROM employees WHERE first_name LIKE '%A%' AND last_name LIKE '%B%';

Scenario: You have a table “orders” with columns “order_id,” “order_date,” and “amount.” Find the total sales for each year.

SQL Query:

• SELECT YEAR(order_date) AS sales_year, SUM(amount) AS total_sales FROM orders GROUP BY YEAR(order_date);

Scenario: You need to retrieve the names of employees who do not have any manager.

SQL Query:

• SELECT first_name, last_name FROM employees WHERE manager_id IS NULL;

Scenario: You have two tables, “students” and “courses,” with columns “student_id,” “course_id,” and “marks.” Retrieve the names of students along with the course names they have taken and the marks obtained in each course.

SQL Query:

• SELECT s.student_name, c.course_name, sc.marks FROM students s INNER JOIN student_courses sc ON s.student_id = sc.student_id INNER JOIN courses c ON sc.course_id = c.course_id;

Scenario: You need to retrieve the details of the oldest employee.

SQL Query:

• SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE hire_date = (SELECT MIN(hire_date) FROM employees);

Scenario: You have a table “products” with columns “product_id” and “category.” Find the number of products in each category and display the category with zero products as well.

SQL Query:

SELECT category, COUNT(*) AS num_products
FROM products
GROUP BY category;

For more articles consider making a follow on my account. Thanks…