---

26. What is a Subquery?

A subquery is a query inside another query.
It is used when you need the result of one query to use in another.

Example: Get employees who earn more than the average salary:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

---

27. What is a Nested Query?

A nested query is another name for a subquery.
It simply means a query inside another query.

---

28. What is a Correlated Subquery?

A correlated subquery runs again and again, once for each row of the outer query.

Example: Find employees whose salary is above their department average:

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

---

29. What is Group By in SQL?

GROUP BY groups rows that have the same values and is used with aggregates.

Example: Count employees in each department:

SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id;

---

30. Difference between Group By and Order By?

GROUP BY	ORDER BY
Groups data	Sorts data
Used with aggregates	Used for sorting
No sorting guarantee	Always sorts output

---

31. What is the use of LIMIT in SQL?

LIMIT restricts the number of rows returned.

Example: Show first 5 records:

SELECT * FROM employees LIMIT 5;

---

32. How to find the Second Highest Salary in SQL?

Method 1: Using LIMIT

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

---

33. How to find Duplicate Records in a table?

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

---

34. What is CTE (Common Table Expression)?

A CTE is a temporary result created using WITH keyword.
It improves readability.

Example:

WITH high_salary AS (
   SELECT name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM high_salary;

---

35. What is Temporary Table in SQL?

A temporary table stores data temporarily for the session.

CREATE TEMPORARY TABLE temp_emp AS
SELECT * FROM employees WHERE salary > 50000;

---

36. What is a Window Function?

Window functions perform calculations across rows without grouping.

Example: Assign row numbers:

SELECT name, salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS sl_no
FROM employees;

---

37. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Function	Output	Duplicate handling
ROW_NUMBER	No duplicates	Always unique
RANK	Skips ranks	1, 2, 2, 4
DENSE_RANK	No skip in ranks	1, 2, 2, 3

---

38. What is CASE Statement in SQL?

CASE works like IF-ELSE.

Example:

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

---

39. What is COALESCE in SQL?

COALESCE returns the first non-NULL value.

SELECT COALESCE(email, 'No Email') AS email_id
FROM employees;

---

40. What is NVL Function in SQL?

NVL is Oracle-only, but MySQL uses IFNULL() instead.

MySQL Example:

SELECT IFNULL(phone, 'Not Provided') FROM employees;

---

41. What is Indexing in SQL?

An index improves search speed in a table, just like an index in a book.

CREATE INDEX idx_name ON employees(name);

---

42. What is a Clustered Index?

MySQL (InnoDB) automatically makes the Primary Key a clustered index.
It stores data physically sorted by the key.

---

43. What is Non-Clustered Index?

A non-clustered index creates a separate structure that points to the data.

Example:

CREATE INDEX idx_email ON employees(email);

---

44. Difference between Clustered and Non-Clustered Index?

Clustered Index	Non-Clustered Index
Sorts actual data	Stores pointers
Only one per table	Many allowed
Fast for range queries	Fast for exact matches

---

45. What is a View in SQL?

A view is a virtual table created from a SELECT query.

CREATE VIEW emp_view AS
SELECT name, salary FROM employees;

---

46. Difference between View and Table?

View	Table
Virtual	Physical
Stores query	Stores data
Auto updates	Manual updates

---

47. What is Stored Procedure?

A stored procedure is a saved SQL block that you can run anytime.

DELIMITER $$
CREATE PROCEDURE get_emps()
BEGIN
   SELECT * FROM employees;
END$$
DELIMITER ;

---

48. Difference between Function and Stored Procedure?

Stored Procedure	Function
Can return multiple values	Returns single value
Can use INSERT/UPDATE	Mostly calculations
Called using CALL	Used inside queries

---

49. What is Trigger in SQL?

Trigger runs automatically when an event (INSERT/UPDATE/DELETE) happens.

Example:

CREATE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();

---

50. What is Cursor in SQL?

A cursor is used to process rows one-by-one (rarely used in MySQL but supported).

DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT name FROM employees;