TodayInterview(EXL health interview)

1.update second table without using subquery.
2.SOLID
3.Abstract vs interface.

UPDATE es

SET es.Salary = es.Salary + 500,  -- Example: Increase salary

    es.Bonus = es.Bonus + 100     -- Example: Increase bonus

FROM EmpSalary es

INNER JOIN Emp e ON es.EmpID = e.EmpID

WHERE e.Department = 'IT';  -- Condition based on Emp table

CTE

WITH CTE_Update AS (

    SELECT es.EmpID, es.Salary, es.Bonus

    FROM EmpSalary es

    INNER JOIN Emp e ON es.EmpID = e.EmpID

    WHERE e.Department = 'IT'  -- Condition based on Emp table

)

UPDATE CTE_Update

SET Salary = Salary + 500,  -- Increase salary

    Bonus = Bonus + 100;    -- Increase bonus

using System;

using System.Linq;

public class HelloWorld

{

    public static void Main(string[] args)

    {

        var names = new string[] { "Alice", "Jonathan", "Christopher", "Bob", "Alexander" };

        var longestName = names

            .Select(name => new { Name = name, Length = name.Length })

            .OrderByDescending(x => x.Length)

            .First();

        Console.WriteLine($"Longest Name: {longestName.Name}");

        Console.WriteLine($"Length: {longestName.Length}");

        

    }

}

TOP AI web sites

 https://v0.dev/

My Learn

 In this video, let's code, test, and deploy a web app using AI only.

Testsprite: https://www.testsprite.com/?ref=K1D We'll be using TestSprite to test the app after coding to ensure that our front and backend work exactly as we want and that everything is safe and secure. v0: https://v0.dev As for creating the app from the ground up, we'll use v0. Just give simple text prompts and let the AI write code for you. 01:16 Code the app using AI 08:20 Deploy the app 09:00 Test the app https://www.youtube.com/watch?v=l5Amu_tyZEE

microservices

 https://github.com/Harsha-Global/Dot-NET-Microservices-with-Azure-and-AKS

duplicates

 with cte as(

SELECT EMPID, Forminfoid, FormName,

       ROW_NUMBER() OVER (PARTITION BY EMPID, Forminfoid,FormName ORDER BY EMPID) AS RowNum

FROM tbl_FilesMigrationLogs)

select * from cte where rownum >1

filtering based on 3 columns duplicates

--USing having can't delete in cte due to aggrigate function..

so use below rownumber....

with cte as(

SELECT EMPID, Forminfoid, FormName,

       ROW_NUMBER() OVER (PARTITION BY EMPID, Forminfoid,FormName ORDER BY EMPID) AS RowNum

FROM tbl_FilesMigrationLogs)

delete from cte where rownum >1


here empid,formid,formname have duplicate data

duplicate recors

 select EMPID ,Forminfoid,FormName ,count(*) as dcount 

from  tbl_FilesMigrationLogs

group by EMPID,Forminfoid,FormName

HAving COUNT(*) >1

)

delete  from cte where dcount >1

Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

solution

To delete duplicate records

 ✅ Using ROW_NUMBER() (Recommended)

WITH CTE AS (
    SELECT 
        EmployeeID, 
        Salary, 
        Department, 
        ROW_NUMBER() OVER (PARTITION BY Salary, Department ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
DELETE FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID FROM CTE WHERE RowNum > 1
);

🔹 Explanation:

The ROW_NUMBER() function assigns a unique number to each duplicate row within the same (Salary, Department).
PARTITION BY Salary, Department ensures numbering resets for each Salary & Department combination.
ORDER BY EmployeeID determines which row to keep (lowest EmployeeID remains).
The DELETE statement removes all rows where RowNum > 1, keeping only one record per (Salary, Department).
✅ Using DELETE with JOIN (Alternative)
DELETE E1
FROM Employees E1
JOIN Employees E2 
ON E1.Salary = E2.Salary 
AND E1.Department = E2.Department
AND E1.EmployeeID > E2.EmployeeID;
Explanation:

The self-join finds duplicate (Salary, Department).
It deletes the record where EmployeeID is greater, keeping the lowest EmployeeID.
SELECT EmployeeID, Salary, Department,
       ROW_NUMBER() OVER (PARTITION BY Salary, Department ORDER BY EmployeeID) AS RowNum
FROM Employees;

To find duplicate salaries within each department, you can modify the query as follows:

 SELECT Salary, Department, COUNT(*) AS DuplicateCount

FROM Employees

GROUP BY Salary, Department

HAVING COUNT(*) > 1;

Explanation:

• GROUP BY Salary, Department ensures that duplicates are checked within each department.
• COUNT(*) counts occurrences of each (Salary, Department) combination.
• HAVING COUNT(*) > 1 filters out unique salaries, showing only duplicates.

Example:

Given Employees Table:

EmployeeID	Name	Salary	Department
1	John	5000	IT
2	Alice	6000	HR
3	Mark	5000	IT
4	David	7000	Sales
5	Steve	6000	HR
6	Bob	5000	HR

Query Output:

Salary	Department	DuplicateCount
5000	IT	2
6000	HR	2

This means that:

• Salary 5000 appears twice in the IT department.
• Salary 6000 appears twice in the HR department.

Find duplicates records IN sql table

 SQL duplicate records

select EMPID ,Forminfoid,FormName ,count(*) as dcount 

from  tbl_FilesMigrationLogs

group by EMPID,Forminfoid,FormName

HAving COUNT(*) >1


track by multiple columns.

Real time issues

 azure blob name spaces issue getting filename has different characters so getting error that 

path not found.
2.don't use increment id as save for the other table  
3.use country code instad of name may be name will changes

SQL Quryies

 1.string saparations. query 

select  STRING_AGG(EmpID,',')  From

(

SELECT EmpID FROM tbl_Employee  ORDER BY updatedOn DESC

OFFSET 70 ROWS FETCH NEXT 20 ROWS ONLY

) AS S;

2.in one table you need to get order by for 2 columns
ex:workflowid inorder after that sequence in desc order
for this user 

SELECT WorkFlow_ID, User_ID, MAX(Approver_Sequence) AS MaxApproverSequence

FROM tbl_WorkFlowMembers

WHERE Division_ID='RAIL'

GROUP BY WorkFlow_ID, User_ID

ORDER BY WorkFlow_ID, MaxApproverSequence DESC;

Duplicate records find and delete

 Check duplicate count based on 3 columns combo

select EMPID,FORMinfoID,FormName,Count(*) as counts from tbl_FilesMigrationLogs 

group by EmpID,FormInfoID,FormName

HAVING COUNT(*) >1; 

WITH CTE AS (

    SELECT EMPID,FORMinfoID,FormName,

           ROW_NUMBER() OVER (PARTITION BY EmpID, FormInfoID,FormName ORDER BY  EMPID) AS rn

    FROM tbl_FilesMigrationLogs

)

Select * FROM CTE WHERE rn > 1;

but for the both quries getting count different..

sql ch2

 Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table. The STATION table is described as follows

To find the difference between the total number of CITY entries and the number of distinct CITY entries in the STATION table, you can use the following SQL query:

SELECT 
    COUNT(CITY) AS TotalCityCount,
    COUNT(DISTINCT CITY) AS DistinctCityCount,
    (COUNT(CITY) - COUNT(DISTINCT CITY)) AS CityCountDifference
FROM STATION;

SQL

 Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

The STATION table is described as follows:

LTInterview

 

ChatGPT said:

In C#, memory is managed using different types of memory allocation and regions. Here are the primary memory types you will encounter:

1. Stack Memory

• Purpose: Stores value types (primitive types like int, float, bool, structs, etc.) and local variables.
• Managed by: The runtime, automatically handled (allocated when a method is called and deallocated when it exits).
• Characteristics:
  • Fast: Allocations and deallocations are quick since they follow a Last-In-First-Out (LIFO) order.
  • Size Limit: The stack has a fixed size (typically around 1 MB to 8 MB).
  • Lifetime: Memory is automatically cleaned up when the method/variable goes out of scope.
  • Access: Direct access with no need for garbage collection.
  Example:

  csharp
  void Method()
  {
      int x = 10;  // 'x' is stored in stack memory
  }
  

2. Heap Memory

• Purpose: Stores reference types (objects, arrays, strings, etc.) and instances of classes.

• Managed by: The garbage collector (GC) automatically cleans up unused objects.

• Characteristics:

  • Slower than stack memory: Memory allocation and deallocation are slower since GC manages cleanup.
  • Dynamic Size: Memory size can grow as needed, as long as there is enough system memory.
  • Lifetime: Objects remain in heap memory until they are no longer referenced, and garbage collection frees them.
  • Access: References to heap objects are stored in the stack.

  Example:

  csharp
  class Person
  {
      public string Name;
  }
  
  void Method()
  {
      Person person = new Person();  // 'person' is allocated on the heap
      person.Name = "John";
  }
  

3. Static Memory

• Purpose: Stores static variables and global data (variables defined with the static keyword).

• Managed by: The runtime and remains available throughout the application’s lifetime.

• Characteristics:

  • Lifetime: Static variables live for the duration of the application.
  • Access: Static members can be accessed without creating an instance of the class.
  • Usage: Used for data or methods that should be shared across all instances of a class.

  Example:

  csharp
  class MyClass
  {
      public static int counter = 0;  // Static variable
  }
  
  void Method()
  {
      MyClass.counter++;  // Access static variable
  }
  

4. Code (Method) Memory

• Purpose: Stores the compiled code (the executable instructions) for methods and functions.
• Managed by: The runtime when the program is loaded into memory.
• Characteristics:
  • Read-only: The code cannot be modified during execution.
  • Lifetime: The code remains in memory for the entire duration of the application.

5. Thread-Local Memory

• Purpose: Stores data specific to the thread (like local variables for each thread).
• Managed by: The runtime and is isolated to the thread that owns it.
• Characteristics:
  • Thread-Specific: Each thread has its own thread-local memory for its local variables.
  • Used for Multi-threading: Essential when managing multiple threads in parallel operations.

---

Memory Management in C#

1. Garbage Collection (GC): C# uses a garbage collector to manage memory on the heap, freeing memory of objects that are no longer in use (i.e., no references to them exist).

   • The GC periodically runs to reclaim memory from unused objects.
   • Generations: The GC uses a generational approach to optimize memory collection (Young Generation, Old Generation).

2. Memory Allocation:

   • Stack: Automatically allocated and deallocated.
   • Heap: Objects are created dynamically via new and deallocated by the GC when no references exist.

3. Memory Leaks:

   • While the garbage collector manages memory, improper management of references (e.g., keeping unnecessary references alive) can lead to memory leaks.

---

Conclusion

• Stack: Fast, short-lived, and used for value types and local variables.
• Heap: Slower, long-lived, and used for reference types (objects, arrays, etc.).
• Static Memory: Used for static variables and shared data.
• Code Memory: Stores method instructions.
• Thread-Local Memory: Isolated to threads.

Let me know if you'd like a deeper dive into garbage collection or memory management strategies in C#! 🚀

L&TInterview

 

Difference Between VARCHAR and NVARCHAR in SQL Server

When to Use What?

• Use VARCHAR when dealing with English or Latin-based languages (saves storage and improves performance).
• Use NVARCHAR when storing multilingual data (Unicode characters like Chinese, Arabic, etc.).

Feature	VARCHAR	NVARCHAR
Storage Type	Non-Unicode	Unicode (UTF-16)
Character Support	Only supports single-byte characters (e.g., English, Latin)	Supports multilingual characters (e.g., Chinese, Arabic, Hindi)
Storage Size	1 byte per character	2 bytes per character (due to Unicode)
Prefix for String Literals	No prefix needed	Prefix N before string (e.g., N'Text')
Max Length	VARCHAR(8000) or VARCHAR(MAX)	NVARCHAR(4000) or NVARCHAR(MAX)
Performance	Faster for English/Latin-based data (requires less storage)	Slightly slower due to Unicode support
Best Use Case	English, simple character sets	Multilingual applications, special characters

L&T interview

1:Remove duplicates and spaces L&T 
2:fetch and pull
3:types of memories
4:try catch implimentations
5:var and nvarchar
6:entity ado major difference
7:localization in sql(for lanaguage specific  save)
using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{

public static void Main()

{

var data = "Hello world";

Console.WriteLine(data.Replace(" ","").ToCharArray().Distinct().ToArray());

2.git fetch and pull difference

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        string data = "Hello world";
        HashSet<char> seen = new HashSet<char>();
        string result = "";

        foreach (char c in data)
        {
            if (c != ' ' && !seen.Contains(c)) // Ignore spaces and duplicates
            {
                seen.Add(c);
                result += c;
            }
        }

        Console.WriteLine(result); // Output: Helowrd
    }
}

Explanation

1. Use a HashSet<char> to store unique characters.
2. Loop through each character in the string.
3. Skip spaces (c != ' ') and only add characters if they haven’t been seen before (!seen.Contains(c)).
4. Build the result string without duplicates.

---

Why Use HashSet?

• Faster lookup (O(1) time complexity for checking if a character is already seen).
• Efficient way to remove duplicates compared to nested loops.

Difference Between git fetch and git pull

Both git fetch and git pull are used to get updates from a remote repository, but they work differently.

Feature	git fetch	git pull
Operation	Downloads changes from the remote repository but does not merge them	Downloads changes and automatically merges them into the current branch
Merging	No merging; updates only the remote-tracking branches	Merges remote changes into the current branch
Use Case	When you want to check for updates before merging	When you want to update your local branch immediately
Risk	No risk; does not change the working directory	Potential conflicts if changes clash with local work
Command Example	git fetch origin	git pull origin main

duplicate remove

 WITH CTE as(

select *,ROW_NUMBER () over (PARTITION BY User_ID order by Approver_Sequence) as row_num FROM tbl_WorkFlowMembers where 

WorkFlow_ID ='RAILCHLuz00001')

delete from cte

where row_num >1

delete duplicate record

over paritition must be use Order by....

Udemy courses

 https://github.com/prateek27/design-patterns-java

COurse Link

AFI workflow member sequence change

 USER_ID Approver_Sequence NewSequence

mlfoster 2 1

ismirnova4 2

hnandha 6 3

tpenny 7 4

cmignat 8 5

ytan      9      6

blerner 10 7

cheuschmid 11 8

tvadaketh 12 9

ngrasberger 13 10

using rownumber will update it 
cte first 

SELECT USER_ID, Approver_Sequence, 

           ROW_NUMBER() OVER (ORDER BY Approver_Sequence ) AS NewSequence

    FROM tbl_WorkFlowMembers

where WorkFlow_ID='RAILAUBre00001'

WITH OrderedApprovers AS (

SELECT USER_ID, Approver_Sequence, 

           ROW_NUMBER() OVER (ORDER BY Approver_Sequence ) AS NewSequence

    FROM tbl_WorkFlowMembers

where WorkFlow_ID='RAILAUBre00001'

)

UPDATE tbl_WorkFlowMembers

SET Approver_Sequence = OrderedApprovers.NewSequence

FROM tbl_WorkFlowMembers

JOIN OrderedApprovers ON tbl_WorkFlowMembers.User_ID = OrderedApprovers.User_ID

where  WorkFlow_ID='RAILAUBre00001'

Add workflow memeber to the exixsting flow

requirement that add new approver in first step 
solution:
1.Update squence with +1 
2.then insert new record
 

UPDATE tbl_WorkFlowMembers

SET Approver_Sequence = Approver_Sequence + 1

WHERE WorkFlow_ID IN ('WF2013RAILGBNot000041',

'WF2014RAILUSSou000043',

INSERT INTO tbl_WorkFlowMembers (WorkFlow_ID,Division_ID,Country_Code,User_ID,User_Email,User_Title,Site_Location,Operation_ID,  Approver_Sequence)

SELECT WorkFlow_ID, 'RAIL',Country_Code, 'mlfoster','mlfoster@harsco.com','Global FP&A Director',Site_Location,Operation_ID, 1

FROM tbl_WorkFlowMembers

WHERE WorkFlow_ID IN ('WF2013RAILGBNot000041',

'WF2014RAILUSSou000043',

'WF2020RAILAUBre000036',

'WF2020RAILBRBra000036',

'WF2020RAILCHLuz000036',

)

AND Approver_Sequence = 2;

Job based only on coding standards

 

Thoughtworks Recruitment cmpany

Thoughtworks Recruitment