76. What is Query Optimization?

Query optimization means improving SQL performance so it runs faster.

Ways to optimize:

Use indexes
Avoid SELECT *
Use proper joins
Use LIMIT when needed

Example:

EXPLAIN SELECT name FROM employees WHERE email = 'abc@test.com';

77. What is EXPLAIN in MySQL?

EXPLAIN shows how MySQL will run a query.
It helps find slow parts.

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

78. What is Sharding in Databases?

Sharding means splitting a large database into smaller pieces (horizontal partition).

Example:

Shard 1: users with id 1–1,00,000
Shard 2: users with id 1,00,001–2,00,000

79. What is Replication in MySQL?

Replication copies data from Master to Slave servers for backup and load sharing.

80. Difference between Master and Slave in MySQL Replication?

Master	Slave
Writes data	Reads data
Sends updates	Receives updates
Main DB	Backup/Read DB

81. What is ACID Property?

ACID ensures reliable transactions:

A – Atomicity (all or nothing)
C – Consistency (valid state)
I – Isolation (separate transactions)
D – Durability (permanent after commit)

82. What is Transaction in SQL?

A transaction is a group of SQL operations that act as one unit.

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

83. What is Savepoint?

Savepoint is a checkpoint inside a transaction.

SAVEPOINT s1;
ROLLBACK TO s1;

84. What is Deadlock?

Deadlock happens when two transactions wait for each other forever.

85. How to prevent Deadlock?

Access tables in the same order
Keep transactions short
Use proper indexes

86. What is SQL Injection?

A hacking technique where attacker injects SQL code into input fields.

Example malicious input:

' OR '1'='1

87. How to prevent SQL Injection?

Use prepared statements
Validate inputs

Example (MySQL Prepared Statement):

PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';

88. What is a JSON field in MySQL?

MySQL supports a JSON data type to store JSON objects.

INSERT INTO products (name, details)
VALUES ('Laptop', JSON_OBJECT('brand','Dell','ram','16GB'));

89. How to extract data from JSON in MySQL?

SELECT details->>'$.brand' AS brand
FROM products;

90. What is Full-Text Search in MySQL?

It allows searching text-based columns faster.

SELECT * FROM articles
WHERE MATCH(content) AGAINST('database tuning');

91. What is UNION?

UNION combines results of two SELECT queries without duplicates.

SELECT name FROM employees
UNION
SELECT name FROM managers;

92. What is UNION ALL?

It returns all rows, including duplicates.

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

93. Difference between UNION and UNION ALL?

UNION	UNION ALL
Removes duplicates	Keeps duplicates
Slower	Faster

94. What is a Materialized View?

A Materialized View stores data physically.
(MySQL does not support it directly, but can be simulated using tables + triggers.)

95. What is IF EXISTS in SQL?

DROP TABLE IF EXISTS temp_table;

It avoids errors when an object does not exist.

96. What is Stored Function?

A function returns a single value.

CREATE FUNCTION get_bonus(salary INT)
RETURNS INT
RETURN salary * 0.10;

97. What is DISTINCT in SQL?

Removes duplicate values.

SELECT DISTINCT city FROM customers;

98. What is Cross Join?

Cross join combines all rows from both tables (Cartesian product).

SELECT a.name, b.product
FROM customers a
CROSS JOIN products b;

99. What is Referential Integrity?

Ensures foreign key values always reference existing rows in parent table.

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

Command	Action	Rollback
DELETE	Removes specific rows	Yes
TRUNCATE	Removes all rows	No (in MySQL sometimes possible)
DROP	Deletes table	No