SQL Backend Developer Interview Questions

31. What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN?

LEFT JOIN with WHERE Clause:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
WHERE table2.column IS NULL;

Explanation:

This query performs a LEFT JOIN between table1 and table2 based on the specified column.
The WHERE clause filters the results to only include rows where there is no match in table2 (i.e., table2.column IS NULL).
It effectively retrieves records from table1 and the matching records from table2, where no match is found, the columns from table2 will be NUL.

Regular LEFT JOIN:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Explanation:

This query performs a standard LEFT JOIN between table1 and table2 based on the specified column.
It retrieves all records from table1, and for each record, it includes matching records from table2. If no match is found in table2, the columns from table2 will be NULL.

Summary:

The primary difference lies in the WHERE clause of the first query. The LEFT JOIN with a WHERE clause specifically filters the results to include only the rows where no match is found in the right table (table2). The regular LEFT JOIN, on the other hand, retrieves all records from the left table (table1) along with matching records from the right table (table2), including NULLs where no match is found.
In summary, the LEFT JOIN with a WHERE clause is often used when you want to filter out rows with matches in the right table, effectively isolating the unmatched rows from the left table.
 

32.How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution?

In SQL Server, informational messages, often called “info” or “print” messages, can be generated during and after the execution of a SQL statement. These messages might include details such as the number of rows affected or certain warnings. If you want to suppress these informational messages, you can use the SET command with the NOCOUNT option.

Here’s an example of how to use it:

SET NOCOUNT ON;
-- Your SQL statements go here
SET NOCOUNT OFF;

Explanation:

• SET NOCOUNT ON;: This setting turns off the “row affected” informational messages for subsequent statements in the same batch.
• SET NOCOUNT OFF;: This setting turns the informational messages back on.

By setting NOCOUNT ON, you prevent SQL Server from returning the “X rows affected” message for each statement in your batch. This can be useful in situations where you want to reduce the amount of information returned by the server, especially in scenarios where large volumes of data are processed.
Remember to turn NOCOUNT back to OFF if you want to receive informational messages for subsequent statements.

33. What is Cursor? How to use a Cursor?

Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML(Data Manipulation Language) operations on the Table by the User. Cursors are used to store Database Tables. 

There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained as following below.

1. Implicit Cursors: Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are allocated by SQL SERVER when the user performs DML operations.
2. Explicit Cursors: Explicit Cursors are Created by Users whenever the user requires them. Explicit Cursors are used for Fetching data from Table in Row-By-Row Manner.

34. How SQL Server executes a statement with nested subqueries?

When SQL Server processes a statement with nested subqueries, the execution process involves evaluating each subquery from the innermost to the outermost level. The general steps for executing a statement with nested subqueries are as follows:

• Parsing and Compilation:
  The SQL Server query processor parses the SQL statement and compiles it into an execution plan.
• Innermost Subquery Execution:
  SQL Server begins executing the innermost subquery first.
  The result of the innermost subquery is typically a set of values or a single value that will be used in the next level of the query.
• Intermediate Result Storage:
  If the result of the innermost subquery is a set of values, SQL Server may store these values in temporary structures or tables in memory or on disk.
• Propagation to the Next Level:
  The result or intermediate results obtained from the innermost subquery are then used in the next level of the query, which could be a higher-level subquery or the main outer query.
• Execution of Higher-Level Subqueries or Main Query:
  The process repeats for higher-level subqueries or the main outer query, using the intermediate results obtained from the lower levels.
• Combination of Results:
  As the execution progresses from the innermost to the outermost levels, the results from each level are combined to produce the final result set.
• Query Optimization:
  SQL Server’s query optimizer may perform various optimization techniques, such as reordering joins or selecting the most efficient indexes, to enhance the overall query performance.

It’s important to note that SQL Server’s query optimizer aims to find the most efficient execution plan for the entire query, taking into account the structure of the query, the available indexes, and the distribution of data in the tables involved. The goal is to minimize the time and resources required to execute the query.
Developers and database administrators can use tools like SQL Server Management Studio (SSMS) to analyze and optimize query execution plans, ensuring that nested subqueries are efficiently processed. Additionally, appropriate indexing and schema design can significantly impact the performance of queries with nested subqueries.

35. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

• Deadlock:A deadlock in database systems occurs when two or more transactions are blocked indefinitely, each waiting for the other to release a resource, resulting in a state where no progress can be made. It’s a situation where transactions are essentially stuck and cannot proceed.
• Live Lock: A live lock is a scenario where two or more transactions are actively trying to resolve a conflict, but their efforts prevent the conflict resolution from progressing. In other words, the transactions are not blocked, but they are caught in a loop of trying to resolve the situation without success.
• Deadlock Prevention: Design the system in a way that makes it impossible for a deadlock to occur. This involves careful resource allocation and transaction scheduling.
• Deadlock Detection and Resolution: Implement a deadlock detection mechanism that identifies when a deadlock has occurred.
  Use techniques like timeout mechanisms or periodic checks to detect deadlocks.
  Once a deadlock is detected, choose one of the transactions as a “victim” and roll back its changes, allowing the other transactions to proceed.
  • Transaction Timeout:
    Set a maximum time limit for transactions. If a transaction cannot complete within the specified time, it will be automatically rolled back, preventing indefinite blocking.
  • Resource Allocation Order:
    Define a standard order for acquiring resources, and ensure that transactions request resources in the same order. This reduces the chances of circular waits.
  • Lock Hierarchy:
    Establish a hierarchy for resource locking. A transaction can only request resources at a lower level in the hierarchy than the ones it currently holds. This helps prevent circular waits.
  • Avoidance Algorithms:
    Use resource allocation algorithms that predict whether a particular resource allocation will lead to a deadlock. These algorithms can help make decisions on resource requests based on the likelihood of deadlock.

Resolving Live Locks:

Identification:
Monitor the system for signs of live locks. This may involve analyzing system logs or using tools for performance monitoring.

1. Adjust Transaction Logic: Review the logic of transactions involved and make adjustments to prevent continuous conflicts. This might involve using retries with backoff strategies.
2. Use Randomized Delays: Introduce randomized delays in transactions to break the cycles that cause live locks.
3. Concurrency Control Mechanisms: Review and optimize the concurrency control mechanisms in use, such as locking and isolation levels, to minimize the chances of live locks.
4. Logging and Monitoring: Implement comprehensive logging and monitoring to capture information about live locks and facilitate analysis.
5. Correct Underlying Issues: Identify and address any underlying issues in the application or database schema that contribute to live locks.

It’s important to note that both deadlocks and live locks are complex issues, and their resolution often involves a combination of careful system design, configuration, and monitoring. The chosen approach may vary based on the specific characteristics of the application and the database system in use.

36. What are the different types of collation sensitivity? 

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties to data. A collation defines bit patterns that represent each character in metadata of database. SQL Server supports storing objects that have different collations in database. The options associated with collation are mentioned below :

• Case-sensitive (_CS)
• Accent-sensitive (_AS)
• Kana-sensitive (_KS)
• Width-sensitive (_WS)
• Variation-selector-sensitive (_VSS)a
• Binary (_BIN)
• Binary-code point (_BIN2)

37. Where is the MyISAM table stored?

In MySQL, the MyISAM storage engine stores tables in files on the file system. Each MyISAM table is represented by three files with the same base name but different extensions. These files are typically stored in the database directory associated with the MySQL server.

• .frm File: The .frm file is a binary file that stores the table definition, including the column names, types, and other metadata.
  It is located in the MySQL database directory and is essential for MySQL to understand the structure of the table.
• .MYD File (MyISAM Data): The .MYD file contains the actual data of the MyISAM table.
  Each MyISAM table has its corresponding .MYD file, which holds the rows of the table.
  The data is stored in a non-compressed format, and the file is organized as a heap structure.
• .MYI File (MyISAM Index): The .MYI file contains the indexes for the MyISAM table.
  Each index created on the table is stored in this file.
  The index file is a B-tree (balanced tree) structure that allows for efficient lookups based on indexed columns.

Location of MyISAM Table Files:

• The default location for storing MyISAM table files is in the MySQL data directory.
• The MySQL data directory is specified in the MySQL configuration file (my.cnf or my.ini).
• Common default locations include /var/lib/mysql/ on Unix/Linux systems and C:\ProgramData\MySQL\MySQL Server X.Y\data\ on Windows.

File-Level Locking:

• MyISAM uses table-level locking, meaning that when a write operation (like an UPDATE or DELETE) is performed, the entire table is locked.
• This can lead to contention in scenarios with concurrent write operations on the same table.

Maintenance and Optimization:

• MyISAM tables may require periodic maintenance and optimization to reclaim disk space and improve performance.
• The OPTIMIZE TABLE command can be used to defragment and optimize MyISAM tables.

Considerations:

While MyISAM has been widely used, especially in earlier versions of MySQL, it lacks some features offered by the InnoDB storage engine, such as support for transactions and foreign keys.
InnoDB is the default storage engine in recent MySQL versions, and it is often recommended for new projects due to its reliability and additional features.
It’s crucial to note that the choice of storage engine depends on the specific requirements of your application, and InnoDB is often preferred for its support of ACID transactions and better handling of concurrency. If you have a choice, consider evaluating whether InnoDB is a better fit for your use case.

Backend development involves working on the server side of web applications, where the logic, database interactions, and server management take place. It focuses on handling data, processing requests from clients, and generating appropriate responses.

In this Top Backend Development interview questions, We cover Interview questions from all important topics from basic to advanced such as JavaScript, Node.js, Express.js, SQL, MongoDB, Django, PHP, Java Spring, and API. No matter whether you are a fresher or an experienced professional we have got questions that will enhance your skills and help you shine in your backend development interview.