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;