Q:-

--- C# ----
What is call by value ,call by ref?

Call By Value:- In C#, value-type parameters are that pass a copy of original value to the function
rather than reference.
It does not modify the original value.
A change made in passed value does not alter the actual value.
In the following example, we have pass value during function call.

Call By Reference:- C# provides a ref keyword to pass argument as reference-type.

It passes reference of arguments to the function rather than copy of original value.
The changes in passed values are permanent and modify the original variable value.

---------------------------------------------------------------------------------------------------------------------------------------
-

Difference between constant and read-only variable?

What is Constant:-
Const is a compile-time constant, meaning its value is determined at compile-time and cannot be
changed at runtime.
It is usually used to declare global constants, such as mathematical constants or other values that are
not expected
to change during the execution of the program. Const values are always static and implicitly static,
meaning they belong to the class and not to an instance of the class.
They are also implicitly public, meaning they can be accessed anywhere in the code.

What is readonly:-
On the other hand, readonly fields are runtime constants, meaning their value is determined at
runtime and can be changed only in
the class's constructor. The readonly fields are instance-level variables, meaning each class instance
has its copy of the field.
They can also be designated as static, which denotes that they belong to the class and not a particular
class instance.
The readonly fields are not implicitly public, so you must specify the access level explicitly.

---------------------------------------------------------------------------------------------------------------------------------------
-
SOLID principal in .NET?

The SOLID Design Principles are used to manage the majority of software design problems that
developers face daily.
These design concepts are tried and true processes that make software designs more clear, flexible,
and maintainable.
As a result, if we follow these rules when creating our application, we can create superior applications.
---------------------------------------------------------------------------------------------------------------------------------------
-
OOPS concepts?

1. Encapsulation
Encapsulation is the technique of bundling the data (fields) and the methods that operate on the data
into a single unit, known as a class.
It restricts direct access to some of an object's components, which is a means of preventing accidental
interference and misuse of the data.
Access to the data is provided through public methods (getters and setters), maintaining control over
how the data is modified or accessed.

2. Inheritance
Inheritance is a mechanism wherein a new class inherits properties and behaviors (fields and methods)
from an existing class.
This allows for code reuse and the creation of a hierarchical relationship between classes. The class
that inherits is called the child or
derived class, and the class being inherited from is called the parent or base class. Inheritance
supports the concept of "is-a" relationships.

3. Polymorphism
Polymorphism allows objects to be treated as instances of their parent class rather than their actual
class. The most common use of polymorphism
is when a parent class reference is used to refer to a child class object. It can be achieved through
method overriding
(a child class providing a specific implementation of a method that is already defined in its parent
class) and method overloading
(multiple methods having the same name but different parameters).

4. Abstraction
Abstraction is the concept of hiding the complex implementation details and showing only the
necessary features of an object.
It allows focusing on what an object does instead of how it does it. In C#, abstraction is achieved
through abstract classes and interfaces,
which can define methods without implementing them, leaving the implementation to the derived
classes.

---------------------------------------------------------------------------------------------------------------------------------------
-
What is CTS,CLR?

CTS (Common Type System)

The Common Type System (CTS) is a standard that defines how types are declared, used, and
managed in the .NET framework.
The CTS ensures that objects written in different .NET languages (such as C#, VB.NET, and F#) can
interact with each other.
It provides a set of data types and rules that all .NET languages must adhere to, ensuring type safety
and consistency across languages.

Key Features of CTS:

Type Compatibility: Ensures that types declared in one language can be used in another, enabling
cross-language integration.
Type Safety: Provides mechanisms to enforce type safety, preventing type errors that can lead to
runtime exceptions.
Unified Type System: Defines a single set of base types, such as System.Int32, System.String, and
System.Object, which all .NET languages must use.
CLR (Common Language Runtime)
The Common Language Runtime (CLR) is the execution engine of the .NET framework. It manages the
execution of .NET programs,
providing services such as memory management, security, exception handling, and garbage collection.
The CLR is responsible for converting the
intermediate language (IL) code, which is generated by the .NET compilers, into native machine code
that can be executed by the underlying hardware.
Key Features of CLR:

Memory Management: Automatically handles the allocation and release of memory for .NET objects,
reducing memory leaks and fragmentation.
Garbage Collection: Periodically frees up memory by removing objects that are no longer in use,
ensuring efficient memory utilization.
Exception Handling: Provides a structured way to handle runtime errors, allowing applications to
recover gracefully from unexpected conditions.
Security: Enforces security policies, such as code access security (CAS), to protect resources and
ensure that only authorized code can execute.
Just-In-Time Compilation (JIT): Converts IL code to native machine code just before execution,
optimizing the performance of .NET applications.
Summary
CTS (Common Type System): Ensures consistent data types across different .NET languages, allowing
for seamless interoperability.
CLR (Common Language Runtime): The runtime environment that executes .NET applications,
providing essential services such as memory management,
garbage collection, and security.

---------------------------------------------------------------------------------------------------------------------------------------
-
Garbage collection?

Garbage collection in the context of .NET and C# refers to the automatic management of memory.
The .NET Framework's Common Language Runtime (CLR)
includes a garbage collector (GC) that manages the allocation and release of memory for applications,
which helps to prevent memory leaks and
other memory-related issues. Here's an overview of how garbage collection works and its key
features:

Key Concepts of Garbage Collection

Automatic Memory Management:

The garbage collector automatically handles the allocation and deallocation of memory for objects
created in .NET applications. Developers
do not need to manually free memory, reducing the risk of memory leaks and pointer errors.
Generations:

The garbage collector in .NET divides objects into three generations to optimize performance:
Generation 0: Short-lived objects. Most objects are expected to be short-lived and are collected
frequently.
Generation 1: Medium-lived objects. These objects have survived one garbage collection.
Generation 2: Long-lived objects. These objects have survived multiple garbage collections.
This generational approach improves efficiency by focusing on collecting short-lived objects more
frequently.
Heap Segments:

The heap is divided into segments:

Small Object Heap (SOH): For small objects.
Large Object Heap (LOH): For large objects (typically objects larger than 85,000 bytes).
Mark and Sweep:

The garbage collector uses a mark-and-sweep algorithm:

Mark: Identifies which objects are still in use (reachable).
Sweep: Reclaims memory occupied by objects that are no longer in use (unreachable).
Compacting:
After reclaiming memory, the garbage collector compacts the remaining objects to reduce
fragmentation. This involves moving objects in memory,
which updates references to these objects.
Finalization:

Objects that require cleanup before being reclaimed (e.g., closing file handles, releasing unmanaged
resources) can implement a finalizer by
defining a ~ClassName method. The garbage collector calls this finalizer before reclaiming the object.
---------------------------------------------------------------------------------------------------------------------------------------
-

Constructor and types of constructor with declaration?

1. Default Constructor
A default constructor is a constructor that takes no parameters. If no constructor is defined in a class,
the C# compiler automatically provides
a default constructor.

Declaration:

csharp

class MyClass
{
public MyClass()
{
// Initialization code
}
}
2. Parameterized Constructor
A parameterized constructor allows you to pass arguments to initialize an object with specific values
at the time of creation.

Declaration:

csharp

class MyClass
{
public int X { get; set; }
public int Y { get; set; }

public MyClass(int x, int y)

{
X = x;
Y = y;
}
}
3. Copy Constructor
A copy constructor creates a new object as a copy of an existing object. This constructor takes an
object of the same class as a parameter.

Declaration:

csharp

class MyClass
{
public int X { get; set; }
public int Y { get; set; }

public MyClass(int x, int y)

{
X = x;
Y = y;
}

// Copy constructor
public MyClass(MyClass other)
{
X = other.X;
Y = other.Y;
}
}
4. Static Constructor
A static constructor is used to initialize static members of a class. It is called automatically before any
static members are accessed or any instance of the class is created.

Declaration:

csharp

class MyClass
{
public static int StaticValue { get; set; }

// Static constructor
static MyClass()
{
StaticValue = 100;
}
}
5. Private Constructor
A private constructor is used to prevent the creation of instances of a class from outside the class.
This is often used in classes that
only static members or in singleton design patterns.

Declaration:

csharp

class MyClass
{
public static int StaticValue { get; set; }

// Private constructor
private MyClass()
{
StaticValue = 100;
}
}
Summary of Constructors
Default Constructor: Initializes an object with default values. It has no parameters.
Parameterized Constructor: Initializes an object with specific values provided through parameters.
Copy Constructor: Initializes an object by copying values from another object of the same class.
Static Constructor: Initializes static members of the class. It has no parameters and cannot be called
directly.
Private Constructor: Restricts the creation of instances from outside the class, often used in singleton
patterns.

---------------------------------------------------------------------------------------------------------------------------------------
-
what is Method override? and how it is implemented in code?

Method Override
Method overriding in C# is a feature that allows a derived class to provide a specific implementation
of a method that is already defined in
its base class. This enables a derived class to extend or modify the behavior of the base class method.

Key Points:
Inheritance: Method overriding requires inheritance. The method to be overridden must be defined in
a base class, and the derived
class must inherit from this base class.
Virtual Keyword: The method in the base class that you want to allow overriding must be marked with
the virtual keyword.
Override Keyword: The method in the derived class that overrides the base class method must be
marked with the override keyword.
Same Signature: The method in the derived class must have the same signature as the method in the
base class.
Example Implementation
Let's go through an example step-by-step:

Base Class with a Virtual Method

csharp

using System;

class Animal
{
// Virtual method in the base class
public virtual void MakeSound()
{
Console.WriteLine("The animal makes a sound.");
}
}
Derived Class Overriding the Method
csharp

class Dog : Animal

{
// Override method in the derived class
public override void MakeSound()
{
Console.WriteLine("The dog barks.");
}
}

class Cat : Animal

{
// Override method in the derived class
public override void MakeSound()
 {
Console.WriteLine("The cat meows.");
}
}

Summary
Method overriding allows derived classes to provide specific implementations for methods defined in
their base classes.
This is achieved using the virtual keyword in the base class and the override keyword in the derived
class. This mechanism enables polymorphism,
where a base class reference can invoke the overridden method of a derived class, allowing for
dynamic method resolution at runtime.

---------------------------------------------------------------------------------------------------------------------------------------
-
Difference Abstract factory and factory design pattern ?

The Factory and Abstract Factory design patterns are both creational design patterns used to create
objects, but they differ in
their purposes and complexities. Here's a detailed comparison to understand their differences:

Factory Method Pattern

Purpose
The Factory Method pattern defines an interface for creating an object, but lets subclasses alter the
type of objects that will be created.
It is used to delegate the instantiation of a class to subclasses.
Structure
Creator (Abstract Class or Interface): Declares the factory method, which returns an object of type
Product. The Creator
may also define a default implementation of the factory method that returns a default Product object.
Concrete Creator (Concrete Class): Overrides the factory method to return an instance of a
ConcreteProduct.
Product (Interface): Declares the interface for objects the factory method creates.
Concrete Product (Concrete Class): Implements the Product interface.

---------------------------------------------------------------------------------------------------------------------------------------
-
How to return multiple values from functions?

In C#, you can return multiple values from a function using various methods such as:

Using Tuple:
csharp

public (int, string, bool) ReturnValues()

{
int value1 = 10;
string value2 = "hello";
bool value3 = true;
return (value1, value2, value3);
}

// Usage
var result = ReturnValues();
Console.WriteLine($"{result.Item1}, {result.Item2}, {result.Item3}");
Using out Parameters:
csharp

public void ReturnValues(out int value1, out string value2, out bool value3)
{
value1 = 10;
value2 = "hello";
value3 = true;
}

// Usage
int intValue;
string stringValue;
bool boolValue;
ReturnValues(out intValue, out stringValue, out boolValue);
Console.WriteLine($"{intValue}, {stringValue}, {boolValue}");
Using a Custom Class or Struct:
csharp

public class ReturnValue

{
public int Value1 { get; set; }
public string Value2 { get; set; }
public bool Value3 { get; set; }
}

public ReturnValue ReturnValues()

{
int value1 = 10;
string value2 = "hello";
bool value3 = true;
return new ReturnValue { Value1 = value1, Value2 = value2, Value3 = value3 };
}

// Usage
var result = ReturnValues();
Console.WriteLine($"{result.Value1}, {result.Value2}, {result.Value3}");
These are some common methods for returning multiple values from a function in C#. Choose the one
that best suits your requirements and
coding practices.

---------------------------------------------------------------------------------------------------------------------------------------
-
Static constructor declaration?

In C#, a static constructor is a special type of constructor that is called automatically before the first
instance of a class is created or
before any static members of the class are accessed. It's primarily used to initialize static fields or
perform any other one-time initialization
tasks that are necessary for the class.

Here's the syntax for declaring a static constructor in C#:

public class MyClass

{
// Static constructor
static MyClass()
{
 // Initialization code goes here
}
}
---------------------------------------------------------------------------------------------------------------------------------------
-
What is generic classes?

In programming, a generic class is a class that can work with any data type. It allows you to define
classes with placeholders for the data types of fields, properties, methods, parameters, and return
types. This enables you to create reusable and type-safe code components.

In C#, for example, you can create a generic class using angle brackets (<>) followed by a placeholder
type parameter. Here's a basic example:

csharp

public class GenericClass<T>

{
private T data;

public GenericClass(T data)

{
this.data = data;
}

public T GetData()
{
return data;
}
}
In this example, T is a placeholder for the data type. You can use this class with any type when you
create an instance of it.

---------------------------------------------------------------------------------------------------------------------------------------
-

what is Singleton design pattern with code implementation?

The Singleton design pattern is a creational pattern that ensures a class has only one instance and
provides a global point of access to that instance.
It's useful when exactly one object is needed to coordinate actions across the system.

---------------------------------------------------------------------------------------------------------------------------------------
-

Difference between DBContext vs DBSet?

n C#, Entity Framework Core is a popular object-relational mapping (ORM) framework used for
building data-driven applications.
Let's clarify the differences between DbContext and DbSet specifically in the context of Entity
Framework Core.

DbContext:

DbContext is a class provided by Entity Framework Core that represents a session with the database.
It is responsible for interacting with the database, managing database connections, transactions, and
executing database operations such as
querying, saving, updating, and deleting data.
Typically, you define a subclass of DbContext in your application to represent your database context.
This subclass contains
DbSet properties that represent collections of entities in your database.
DbSet:

DbSet is a generic class provided by Entity Framework Core that represents a collection of entities of a
specific type.
It represents a table in the database and provides methods for querying, inserting, updating, and
deleting entities of that type.
Each DbSet property defined in a DbContext subclass corresponds to a table in the database, and the
type parameter specifies the type of
entity stored in that table.
Here's an example illustrating the use of DbContext and DbSet in Entity Framework Core:

csharp

using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext

{
public DbSet<User> Users { get; set; } // Represents the Users table

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

{
optionsBuilder.UseSqlServer("connection_string_here");
}
}

public class User

{
public int Id { get; set; }
public string Name { get; set; }
}

class Program
{
static void Main(string[] args)
{
using (var dbContext = new MyDbContext())
{
// Querying data using DbSet
var users = dbContext.Users.ToList();

// Adding new entity using DbSet

dbContext.Users.Add(new User { Name = "John" });

// Saving changes to the database using DbContext

dbContext.SaveChanges();
}
}
}
---------------------------------------------------------------------------------------------------------------------------------------
-

Difference between View and PartialView?

In the context of ASP.NET MVC or ASP.NET Core MVC with C#, the concepts of View and PartialView
remain the same as described earlier. Let's delve into the differences in C# within this framework:

View:

In ASP.NET MVC or ASP.NET Core MVC, a View is typically a Razor view file (.cshtml) that represents a
complete HTML page or a significant portion of a page.
It combines HTML markup with embedded server-side code using Razor syntax, which allows you to
dynamically generate content based on the model data passed from the controller.
Views are associated with controller actions and are used to render the final HTML output that is sent
to the client's browser.
Views often contain layout markup, structural elements, and content specific to the page being
rendered.

PartialView:

Similarly, a PartialView in ASP.NET MVC or ASP.NET Core MVC is also a Razor view file (.cshtml), but it
represents a reusable portion of HTML
markup that can be embedded within other views.
Unlike a regular view, a PartialView does not render a complete HTML page. Instead, it focuses on
rendering a specific component or section of a
page.
PartialView files are commonly used for rendering components that are shared across multiple views,
such as headers, footers, sidebars, navigation
menus, or any other component that needs to be reused.
They are particularly useful for breaking down complex views into smaller, more manageable
components, promoting code reusability and maintainability.
Here's a basic comparison:

View: Represents a complete or significant portion of an HTML page, typically associated with
controller actions.
PartialView: Represents a reusable component or smaller portion of an HTML page, embedded within
other views for reuse and improved code
organization.
In summary, whether you're working with ASP.NET MVC or ASP.NET Core MVC in C#, the concepts of
View and PartialView remain fundamental for
generating HTML markup and structuring your web application's user interface.
---------------------------------------------------------------------------------------------------------------------------------------
-
Difference between View and Session?

ViewState: It is maintained at only one level that is page-level. Changes made on a single page is not
visible on other pages.
Information that is gathered in view state is stored for the clients only and cannot be transferred to
any other place.
View state is synonymous with serializable data only.

ViewState has a tendency for the persistence of page-instance-specific data. When view state is used,
the values posted of a particular page
persist in the browse area that the client is using and post back only when the entire operation is
done. The data of the previous page is no
longer available when another page is loaded. Also, Data is not secure in this case because it is
exposed to clients. Encryption can be used for
data security.

SessionState: It is maintained at session-level and data can be accessed across all pages in the web
application. The information is stored
within the server and can be accessed by any person that has access to the server where the
information is stored.

---------------------------------------------------------------------------------------------------------------------------------------
-
What is the use of ISPOSTBACK?

postback : request is sent to the server for a page and it will go for a full trip to the server and get all
the respective data.

Ispostback : this property will check if the page is called for the first time or not, suppose you open a
page for the first time it will
get postback and next time you click on a button on the same page to submit some data and now if
you check the property ispostback is false
cause the page is not called for the first time so it will only send / receive data which are required by
that event.

autopostback : autopostback means when you want to send some request to the server on some
control's event you can use this property.
for example on text change event of the textbox you want to check whether username is unique is
not then you create ontextchange event for
that textbox and you also set the autopostback to true. so when the event occur it will send request
to server with the username to check
that it is unique or not. if autopostback is false your event will not be called.

---------------------------------------------------------------------------------------------------------------------------------------
-

What is collection and its types?

C# collections are made to more effectively organize, store, and modify comparable data. Adding,
deleting, discovering, and inserting data into the collection are all examples of data manipulation.
These classes support stacks, queues, lists, and hash tables. Most collection classes implement the
same interfaces.

There are several applications for collection classes, such as dynamic memory allocation for elements
and index-based access to lists of objects.
These classes construct collections of objects of the Object class, the building blocks of all other C#
data types.
---------------------------------------------------------------------------------------------------------------------------------------
-

What are the extension method in .NET?

In C#, the extension method concept allows you to add new methods in the existing class or in the
structure without modifying the source code of
the original type and you do not require any kind of special permission from the original type and
there is no need to re-compile the original type.
It is introduced in C# 3.0.
---------------------------------------------------------------------------------------------------------------------------------------
-

what is static class with code implementation?

In C#, one is allowed to create a static class, by using static keyword. A static class can only contain
static data members and static methods.
It is not allowed to create objects of the static class and since it does not allow to create objects it
means it does not allow instance
constructor. Static classes are sealed, means you cannot inherit a static class from another class.

Syntax:

static class Class_Name

{
// static data members
// static method
}

In C#, the static class contains two types of static members as follows:

Static Data Members: As static class always contains static data members, so static data members are
declared using static keyword and they are
directly accessed by using the class name. The memory of static data members is allocating
individually without any relation with the object.
Syntax:
static class Class_name
{
public static nameofdatamember;
}

Static Methods: As static class always contains static methods, so static methods are declared using
static keyword. These methods only access
static data members, they can not access non-static data members.
Syntax:
static class Class_name {
public static nameofmethod()
{
// code
}
}

---------------------------------------------------------------------------------------------------------------------------------------
-

IEnumerable vs IQueryable?

IEnumerable and IQueryable are both interfaces in .NET that represent sequences of data. However,
they are designed for different purposes and
have different characteristics.

IEnumerable:

IEnumerable is the most basic interface for working with collections of data in .NET.
It represents a forward-only cursor for iterating over a collection of objects.
It is part of the LINQ (Language Integrated Query) framework in .NET and provides a set of extension
methods for querying data in-memory
collections.
When you use LINQ methods such as Where, Select, OrderBy, etc., on an IEnumerable, they are
executed in-memory on the entire collection.
IEnumerable is suitable for working with in-memory collections such as arrays, lists, and other data
structures.
IQueryable:
IQueryable is an interface that inherits from IEnumerable and extends its capabilities to query data
from data sources such as databases, remote
services, or other data providers.
It represents a queryable data source that supports deferred execution and expression trees.
IQueryable is used with LINQ to SQL, LINQ to Entities, and other ORM (Object-Relational Mapping)
frameworks to write LINQ queries that are
translated into SQL or other query languages and executed on the server.
When you use LINQ methods such as Where, Select, OrderBy, etc., on an IQueryable, the query is not
executed immediately. Instead, it builds an
expression tree representing the query, which can be translated and executed on the data source
when needed.
IQueryable is suitable for querying large datasets where you want to push the query processing to the
database or other data provider.
Here's a summary of the differences:

IEnumerable: Suitable for in-memory collections, executes LINQ queries in-memory.

IQueryable: Suitable for querying data from external data sources, executes LINQ queries on the
server.

---------------------------------------------------------------------------------------------------------------------------------------
-

Error handling in Projects?

An exception is defined as an event that occurs during the execution of a program that is unexpected
by the program code.
The actions to be performed in case of occurrence of an exception is not known to the program. In
such a case, we create an exception object and
call the exception handler code. The execution of an exception handler so that the program code does
not crash is called exception handling.
Exception handling is important because it gracefully handles an unwanted event, an exception so
that the program code still makes sense to the user.

---------------------------------------------------------------------------------------------------------------------------------------
-

--- DB ---

What is cursor? and it's alternative ?

A Cursor in PL/SQL is a pointer to a context area that stores the result set of a query.

PL/SQL Cursors
The cursor is used to retrieve data one row at a time from the results set, unlike other SQL commands
that operate on all rows at once.

Cursors update table records in a singleton or row-by-row manner.

The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another
predefined area in the main memory Set,
within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-
defined area.

---------------------------------------------------------------------------------------------------------------------------------------
-

Types of Trigger,Functions?
In database systems like Oracle, SQL Server, MySQL, and PostgreSQL, there are various types of
triggers and functions available to implement
business logic, enforce data integrity, and perform calculations or transformations on data. Here's an
overview of the types of triggers and
functions commonly used in database systems:

Triggers:

DML Triggers (Data Manipulation Language Triggers):

DML triggers are fired in response to data manipulation language (DML) events, such as INSERT,
UPDATE, or DELETE operations on tables.
They can be defined at the row level or statement level, depending on whether they fire once for
each affected row or once for each affected
statement.
DML triggers can be used to enforce business rules, audit changes, or perform additional data
validation or processing.
DDL Triggers (Data Definition Language Triggers):

DDL triggers are fired in response to data definition language (DDL) events, such as CREATE, ALTER, or
DROP operations on database objects
(tables, views, procedures, etc.).
They can be used to enforce data schema rules, log changes to database structure, or prevent
unauthorized changes to database objects.
Instead Of Triggers:

Instead Of triggers are used in databases that support views with updateable columns.
They are fired instead of the default INSERT, UPDATE, or DELETE operations on a view, allowing you to
customize the behavior of these operations.
System Triggers:

System triggers are automatically executed in response to specific system-level events, such as
startup, shutdown, or login/logout events.
Functions:

Scalar Functions:

Scalar functions return a single value based on input parameters.

They can perform calculations, string manipulation, date/time operations, or other types of
computations.
Examples include SUM(), CONCAT(), DATEDIFF(), etc.
Table-Valued Functions:

Table-valued functions return a result set (i.e., a table) as output.

They can be used in the FROM clause of a SELECT statement, just like a table or a view.
Examples include inline table-valued functions and multi-statement table-valued functions.
Aggregate Functions:

Aggregate functions perform calculations across a set of values and return a single aggregated result.
Examples include SUM(), AVG(), MIN(), MAX(), COUNT(), etc.
Window Functions (Analytic Functions):

Window functions perform calculations across a set of rows related to the current row.
They can be used to calculate moving averages, cumulative sums, rankings, and other types of
analytical calculations.
Examples include ROW_NUMBER(), RANK(), LEAD(), LAG(), SUM() OVER(), etc.
Stored Procedures:

Stored procedures are precompiled and stored in the database for reuse.
They can contain multiple SQL statements, control-of-flow statements, variables, and parameters.
Stored procedures are often used to encapsulate complex business logic or frequently performed
tasks.
User-Defined Functions (UDFs):

User-defined functions allow you to extend the functionality of the database by defining custom
functions.
They can be scalar functions, table-valued functions, or aggregate functions.
UDFs are commonly used to encapsulate reusable logic that can be called from SQL queries or stored
procedures.
These are some of the common types of triggers and functions available in database systems.
Depending on the specific requirements of your
application, you may use one or more of these types to implement the desired functionality and
business logic in your database environment.

---------------------------------------------------------------------------------------------------------------------------------------
-

VW vs MVW, having vs Where clause, SP vs Function

Let's break down the comparisons between VW (View), MVW (Materialized View), and the
differences between using a HAVING clause versus a
WHERE clause in SQL queries, as well as the distinctions between stored procedures (SP) and
functions.

View (VW):

Definition: A view is a virtual table derived from one or more tables or other views. It does not store
data on its own but provides a way to
present data stored in tables in a specific format.
Purpose: Views are primarily used for simplifying complex queries, abstracting underlying table
structures, enforcing security (by limiting access
to certain columns or rows), and providing a consistent interface to users or applications.
Execution: Views are not materialized by default; they are executed dynamically whenever they are
referenced in a query.
Materialized View (MVW):

Definition: A materialized view is a database object that contains the results of a query. Unlike a
regular view, a materialized view stores
the data physically, allowing for faster query execution.
Purpose: Materialized views are used to improve query performance by pre-computing and storing
aggregated or joined data, which can be refreshed
periodically to synchronize with the underlying data.
Execution: Materialized views store the query results in the database, so they incur storage overhead.
However, they provide faster query
performance by avoiding expensive computations during query execution.
HAVING Clause vs WHERE Clause:

HAVING Clause: The HAVING clause is used in SQL queries with aggregate functions (e.g., SUM, AVG,
COUNT) to filter the results of a GROUP BY
clause based on specified conditions.
WHERE Clause: The WHERE clause is used to filter rows based on a specified condition or set of
conditions. It is typically used to filter rows
before any grouping or aggregation is performed.
Stored Procedure (SP) vs Function:

Stored Procedure (SP):

Definition: A stored procedure is a precompiled collection of SQL statements and procedural logic
stored in the database. It can accept input
parameters, perform operations, and return results.
Purpose: Stored procedures are used to encapsulate and execute complex business logic or data
manipulation tasks. They can perform multiple
operations, interact with multiple tables, and return multiple result sets.
Execution: Stored procedures can execute DML (Data Manipulation Language) statements (e.g.,
SELECT, INSERT, UPDATE, DELETE) and DDL
(Data Definition Language) statements within a transaction.
Function:

Definition: A function is a database object that accepts input parameters, performs calculations or
operations, and returns a single value or
result set. Functions can be scalar functions, table-valued functions, or aggregate functions.
Purpose: Functions are used to encapsulate reusable logic and perform calculations or
transformations on data. They are commonly used within
SQL queries, stored procedures, or other functions.
Execution: Functions are executed within the context of a SQL statement or another database object.
They can be called from SQL queries, stored
procedures, or other functions to perform specific tasks.
In summary, views and materialized views provide ways to abstract and present data from underlying
tables, the HAVING clause is used to filter
grouped data based on aggregate functions, the WHERE clause is used to filter rows before grouping,
and stored procedures and functions are used to
encapsulate and execute reusable logic in the database. The choice between these options depends
on the specific requirements and performance
considerations of your application.

---------------------------------------------------------------------------------------------------------------------------------------
-
---------------------------------------------------------------------------------------------------------------------------------------
-

Query to swap the row values from same column without using update command

You can swap the values of two rows in the same column without using the UPDATE command by
leveraging temporary variables. Here's an example
SQL query to achieve this:

sql:

SET @temp := (SELECT column_name FROM your_table WHERE condition1 ORDER BY

ordering_column LIMIT 1);
UPDATE your_table SET column_name = (SELECT column_name FROM your_table WHERE
condition2 ORDER BY ordering_column LIMIT 1) WHERE condition1;
UPDATE your_table SET column_name = @temp WHERE condition2;
Replace your_table, column_name, condition1, condition2, and ordering_column with appropriate
values based on your actual table structure and
requirements.

This query performs the following steps:

It uses a SELECT statement to fetch the value from the column (column_name) of the first row that
meets condition1, and assigns it to a temporary
variable (@temp).
It uses another SELECT statement to fetch the value from the column (column_name) of the first row
that meets condition2, and updates the column's
value in the rows that meet condition1.
Finally, it updates the column's value in the rows that meet condition2 with the value stored in the
temporary variable (@temp).
This way, the values of the rows are effectively swapped without using the UPDATE command directly.
However, this approach may not be suitable
for large datasets or in scenarios where concurrency and consistency are critical, as it relies on
multiple steps and temporary variables.
---------------------------------------------------------------------------------------------------------------------------------------
-

Query to transform multiple rows into single column

let's consider an example where we have a table named employees with a column named name. We
want to concatenate all the names from the
employees table into a single column.

Assuming our employees table looks like this:

lua

| name |
|---------|
| Alice |
| Bob |
| Charlie |

The SQL query using STRING_AGG() function in SQL Server would be:

sql

SELECT STRING_AGG(name, ', ') AS concatenated_names

FROM employees;
The expected output would be a single row with the concatenated names:

lua

| concatenated_names |
|-----------------------------|
| Alice, Bob, Charlie |

This output shows all the names from the name column concatenated into a single string, separated
by commas.

---------------------------------------------------------------------------------------------------------------------------------------
-

Sql Profiler?

SQL Profiler is a tool provided by Microsoft SQL Server for monitoring and analyzing SQL Server
Database Engine activities.
It allows database administrators, developers, and analysts to capture and analyze SQL Server events
in real-time or from saved trace files.

Here are some key features and functionalities of SQL Profiler:

Event Capture: SQL Profiler allows you to capture various types of events occurring on the SQL Server,
such as SQL queries,
stored procedure executions, database connections, errors, locks, and more.

Customizable Traces: You can create custom traces by selecting specific events, columns, and filters
to capture only the relevant information for
analysis.

Real-Time Monitoring: SQL Profiler can monitor SQL Server activity in real-time, allowing you to
observe database performance and behavior as
it happens.

Analysis and Troubleshooting: You can use SQL Profiler to troubleshoot performance issues, identify
slow-running queries, diagnose locking and
blocking problems, and optimize database performance.

Trace Templates: SQL Profiler provides predefined trace templates for common profiling scenarios,
making it easier to set up traces for
specific purposes, such as performance tuning or security auditing.

Server-Side Tracing: You can run traces directly on the SQL Server instance using server-side tracing,
which minimizes overhead on client
machines and provides more accurate performance data.

Export and Playback: SQL Profiler allows you to export captured traces to files for further analysis or
share them with other team members.
You can also replay captured traces against a different server for performance testing or
troubleshooting purposes.

SQL Profiler is a powerful tool for monitoring and troubleshooting SQL Server performance and
behavior. However, it's essential to use it
judiciously to avoid performance overhead on the server due to excessive tracing or capturing
unnecessary events. Additionally, Microsoft has
introduced Extended Events as a replacement for SQL Profiler in newer versions of SQL Server,
providing similar functionality with improved
performance and flexibility.

---------------------------------------------------------------------------------------------------------------------------------------
-

What is NOLOCK?

NOLOCK is a query hint in SQL Server that allows you to read data from a table without acquiring
shared locks on the data. It is also known
as "READUNCOMMITTED" isolation level.

When you use NOLOCK, the SQL Server query engine bypasses locking mechanisms and reads data
directly from the table or index without waiting
for locks to be released. This can improve query performance in certain scenarios, especially in high-
concurrency environments where
locking contention is a concern.
However, there are several important considerations and potential drawbacks to using NOLOCK:

Dirty Reads: Since NOLOCK reads data without acquiring locks, it may return uncommitted data (i.e.,
data that has been modified
but not yet committed) from concurrent transactions. This can lead to inconsistent or inaccurate
results, known as "dirty reads."

Non-repeatable Reads: NOLOCK can also result in non-repeatable reads, where the same query
executed multiple times returns different
results due to concurrent modifications to the data.

Phantom Reads: In addition to dirty reads and non-repeatable reads, NOLOCK can allow "phantom
reads," where a query retrieves rows
that were inserted or deleted by concurrent transactions after the query started.

Data Integrity Risks: Using NOLOCK can compromise data integrity and consistency, especially in
transactional systems where accurate and
reliable data is critical.

Given these considerations, it's generally recommended to use NOLOCK cautiously and only in specific
situations where the trade-offs in
data consistency and integrity are acceptable, and the potential performance benefits outweigh the
risks. Additionally, alternative approaches
such as optimizing indexes, using appropriate isolation levels, and tuning queries may provide better
long-term solutions to performance issues
without sacrificing data integrity.

---------------------------------------------------------------------------------------------------------------------------------------
-

Global Table,Temp table, Common Table?

In SQL, "global table," "temp table," and "common table" are terms that refer to different types of
tables or table-like structures,
each serving distinct purposes. Here's an overview of each:

Global Table:

The term "global table" is not commonly used in SQL. In a general context, a "global table" could refer
to a regular table that is accessible
globally within a database schema.
Regular tables in SQL databases store persistent data and are typically created using CREATE TABLE
statements. They can be accessed by any user
or application with appropriate permissions.
Temp Table (Temporary Table):

A temporary table is a table that exists only for the duration of a database session or transaction.
It is typically used to store temporary data that is needed for a specific task or calculation.
Temporary tables are created using CREATE TABLE statements with the # prefix for session-specific
temporary tables or the ##
prefix for global temporary tables in SQL Server. In other database systems like PostgreSQL and
MySQL, temporary tables are created using
the CREATE TEMPORARY TABLE statement.
Temporary tables are useful for storing intermediate results, breaking down complex queries into
smaller steps, and reducing the
need for repeated subqueries or complex joins.
Common Table (CTE - Common Table Expression):

A common table expression (CTE) is a temporary result set that is defined within the scope of a single
SQL statement. It allows you to define a
named query expression (CTE) that can be referenced within the same SQL statement, similar to a
subquery.
CTEs are defined using the WITH keyword followed by a list of one or more named queries. They are
often used to improve the readability,
maintainability, and performance of complex SQL queries by breaking them down into smaller, more
manageable parts.
Unlike temporary tables, CTEs do not create physical table structures in the database. They are purely
a logical construct used within a
single SQL statement.
In summary:

Global tables are regular tables accessible within a database schema.

Temp tables are temporary tables that exist for the duration of a session or transaction and are used
to store temporary data.
Common tables (CTEs) are temporary result sets defined within a single SQL statement and are used
to break down complex queries into smaller,
more manageable parts.

---------------------------------------------------------------------------------------------------------------------------------------
-

DB partitioning?

Database partitioning is a technique used to divide large tables and indexes into smaller, more
manageable pieces called partitions.
Each partition contains a subset of the table's data, and partitions are typically defined based on
specific criteria, such as ranges of
values or hash values.

Here are some key benefits and considerations of database partitioning:

Benefits:

Improved Performance: Partitioning can improve query performance by allowing the database engine
to access and manipulate smaller subsets of data,
leading to faster query execution times.

Efficient Data Management: Partitioning makes it easier to manage and maintain large tables and
indexes by dividing them into smaller,
more manageable units. This can reduce maintenance overhead and downtime associated with
operations such as backups, index rebuilds,
and data archiving.

Increased Scalability: Partitioning can improve scalability by distributing data across multiple storage
devices or servers, allowing for
parallel processing and improved resource utilization.

Enhanced Availability and Reliability: Partitioning can increase availability and reliability by allowing
for partition-level operations,
uch as backup and restore, recovery, and maintenance activities, without affecting the entire dataset.

Cost-Effective Storage Solutions: Partitioning can be used to implement tiered storage solutions,
where hot, warm, and cold data are stored
on different storage tiers based on access patterns and performance requirements. This can optimize
storage costs and resource utilization.

Considerations:

Design Considerations: Effective partitioning requires careful planning and consideration of factors
such as data distribution,
access patterns, query performance, and maintenance requirements.

Query Optimization: While partitioning can improve query performance, poorly designed partitions
or inefficient query plans can
result in performance degradation. It's essential to monitor and optimize queries to ensure optimal
performance.

Maintenance Overhead: Partitioning adds complexity to database maintenance tasks such as data
loading, index maintenance, and partition
management. Proper automation and monitoring tools are necessary to streamline maintenance
activities.

Data Distribution and Skew: Uneven data distribution or data skew across partitions can lead to
performance issues and resource contention. I
t's essential to design partitions carefully to ensure balanced data distribution and optimal
performance.

Compatibility and Limitations: Database partitioning features and capabilities vary across different
database systems. It's important
to understand the limitations and compatibility considerations of partitioning features in your chosen
database system.

Overall, database partitioning is a powerful technique for improving performance, scalability, and
manageability of large databases.
However, it requires careful planning, monitoring, and optimization to realize its full benefits and
avoid potential pitfalls.

---------------------------------------------------------------------------------------------------------------------------------------
-

Types of DB exceptions?

Database exceptions, also known as errors or faults, can occur during the execution of database
operations due to various reasons.
Here are some common types of database exceptions:

Constraint Violation: These exceptions occur when an operation violates a constraint defined on the
database schema. Examples include:

Unique constraint violations: Attempting to insert a duplicate value into a column with a unique
constraint.
Foreign key constraint violations: Attempting to insert or update a value in a foreign key column that
does not exist in the referenced table.
Check constraint violations: Attempting to insert or update a value that does not satisfy the condition
specified in a check constraint.
Data Integrity Violation: These exceptions occur when the integrity of the data in the database is
compromised. Examples include:

Data type mismatch: Attempting to insert a value of an incorrect data type into a column.
Data truncation: Attempting to insert a value that exceeds the maximum length allowed for a column.
Concurrency Issues: These exceptions occur when multiple transactions attempt to modify the same
data concurrently. Examples include:

Deadlocks: Two or more transactions are waiting for each other to release locks on resources,
resulting in a deadlock situation.
Lock timeouts: Transactions are unable to acquire locks within a specified time limit, resulting in a
timeout error.
Resource Limitations: These exceptions occur when the database system reaches its resource limits.
Examples include:

Memory exhaustion: The database system runs out of memory to execute queries or store data.
Disk space exhaustion: The database system runs out of disk space to store data or log files.
Connection Issues: These exceptions occur when there are problems establishing or maintaining a
connection to the database. Examples include:

Network errors: Problems with network connectivity between the application server and the
database server.
Database server downtime: The database server is not accessible due to maintenance, hardware
failure, or other reasons.
System Errors: These exceptions occur due to internal errors within the database system or
underlying infrastructure. Examples include:

Database server crashes: The database server terminates unexpectedly due to a software bug or
hardware failure.
Disk I/O errors: Problems reading from or writing to disk storage.
Handling these exceptions appropriately is essential to ensure the reliability, availability, and
performance of database-driven applications.
This often involves implementing error handling mechanisms in the application code, such as try-
catch blocks, transaction management,
and retry strategies, to gracefully handle exceptions and provide meaningful feedback to use

---------------------------------------------------------------------------------------------------------------------------------------
-