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

Answer:

• UNION removes duplicate records.

• UNION ALL keeps all records, including duplicates.

2. What is the difference between INNER JOIN and OUTER JOIN?

Answer:

• INNER JOIN returns only matching rows from both tables.

• OUTER JOIN returns matching and non-matching rows (includes LEFT JOIN, RIGHT JOIN, FULL JOIN).

3. What is the purpose of the HAVING clause?

Answer: HAVING filters aggregated results after the GROUP BY clause.

4. What are Subqueries in SQL?

Answer: A Subquery is a nested query inside another query.

5. What is a Self Join?

Answer: A Self Join joins a table with itself.

SELECT A.employee_name, B.manager_name FROM employees A JOIN employees B ON A.manager_id = B.employee_id;

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

Answer:

• DELETE removes specific rows.

• TRUNCATE removes all rows but keeps the table structure.

• DROP deletes the table permanently.

7. What is the difference between EXISTS and IN?

Answer:

• EXISTS checks if a subquery returns any records (faster for large datasets).

• IN checks if a value exists in a given set.

8. What is the difference between COUNT(*), COUNT(column_name), and COUNT(DISTINCT column_name)?

Answer:

• COUNT(*) – Counts all rows.

• COUNT(column_name) – Counts non-null values.

• COUNT(DISTINCT column_name) – Counts unique non-null values.

9. How do you update data using a JOIN?

Answer:

UPDATE employees SET employees.salary = departments.salary * 1.1 FROM employees JOIN departments ON employees.dept_id = departments.dept_id;

10. How do you fetch the second-highest salary?

Answer:

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

11. What is a VIEW in SQL?

Answer: A VIEW is a virtual table based on a query.

12. How do you create and drop a VIEW?

Answer:

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name; DROP VIEW view_name;

13. What is the difference between VARCHAR and TEXT?

Answer:

• VARCHAR has a length limit and is stored in-row.

• TEXT can store large strings but is stored separately.

14. What is the use of the COALESCE() function?

Answer: Returns the first non-null value from a list.

SELECT COALESCE(column1, column2, 'Default Value') FROM table_name;

15. How do you optimize SQL queries?

Answer:

• Use Indexes for faster searching.

• Avoid SELECT *, select only necessary columns.

• Use joins efficiently instead of subqueries.

• Optimize with Partitioning and Clustering.

16. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Answer:

• RANK() – Skips ranks for duplicates.

• DENSE_RANK() – No gaps in ranking.

• ROW_NUMBER() – Assigns unique row numbers.

17. What is CROSS JOIN?

Answer: Produces a Cartesian Product of two tables.

SELECT * FROM table1 CROSS JOIN table2;

18. What is a Stored Procedure?

Answer: A Stored Procedure is a precompiled SQL block that can be executed multiple times.

19. How do you create a Stored Procedure?

Answer:

CREATE PROCEDURE procedure_name AS BEGIN SELECT * FROM employees; END;

20. How do you call a Stored Procedure?

Answer:

EXEC procedure_name;

21. What is the difference between a Function and a Stored Procedure?

Answer:

• Functions return a value and can be used in SELECT statements.

• Stored Procedures execute SQL statements but do not return values directly.

22. How do you prevent SQL Injection?

Answer:

• Use prepared statements and parameterized queries.

• Sanitize user inputs.

• Use ORM frameworks.

23. How do you check database constraints on a table?

Answer:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'your_table';

24. How do you rollback a transaction?

Answer:

BEGIN TRANSACTION; UPDATE employees SET salary = 100000 WHERE emp_id = 1; ROLLBACK;

25. How do you commit a transaction?

Answer:

BEGIN TRANSACTION; UPDATE employees SET salary = 100000 WHERE emp_id = 1; COMMIT;

1. What is a Common Table Expression (CTE)?

Answer: A CTE is a temporary result set that can be referenced in a query.

WITH EmployeeCTE AS ( SELECT emp_id, emp_name, salary FROM employees ) SELECT * FROM EmployeeCTE;

2. What is the difference between CTE and TEMP TABLE?

Answer:

• CTE exists only for a single query execution.

• TEMP TABLE exists for the entire session.

3. How do you use Recursive CTEs in SQL?

Answer:

WITH RecursiveCTE (emp_id, emp_name, manager_id) AS ( SELECT emp_id, emp_name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id FROM employees e JOIN RecursiveCTE r ON e.manager_id = r.emp_id ) SELECT * FROM RecursiveCTE;

4. What is the purpose of CASE in SQL?

Answer: Conditional logic inside SQL queries.

SELECT emp_name, CASE WHEN salary > 50000 THEN 'High' WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees;

5. What is a Materialized View?

Answer: A Materialized View stores query results physically, improving performance.

6. How do you refresh a Materialized View?

Answer:

REFRESH MATERIALIZED VIEW view_name;

7. What is a Trigger in SQL?

Answer: A Trigger is an event-driven SQL procedure executed before/after INSERT, UPDATE, or DELETE.

8. How do you create a Trigger?

CREATE TRIGGER salary_update AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_salary_change();

9. What is an Index?

Answer: An Index speeds up data retrieval.

10. What are the different types of Indexes?

Answer:

• Clustered Index (stores rows in a sorted order)

• Non-Clustered Index (stores only pointers to data)

• Unique Index (ensures column uniqueness)

• Full-Text Index (for text searching)

11. How do you create an Index?

CREATE INDEX idx_name ON employees (emp_name);

12. What is Partitioning in SQL?

Answer: Dividing a large table into smaller partitions for faster query performance.

13. How do you implement Table Partitioning?

CREATE TABLE employees_partitioned ( emp_id INT NOT NULL, emp_name VARCHAR(255), salary INT ) PARTITION BY RANGE (salary);

14. What is Sharding?

Answer: Horizontal partitioning of data across multiple databases.

15. How do you handle Duplicate Records in SQL?

DELETE FROM employees WHERE emp_id NOT IN ( SELECT MIN(emp_id) FROM employees GROUP BY emp_name, salary );

16. How do you perform Full Outer Join?

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;

17. How do you fetch the 5th highest salary?

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) AS ranked_salaries WHERE rnk = 5;

18. How do you find employees with duplicate names?

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

19. How do you detect missing values in a table?

SELECT * FROM employees WHERE salary IS NULL;

20. How do you swap two column values in a table?

UPDATE employees SET column1 = column2, column2 = column1;

21. What is a Deadlock in SQL?

Answer: A Deadlock occurs when two transactions block each other while waiting for a resource.

22. How do you prevent Deadlocks?

Answer:

• Use LOCK_TIMEOUT.

• Use transactions in the same order.

• Avoid long transactions.

23. What is the difference between Serializable and Read Committed Isolation Levels?

Answer:

• Serializable – Strictest, avoids all conflicts.

• Read Committed – Allows non-repeatable reads.

24. How do you check a query’s Execution Plan?

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

25. What is an ACID Transaction?

Answer:

• Atomicity – All or nothing.

• Consistency – Maintains database integrity.

• Isolation – Transactions are independent.

• Durability – Changes persist after commits

1. What is the difference between OLTP and OLAP?

Answer:

• OLTP (Online Transaction Processing): Handles real-time transactions (e.g., banking).

• OLAP (Online Analytical Processing): Used for data analysis & reporting (e.g., business intelligence).

2. How do you optimize a slow SQL query?

Answer:

• Use Indexes on frequently searched columns.

• Avoid SELECT *, fetch only required columns.

• Use EXPLAIN ANALYZE to check query execution plans.

• Normalize tables to avoid redundancy.

• Use joins efficiently instead of subqueries.

3. What is a Query Execution Plan?

Answer: It shows how SQL processes a query, helping optimize performance.

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

4. What is a SQL Injection Attack? How to prevent it?

Answer: SQL Injection occurs when attackers insert malicious SQL into a query.
Prevention:

• Use Prepared Statements.

• Validate user inputs.

• Use Stored Procedures instead of dynamic SQL.

SELECT * FROM users WHERE username = ? AND password = ?;

5. What are Stored Procedures?

Answer: A stored procedure is a set of SQL statements stored in a database.

CREATE PROCEDURE GetEmployees() AS BEGIN SELECT * FROM employees; END;

6. What are the advantages of Stored Procedures?

Answer:

• Improves performance.

• Reduces network traffic.

• Enhances security (hides business logic).

7. What is a Deadlock in SQL?

Answer: A Deadlock occurs when two transactions block each other while waiting for a resource.

8. How do you resolve Deadlocks?

Answer:

• Use LOCK_TIMEOUT.

• Order transactions consistently.

• Keep transactions short.

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

Answer:

• UNION removes duplicates.

• UNION ALL keeps all records, including duplicates.

SELECT name FROM employees UNION SELECT name FROM managers;

10. How do you get the third highest salary from a table?

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) AS ranked_salaries WHERE rnk = 3;

11. How do you detect duplicate records in a table?

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

12. What is the difference between WHERE and HAVING?

Answer:

• WHERE filters rows before aggregation.

• HAVING filters rows after aggregation.

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

13. What is Normalization?

Answer: The process of organizing a database to remove redundancy and improve consistency.

14. Explain different Normal Forms (1NF, 2NF, 3NF, BCNF).

Answer:

• 1NF: No duplicate columns, atomic values only.

• 2NF: 1NF + no partial dependencies.

• 3NF: 2NF + no transitive dependencies.

• BCNF: Stronger form of 3NF, no functional dependencies except primary key.

15. What is Denormalization?

Answer: The process of combining tables to improve read performance by reducing joins.

16. What is the difference between Clustered and Non-Clustered Indexes?

Answer:

• Clustered Index: Sorts data physically (only one per table).

• Non-Clustered Index: Stores pointers to data (multiple allowed).

CREATE CLUSTERED INDEX idx_employee ON employees(emp_id);

17. What is an Execution Plan, and how do you view it?

Answer: Shows how a query will be executed.

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'IT';

18.What is the difference between INNER JOIN and LEFT JOIN?

Answer:

• INNER JOIN returns only matching records from both tables.

• LEFT JOIN returns all records from the left table and matching records from the right table. If no match is found, NULL values are returned for columns from the right table.

19.What are SQL Joins?

Answer: SQL Joins are used to combine data from multiple tables.

Join TypeDescription

INNER JOINReturns matching rows.

LEFT JOINReturns all rows from left table + matching from right.

RIGHT JOINReturns all rows from right table + matching from left.

FULL JOINReturns all rows from both tables.

20. How do you use an Index to optimize queries?

CREATE INDEX idx_salary ON employees(salary);

21. What are Window Functions in SQL?

Answer: Functions that perform calculations across a result set without grouping rows.

SELECT emp_name, salary, RANK() OVER (ORDER BY salary DESC) AS SalaryRank FROM employees;

22. What is a Pivot Table in SQL?

Answer: A Pivot Table transforms row data into columns.

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department PIVOT(total_salary FOR department IN ('IT', 'HR', 'Finance'));

23. How do you update a column using a Subquery?

UPDATE employees SET salary = salary * 1.10 WHERE department = (SELECT department FROM departments WHERE dept_name = 'HR');

24. How do you check for NULL values in SQL?

SELECT * FROM employees WHERE salary IS NULL;

25. What is the difference between a Primary Key and a Unique Key?

FeaturePrimary KeyUnique Key

Uniqueness✅ Yes✅ Yes

NULL Allowed❌ No✅ Yes