51. What is the ACID Property in SQL?

ACID ensures reliable transactions.

Property	Meaning
A – Atomicity	All steps succeed or all fail.
C – Consistency	Data must remain valid before & after transaction.
I – Isolation	One transaction does not disturb another.
D – Durability	Changes survive even system crash.

---

52. What is a Transaction in SQL?

A transaction is a group of SQL operations executed as a single unit.

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;   -- save permanently

---

53. Difference between COMMIT and ROLLBACK?

COMMIT	ROLLBACK
Saves changes permanently	Undo all uncommitted changes
Cannot revert	Can revert until commit

---

54. What is Savepoint in SQL?

A savepoint marks a checkpoint inside a transaction.

START TRANSACTION;

UPDATE employees SET salary = salary + 2000;

SAVEPOINT step1;

UPDATE employees SET salary = salary + 5000;

ROLLBACK TO step1;   -- undo only second update
COMMIT;

---

55. Difference between IN and EXISTS?

IN	EXISTS
Faster for small lists	Faster for large tables
Compares values	Checks row existence
Works on static values	Works with correlated subqueries

Example (EXISTS):

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

---

56. Difference between DELETE and TRUNCATE?

DELETE	TRUNCATE
Removes selected rows	Removes all rows
Slower	Very fast
Can ROLLBACK	Cannot rollback (in MySQL for many engines)
Triggers fire	Triggers don’t fire

---

57. What is Index Fragmentation?

Index fragmentation means the index pages are not stored in order, leading to slow performance.

It occurs due to:

• Frequent insert/update/delete
• Page splits

Solution: rebuild index.

---

58. Difference between RANK() and DENSE_RANK()?

RANK()	DENSE_RANK()
Skips rank numbers	Doesn’t skip
1, 2, 2, 4	1, 2, 2, 3

---

59. How to fetch common records from two tables?

Method 1 – Using INNER JOIN

SELECT a.id
FROM tableA a
INNER JOIN tableB b ON a.id = b.id;

Method 2 – Using IN

SELECT id FROM tableA
WHERE id IN (SELECT id FROM tableB);

---

60. Difference between UNION and JOIN?

UNION	JOIN
Combines rows vertically	Combines columns horizontally
Same number of columns required	No column count requirement
Removes duplicates (UNION)	Does not remove duplicates

---

61. What is Pivot Table in SQL?

MySQL has no direct PIVOT, but we create pivot using CASE + GROUP BY.

Example: Show total salary by department:

SELECT
  SUM(CASE WHEN dept_id = 1 THEN salary END) AS dept1_salary,
  SUM(CASE WHEN dept_id = 2 THEN salary END) AS dept2_salary
FROM employees;

---

62. What is Case Sensitivity in SQL?

SQL keywords are not case sensitive,
But MySQL table names may be case-sensitive depending on OS.

---

63. How to find the Nth Highest Salary?

Example for 5th highest salary:

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

---

64. How to get First 3 Maximum Salaries?

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

---

65. Difference between DROP, DELETE, and TRUNCATE?

Command	Description
DELETE	Removes specific rows (slow)
TRUNCATE	Removes all rows (fast)
DROP	Deletes whole table structure

---

66. How to calculate Age from Date of Birth in SQL?

SELECT name,
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM employees;

---

67. What is Recursive Query in SQL?

A recursive query is a query that calls itself using a CTE.

MySQL Example: Generate numbers 1 to 5

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

---

68. Difference between Temporary Table and CTE?

Temporary Table	CTE
Stored physically	In memory
Can be reused multiple times	Used once per query
Slightly slower	Faster
Needs CREATE TEMPORARY TABLE	Uses WITH keyword

---

69. How to find Odd and Even records in SQL?

Assume id is numeric.

Even rows:

SELECT * FROM employees WHERE id % 2 = 0;

Odd rows:

SELECT * FROM employees WHERE id % 2 = 1;

---

70. What is JSON in SQL?

MySQL supports a JSON data type to store structured JSON data.

Example:

CREATE TABLE users (
   id INT,
   details JSON
);

---

71. What is XML in SQL?

MySQL doesn’t have a native XML type.
XML is stored as TEXT.

INSERT INTO xml_table (xml_text)
VALUES ('<employee><name>Asha</name></employee>');

---

72. How to handle NULL values in SQL?

Use functions like:

• IFNULL()
• COALESCE()

Example:

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

---

73. What is Dynamic SQL?

Dynamic SQL means building SQL queries as strings at runtime.

Used mostly inside procedures.

Example:

SET @q = 'SELECT * FROM employees WHERE salary > 50000';
PREPARE stmt FROM @q;
EXECUTE stmt;

---

74. How to calculate Percentage in SQL?

SELECT name,
       (score / total_score) * 100 AS percentage
FROM students;

---

75. How to find Employees who earn more than their Manager?

Assume manager_id exists:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;