Complex mysql inteview questions for experienced in 2024

 Here are some complex MySQL interview questions & answers that can help experienced candidates prepare:

1. Query with Subqueries:

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

SELECT e.name, e.salary, e.department_id

FROM employees e

WHERE e.salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

2.Common Table Expressions (CTEs)

Using a CTE, write a query to find the second highest salary in the employees table.

WITH RankedSalaries AS (

    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank

    FROM employees

)

SELECT salary

FROM RankedSalaries

WHERE salary_rank = 2;

3. Joining Multiple Tables

Write a query to find all customers who have placed an order, along with the details of the order.

SELECT c.customer_id, c.name, o.order_id, o.order_date

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id;

4. Grouping and Aggregation

Write a query to get the total sales for each product category, including categories with no sales.

SELECT pc.category_name, COALESCE(SUM(o.amount), 0) AS total_sales

FROM product_categories pc

LEFT JOIN products p ON pc.category_id = p.category_id

LEFT JOIN orders o ON p.product_id = o.product_id

GROUP BY pc.category_name;

5. Window Functions

Write a query to show the running total of sales for each month.

SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders;

6. Conditional Aggregation

Write a query to count the number of employees in each department, distinguishing between full-time and part-time employees.

Answer:

SELECT department_id,
       COUNT(CASE WHEN employment_type = 'Full-time' THEN 1 END) AS full_time_count,
       COUNT(CASE WHEN employment_type = 'Part-time' THEN 1 END) AS part_time_count
FROM employees
GROUP BY department_id;

7. Self Join

Question: Write a query to find all employees who work in the same department as 'John Doe'.

Answer:

SELECT e1.name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e2.name = 'John Doe' AND e1.name != 'John Doe';

8. Pivot Table

Question: Write a query to pivot the sales data to show total sales per product per month.

Answer:

SELECT product_id,
       SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS January,
       SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS February,
       SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS March
FROM orders
GROUP BY product_id;

9. Finding Duplicates

Question: Write a query to find duplicate emails in the users table.

Answer:

SELECT email, COUNT(*) AS email_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

10. Using JSON Functions

Question: Assume you have a table users with a JSON column attributes. Write a query to find users whose age attribute is greater than 30.

Answer:

SELECT *
FROM users
WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.age')) > 30;

11. Date Functions

Question: Write a query to find the total number of orders placed in each year.

Answer:

SELECT YEAR(order_date) AS order_year, COUNT(*) AS total_orders
FROM orders
GROUP BY order_year;



12. Using GROUP_CONCAT
Question: Write a query to get a list of product names for each order.
Answer:
SELECT o.order_id, GROUP_CONCAT(p.product_name) AS products
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id;



13.Cross Join
Question: Write a query to create a list of all possible combinations of products and customers.
Answer:
SELECT p.product_name, c.name AS customer_name
FROM products p
CROSS JOIN customers c;

14. Using CASE for Conditional Logic
Question: Write a query to categorize employees based on their salaries (e.g., 'Low', 'Medium', 'High').
Answer:
SELECT name, 
       CASE 
           WHEN salary < 30000 THEN 'Low'
           WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium'
           ELSE 'High'
       END AS salary_category
FROM employees;


15. Using EXISTS
Question: Write a query to find all products that have been ordered at least once.
Answer:
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);