Tech Mahindra C# and SQL Interview Questions for 2023

n this article, we will cover a wide range of Tech Mahindra C# and SQL Interview Questions for 2023 and answers related to various programming concepts and SQL. Each question will be accompanied by a detailed explanation, relevant code examples, and query snippets. Let's dive in!

The Use of the 'using' Keyword

The 'using' keyword in C# is primarily used for automatic disposal of unmanaged resources. It ensures that the Dispose method of an object is called when it goes out of scope. Here's an example of its usage:

using (var connection = new SqlConnection(connectionString))

{

    // Code block where the connection is used

    // The connection will be automatically disposed at the end of the block

}

Types of Constructors

In C#, constructors are special methods used for initializing objects. There are three types of constructors:

• Default Constructor: It has no parameters and is automatically generated if no constructor is defined explicitly.
• Parameterized Constructor: It accepts parameters and initializes the object with provided values.
• Copy Constructor: It creates a new object by copying the values from an existing object.

Object with a Class with a Private Constructor

If a class has a private constructor, objects cannot be directly instantiated outside the class. However, the class itself can create instances using static methods or properties within its scope. Here's an example:

public class MyClass

{

    private MyClass()

    {

        // Private constructor

    }

    public static MyClass CreateInstance()

    {

        return new MyClass();

    }

}

// Creating an object using the static method

var myObject = MyClass.CreateInstance();

Difference between String and StringBuilder

In C#, 'String' is an immutable type, which means once created, it cannot be changed. 'StringBuilder', on the other hand, is a mutable type specifically designed for efficient string manipulation. 'StringBuilder' is preferred for concatenating multiple strings or when frequent modifications to a string are required.

Sealed Class

A sealed class in C# is a class that cannot be inherited. It is marked with the 'sealed' keyword to prevent other classes from deriving from it. Sealed classes are used when you want to restrict inheritance to maintain control over the behavior and implementation of a class.

Extension Method

An extension method in C# allows adding new methods to an existing type without modifying the original type. It is defined in a static class and must be a static method. Extension methods are called as if they were instance methods of the extended type. Here's an example:

public static class StringExtensions

{

    public static bool IsNullOrEmpty(this string value)

    {

        return string.IsNullOrEmpty(value);

    }

}

// Usage of the extension method

string myString = "Hello";

bool isEmpty = myString.IsNullOrEmpty();

Difference between Private Constructor and Static Constructor

A private constructor is used to restrict the creation of objects from outside the class, while a static constructor is used to initialize the class itself. A private constructor can be called within the class, whereas a static constructor is invoked automatically before any static members of the class are accessed.

Difference between 'ref' and 'out'

Both 'ref' and 'out' are used to pass arguments by reference in C#. However, there is a key difference:

• 'ref' requires the variable to be initialized before passing it to the method, whereas 'out' does not.
• In 'ref', the variable passed to the method must be initialized, but in 'out', it must be assigned a value within the method before returning.

Encapsulation

Encapsulation is an object-oriented programming concept that combines data and methods into a single unit called a class. It provides data abstraction, hiding the internal details of how data is stored or processed, and exposes only necessary information through public methods or properties.

Types of Access Modifiers

In C#, there are five access modifiers:

•     Public: Accessible from anywhere.
•     Private: Accessible only within the same class.
•     Protected: Accessible within the same class and derived classes.
•     Internal: Accessible within the same assembly.
•     Protected Internal: Accessible within the same assembly and derived classes.

How to access the protected modifier?

The protected modifier in object-oriented programming languages allows access to a member within the same class and its subclasses. To access a protected member in C#, you can create an instance of the subclass and access the protected member using the dot operator. Here's an example:

public class MyBaseClass

{

    protected int myProtectedField;

}

public class MySubClass : MyBaseClass

{

    public void AccessProtectedField()

    {

        myProtectedField = 10; // Accessing protected field

    }

}

What is garbage collection?

Garbage collection is an automatic memory management technique used in languages like C# to reclaim memory occupied by objects that are no longer in use. The garbage collector identifies and frees up memory that is no longer referenced by any active objects in the program, preventing memory leaks and reducing manual memory management overhead.

Difference between abstract class and interface?

In C#, an abstract class and an interface both provide a way to define contracts for derived classes, but they have some key differences. 

Abstract class: An abstract class can have both defined and undefined (abstract) members. It can provide partial implementation of methods and can have fields and constructors. It cannot be instantiated directly but serves as a base for derived classes to inherit from. A class can inherit from only one abstract class.

Interface: An interface is a contract that defines a set of methods and properties. It only contains method signatures, properties, events, and indexers. It cannot have fields or constructors. A class can implement multiple interfaces, enabling multiple inheritance of behavior.

How to implement multiple inheritance in C#?

C# does not support multiple inheritance of classes, but you can achieve a similar effect using interfaces. By implementing multiple interfaces, a class can inherit and define the behavior of each interface. Here's an example:

public interface IInterface1

{

    void Method1();

}

public interface IInterface2

{

    void Method2();

}

public class MyClass : IInterface1, IInterface2

{

    public void Method1()

    {

        // Implementation

    }

    public void Method2()

    {

        // Implementation

    }

}

What is an abstract class?

An abstract class is a class that cannot be instantiated directly and is intended to serve as a base for other classes to inherit from. It can contain abstract and non-abstract members. Abstract members do not have an implementation in the abstract class and must be implemented in derived classes. An abstract class is declared using the `abstract` keyword.

Can you create an object of an abstract class? Why?

No, you cannot create an object of an abstract class. Abstract classes are incomplete and contain one or more abstract members without implementation. They are designed to be inherited by derived classes, which provide implementations for abstract members. Attempting to instantiate an abstract class directly would result in a compilation error.

Difference between abstraction and abstract class?

Abstraction is a broader concept that refers to the process of hiding unnecessary details and exposing only essential features to the user. It allows users to work with high-level concepts without worrying about the underlying implementation.

An abstract class is a specific implementation in object-oriented programming that allows creating classes with both defined and undefined

What is polymorphism?

Polymorphism is a fundamental concept in object-oriented programming that allows objects of different types to be treated as objects of a common base type. It enables code to be written that can work with objects of multiple classes, providing flexibility and extensibility. Polymorphism can be achieved through method overriding and method overloading.

What is runtime polymorphism?

Runtime polymorphism, also known as dynamic polymorphism, occurs when the appropriate method implementation is determined at runtime based on the actual type of the object. It is achieved through method overriding, where a derived class provides its own implementation of a method defined in the base class. The decision of which implementation to execute is made dynamically during program execution.

What is method overriding?

Method overriding is a feature in object-oriented programming that allows a subclass to provide its own implementation of a method that is already defined in its superclass. The overridden method in the subclass must have the same name, return type, and parameter list as the method in the superclass. It allows for the specialization of behavior in derived classes.

Keywords in method overriding include:

• `override`: Used in the derived class to indicate that a method is intended to override a method in the base class.
• `base`: Used within the derived class to refer to the base class implementation of the overridden method.

Method overriding is applicable to classes that have an inheritance relationship, where the derived class extends or inherits from the base class.

Main objects in ADO.NET?

In ADO.NET (ActiveX Data Objects for .NET), the main objects include:

• Connection: Represents a connection to a data source.
• Command: Represents an SQL statement or a stored procedure to execute against a data source.
• DataReader: Provides a fast, forward-only, read-only stream of data from a data source.
• DataSet: Represents an in-memory cache of data, which can contain multiple DataTable objects.
• DataTable: Represents a table of data in memory, consisting of rows and columns.
• DataAdapter: Serves as a bridge between a DataSet and a data source, enabling data retrieval and update operations.

Difference between DataSet and DataTable?

DataSet: A DataSet is an in-memory cache of data that can hold multiple DataTable objects along with their relationships. It represents a disconnected set of data and can persist its contents in XML format. It can hold data from multiple tables and can be used for offline data manipulation and synchronization.

DataTable: A DataTable represents a single table of data within a DataSet. It consists of rows and columns and is similar to a table in a relational database. It stores data in a tabular form and provides methods and properties for data manipulation and querying.

What are all the different types of execute methods in ADO.NET?

In ADO.NET, the different types of execute methods commonly used are:

• `ExecuteNonQuery()`: Executes a command that does not return any result set, such as an INSERT, UPDATE, DELETE, or DDL statement.
• `ExecuteScalar()`: Executes a command and returns the value of the first column of the first row in the result set. Useful when a single value is expected as the result.
• `ExecuteReader()`: Executes a command and returns a DataReader object for retrieving a forward-only, read-only stream of data.
• `ExecuteXmlReader()`: Executes a command and returns an XMLReader object for reading XML data from the result set.

Difference between ExecuteScalar and ExecuteNonQuery?

`ExecuteScalar()`: It is used to execute a command and return the value of the first column of the first row in the result set. It is typically used when a single value is expected as the result, such as retrieving a count or an aggregated value. It returns an object that needs to be cast to the appropriate type.

`ExecuteNonQuery()`: It is used to execute a command that does not return any result set, such as INSERT, UPDATE, DELETE, or DDL statements. It returns the number of rows affected by the command.

What is the usage of DataView in C#?

A DataView in C# is a customized view of a DataTable that allows sorting, filtering, and searching the data in various ways. It provides a dynamic and flexible way to present and manipulate data from a DataTable. DataView can be used to apply sorting and filtering conditions to a DataTable, and it also supports data binding with UI controls for efficient data presentation and manipulation.

Types of authentication in SQL?

In SQL, the common types of authentication include:

Windows Authentication: Uses the credentials of the currently logged-in Windows user to authenticate and authorize access to the SQL Server. It relies on Windows security and Active Directory.

SQL Server Authentication: Requires a username and password specific to SQL Server. It does not rely on Windows security but instead maintains its own set of user accounts and passwords.

Write a connection string

The connection string is used to establish a connection to a data source. Here's an example of a connection string for SQL Server using Windows Authentication:

string connectionString = "Data Source=myServerAddress;Initial  Catalog=myDatabase;Integrated Security=True;";

Replace `myServerAddress` with the address of the SQL Server and `myDatabase` with the name of the database you want to connect to.

Types of constraints in SQL?

In SQL, the common types of constraints are:

• Primary Key: A primary key constraint ensures that a column or a combination of columns uniquely identifies each row in a table.
• Unique: A unique constraint ensures that the values in a column or a combination of columns are unique across the table.
• Foreign Key: A foreign key constraint establishes a relationship between two tables by enforcing referential integrity. It ensures that values in a column match values in another table's primary key.
• Check: A check constraint validates the values in a column to meet a specific condition or range of values.
• Not Null: A not null constraint ensures that a column does not contain any null values.
• Default: A default constraint provides a default value for a column if no value is specified during an insert operation.

Queries under DML?

DML (Data Manipulation Language) in SQL is used to modify and retrieve data from tables. Common DML queries include:

• SELECT: Retrieves data from one or more tables based on specified conditions.
• INSERT: Inserts new rows of data into a table.
• UPDATE: Modifies existing data in one or more rows of a table.
• DELETE: Removes one or more rows of data from a table.

Difference between primary key and unique key?

The primary key and unique key are both used to enforce uniqueness in SQL, but they have some differences:

Primary Key: It uniquely identifies each row in a table and ensures that the identified column(s) have unique values. It is a combination of the unique and not null constraints. Only one primary key can be defined per table.

Unique Key: It ensures that the identified column(s) have unique values. Unlike a primary key, a table can have multiple unique keys. Unique keys can allow null values, except for the columns defined as the primary key.

Can we use multiple primary keys?

No, a table can have only one primary key. The primary key uniquely identifies each row in a table. However, you can use composite primary keys by combining two or more columns to create a unique identifier for a row.

What constraint do you use to check some condition?

To check a condition, you can use the CHECK constraint in SQL. The CHECK constraint allows you to specify a condition that must be satisfied for each row in a table. If the condition evaluates to false, the constraint prevents the insertion or modification of the row.

Difference between DELETE and TRUNCATE?

The DELETE and TRUNCATE statements are used to remove data from tables in SQL, but they differ in their behavior:

DELETE: The DELETE statement is a DML statement that removes specific rows from a table based on specified conditions. It provides more flexibility by allowing you to specify complex filtering criteria. DELETE operation can be rolled back using a transaction.

TRUNCATE: The TRUNCATE statement is a DDL statement that removes all rows from a table. It is faster than DELETE because it does not generate individual undo logs for each deleted row. TRUNCATE operation cannot be rolled back as it is considered a non-logged operation.

Tell me a TRUNCATE query.

The TRUNCATE query is used to remove all rows from a table. The syntax for TRUNCATE is as follows:

TRUNCATE TABLE table_name;

Replace `table_name` with the name of the table you want to truncate. Be cautious when using TRUNCATE as it permanently deletes all data from the table.

How to filter particular department data from a textfile table?

To filter particular department data from a textfile table, you can use the SQL SELECT statement with a

 WHERE clause. Assuming you have a column named 'department' in your textfile table, the query would be:

SELECT * FROM textfile_table WHERE department = 'desired_department';

Replace `textfile_table` with the name of your table and `'desired_department'` with the specific department you want to filter.

How to find duplicate data from a table?

To find duplicate data in a table, you can use the SQL SELECT statement with the GROUP BY and HAVING clauses. Here's an example:

SELECT column1, column2, COUNT(*) as count

FROM your_table

GROUP BY column1, column2

HAVING COUNT(*) > 1;

Replace `your_table` with the name of your table and specify the column(s) you want to check for duplicates in the SELECT, GROUP BY, and HAVING clauses.

Usage of knowledge?

The term "knowledge" is quite broad, but in the context of software development and programming, knowledge refers to the understanding and expertise in various programming languages, frameworks, algorithms, design patterns, and best practices. Having knowledge in these areas allows developers to effectively analyze problems, design efficient solutions, and write high-quality code. Knowledge is acquired through learning, experience, and continuous self-improvement, and it is essential for building robust and scalable software systems.

Write a query to rename a column in a table?

To rename a column in a table, you can use the ALTER TABLE statement with the RENAME COLUMN clause. Here's an example:

ALTER TABLE your_table

RENAME COLUMN old_column_name TO new_column_name;

Replace `your_table` with the name of your table, `old_column_name` with the current name of the column you want to rename, and `new_column_name` with the desired new name for the column.

How to change the data type of a particular column in a table?

To change the data type of a column in a table, you can use the ALTER TABLE statement with the ALTER COLUMN clause. Here's an example:

ALTER TABLE your_table

ALTER COLUMN your_column_name NEW_DATA_TYPE;

Replace `your_table` with the name of your table, `your_column_name` with the name of the column you want to change the data type of, and `NEW_DATA_TYPE` with the desired new data type for the column.

How to give auto-generated fields?

To give auto-generated fields, you can use identity columns or sequences in SQL. Identity columns automatically generate incrementing numeric values for each new row inserted into a table. Sequences generate a sequence of numeric values. The specific syntax for implementing auto-generated fields may vary depending on the database system you are using.

Difference between stored procedure and function?

The main differences between stored procedures and functions are:

• Purpose: A stored procedure is primarily used to perform an action or a series of actions, such as modifying data, executing complex logic, or generating reports. A function is designed to return a single value or a table of values.
• Return Type: A stored procedure does not have a mandatory return type. It can return zero or more result sets or output parameters. A function has a defined return type and must return a value or a table of values.
• Usage in Queries: A stored procedure can be invoked from within a query or used as a standalone statement. A function is typically used within a query as part of an expression or a select statement.
• Transaction Control: A stored procedure can initiate and control transactions. It can include transaction management statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK. A function cannot initiate or control transactions.

How to use exception handling in functions?

In SQL, exception handling in functions is limited compared to stored procedures. Functions can only handle exceptions related to user-defined errors using the `TRY...CATCH` construct. Here's an example:

CREATE FUNCTION your_function

(

    -- Function parameters

)

RETURNS data_type

AS

BEGIN

    BEGIN TRY

        -- Function logic

    END TRY

    BEGIN CATCH

        -- Error handling

    END CATCH

    RETURN -- Return statement

END;

Within the `BEGIN TRY` block, you can write your function's logic. If an error occurs, the `BEGIN CATCH` block is executed, allowing you to handle the exception appropriately.

How to handle exceptions in stored procedures?

In SQL, stored procedures provide robust exception handling capabilities using the `TRY...CATCH` construct. Here's an example:

CREATE PROCEDURE your_procedure

(

    -- Procedure parameters

)

AS

BEGIN

    BEGIN TRY

        -- Procedure logic

    END TRY

    BEGIN CATCH

        -- Error handling

    END CATCH

END;

Within the `BEGIN TRY` block, you can write your procedure's logic. If an error occurs, the `BEGIN CATCH` block is executed, allowing you to handle the exception appropriately.

What is a transaction in a stored procedure?

A transaction in a stored procedure is a logical unit of work that consists of one or more database operations. Transactions ensure that all operations within the unit are treated as a single, indivisible entity. They provide the ACID (Atomicity, Consistency, Isolation, Durability) properties to maintain data integrity and consistency. Transactions can be initiated using the `BEGIN TRANSACTION` statement, and changes made within the transaction can be either committed (`COMMIT`) or rolled back (`ROLLBACK`) based on the desired outcome.

Types of functions in SQL?

In SQL, there are several types of functions, including:

• Scalar Functions: Return a single value based on input parameters.
• Table-Valued Functions: Return a table as a result, allowing multiple rows and columns to be returned.
• Aggregate Functions: Perform calculations on a set of values and return a single value, such as SUM, AVG, COUNT, MIN, MAX, etc.
• String Functions: Manipulate and operate on string values, such as CONCAT, SUBSTRING, LEN, etc.
• Date and Time Functions: Perform operations on date and time values, such as GETDATE, DATEPART, DATEADD, etc.

What are the built-in aggregate functions in SQL Server?

SQL Server provides several built-in aggregate functions, including:

• SUM: Calculates the sum of a set of values.
• AVG: Calculates the average of a set of values.
• COUNT: Counts the number of rows or non-null values in a set.
• MIN: Retrieves the minimum value from a set.
• MAX: Retrieves the maximum value from a set.

Built-in string functions in SQL Server?

SQL Server provides various built-in string functions, including:

• CONCAT: Concatenates two or more strings together.
• SUBSTRING: Extracts a portion of a string.
• LEN: Returns the length of a string.
• UPPER: Converts a string to uppercase.
• LOWER: Converts a string to lowercase.
• REPLACE: Replaces occurrences of a specified string with another string.

What is SQL Profiler?

SQL Profiler is a tool provided by Microsoft SQL Server that allows you to monitor and capture events occurring in a SQL Server database. It provides a graphical interface to trace and analyze database activities, including queries, stored procedure executions, errors, and performance-related information. SQL Profiler is useful for debugging, optimizing queries, troubleshooting, and auditing database activities.

How to create an index in SQL Server?

To create an index in SQL Server, you can use the CREATE INDEX statement. Here's an example:

CREATE INDEX index_name

ON your_table (column1, column2, ...);

Replace `index_name` with the desired name for the index, `your_table` with the name of your table, and `column1`, `column2`, etc., with the columns you want to include in the index. Indexes improve query performance by allowing faster data retrieval based on the indexed columns.

Types of joins in SQL Server?

In SQL Server, the common types of joins are:

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables, with NULL values for non-matching rows.
• CROSS JOIN: Returns the Cartesian product of both tables (all possible combinations of rows).

Difference between left outer join and right outer join?

The main difference between a left outer join and a right outer join is the tables from which the non-matching rows are retrieved:

• Left Outer Join: Retrieves all rows from the left (or first) table and the matching rows from the right (or second) table. Non-matching rows from the right table will have NULL values in the result set.
• Right Outer Join: Retrieves all rows from the right (or second) table and the matching rows from the left (or first) table. Non-matching rows from the left table will have NULL values in the result set.

The choice between left and right outer join depends on the desired output and the relationship between the tables.

In what situation do you use a self-join?

A self-join is used when a table needs to be joined with itself based on a relationship between two columns within the same table. It is commonly used when working with hierarchical data or when you need to compare rows within the same table.

For example, consider a table that stores employee information, where each row contains an employee ID and a manager ID that references another employee in the same table. By performing a self-join on the employee ID and manager ID columns, you can retrieve information about employees and their respective managers.

How to improve the performance of an existing stored procedure?

To improve the performance of an existing stored procedure, you can consider the following approaches:

• Optimize Query Logic: Review the SQL statements within the stored procedure and ensure they are efficient. Use appropriate indexes, avoid unnecessary joins or subqueries, and optimize WHERE clauses.
• Use Proper Indexing: Analyze the execution plan of the stored procedure and identify missing or inefficient indexes. Create indexes on columns used in join conditions, WHERE clauses, or ORDER BY clauses.
• Minimize Data Retrieval: Retrieve only the necessary columns and rows instead of fetching all data. Use appropriate filtering conditions and limit the result set size.
• Re-evaluate Cursors: If your stored procedure uses cursors, consider alternative approaches like set-based operations to improve performance.
• Regularly Update Statistics: Keep the statistics of the database up to date to ensure the query optimizer has accurate information for generating efficient execution plans.
• Consider Stored Procedure Recompilation: In some cases, forcing a stored procedure to recompile can help improve performance. This can be done using the `WITH RECOMPILE` option.

Types of triggers in SQL?

In SQL, there are two types of triggers:

• DML Triggers: These triggers are fired in response to data manipulation language (DML) events, such as INSERT, UPDATE, and DELETE statements on a table.
• DDL Triggers: These triggers are fired in response to data definition language (DDL) events, such as CREATE, ALTER, and DROP statements on a database or table.

Triggers allow you to define custom actions or validations that are automatically executed when specific events occur.

Which one is faster between stored procedures and functions? How?

Stored procedures are generally faster than functions because they are precompiled and cached by the database server. When a stored procedure is executed, the execution plan is already compiled, resulting in faster execution.

Functions, on the other hand, need to be evaluated for each row or record being processed. This can lead to additional overhead and slower performance, especially when functions are used in queries that involve large datasets.

However, it's important to note that the actual performance can vary depending on the specific scenario, the complexity of the logic, the amount of data being processed, and other factors. It's recommended to benchmark and analyze the performance of both stored procedures and functions in your specific environment to determine the optimal choice.

How to create a view?

To create a view in SQL, you can use the CREATE VIEW statement. Here's an example:

CREATE VIEW your_view_name AS

SELECT column1, column2, ...

FROM your_table

WHERE condition;

Replace `your_view_name` with the desired name for the view, `column1`, `column2`, etc., with the columns you want to include in the view, `your_table` with the name of the table you want to create the view from, and `condition` with any desired filtering condition.

A view is a virtual table that is based on the result of a SELECT statement. It allows you to simplify complex queries, provide a layer of abstraction, and present a subset of data to users or applications.