SQL Interview Questions and Answers for 10 years experience

1. What is the difference between clustered and non-clustered indexes?

   • Answer: A clustered index physically reorders the data rows in a table based on the index key. There can only be one clustered index per table. A non-clustered index creates a separate structure that points to the data rows, allowing for faster lookups without altering the physical order of the data. A table can have multiple non-clustered indexes.

2. Explain the different types of joins in SQL.

   • Answer: SQL offers various JOIN types: INNER JOIN (returns rows only when there is a match in both tables), LEFT (OUTER) JOIN (returns all rows from the left table and matching rows from the right table; unmatched rows from the right table are filled with NULLs), RIGHT (OUTER) JOIN (similar to LEFT JOIN but for the right table), and FULL (OUTER) JOIN (returns all rows from both tables, filling unmatched rows with NULLs).

3. How do you handle NULL values in SQL?

   • Answer: NULL represents the absence of a value. Use IS NULL and IS NOT NULL to check for NULLs in WHERE clauses. Functions like COALESCE or ISNULL can replace NULLs with a specific value. Use NVL (in Oracle) or similar functions for similar purposes. Be cautious when using comparison operators directly with NULLs as they typically result in UNKNOWN.

4. What are different ways to optimize SQL queries?

   • Answer: Optimization techniques include creating appropriate indexes, using efficient JOIN types, avoiding SELECT *, optimizing WHERE clauses (using appropriate data types and avoiding functions on indexed columns), using set-based operations instead of row-by-row processing, analyzing query execution plans, and using stored procedures for frequently executed queries. Proper database normalization and data partitioning also contribute significantly.

5. Explain the concept of normalization in databases.

   • Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves applying normal forms (1NF, 2NF, 3NF, BCNF, etc.), each addressing specific types of redundancy. Higher normal forms reduce redundancy further but may increase query complexity.

6. What are transactions and ACID properties?

   • Answer: A transaction is a sequence of database operations performed as a single logical unit of work. ACID properties ensure data integrity: Atomicity (all operations succeed or none do), Consistency (database remains consistent after a transaction), Isolation (concurrent transactions don't interfere), Durability (changes persist even after system failures).

7. How do you handle concurrency issues in SQL?

   • Answer: Concurrency issues arise when multiple transactions access and modify the same data simultaneously. Techniques to handle them include using transactions with appropriate isolation levels (e.g., serializable, repeatable read), optimistic locking (checking for data changes before committing), pessimistic locking (locking resources explicitly), and using stored procedures with appropriate locking mechanisms.

8. Explain different types of database locks.

   • Answer: Common database locks include shared locks (multiple transactions can read but not modify), exclusive locks (only one transaction can access), and update locks (preventing others from reading or writing while a transaction updates).

9. What are stored procedures? What are their advantages?

   • Answer: Stored procedures are pre-compiled SQL code stored in the database. Advantages include improved performance (pre-compilation), enhanced security (access control), reduced network traffic (code executed on the server), and easier code maintenance and reusability.

Thank you for reading our blog post on 'SQL Interview Questions and Answers for 10 years experience'.We hope you found it informative and useful.Stay tuned for more insightful content!