Sunday, May 14, 2023

C# Programs - need for interview n c# Questions

==========to be read==========

https://www.c-sharpcorner.com/article/async-and-await-in-c-sharp/

https://www.c-sharpcorner.com/article/task-and-thread-in-c-sharp/

https://learn.microsoft.com/en-us/aspnet/core/fundamentals/middleware/?view=aspnetcore-5.0

https://learn.microsoft.com/en-us/aspnet/core/fundamentals/middleware/write?view=aspnetcore-7.0

https://endjin.com/blog/2022/09/service-lifetimes-in-aspnet-core

https://www.c-sharpcorner.com/article/understanding-addtransient-vs-addscoped-vs-addsingleton-in-asp-net-core/

https://www.c-sharpcorner.com/UploadFile/a20beb/ienumerable-vs-iqueryable-in-linq/

==================


Differences between Middleware and Filter


  • Middleware has access to HttpContext but Filter has access to wider MVC Context which helps us to access routing data and model binding information.

  • Filters are only part of MVC Middleware but middlewares are part of every request pipeline.

  • The Execution of Middleware occurs before MVC Context becomes available in the pipeline.

  • Middleware will be executed irrespective of the Controller or Action Method we are hitting but Filters will be executed based on which Controller or Action Method it has been configured.


=======================C# Program=====================


1. Second Highest Value ?

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

            int[] myArray = new int[] { 0, 1, 2, 3, 13, 8, 5 };

            int first = 0;

            int second = 0;


            for (int i = 0; i < myArray.Length; i++)

            {

                if (myArray[i] > first)

                {

                    second = first;

                    first = myArray[i];

                }

                else if (myArray[i] > second)

                {

                    second = myArray[i];

                }

            }


            Console.WriteLine(first);

            Console.WriteLine("\n");

            Console.WriteLine(second);


            Array.Sort(myArray);

            Array.Reverse(myArray);

            Console.WriteLine(myArray[1]);


            var temp = (from number in myArray

                        orderby number descending

                        select number).Distinct().Skip(1).First();

            Console.WriteLine(temp);


            var temp1 = myArray.OrderByDescending(x => x).Skip(1).First();

            Console.WriteLine(temp1);

2. Number of Repeated value ?

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

            int[] num = { 1, 2, 3, 2, 2, 1, 4, 5, 6, 1, 1, 1, 1 };


            var query = num.GroupBy(x => x)

              .Where(g => g.Count() > 1)

              .Select(y => new { Element = y.Key, Counter = y.Count() })

              .ToList();


            foreach (var item in query)

            {

                Console.WriteLine("Duplicate value : {0}", item.Element);

                Console.WriteLine("MaxCount : {0}", item.Counter);


            }


            IEnumerable<int> duplicates = num.GroupBy(x => x)

                                        .Where(g => g.Count() > 1)

                                        .Select(x => x.Key);

            Console.WriteLine("Duplicate elements are: " + String.Join(",", duplicates));



            var result = num.GroupBy(x => x).Select(x => new { key = x.Key, val = x.Count() });

            foreach (var item in result)

            {

                if (item.val > 1)

                {

                    Console.WriteLine("Duplicate value : {0}", item.key);

                    Console.WriteLine("MaxCount : {0}", item.val);

                }


            }


            var temp = num.GroupBy(x => x);

            foreach (var group in temp)

                Console.WriteLine("Value {0} has {1} items", group.Key, group.Count());


            int[] count = new int[10];


            //Loop through 0-9 and count the occurances

            for (int x = 0; x < 10; x++)

            {

                for (int y = 0; y < num.Length; y++)

                {

                    if (num[y] == x)

                        count[x]++;

                }

            }


            //For displaying output only            

            for (int x = 0; x < 10; x++)

                Console.WriteLine("Number " + x + " appears " + count[x] + " times");

################################################################################################################

============================================================Word Duplicate==============================

 string str = "this is lazy dog and lazy cat";

            var temp = str.Split(" ");

            var result = temp.GroupBy(x => x).Where(y => y.Count() > 1).Select(r => new { Word = r.Key, Counter = r.Count() }).ToList();

            foreach (var item in result)

            {

                Console.WriteLine("Duplicate value : {0}", item.Word);

                Console.WriteLine("MaxCount : {0}", item.Counter);


            }

Output: lazy 2 

====================================================================================================================

 public class Employee

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public int Salary { get; set; }


    }

        List<Employee> objEmp = new List<Employee>() {

            new Employee { Id = 1, Name = "Raj", Salary = 5000 },

            new Employee { Id = 2, Name = "Raju", Salary = 9000 },

            new Employee { Id = 3, Name = "Ramu", Salary = 3000 },

            new Employee { Id = 4, Name = "Ram", Salary = 7000 }};

var temp = objEmp.Where(x => x.Salary > 5000).Select(y => new { y.Name, y.Salary }).ToList();

            var teenStudentsName = from s in objEmp

                                   where s.Salary > 5000

                                   select new { Name = s.Name,Salary=s.Salary };

   

   

######################################################################################################################

Find Out Common Char


class GFG

{

 

    static int MAX_CHAR = 26;

 

    public static void commonCharacters(String []str,

                                            int n)

    {

         

        // primary array for common characters

        // we assume all characters are seen before.

        Boolean[] prim = new Boolean[MAX_CHAR];

         

        for(int i = 0; i < prim.Length; i++)

            prim[i] = true;

 

        // for each string

        for (int i = 0; i < n; i++)

        {

 

            // secondary array for common characters

            // Initially marked false

            Boolean[] sec = new Boolean[MAX_CHAR];

             

            for(int s = 0; s < sec.Length; s++)

                sec[s]=false;

 

            // for every character of ith string

            for (int j = 0; j < str[i].Length; j++)

            {

 

                // if character is present in all

                // strings before, mark it.

                if (prim[str[i][j] - 'a'])

                sec[str[i][j] - 'a'] = true;

            }

 

            // Copy whole secondary array into primary

            Array.Copy(sec, 0, prim, 0, MAX_CHAR);

        }

 

        // Displaying common characters

        for (int i = 0; i < 26; i++)

            if (prim[i])

            {

                Console.Write((char)(i + 97));

                Console.Write(" ");

            }

    }

 

    // Driver code

    public static void Main(String[] args)

    {

        String []str = { "geeksforgeeks",

                        "gemkstones",

                        "acknowledges",

                        "aguelikes" };

        //String []arr ={"bella", "label", "roller"};

        //var word = new string[] { "cool", "lock", "cook" };    

        int n = str.Length;

        commonCharacters(str, n);

    }

}

 

      OR  Short Way

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

            //var arr = new string[]{"bella", "label", "roller"};

            //var word = new string[] { "cool", "lock", "cook" };

            //var str1 = arr[0];

            //var str2 = arr[1];

            //var str3 = arr[2];


            //var st1 = word[0];

            //var st2 = word[1];

            //var st3 = word[2];


            //var common = str1.Intersect(str2).Intersect(str3);

            //var common1 = st1.Intersect(st2).Intersect(st3);


            //foreach (var c in common)

            //    Console.WriteLine(c); // "i", "r", "e"



===================================================================================

 public static Boolean canConstuct(string ransomeNote, string magazine) 

        {

            int[] alphabet = new int[26];

            for (int i = 0; i < magazine.Length; i++)

            {

                alphabet[magazine[i] - 'a'] += 1;

            }

            for (int j = 0; j < ransomeNote.Length; j++)

            {

                alphabet[ransomeNote[j] - 'a'] -= 1;

                if (alphabet[ransomeNote[j] - 'a'] < 0) 

                {

                    return false;

                }

            }

            return true;

        }



            bool temp= canConstuct("a", "b");

            bool temp1 = canConstuct("aa", "ab");

            bool temp2 = canConstuct("aa", "aab");

============================================================================================


public static String swap(String a,

                            int i, int j)

        {

            char temp;

            char[] charArray = a.ToCharArray();

            temp = charArray[i];

            charArray[i] = charArray[j];

            charArray[j] = temp;

            string s = new string(charArray);

            return s;

        }


 private static void permute(String str,

                               int l, int r)

        {

            if (l == r)

                Console.WriteLine(str);

            else

            {

                for (int i = l; i <= r; i++)

                {

                    str = swap(str, l, i);

                    permute(str, l + 1, r);

                    str = swap(str, l, i);

                }

            }

        }



 String str = "cat";

            int n = str.Length;

            permute(str, 0, n - 1);


Input : "cat"

Output:  cat


 cta


 act


 atc


 tac


 tca

 ===================================================================================================

 ==============================================Pagignation in Linq

  List<Employee> objEmp = new List<Employee>() {

            new Employee { Id = 1, Name = "Raj", Salary = 5000 },

            new Employee { Id = 2, Name = "Raju", Salary = 9000 },

            new Employee { Id = 3, Name = "Ramu", Salary = 3000 },

            new Employee { Id = 4, Name = "Ram", Salary = 70001 },

             new Employee { Id = 5, Name = "Ram2", Salary = 70002 },

              new Employee { Id = 6, Name = "Ram3", Salary = 70003 },

               new Employee { Id = 7, Name = "Ram4", Salary = 70004 },

                new Employee { Id = 8, Name = "Ram5", Salary = 70005 },

                 new Employee { Id = 9, Name = "Ram6", Salary = 70006 },

                  new Employee { Id = 10, Name = "Ram7", Salary = 70007 },

                   new Employee { Id = 11, Name = "Ram8", Salary = 70008 },

                    new Employee { Id = 12, Name = "Ram9", Salary = 70009 }};


            int totalPage = 4;

            do {

                Console.WriteLine("Eneter Page Number");

                if (int.TryParse(Console.ReadLine(), out int pageNumber))

                {

                    var result = objEmp.Skip((pageNumber - 1) * totalPage).Take(totalPage);

                    foreach (var item in result)

                    {

                        Console.WriteLine($"Id= {item.Id} and Name= {item.Name}");

                    }

                }

                else 

                {

                    Console.WriteLine("Eneter Valid Page");

                }


            } while (true);

==========================================================================================================================

Example 1:


Input: s = "anagram", t = "nagaram"

Output: true

Example 2:


Input: s = "rat", t = "car"

Output: false


            char[] ca = s.ToCharArray();

            char[] ct = t.ToCharArray();

            Array.Sort(ca);

            Array.Sort(ct);

            String ss = new String(ca);

            String st = new String(ct);

            return ss.Equals(st);


============================================================================================================================

===========================================Array Sorting====================================================================

 var arr = new int[] { 3, 5, 7, 1, 4 };

            int temp = 0;

            for (int i = 0; i < arr.Length-1; i++)

            {

                for (int j = i+1; j < arr.Length; j++)

                {

                    if (arr[i] > arr[j])

                    {

                        temp = arr[i];

                        arr[i] = arr[j];

                        arr[j] = temp;

                    }

                }

            }

            foreach (var item in arr)

            {

                Console.WriteLine(item);

            }

===============================================================================================================================

=========================================================Binary to Decimal===============================

int num, binVal, decVal = 0, baseVal = 1, rem;

            num = 101;

            binVal = num;

            while (num > 0)

            {

                rem = num % 10;

                decVal = decVal + rem * baseVal;

                num = num / 10;

                baseVal = baseVal * 2;

            }

            Console.Write("Binary Number: " + binVal);

            Console.Write("\nDecimal: " + decVal);

================================================================================================================================

==========================================================Output==========================================

 public class ABC

    {

        public ABC() 

        {

            Console.WriteLine("Constructor ABC");

        }

        public void abc() 

        {

            Console.WriteLine("Method ABC");

        }

    }

    public class XYZ :ABC

    {

        public XYZ()

        {

            Console.WriteLine("Constructor XYZ");

        }

        public void abc()

        {

            Console.WriteLine("Method XYZ");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            XYZ a = new XYZ();

            ABC b = a;

            a.abc();

           

        }

    }


Output : Constructor ABC

         Constructor XYZ

Method XYZ

 

==============================================================================================================================

================================================================Output

public class ABC

    {

        public void abc(int x) 

        {

            Console.WriteLine("Method ABC");

        }

    }

    public class XYZ :ABC

    {

        

        public void abc(double y)

        {

            Console.WriteLine("Method XYZ");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            XYZ x = new XYZ();

            x.abc(2);

            Console.ReadLine();

        }

    }


Output : XYZ


===============================================================================================================================

========================================================================Output==============================

 static void Main(string[] args)

        {

            int someValue;

            Method2(out someValue);

            Method1(ref someValue);

            Method(someValue);


            Console.WriteLine(someValue); // 1

            Console.WriteLine(someValue); // 1

            Console.WriteLine(someValue); // 1

            Console.ReadLine();

        }

        static void Method(int val) 

        {

            val = 0;

        }

        static void Method1(ref int val)

        {

            val = 1;

        }

        static void Method2(out int val)

        {

            val = 2;

        }

         

================================================================================================================================

=============================================================Output==============================

int val=15;


            Console.WriteLine(val++);  //15

            Console.WriteLine(++val);  //17

            Console.WriteLine(val);    //17

            Console.WriteLine("*********");

            Console.WriteLine(val--);  //17

            Console.WriteLine(--val);  //15

            Console.WriteLine(val);    //15

          

            Console.WriteLine(++val);  //16

            Console.WriteLine(val++);  //16

            Console.WriteLine(val);    //17

            Console.WriteLine("*********");

            Console.WriteLine(--val);  //16

            Console.WriteLine(val--);  //16

            Console.WriteLine(val);    //15

===============================================================================================================================

============================================================Output===============================

public class XX

    {

        public XX()

        {

            

            Console.WriteLine("Constructor XX");

        }

        public XX(int x)

        {

            Console.WriteLine("Constructor XX1");

        }

    }

    public class YY:XX

    {


        public YY():base(1)

        {

           

            Console.WriteLine("Constructor YY");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            YY y = new YY();

            Console.ReadLine();   // Output: Constructor XX1

                                 Constructor YY

        }

}


===============================================================================================================================

======================================================Output=========================================================


    public class ABC 

    {

        public void show(int x) 

        {

            Console.WriteLine("From ABC");

        }

    }

    public class XYZ : ABC

    {

        public void show(double y)

        {

            Console.WriteLine("From XYZ");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            XYZ b = new XYZ();

            b.show(2);    //  Output : From XYZ

            Console.ReadLine();

        }}

=================================================================================================================================

======================================================Output===================

   public class P 

    { 

    }

   public class Q:P 

    {

    }

    public class ABC 

    {

        public void show(Q q) 

        {

            Console.WriteLine("From ABC");

        }

    }

    public class XYZ : ABC

    {

        public void show(P p)

        {

            Console.WriteLine("From XYZ");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            XYZ b = new XYZ();

            b.show(new Q());    //  Output : From XYZ

            Console.ReadLine();

        }}

================================================================================================================================

==========================================================Output=====================================================

 public class P 

    { 

    }

   public class Q:P 

    {

    }

    public class ABC 

    {

        public ABC(int y) 

        {

            Console.WriteLine("Constructor ABC");

        }

        public void show(Q q) 

        {

            Console.WriteLine("From ABC");

        }

    }

    public class XYZ : ABC

    {

        public XYZ(int x):base(2)

        {

            Console.WriteLine("Constructor XYZ");

        }

        public void show(P p)

        {

            Console.WriteLine("From XYZ");

        }

    }

    class Program

    {

        static void Main(string[] args)

        {

            XYZ b = new XYZ(1);

            b.show(new Q());

            Console.ReadLine();

        }

 

output: Constructor ABC

        Constructor XYZ

        From XYZ


============================ 

1. Hello world ->   how many times all chars

2.Delete Duplicate chars from line 

https://dotnettutorials.net/lesson/how-to-remove-duplicate-characters-from-a-string-in-chsrap/

3. Remove 1st and Last/nth  char String 

2.  Extension method implement 

3.  Duplicate char finding from string

4.  Sorting bubble (LINQ -> 

5.  Array from Dublipcate  number

6.  Reverse  string/palindrome

7.  Nunit Test case Visual studio API 

8. Readonly in the declaration and only can assign in constructor only , example : injectionDi

9.Memory leak c#

10. stack n Heap memory management  c#

11.Access modifier (public private internal , others combinations) 

12. Collections , dictionary , dictionary to array conversion 

https://www.techiedelight.com/convert-dictionary-values-to-an-array-in-csharp/

13. lemda expressions n func n actions

14. bubble sort -> complexity 

14. Tupple when use tupple 

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/value-tuples

15 display * as triangle 

https://eddiejackson.net/wp/?p=21062

16 

9. leftjoin Linq

var QSOuterJoin = from emp in Employee.GetAllEmployees()

join add in Address.GetAddress()

on emp.AddressId equals add.ID

into EmployeeAddressGroup

from address in EmployeeAddressGroup.DefaultIfEmpty()

select new {emp, address };

10. RightJoin Linq :

//Defered Query Execution

var rightJoin = from skill in skills

join deve in developers

on skill.Id equals deve.SkillID into joinDeptEmp

from employee in joinDeptEmp.DefaultIfEmpty()

select new {

EmployeeName = employee != null ? employee.Name : null,

SkillName = skill.Name

};

11.The basic difference between a Deferred execution vs Immediate execution

is that Deferred execution of queries produce a sequence of values, 

whereas Immediate execution of queries return a singleton value and is executed immediately. 

Examples are using Count(), Average(), Max() etc.

The basic difference between a Deferred execution vs Immediate execution

https://www.dotnetcurry.com/linq/750/deferred-vs-immediate-query-execution-linq#:~:text=The%20basic%20difference%20between%20a,Average()%2C%20Max()%20etc.

pelingrom

12.

Revser of line 


using System;


public class HelloWorld

{

    public static void Main(string[] args)

    {

        var inPutString = Console.ReadLine ();

        var reveseStriing = "";

        foreach(var item in inPutString.Split())

        {

         // item.Reverse()

       //  char[] charArray = item.ToCharArray();  

         //Array.Reverse(charArray);  

        // Console.WriteLine(new string(charArray));

       

       //  reveseStriing +=" " +new string(charArray);

        reveseStriing +=" " +RevserCustom(item);

        }

        Console.WriteLine (reveseStriing.Trim());


       // Console.WriteLine (inPutString);

    }

    

    public static string RevserCustom(string inputString)

    {

        var charrArray = inputString.ToCharArray();

        var length = charrArray.Length;

        var reverseArray = new char[charrArray.Length];

        

        for(int i=0;i < length ; i++)

        {

            reverseArray[length-i-1] = charrArray[i];

        }

        // Console.WriteLine (new string(reverseArray));

       return new string(reverseArray);

    }

    

    

}

Q2. PelineDrome


// Online C# Editor for free

// Write, Edit and Run your C# code using C# Online Compiler


using System;


public class HelloWorld

{

    public static void Main(string[] args)

    {

       var inPutString = Console.ReadLine();

       var charArray = inPutString.ToCharArray();

       var ispalindrome = true;

       for(int i=0 ;i<charArray.Length ; i++ )

       {

           if(charArray[i]!=charArray[charArray.Length-i-1])

           {

               ispalindrome = false;

               break;

           }

          continue;

       }

       

    if(ispalindrome)

    Console.WriteLine ("Pelindrom");

    else

    Console.WriteLine ("Not Palindrome");


    }

}


Q3 : Reverse 

Reverse String 

// Online C# Editor for free

// Write, Edit and Run your C# code using C# Online Compiler


using System;


public class HelloWorld

{

    public static void Main(string[] args)

    {

       var inPutString = Console.ReadLine();

       var charArray = inPutString.ToCharArray();

       var ispalindrome = true;

       var length = charArray.Length;

       var reverseCharArray = new char[length]; 

       for(int i=0 ;i<length ; i++ )

       {

           if(charArray[i]!=charArray[length-i-1])

           {

               ispalindrome = false;

               break;

           }

           reverseCharArray[i] = charArray[length-i-1];

          continue;

       }

       

    if(ispalindrome)

    {

    Console.WriteLine("Pelindrom and Reseverse string is " + 

    new string(reverseCharArray));

    }

    else

    Console.WriteLine ("Not Palindrome");


    }

}

------------------------Reverse array and show with spaces

using System;


namespace HelloWorld

{

  class Program

  {

    static void Main(string[] args)

    {

      char[] charArray = new char[] { 'a', 'b', 'c' };

      char[] reverseArray = new char[charArray.Length] ;

      for (int i = 0; i < charArray.Length; i++)

      {

          //reverseArray[charArray.Length-i-1] = charArray[i];

          var result = new string('\t', i)+                 charArray[charArray.Length-i-1] ;

          Console.WriteLine(result);

      }

      Console.ReadKey(); 

    }

  }

}

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

How to Implement Bubble Sort in C#?

We are going to define an integer array property NumArray, that we are going to use to implement the bubble sort algorithm:

public int[]? NumArray { get; set; }

Let’s create a method that implements the bubble sort algorithm in C# that sorts the values in the NumArray property:

public int[] SortArray()
{
var n = NumArray.Length;
for (int i = 0; i < n - 1; i++)
for (int j = 0; j < n - i - 1; j++)
if (NumArray[j] > NumArray[j + 1])
{
var tempVar = NumArray[j];
NumArray[j] = NumArray[j + 1];
NumArray[j + 1] = tempVar;
}
return NumArray;
}                                                                 
var array = new int[] { 73, 57, 49, 99, 133, 20, 1 };
var expected = new int[] { 1, 20, 49, 57, 73, 99, 133 };
var sortFunction = new Bubble();
sortFunction.NumArray = array;
var sortedArray = sortFunction.SortArray();
Assert.IsNotNull(sortedArray);
CollectionAssert.AreEqual(sortedArray, expected);


Time and Space Complexity

The space complexity of the bubble sort algorithm is O(1) because it requires a single additional space that holds the temporary value we are using to swap the elements. 

Nested loops have detrimental effects on how algorithms perform as the size of the array grows. The bubble sort algorithm has a time complexity of O(N²) as it has two nested loops. Let’s analyze how the algorithm performs in different levels of complexity.

Q.---------CIRCLE with *

using System;


class Program

{

    static void Main()

    {

        int radius = 5; // Radius of the circle

        int centerX = 10; // X-coordinate of the circle's center

        int centerY = 10; // Y-coordinate of the circle's center


        DrawCircle(radius, centerX, centerY);

    }


    static void DrawCircle(int radius, int centerX, int centerY)

    {

        int diameter = radius * 2;


        for (int y = 0; y <= diameter; y++)

        {

            for (int x = 0; x <= diameter; x++)

            {

                int distanceX = Math.Abs(x - radius);

                int distanceY = Math.Abs(y - radius);

                double distance = Math.Sqrt(distanceX * distanceX + distanceY * distanceY);


                if (distance > radius - 0.5 && distance < radius + 0.5)

                {

                    Console.Write("*");

                }

                else

                {

                    Console.Write(" ");

                }

            }

            Console.WriteLine();

        }

    }

}

Q. DIAMOND program

using System;

class DiamondPattern
{
    static void Main()
    {
        int n, i, j, space, k;

        
        n = 13;

        space = n / 2;

        // Upper half of the diamond
        for (i = 1; i <= n; i += 2)
        {
            for (k = 0; k < space; k++)
            {
                Console.Write(" ");
            }

            for (j = 0; j < i; j++)
            {
                Console.Write("*");
            }

            Console.WriteLine();
            space--;
        }

        space = 1;

        // Lower half of the diamond
        for (i = n - 2; i >= 1; i -= 2)
        {
            for (k = 0; k < space; k++)
            {
                Console.Write(" ");
            }

            for (j = 0; j < i; j++)
            {
                Console.Write("*");
            }

            Console.WriteLine();
            space++;
        }
    }
}


===============================   QUESTIONS   SQLLLL =============


http://venkateswarlu.co.in/Interview-Questions/ssrs_interview_questions_1.aspx
https://www.c-sharpcorner.com/blogs/differences-between-sql-server-2005-2008-2008r2-2012

1. What is Database ?
   A database is a collection of information that is organized so
   that it can easily be accessed, managed, and updated. 

2. What is Normalization ?
  
    Normalization is the process of efficiently organizing data 
in a database. There are two goals of the normalization 
process: eliminating redundant data (for example, storing the 
same data in more than one table) and ensuring data dependencies 
make sense (only storing related data in a table). Both of these
are worthy goals as they reduce the amount of space a database
consumes and ensure that data is logically stored. 
Benifits:
 
a.Eliminate data redundancy
b.Improve performance
c.Query optimization
d.Faster update due to less number of columns in one table
e.Index improvement

1. First Normal Form (1NF)
    Eliminate duplicative columns from the same table.
2. Second Normal Form (2NF)Second normal form (2NF) further
      addresses the concept of removing duplicative data:
·         Meet all the requirements of the first normal form.

3. SQL Tuning or SQL Optimization ?
   
    Sql Statements are used to retrieve data from the database. 
We can get same results by writing different sql queries. But 
use of the best query is important when performance is considered.

4.  What is Foreign key ?

    A foreign key is a column in a relational database table that 
provides a link between data in two tables.

5. Difference between datareader and dataset?

    Datareader:
    DataReader is used to read the data from database and it is a 
read and forward only connection oriented architecture during 
fetch the data from database.
DataSet:
    DataSet is a disconnected orient architecture that means there
is no need of active connections during work with datasets and
it is a collection of DataTables and relations between tables.
It is used to hold multiple tables with data.
DataAdapter:
    DataAdapter will acts as a Bridge between DataSet and database.
This dataadapter object is used to read the data from database
and bind that data to dataset. Dataadapter is a disconnected 
oriented architecture. 

6. What is Schemas ?

   It is Collection of object,such as tables,views and sequences.    
   
7. What is Index ?
   
   Index are used to improve query retrival speed.
   When to create an Index:
   a.A column contain a wide range of Value.
   b.A column contain a large number of Null Value.
   c.Where clause or Join Condition.
   
   Ex- Create Index raj on emploee(Last_Name);
   
8.Benefits of Index in Table ?
   
   An index helps speed up SELECT queries and WHERE clauses, 
   but it slows down data input, with UPDATE and INSERT statements.
   Indexes can be created or dropped with no effect on the data.    

9. Find 2nd,3rd highest salary in sql server.

    select min(salary) from Emp 
where salary in(select distinct top n salary from emp 
order by salary desc)
              Or
    SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP 2 salary
FROM employee 
ORDER BY salary DESC) a
ORDER BY salary
100,200,300
 
or 
SELECT * FROM (  
SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary  
FROM Employees )  
     AS foo  
     WHERE rownumber = 4  

9.  Find 2nd Lowest salary in sql server
   
     Select TOP 1 Salary as '2rd Lowest Salary' 
   from (SELECT DISTINCT TOP 2 Salary from Emp ORDER BY Salary ASC) 
    a ORDER BY Salary DESC   

  
var query =db.Employees
                     .OrderByDescending(e => e.Salary)
                     .Skip(1)
                     .First();  
 
10.Differences between Clustered and Non-Clustered Indexes ?   
   Ans:
       Index are used to improve query retrival speed.
   Indexing  is way to sort and search records in the table. 
Clustered Index:
----------
    1.In a table only one clustered index is possible.
2.Clustered Index sequential order .
3.primary key default clustered index
4.A Clustered Index always has Index Id of 1
5.Clustered Index doen't require any additional storage.
6. Faster
    Non-Clustered Indexes
    ---------------------
    1.unique key default non clustered index.
2.Prior to SQL Server 2008 only 249 Nonclustered Indexes can be 
  created. With SQL Server 2008 and above 999 Nonclustered Indexes 
  can be created.
3.Nonclustered Indexes have Index Id > 1  
      Syntax:
    create Nonclustered index NClx_Idx_SNo on Index_Table(Sno)
    4.Nonclustered Indexes require additional space as it is store 
  separately from the table.
    5.Slower

11. Differences between Stored Procedures and Functions ?
    
    Ans:
1.Procedure can return zero or n values whereas 
  function can return one value which is mandatory.
2.Procedures can have input/output parameters 
  whereas functions can have only input parameters.
3.Procedure allows select as well as DML statement 
  whereas function allows only select statement.
4.procedures cannot be called from function 
  whereas Functions can be called from procedure.
    5.Procedure is Pre-Compile While Function Compile Every Time.
12. Difference between Primary key and Unique Key in Sql ?
    
    Ans:
    Primary key
--------------
    a.Primary key cannot have a NULL value.
b.Each table can have only single primary key.
    c.Primary key is implemented as indexes on the table. By 
  default this index is clustered index.
    d.Primary key can be related with another table's as a 
  Foreign Key.
    e.We can generated ID automatically with the help of Auto
      Increment field. Primary key supports Auto Increment value. 
    Unique Constraint
    ----------------
    a.Unique Constraint may have a NULL value.
    b.Each table can have more than one Unique Constraint.
    c.Unique Constraint is also implemented as indexes on the
      table. By default this index is Non-clustered index.
    d.Unique Constraint can not be related with another 
table's as a Foreign Key.
    e.Unique Constraint doesn't supports Auto Increment value.
Define Primary and Unique Key
    Create table Student
   (
    StuId int primary key, ---- Define Primary Key
    StuName varchar(50) Not Null,
    ContactNo int Unique --- Define Unique Key
    )   
    

13.What is the difference between DELETE and TRUNCATE in SQL 
   Server ?
   Ans:
    DELETE 
1. DELETE is a DML Command. 
2. DELETE statement is executed using a row lock, each row in the
   table is locked for deletion. 
3. We can specify filters in where clause 
4. It deletes specified data if where condition exists. 
5. Delete activates a trigger because the operation are logged 
   individually. 
6. Slower than truncate because, it keeps logs. 
7. Rollback is possible. 

TRUNC ATE 
1. TRUNCATE is a DDL command. 
2. TRUNCATE TABLE always locks the table and page but not each 
   row. 
3. Cannot use Where Condition. 
4. It Removes all the data. 
5. TRUNCATE TABLE cannot activate a trigger because the 
   operation does not log individual row deletions. 
6. Faster in performance wise, because it doesn't keep any 
   logs. 
7. Rollback is not possible. 

   DELETE and TRUNCATE both can be rolled back when used with 
   TRANSACTION.    

14.Difference between the Stored Procedures and Trigger ?
   Ans:
   
    The stored procedures used for performing user specified task
The triggers normally used for auditing work. It can be used 
to trace the activities of the table events.
 
The stored procedures can have the input and output parameters.
    The triggers cannot have any parameters.
 
The stored procedure can be run independently
The triggers executes based on table events

The stored procedures cannot call the triggers directly. 
    The triggers can call stored procedures

15. Difference between WHERE Clause and HAVING ?

    Where Clause: 
1.Where Clause can be used other than Select statement also 
2.Where applies to each and single row 
3.In where clause the data that fetched from memory according 
to condition 
4.Where is used before GROUP BY clause 
Ex:Using Condition for the data in the memory. 

Having Clause: 
1.Having is used only with the SELECT statement. 
2.Having applies to summarized rows (summarized with GROUP BY) 
3.In having the completed data firstly fetched and then 
separated according to condition. 
4.HAVING clause is used to impose condition on GROUP Function 
and is used after GROUP BY clause in the query 
Ex: when using the avg function and then filter the data like 
ava(Sales)>0 

Summary: 
Having works like Where clause with out Group By Clause
16.

   #table refers to a local (visible to only the user who created 
   it) temporary table.

  ##table refers to a global (visible to all users) temporary table.

   @variableName refers to a variable which can hold values 
   depending on its type.

17. Which Department Highset Salary ?

    SELECT D.DEPARTMENT_NAME, E.SALARY
    FROM DEPARTMENTS D, EMPLOYEES E
    WHERE E.DEPARTMENT_ID= D.DEPARTMENT_ID
    AND SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES); 
**. Department wise highest salary ?

    SELECT EmployeeId,FirstName,Salary,Department,Gender 
FROM Employee
WHERE Salary IN
(SELECT max(Salary) AS salary
From Employee
GROUP BY Department)

18. Highest Number of Department Name ?

    SELECT MAX(DEPARTMENT_NAME)"DEPARTMENT" FROM 
    (SELECT D.DEPARTMENT_NAME,COUNT(FIRST_NAME)FROM EMPLOYEES E,
     DEPARTMENTS D WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID GROUP BY 
     DEPARTMENT_NAME )
19. SQL query to find duplicate rows.
    
select ShopName,count(ShopName) from Sales1
    Group by ShopName Having (count(ShopName) > 1)
or 
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

20.SQL query to delete All duplicate rows.     
    
    delete from Sales1 where                
    ShopName in(select ShopName from Sales1 
group by ShopName having count(*) >1)
# And Below query Left one duplicate Value.
with EmployeeCTE as
(
    select *,RN=ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) from Employee
)
 
delete from EmployeeCTE where RN >1
         
or
 
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
     FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 
select * from Employee

21.What is a Trigger

    A trigger is a special kind of a store procedure that executes 
in response to certain action on the table like insertion, 
deletion or updation of data. 

22.What is Transaction ?

    It is a group of command that change the data store in database.
   Example:
      create pocedure spupdatetran
       as 
         begin
          begin try
           begin Transaction
            update employee set salary=30000 where deptid=1
            commit Transaction
           end try
          begin catch
            rollback Transaction
          end catch
end 
23.How to Implement User Define Function(UDF) in Sql Server ?

   Step 1:
          create function RectArea(@RectLength decimal(4,1),
  @RectWith decimal(4,1))
             returns decimal(8,2)
             as
             begin
          return (@RectLength * @RectWith )
        end  

   Step 2:create table Papers(PaperID int primary key,PaperLength 
          decimal(4,1),PaperWidth decimal(4,1),
  PaperArea AS
                 ( 
                dbo.RectArea(PaperLength,PaperWidth)
  )
               )

24.What command is used to create a table by copying
   the structure of another table?
       
Sql Server:
select * into Newtable from existingtable where 1=2;
 
25. How to copy all Data into existin table to New Table ?
     
SELECT *INTO Newtable FROM existingtable;
 
26.What is an integrity constraint ? 

   An integrity constraint allows the definition of certain 
   restrictions, at the table level, on the data that is entered 
   into a table.
 

27.What is Use of LINQ ?

   it is a uniform programing model for any kind of data access.  
   
   It stands for Language Integrated Query. LINQ is collection of
   standard query operators that provides the query facilities 
   into .NET framework language like C# , VB.NET.

28.How to recover the deleted records from SQL SERVER ?
   http://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
  We have two methods to do it.
  a.Through SQL SERVER LOG
  b.Through Backup :
  BACKUP DATABASE raj
  TO DISK = 'c:\RAJ_FULL_BACKUP.bak'
  
  
 29. Find the each Department wise Employee ?
 
   select DepartmentName,COUNT(*) as EmployeCount from Employees e join Departments d on e.DepartmentID=d.DepartmentID 
   group by DepartmentName   
 
 30. Find the Number of string repeated ?
     
Select * From Employee where Len(Username)-Len(Replace(Username,'a',''))=1 or 2 or 3 etc
 
 31. Specific Department value find out. ?

     
     select d.Dname,d.Org from Emp e join Dep d on e.Did=d.Did and d.Did=2 
 
 32. Employee wise Manager.?
 
    select distinct e.Ename as Employee, m.mgr as reports_to, m.Ename as Manager
from EMP1 e
inner join EMP1 m on e.mgr = m.EmpID;
or

    select e1.ename Emp,e2.eName Mgr from EMP1 e1
left join EMP1 e2
on e1.mgr = e2.empid

or

select e.ename as Employee, m.ename as Manager
from EMP1 e, EMP1 m
where e.mgr = m.EmpID
**. Get organization hierarchy
Declare @ID int ;
Set @ID = 5;

WITH EmployeeCTE AS
(
     Select EmployeeId, EmployeeName, ManagerID
     From tblEmployees
     Where EmployeeId = @ID
    
     UNION ALL
    
     Select tblEmployees.EmployeeId , tblEmployees.EmployeeName,
             tblEmployees.ManagerID
     From tblEmployees
     JOIN EmployeeCTE
     ON tblEmployees.EmployeeId = EmployeeCTE.ManagerID
)

    Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID=e2.EmployeeID

    https://www.youtube.com/watch?v=Kd3HTph0Mds&list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR&index=2
33. How to Debug SP ?
    
    Declare @Id int
    Set @Id=10
    Execute Sp_Employee @ID
    Print 'Done'
34. What is Composite key ?

35. Why Only One Clustere Index in Table ?

36. what is the difference between @, # and ##?

    #table refers to a local (visible to only the user who created it) temporary table.

    ##table refers to a global (visible to all users) temporary table.

    @variableName refers to a variable which can hold values depending on its type.
37. How to use RANK() and DENSE_RANK() function ?

    RANK=It's Skips Sequence Number.
DENSE_RANK=It's not Skips Sequence Number.
    select ID,Name,Salary,RANK() OVER(ORDER BY Salary DESC) as [Rank],
    DENSE_RANK() OVER(ORDER BY Salary DESC) as [Rank] from Employee
https://www.youtube.com/watch?v=cvrwOoGwgz8

38. Trigger ?

    CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)


CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'
GO


insert into Employee_Test values('Chris',1500);

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'
GO


CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'
GO

38. ACID(Automicity,Cosistency,Isolation,Durability) ?

    A-Either transaction is completed or nothing has been done.
C-Through which valid data gets into database.
  E.q- given a paricular condition Salary >5000(Valuse shoud be given).
    I-if you are start one transaction if that transaction is not completed then other process wont be able to see the data 
      until the transaction is completed.
    D-Once the transaction is committed, even if the system crashes ,there should be a way to get back the data.   
39. how to find recent value inserted in table in sql ?

    SELECT SCOPE_IDENTITY() -> Same Session and Same Scope

    SELECT @@IDENTITY -> Same Session and across Any Scope

    select IDENT_CURRENT('tableName') -> Any Session and Any Scope
40. Difference Between After Trigger vs Instead of Trigger in SQL Server ?

    After Trigger: These kinds of triggers fire after the execution of an action query that can be either DDL statements like 
Create, Alter and Drop or DML statements like Insert, Update and Delete.
Instead of Trigger: These kinds of triggers fire before the execution of an action query that can only be DML statements 
like Insert, Update and Delete but after the execution of that query. The table data will not be affected, in other words 
if you want to insert or update the data of the table then you need to write it in the trigger using "inserted" or "deleted"
virtual tables.
41. how to Improve SQL Query Performance ?

    http://www.winwire.com/25-tips-to-improve-sql-query-performance/
42. How to find out which index is missing ?

    sys.dm_db_missing_index_details - Returns detailed information about a missing index
    sys.dm_db_missing_index_group_stats  - Returns summary information about missing index groups
    sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
    sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for 
an index. This is a function and requires the index_handle to be passed.
43. What is Use of PIVOT Key word ?

    We use pivot queries when we need to transform data from row-level to columnar data.

SELECT jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt
44. How to Insert Bulk Data ?

    https://www.codeproject.com/Tips/775961/Import-CSV-or-txt-File-Into-SQL-Server-Using-Bulk
https://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server
http://www.c-sharpcorner.com/UploadFile/0c1bb2/insert-bulk-records-into-database-using-Asp-Net-C-Sharp/

45. Temp Variable va Temp Table ?
https://www.c-sharpcorner.com/UploadFile/f0b2ed/temporary-table-vs-temporary-variable-in-sql-server/



46. @ and @@ ?

47. How to Auto Execute Stored Procedure on Sql Server ?
    https://stackoverflow.com/questions/12158158/how-to-automatically-run-a-stored-procedure-on-scheduler-basis
48. Type of Constraint in Sql Server ?

    NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

49. Relationship in Sql Server ?

50. How to Join in Linq Q ?

51. Magic Table ?
    https://www.c-sharpcorner.com/UploadFile/rohatash/magic-tables-in-sql-server-2012/

52. How to insert bulk data in Sql Server ?
    https://www.c-sharpcorner.com/blogs/bulk-insert-in-sql-server-from-c-sharp
53. Types of View ?
    https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-views


=========================================