Top 40 SQL Interview Questions for 3 Years Experience with Answers

Preparing for an SQL interview can be challenging for professionals with three years of experience. To help you out, we have compiled the top 40 SQL interview questions for professionals with 3 years of experience, along with detailed answers. This guide covers basic to advanced questions, so you can confidently demonstrate your SQL proficiency and secure your desired role.

Top 40 SQL Interview Questions for 3 Years Experience with Answers

1. What is a Common Table Expression (CTE), and how does it differ from a subquery?
2. How can you retrieve the nth highest salary from an employee table?
3. Explain the difference between INNER JOIN and LEFT JOIN.
4. What is indexing, and what are the different types of indexes in SQL?
5. Provide an example of a window function in SQL.
6. What are recursive CTEs, and when are they useful?
7. How can you find the second highest salary in a table?
8. What is the difference between DELETE and TRUNCATE commands?
9. What is normalization, and what are its different forms?
10. What is denormalization, and when is it used?
11. Explain ACID properties in the context of database transactions.
12. How do you optimize a slow-running query?
13. What is a stored procedure, and what are its advantages?
14. How does the GROUP BY clause work, and how is it different from ORDER BY?
15. What are transactions, and why are they important in databases?
16. How can you handle exceptions in SQL?
17. What is a trigger, and when would you use one?
18. Explain the concept of indexing and its impact on database performance.
19. What are the differences between clustered and non-clustered indexes?
20. How do you maintain indexes in a database?
21. What is a correlated subquery, and how does it differ from a regular subquery?
22. How can you delete duplicate rows from a table?
23. What is a materialized view, and how does it differ from a regular view?
24. How do you implement a recursive query in SQL?
25. What is the difference between RANK() and DENSE_RANK() functions?
26. How can you perform a full outer join in databases that do not support it natively?
27. What are window functions, and how do they differ from aggregate functions?
28. How do you handle NULL values in SQL?
29. What is the difference between COALESCE() and ISNULL()?
30. How can you prevent SQL injection attacks?
31. What is the purpose of the HAVING clause in SQL?
32. What is a self-join, and when would you use it?
33. How can you retrieve the top N records for each group in SQL?
34. What is the difference between UNION and UNION ALL?
35. What is a Common Table Expression (CTE), and how does it differ from a subquery?
36. What are common causes of database deadlocks, and how can they be prevented?
37. What is the difference between the RANK() and DENSE_RANK() functions in SQL?
38. How can you optimize a slow-running query in SQL?
39. What is the difference between a primary key and a unique key?
40. How do you implement transactions in SQL, and what are ACID properties?

1. What is a Common Table Expression (CTE), and how does it differ from a subquery?

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is created using the WITH keyword. Unlike subqueries, CTEs enhance readability and can be referenced multiple times within the main query, simplifying complex queries. Additionally, CTEs support recursive queries, which are challenging to implement with traditional subqueries.

2. How can you retrieve the nth highest salary from an employee table?

To obtain the nth highest salary, you can use the DENSE_RANK() function, which assigns ranks to salaries in descending order, allowing you to filter for the desired rank efficiently. Here’s an example query:

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

Replace n with the desired rank to retrieve the corresponding salary.

3. Explain the difference between INNER JOIN and LEFT JOIN.

An INNER JOIN retrieves only the rows that have matching values in both tables involved in the join. In contrast, a LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there’s no match, the result is NULL on the side of the right table.

4. What is indexing, and what are the different types of indexes in SQL?

Indexing is a technique used to enhance database performance by speeding up data retrieval processes. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. The different types of indexes include:

• Clustered Index: Sorts and stores the data rows of the table or view in order based on the index key. There can be only one clustered index per table.
• Non-Clustered Index: Contains a sorted list of references to the table data. A table can have multiple non-clustered indexes.
• Unique Index: Ensures that all the values in the index key are unique.

5. Provide an example of a window function in SQL.

Window functions perform calculations across a set of table rows that are somehow related to the current row. They are often used for running totals, ranking, and moving averages. Here’s an example:

SELECT employee_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS department_avg_salary
FROM employees;

This query calculates the average salary within each department without collapsing the result set, meaning each row retains its identity while displaying the department’s average salary.

6. What are recursive CTEs, and when are they useful?

Recursive CTEs are Common Table Expressions that reference themselves, allowing the output of one iteration to be used as the input for the next. They are particularly useful for querying hierarchical data structures, such as organizational charts or bill-of-materials structures, where data is naturally recursive.

7. How can you find the second highest salary in a table?

To find the second highest salary, you can use the DENSE_RANK() function to assign ranks to each salary and then filter for the second rank:

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

This query assigns a rank to each salary in descending order and retrieves the salary with a rank of 2, which corresponds to the second highest salary.

8. What is the difference between DELETE and TRUNCATE commands?

Both DELETE and TRUNCATE are used to remove data from a table, but they operate differently:

• DELETE: Removes rows one at a time and logs each deletion. It can include a WHERE clause to specify which rows to delete and can be rolled back if within a transaction.
• TRUNCATE: Removes all rows from a table by deallocating the data pages. It is faster than DELETE because it doesn’t log individual row deletions. However, it cannot be rolled back in some database systems and doesn’t fire triggers.

9. What is normalization, and what are its different forms?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The common normal forms include:

• First Normal Form (1NF): Ensures that each column contains atomic (indivisible) values and each record is unique.
• Second Normal Form (2NF): Meets all requirements of 1NF and ensures that all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Meets all requirements of 2NF and ensures that all non-key attributes are not transitively dependent on the primary key.

10. What is denormalization, and when is it used?

Denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding redundant data to optimize read performance. While normalization aims to reduce redundancy and improve data integrity, denormalization is employed to enhance query performance, especially in read-heavy operations.

It’s commonly used in data warehousing and reporting systems where complex queries require data from multiple tables, and the overhead of joining these tables can be significant. By denormalizing, you can reduce the number of joins, thereby speeding up data retrieval. However, this comes at the cost of potential data anomalies and increased storage requirements.

11. Explain ACID properties in the context of database transactions.

ACID is an acronym representing the four key properties of database transactions to ensure data integrity:

• Atomicity: Ensures that all operations within a transaction are completed successfully; if any operation fails, the entire transaction is rolled back.
• Consistency: Guarantees that a transaction brings the database from one valid state to another, maintaining database invariants.
• Isolation: Ensures that concurrently executing transactions do not affect each other’s operations, leading to consistent results as if transactions were processed sequentially.
• Durability: Once a transaction is committed, its changes are permanent, even in the event of a system failure.

These properties collectively ensure reliable processing of database transactions.

12. How do you optimize a slow-running query?

Optimizing a slow-running query involves several steps:

1. Analyze the Query Execution Plan: Use the database’s explain plan feature to understand how the query is executed and identify bottlenecks.
2. Index Appropriately: Ensure that columns used in WHERE, JOIN, and ORDER BY clauses are indexed.
3. Avoid Select: Specify only the necessary columns to reduce the amount of data retrieved.
4. Optimize Joins: Use the most efficient join types and ensure that join keys are indexed.
5. Use Appropriate Data Types: Ensure that columns have the correct data types to optimize storage and comparison operations.
6. Limit the Result Set: Use LIMIT or equivalent to restrict the number of rows returned when appropriate.
7. Update Statistics: Keep database statistics up-to-date to help the optimizer make informed decisions.
8. Refactor Complex Queries: Break down complex queries into simpler parts or use temporary tables to manage intermediate results.

By systematically addressing these areas, you can significantly improve query performance.

13. What is a stored procedure, and what are its advantages?

A stored procedure is a precompiled collection of one or more SQL statements stored under a name and processed as a unit. Stored procedures are stored in the database and can be executed by applications to perform a specific task. Advantages include:

• Performance Improvement: Precompilation reduces parsing time for complex queries.
• Reusability: Encapsulates logic that can be reused across multiple applications.
• Security: Allows users to execute procedures without granting them direct access to underlying tables.
• Maintainability: Centralizes business logic in the database, simplifying maintenance and updates.

Stored procedures enhance both performance and manageability in database applications.

14. How does the GROUP BY clause work, and how is it different from ORDER BY?

The GROUP BY clause aggregates data based on one or more columns, allowing you to perform aggregate functions like SUM, COUNT, or AVG on grouped data. In contrast, the ORDER BY clause sorts the result set based on specified columns. While GROUP BY is used for aggregation, ORDER BY is used for sorting. For example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

This query groups employees by department, counts the number of employees in each department, and then orders the results by the employee count in descending order.

15. What are transactions, and why are they important in databases?

A transaction is a sequence of one or more SQL operations treated as a single logical unit of work. Transactions are crucial because they ensure data integrity and consistency. They adhere to the ACID properties, guaranteeing that database operations are completed accurately and reliably. For example, in a banking system, transferring funds between accounts involves multiple operations that must all succeed or fail together; transactions ensure this consistency.

16. How can you handle exceptions in SQL?

Exception handling in SQL is managed using constructs like TRY...CATCH blocks (in SQL Server) or BEGIN...EXCEPTION blocks (in PL/pgSQL for PostgreSQL). These constructs allow you to catch runtime errors and handle them gracefully. For example, in SQL Server:

BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH;

This structure enables you to manage errors without terminating the execution flow abruptly.

17. What is a trigger, and when would you use one?

A trigger is a special type of stored procedure that automatically executes in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE operations. Triggers are used to enforce business rules, maintain audit trails, or synchronize tables. For example, a trigger can ensure that when a record is inserted into an orders table, the inventory is automatically updated accordingly.

18. Explain the concept of indexing and its impact on database performance.

Indexing involves creating data structures that improve the speed of data retrieval operations on a database table. Indexes function like a table of contents, allowing the database engine to locate data efficiently without scanning the entire table. The primary types of indexes are:

• Clustered Index: Determines the physical order of data in a table. There can be only one clustered index per table, and it directly affects how data rows are stored.
• Non-Clustered Index: Stored separately from the actual data, it contains pointers to the data’s physical location. A table can have multiple non-clustered indexes, each facilitating faster searches on different columns.

Proper indexing can significantly enhance query performance by reducing the amount of data the database engine needs to process. However, excessive or improper indexing can lead to increased storage requirements and slower write operations, as indexes need to be updated with data modifications. Therefore, it’s crucial to balance the number and type of indexes to optimize both read and write performance.

19. What are the differences between clustered and non-clustered indexes?

The key differences between clustered and non-clustered indexes include:

• Data Storage: A clustered index sorts and stores the data rows of the table based on the index key. In contrast, a non-clustered index maintains a separate structure that points to the physical data rows.
• Number of Indexes per Table: Each table can have only one clustered index because the data rows can be sorted in only one order. However, a table can have multiple non-clustered indexes to facilitate various search operations.
• Performance Impact: Clustered indexes can improve the performance of data retrieval operations that involve range scans or sorting. Non-clustered indexes are beneficial for queries that search on columns not covered by the clustered index.

Understanding these differences is crucial for designing efficient indexing strategies that enhance query performance while minimizing maintenance overhead.

20. How do you maintain indexes in a database?

Maintaining indexes is essential to ensure optimal database performance. Key maintenance activities include:

• Monitoring Fragmentation: Over time, indexes can become fragmented, leading to inefficient data retrieval. Regularly check fragmentation levels to determine if maintenance is needed.
• Reorganizing Indexes: For indexes with low to moderate fragmentation (typically 5% to 30%), perform an index reorganize operation. This process defragments the leaf level of the index pages without locking the database resources extensively.
• Rebuilding Indexes: For indexes with high fragmentation (above 30%), perform an index rebuild. This operation drops and recreates the index, thoroughly removing fragmentation but can be resource-intensive and may require downtime.
• Updating Statistics: Ensure that the database statistics are up-to-date to help the query optimizer make informed decisions.
• Removing Unused Indexes: Identify and drop indexes that are not being utilized, as they consume storage and can degrade performance during data modification operations.

Regular index maintenance helps in sustaining query performance and overall database efficiency.

21. What is a correlated subquery, and how does it differ from a regular subquery?

A correlated subquery is a subquery that references columns from the outer query, creating a dependency between the two. This means the subquery is executed repeatedly, once for each row processed by the outer query. In contrast, a regular (or non-correlated) subquery is independent and executes once, with its result used by the outer query.

Example of a correlated subquery:

SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

In this example, the subquery calculates the average salary for each department, correlating with the department_id of the outer query’s current row.

22. How can you delete duplicate rows from a table?

To remove duplicate rows while retaining one instance, you can use the ROW_NUMBER() window function to assign a unique sequential integer to rows within a partition of duplicates and then delete rows where this number is greater than one.

Example:

WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS rn
    FROM your_table
)
DELETE FROM CTE WHERE rn > 1;

Replace column1, column2, and your_table with the actual column names and table name. This query partitions the data by the columns that define duplicates and assigns a row number to each row within the partition. Rows with a row number greater than one are considered duplicates and are deleted.

23. What is a materialized view, and how does it differ from a regular view?

A materialized view is a database object that contains the results of a query and stores them physically. Unlike a regular view, which is a virtual table that dynamically retrieves data upon each access, a materialized view stores the query result and can be refreshed periodically. This can significantly improve performance for complex queries, as the data is precomputed and stored.

Key differences:

• Storage: Materialized views occupy storage space as they store data physically; regular views do not.
• Performance: Materialized views can enhance performance for complex and resource-intensive queries by providing precomputed results.
• Data Freshness: Regular views always display the most current data, while materialized views may show stale data depending on their refresh policy.

24. How do you implement a recursive query in SQL?

Recursive queries are implemented using Common Table Expressions (CTEs) with the WITH RECURSIVE clause. They are particularly useful for querying hierarchical data, such as organizational structures or bill-of-materials.

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

This query starts with employees who have no manager (top-level) and recursively joins employees to their managers, building the hierarchy level by level.

25. What is the difference between RANK() and DENSE_RANK() functions?

Both RANK() and DENSE_RANK() are window functions used to assign ranks to rows within a partition. The difference lies in how they handle ties:

• RANK(): Assigns the same rank to tied rows but skips subsequent ranks. For example, if two rows are tied at rank 1, the next rank assigned will be 3.
• DENSE_RANK(): Assigns the same rank to tied rows without skipping ranks. Following the same example, the next rank assigned would be 2.

Example:

SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

This query demonstrates the difference in ranking when there are ties in the salary column.

26. How can you perform a full outer join in databases that do not support it natively?

In databases that lack native support for FULL OUTER JOIN, you can simulate it by combining LEFT JOIN and RIGHT JOIN with a UNION.

Example:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT *
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.id;

This approach ensures that all records from both tables are included, with NULLs in place where there are no matches.

27. What are window functions, and how do they differ from aggregate functions?

Window functions perform calculations across a set of table rows related to the current row, without collapsing the result set. They provide the ability to perform operations like running totals, rankings, and moving averages.

Key differences from aggregate functions:

• Result Set: Window functions do not reduce the number of rows returned; aggregate functions do.
• Context: Window functions can access data from other rows without grouping; aggregate functions require grouping.

Example of a window function:

SELECT
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

This query calculates the average salary within each department without collapsing the result set.

28. How do you handle NULL values in SQL?

Handling NULL values in SQL is crucial for ensuring accurate data processing and avoiding unexpected results. Here are several methods to manage NULL values:

• Using Conditional Statements: The CASE statement allows for custom handling of NULL values.

SELECT
    employee_id,
    CASE
        WHEN birthdate IS NULL THEN 'N/A'
        ELSE DATE_DIFF(CURRENT_DATE, birthdate) / 365
    END AS age
FROM employees;

In this example, if birthdate is NULL, the query returns ‘N/A’; otherwise, it calculates the employee’s age.

Using Functions to Replace NULL Values:

• COALESCE(): Returns the first non-NULL value from a list of expressions.

SELECT COALESCE(column1, 'default_value') AS result FROM table_name;

This function checks column1; if it’s NULL, it returns ‘default_value’.

• ISNULL(): Replaces NULL with a specified replacement value.

SELECT ISNULL(column1, 'replacement_value') AS result FROM table_name;

If column1 is NULL, this function returns ‘replacement_value’.

• NULLIF(): Returns NULL if the two arguments are equal; otherwise, it returns the first argument.

SELECT NULLIF(column1, column2) AS result FROM table_name;

If column1 equals column2, the result is NULL; otherwise, it’s column1. ByteScout

Using IS NULL and IS NOT NULL: These operators are used to filter records with or without NULL values.

SELECT * FROM table_name WHERE column1 IS NULL;

This query retrieves all records where column1 is NULL.

By employing these methods, you can effectively manage NULL values in your SQL queries, ensuring data integrity and accurate results.

29. What is the difference between COALESCE() and ISNULL()?

Both COALESCE() and ISNULL() are used to handle NULL values, but they have distinct differences:

Functionality:

• COALESCE(): Evaluates a list of expressions and returns the first non-NULL value. It can take multiple arguments.

SELECT COALESCE(column1, column2, 'default') AS result FROM table_name;

This function returns the first non-NULL value among column1, column2, and ‘default’. W3Schools

• ISNULL(): Evaluates a single expression and replaces it with a specified value if it is NULL. It takes only two arguments.

SELECT ISNULL(column1, 'replacement') AS result FROM table_name;

If column1 is NULL, this function returns ‘replacement’.

Argument Handling:

• COALESCE(): Can handle multiple arguments and returns the first non-NULL value.
• ISNULL(): Handles only two arguments: the expression to check and the replacement value.

Data Type Precedence:

• COALESCE(): Returns the data type with the highest precedence among the arguments.
• ISNULL(): Returns the data type of the first argument.

Understanding these differences is crucial for effectively handling NULL values in SQL queries.

30. How can you prevent SQL injection attacks?

Preventing SQL injection attacks is vital for database security. Key practices include:

• Using Prepared Statements (Parameterized Queries): Ensure that user inputs are treated as data, not executable code.

-- Example in Python with SQLite
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

This approach ensures that username is treated as a parameter, not executable SQL code.

• Validating and Sanitizing User Inputs: Implement strict validation rules to ensure inputs conform to expected formats and types.

• Using Stored Procedures: Encapsulate SQL code within stored procedures to separate it from user inputs.

CREATE PROCEDURE GetUser
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT * FROM users WHERE username = @Username;
END;

This method ensures that input parameters are handled safely within the procedure.

• Implementing Least Privilege Principle: Grant users the minimum database permissions necessary to reduce potential damage from an injection attack.
• Employing Web Application Firewalls (WAF): Use WAFs to detect and block malicious SQL queries.

By adhering to these practices, you can significantly reduce the risk of SQL injection attacks and enhance the security of your database systems.

31. What is the purpose of the HAVING clause in SQL?

The HAVING clause in SQL is used to filter groups of rows after aggregation has been performed, typically in conjunction with the GROUP BY clause. While the WHERE clause filters rows before any aggregation, the HAVING clause applies conditions to the aggregated data.

Example:

SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 10;

In this query, the GROUP BY clause groups the rows by department_id, and the HAVING clause filters these groups to include only those with more than 10 employees.

Key Differences Between WHERE and HAVING:

• WHERE Clause: Filters individual rows before any grouping or aggregation. It cannot be used with aggregate functions.
• HAVING Clause: Filters groups after aggregation has occurred. It can be used with aggregate functions like COUNT, SUM, AVG, etc.

Understanding the distinction between WHERE and HAVING is crucial for constructing accurate SQL queries that involve aggregation.

32. What is a self-join, and when would you use it?

A self-join is a join operation where a table is joined with itself. This is useful when dealing with hierarchical data or when comparing rows within the same table.

Example:

SELECT e1.employee_id, e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

In this example, the employees table is joined with itself to pair each employee with their respective manager.

Use Cases for Self-Joins:

• Hierarchical Data: Representing organizational structures where employees have managers who are also employees.
• Comparative Analysis: Comparing rows within the same table, such as finding pairs of products with similar prices.

Self-joins are a powerful tool for querying hierarchical or self-referential data within a single table.

33. How can you retrieve the top N records for each group in SQL?

Retrieving the top N records for each group can be achieved using window functions like ROW_NUMBER() combined with a Common Table Expression (CTE) or a subquery.

Example:

WITH RankedEmployees AS (
    SELECT
        department_id,
        employee_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT department_id, employee_id, salary
FROM RankedEmployees
WHERE rank <= 3;

This query assigns a rank to each employee within their department based on salary and then filters to retrieve the top 3 highest-paid employees per department.

Steps Involved:

1. Partitioning: Use PARTITION BY to divide the data into groups (e.g., by department_id).
2. Ordering: Use ORDER BY within the window function to rank rows within each partition.
3. Filtering: In the outer query, filter rows based on the assigned rank to get the top N records per group.

This method is efficient and widely supported in SQL databases that implement window functions.

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

Both UNION and UNION ALL are used to combine the result sets of two or more SELECT statements.

Differences:

• UNION: Eliminates duplicate rows from the combined result set. This involves an additional sorting operation to identify and remove duplicates, which can impact performance.
• UNION ALL: Includes all rows from the combined result sets, including duplicates. It does not perform the duplicate elimination step, making it faster than UNION.

Example:

SELECT employee_id FROM employees_2023
UNION
SELECT employee_id FROM employees_2024;

This query combines employee IDs from two tables and removes duplicates.

SELECT employee_id FROM employees_2023
UNION ALL
SELECT employee_id FROM employees_2024;

This query combines employee IDs from two tables and includes all duplicates.

When to Use:

• Use UNION when you need to eliminate duplicates and are willing to incur the performance cost.
• Use UNION ALL when you want to retain all records and prioritize performance.

Choosing between UNION and UNION ALL depends on the specific requirements regarding duplicates and performance considerations.

35. What is a Common Table Expression (CTE), and how does it differ from a subquery?

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and maintainability of complex queries by breaking them into simpler, more manageable parts.

Syntax:

WITH CTE_Name (Column1, Column2, ...)
AS
(
    -- CTE Query Definition
    SELECT ...
)
-- Main Query
SELECT ...
FROM CTE_Name

Example:

WITH Sales_CTE AS
(
    SELECT
        SalesPersonID,
        SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPersonID
)
SELECT
    s.SalesPersonID,
    s.TotalSales,
    e.Name
FROM Sales_CTE s
JOIN Employees e ON s.SalesPersonID = e.EmployeeID;

Differences Between CTEs and Subqueries:

• Readability: CTEs enhance readability by allowing the definition of named result sets, making complex queries easier to understand.
• Recursion: CTEs support recursion, enabling operations like traversing hierarchical data structures.
• Reusability: CTEs can be referenced multiple times within the main query, whereas subqueries are typically evaluated each time they appear.

Understanding and utilizing CTEs can lead to more efficient and maintainable SQL code, especially when dealing with complex queries.

36. What are common causes of database deadlocks, and how can they be prevented?

A deadlock occurs when two or more transactions are each waiting for the other to release a resource, causing a cycle of dependencies that halts their progress. Understanding the common causes of deadlocks and implementing preventive measures is crucial for maintaining database performance and reliability.

Common Causes of Deadlocks:

1. Inconsistent Lock Acquisition Order: Transactions acquire locks on resources in different sequences, leading to circular wait conditions. Example:
   • Transaction A locks Resource 1, then attempts to lock Resource 2.
   • Transaction B locks Resource 2, then attempts to lock Resource 1.
   • Both transactions are now waiting for each other to release the locked resources, resulting in a deadlock.
2. Long-Running Transactions: Transactions that hold locks for extended periods increase the likelihood of deadlocks, as other transactions wait longer for resources to be released.
3. Exclusive Locks: Operations that require exclusive locks, such as updates or deletions, can block other transactions, leading to potential deadlocks, especially when combined with other locking behaviors.
4. Resource Contention: High competition for the same set of resources among multiple transactions can escalate the chances of deadlocks.

Preventive Measures:

1. Consistent Lock Ordering: Ensure that all transactions acquire locks in a predetermined, consistent order to prevent circular wait conditions.
2. Minimize Transaction Duration: Keep transactions short and efficient to reduce the time locks are held, thereby decreasing the window for deadlocks to occur.
3. Use Appropriate Isolation Levels: Choose transaction isolation levels that balance data integrity with concurrency needs. For instance, using Snapshot Isolation can reduce locking contention.
4. Implement Deadlock Detection and Resolution Mechanisms: Many database systems have built-in mechanisms to detect and resolve deadlocks by automatically choosing a transaction to roll back.
5. Index Optimization: Proper indexing can reduce the amount of data scanned during transactions, thereby decreasing lock contention and the potential for deadlocks.
6. Avoid Unnecessary Exclusive Locks: Where possible, use shared locks or implement row-versioning to minimize the use of exclusive locks that can lead to deadlocks.

By understanding the causes of deadlocks and implementing these preventive strategies, you can enhance the stability and performance of your database systems.

37. What is the difference between the RANK() and DENSE_RANK() functions in SQL?

Both RANK() and DENSE_RANK() are window functions used to assign ranks to rows within a partition of a result set, based on the order specified in the ORDER BY clause. They are commonly used for ranking results, such as identifying top performers or ordering items.

Differences:

• Handling of Ties:
  • RANK(): Assigns the same rank to tied rows but skips subsequent ranks. For example, if two rows are tied for rank 1, the next rank assigned will be 3.
  • DENSE_RANK(): Assigns the same rank to tied rows without skipping ranks. Following the same example, the next rank assigned would be 2.

Example:

SELECT
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

Output:

In this example, employees with the same salary receive the same rank. RANK() skips the next rank after a tie, while DENSE_RANK() does not.

Understanding these functions is crucial for scenarios requiring precise ranking, such as generating reports or implementing business logic based on rank.

38. How can you optimize a slow-running query in SQL?

Optimizing slow-running SQL queries is essential for maintaining efficient database performance. Here are several strategies to enhance query execution:

1. Analyze Execution Plans: Use the database’s execution plan feature to understand how queries are executed. This helps identify bottlenecks, such as full table scans or inefficient joins.
2. Index Appropriately: Create indexes on columns frequently used in WHERE clauses, join conditions, and sorting operations. Proper indexing can significantly reduce data retrieval time.
3. Avoid Using SELECT: Specify only the necessary columns in the SELECT statement to reduce the amount of data processed and transmitted.
4. Optimize Joins: Ensure that join operations are performed on indexed columns and consider the join order to minimize the dataset size at each stage.
5. Filter Early: Apply filters in the WHERE clause to limit the number of rows processed in subsequent operations. This reduces the workload on the database engine.
6. Use Appropriate Data Types: Ensure that columns are defined with the most efficient data types to save storage space and improve query performance.
7. Avoid Functions on Indexed Columns: Applying functions to indexed columns can prevent the use of indexes. Rewrite queries to avoid such functions when possible.
8. Limit Use of Wildcards: Avoid leading wildcards in LIKE patterns, as they can cause full table scans. Instead, use trailing wildcards or full-text search features.
9. Regularly Update Statistics: Keep database statistics up to date to help the query optimizer make informed decisions.
10. Partition Large Tables: Divide large tables into smaller, more manageable pieces to improve query performance and maintenance.

By implementing these strategies, you can effectively enhance the performance of slow-running SQL queries.

39. What is the difference between a primary key and a unique key?

Both primary keys and unique keys enforce uniqueness of the column(s) they are defined on, but they have distinct differences:

• Primary Key:
  • Uniquely identifies each record in a table.
  • Cannot contain NULL values.
  • Each table can have only one primary key.
  • Often used as a reference in foreign key relationships.
• Unique Key:
  • Ensures all values in the column(s) are unique.
  • Can contain a single NULL value (depending on the database system).
  • A table can have multiple unique keys.
  • Primarily used to enforce data integrity.

Understanding these differences is crucial for proper database design and integrity enforcement.

40. How do you implement transactions in SQL, and what are ACID properties?

Transactions in SQL are implemented to ensure a sequence of operations is executed as a single unit, maintaining data integrity. The ACID properties define the key characteristics of a reliable transaction:

• Atomicity: Ensures that all operations within a transaction are completed successfully; if any operation fails, the entire transaction is rolled back.
• Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining database invariants.
• Isolation: Ensures that concurrently executing transactions do not interfere with each other, maintaining data consistency.
• Durability: Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.

Implementing Transactions in SQL:

BEGIN TRANSACTION;

-- SQL operations

IF (/* check for errors */)
BEGIN
    COMMIT TRANSACTION;
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
END;

This structure ensures that the series of operations within the transaction are executed safely, adhering to the ACID properties.

Learn More: Carrer Guidance | Hiring Now!

Top 40 Advanced TOSCA Test Automation Engineer Interview Questions and Answers with 5+ Years of Experience

DSA Interview Questions and Answers

Angular Interview Questions and Answers for Developers with 5 Years of Experience

Android Interview Questions for Senior Developer with Detailed Answers

SQL Query Interview Questions for Freshers with Answers

GCP (Google Cloud Platform) Interview Questions and Answers

Selenium Coding Interview Questions and Answers