MySQL Interview Questions

1. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

Explanation: These are types of joins used to fetch records from two or more tables.

Example:

-- INNER JOIN
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN
SELECT * FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN (Not supported in MySQL, emulate with UNION)
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT * FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

---

2. How do you retrieve the nth highest salary from a table?

Explanation: Use subqueries with LIMIT and OFFSET.

Example:

-- 3rd highest salary
SELECT DISTINCT salary FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 2;

---

3. What is a CROSS JOIN and when should you use it?

Explanation: A CROSS JOIN returns the Cartesian product of two tables.

Example:

SELECT * FROM colors
CROSS JOIN sizes;

If colors has 3 rows and sizes has 4, the result will have 3 × 4 = 12 rows.

---

4. How do you find duplicate rows in a table?

Explanation: Use GROUP BY and HAVING COUNT > 1.

Example:

SELECT email, COUNT(*) as count 
FROM users 
GROUP BY email 
HAVING count > 1;

---

5. What is the difference between WHERE and HAVING clause?

Explanation:

• WHERE filters rows before aggregation

• HAVING filters after aggregation

Example:

-- WHERE filters individual rows
SELECT * FROM employees WHERE salary > 50000;

-- HAVING filters groups
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING avg_salary > 50000;

---

6. How can you perform a recursive query in MySQL?

Explanation: Use Common Table Expressions (CTE) with WITH RECURSIVE.

Example:

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;

Output: 1, 2, 3, 4, 5

---

7. How to find the second highest value in a column without using LIMIT?

Explanation: Use subqueries with < MAX() logic.

Example:

SELECT MAX(salary) FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

---

8. What is the purpose of EXPLAIN in MySQL?

Explanation:
It shows how MySQL executes a query—used for optimization.

Example:

EXPLAIN SELECT * FROM employees WHERE dept_id = 2;

It returns details like table used, index used, rows examined, etc.

---

9. How can you improve the performance of a slow query?

Explanation: Use indexes, avoid SELECT *, optimize joins, use LIMIT, and analyze query plan.

Tips:

• Create indexes on filtering and joining columns

• Avoid subqueries inside loops

• Use EXPLAIN to debug query path

---

10. 

Write a query to detect duplicate usernames regardless of case sensitivity.

Explanation: Converts usernames to lowercase before grouping.

SELECT LOWER(username) AS uname, COUNT(*) AS count
FROM users
GROUP BY uname
HAVING count > 1;

---

11. What is the difference between UNION and UNION ALL?

Explanation:

• UNION removes duplicates.

• UNION ALL keeps all duplicates.

Example:

-- Removes duplicate rows
SELECT name FROM employees
UNION
SELECT name FROM managers;

-- Keeps duplicate rows
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

---

12. How do you use CASE in MySQL?

Explanation: CASE works like an IF-ELSE or switch-case.

Example:

SELECT name, salary,
  CASE
    WHEN salary >= 50000 THEN 'High'
    WHEN salary >= 30000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

---

13. What is a VIEW in MySQL? How do you create and use one?

Explanation:
A VIEW is a virtual table based on a query result.

Example:

CREATE VIEW high_salaries AS
SELECT name, salary FROM employees WHERE salary > 50000;

-- Use the view
SELECT * FROM high_salaries;

---

14. How do you update data using joins in MySQL?

Explanation:
Use UPDATE with JOIN to modify values based on related table.

Example:

UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.salary = e.salary + 1000
WHERE d.name = 'HR';

---

15. What is a STORED PROCEDURE in MySQL?

Explanation:
A stored procedure is a set of SQL statements saved and reused.

Example:

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
  SELECT * FROM employees;
END //
DELIMITER ;

-- Call the procedure
CALL GetAllEmployees();

---

16. What is a TRIGGER in MySQL?

Explanation:
A TRIGGER is a block of SQL that runs automatically before/after INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER before_insert_log
BEFORE INSERT ON employees
FOR EACH ROW
INSERT INTO audit_log(message) VALUES('New employee added');

---

17. How do you implement pagination in MySQL?

Explanation: Use LIMIT and OFFSET.

Example:

-- Page 2, 10 records per page
SELECT * FROM products
LIMIT 10 OFFSET 10;

---

18. How do you use aggregate functions with GROUP BY?

Explanation:
Functions like SUM(), AVG(), COUNT(), etc., group data.

Example:

SELECT dept_id, COUNT(*) AS total_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

---

19. What is a PRIMARY KEY vs a UNIQUE KEY?

Explanation:

• PRIMARY KEY: Cannot be null and must be unique.

• UNIQUE KEY: Can be null, but values must still be unique.

Example:

-- Primary key
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

---

20. How do you perform a ROLLUP in MySQL?

Explanation:
WITH ROLLUP adds subtotals and grand totals to grouped data.

Example:

SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id WITH ROLLUP;

---

21. What are Indexes in MySQL and how do they improve performance?

Explanation:
Indexes are data structures that speed up data retrieval operations on a database table.

Example:

-- Creating an index
CREATE INDEX idx_name ON employees(name);

-- Index improves this query
SELECT * FROM employees WHERE name = 'John';

Note: Overusing indexes can slow down write operations.

---

22. What is a Composite Index and when should it be used?

Explanation:
A composite index is an index on multiple columns. It's useful when queries filter on several columns.

Example:

CREATE INDEX idx_name_dept ON employees(name, dept_id);

Used for queries like:

SELECT * FROM employees WHERE name = 'Alice' AND dept_id = 2;

---

23. What is the difference between CHAR and VARCHAR in MySQL?

Explanation:

• CHAR(n) is fixed-length.

• VARCHAR(n) is variable-length.

Example:

name CHAR(10)     -- always takes 10 bytes
name VARCHAR(10)  -- takes space as per actual length

---

24. What is normalization? Explain 1NF, 2NF, and 3NF.

Explanation:

• 1NF: Atomic columns (no repeating groups)

• 2NF: 1NF + No partial dependency (composite PK)

• 3NF: 2NF + No transitive dependency

Example:

Split one table with repeating groups into multiple related tables for efficiency and no redundancy.

---

25. What is denormalization?

Explanation:
Denormalization is the process of combining tables to reduce joins and improve read performance.

Used When:
Read speed > Storage efficiency.

Example:
Instead of joining users and countries, you can store country_name directly in the users table.

---

26. What is the use of IFNULL() and COALESCE() in MySQL?

Explanation:

• IFNULL(val1, val2) returns val2 if val1 is NULL

• COALESCE(val1, val2, ..., valN) returns the first non-NULL value

Example:

SELECT name, IFNULL(phone, 'N/A') FROM users;
SELECT COALESCE(NULL, NULL, 'Hello', 'World'); -- Output: 'Hello'

---

27. How can you calculate running totals in MySQL?

Explanation:
Use variables or OVER() clause (MySQL 8+).

Example (MySQL 8+):

SELECT id, amount, 
SUM(amount) OVER (ORDER BY id) AS running_total
FROM payments;

---

28. How do you use AUTO_INCREMENT and reset it?

Explanation:
AUTO_INCREMENT creates unique values. You can reset it using ALTER.

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- Reset to 1
ALTER TABLE users AUTO_INCREMENT = 1;

---

29. What is the difference between DELETE, TRUNCATE, and DROP?

Explanation:

• DELETE: removes rows, logs each row (can rollback)

• TRUNCATE: removes all rows, faster, can’t rollback

• DROP: removes table structure and data

Example:

DELETE FROM users WHERE id = 1;
TRUNCATE TABLE users;
DROP TABLE users;

---

30. How to prevent SQL injection in MySQL (via PHP or backend)?

Explanation:
Use prepared statements with bound parameters.

Example (using PDO in PHP):

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

---

31. What are temporary tables in MySQL and when are they used?

Explanation:
Temporary tables store intermediate results and are automatically dropped when the session ends.

Example:

CREATE TEMPORARY TABLE temp_sales AS
SELECT * FROM sales WHERE year = 2024;

SELECT * FROM temp_sales;

Useful in complex procedures or large data transformations.

---

32. What is the difference between IN and EXISTS?

Explanation:

• IN: good for small result sets.

• EXISTS: efficient for checking existence, especially with large datasets.

Example:

-- IN
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments);

-- EXISTS
SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE e.dept_id = d.id
);

---

33. What are the different types of subqueries in MySQL?

Explanation:

• Scalar Subquery: Returns one value

• Row Subquery: Returns one row

• Table Subquery: Returns multiple rows

• Correlated Subquery: Depends on outer query

Example (Correlated):

SELECT name FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);

---

34. What is the difference between NOW(), CURDATE(), and SYSDATE()?

Explanation:

• NOW(): Current date and time

• CURDATE(): Only the current date

• SYSDATE(): Same as NOW(), but not cached (runs at call time)

Example:

SELECT NOW(), CURDATE(), SYSDATE();

---

35. What is REPLACE INTO in MySQL?

Explanation:
REPLACE INTO works like INSERT, but if a row with the same primary/unique key exists, it deletes it and inserts the new one.

Example:

REPLACE INTO users (id, name) VALUES (1, 'Alice');

---

36. What are ENUM and SET data types?

Explanation:

• ENUM: A string object with one value from a predefined list.

• SET: Can store multiple values from a predefined list.

Example:

-- ENUM
CREATE TABLE orders (status ENUM('pending', 'shipped', 'delivered'));

-- SET
CREATE TABLE users (roles SET('admin', 'editor', 'subscriber'));

---

37. How to handle NULLs in MySQL sorting and comparison?

Explanation:
NULLs are considered unknown values. In ORDER BY, NULLs come first unless sorted explicitly.

Example:

SELECT * FROM employees ORDER BY salary IS NULL, salary DESC;

---

38. What is the difference between FLOAT, DOUBLE, and DECIMAL?

Explanation:

• FLOAT & DOUBLE: Approximate values (fast, less accurate).

• DECIMAL: Exact values (better for money, accurate).

Example:

price FLOAT(8,2)
price DOUBLE(16,4)
price DECIMAL(10,2)

---

39. How do transactions work in MySQL?

Explanation:
A transaction is a group of SQL queries executed as a single unit using BEGIN, COMMIT, ROLLBACK.

Example:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

---

40. How to monitor query performance using slow query log?

Explanation:
Enable slow query log to track queries that exceed a certain duration.

Enable:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- in seconds

---

41. Write a query to find the second highest salary from the employees table.

Query:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

---

42. Write a query to fetch duplicate rows based on email in a users table.

Query:

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

---

43. Write a query to get the department with the highest total salary.

Query:

SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id
ORDER BY total_salary DESC
LIMIT 1;

---

44. Write a query to get employees who have the same salary.

Query:

SELECT salary
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

---

45. Write a query to get the top 3 highest salaries without using LIMIT.

Query:

SELECT DISTINCT salary
FROM employees e1
WHERE 3 > (
  SELECT COUNT(DISTINCT salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
)
ORDER BY salary DESC;

---

46. Write a query to count the number of employees in each department and sort by count descending.

Query:

SELECT dept_id, COUNT(*) AS total_employees
FROM employees
GROUP BY dept_id
ORDER BY total_employees DESC;

---

47. Write a query to fetch employees who joined in the last 30 days.

Query:

SELECT * FROM employees
WHERE join_date >= CURDATE() - INTERVAL 30 DAY;

---

48. Write a query to fetch the top 2 highest salaries per department.

Query:

SELECT * FROM (
  SELECT *, 
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked_employees
WHERE rnk <= 2;

Requires MySQL 8.0+ (for RANK())

---

49. Write a query to find all managers who don’t have any employees reporting to them.

Query:

SELECT m.id, m.name
FROM managers m
LEFT JOIN employees e ON m.id = e.manager_id
WHERE e.id IS NULL;

---

50. Write a query to fetch employees with palindrome names.

Query (if function allowed):

SELECT * FROM employees
WHERE name = REVERSE(name);

---