Write a query to delete duplicate rows in a table (SQL)

Delete duplicate records from the emp table

The Row_Number() built in function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
So when for a partition by empID, ROW_NUMBER() function will return the numbered rows grouped by empID.
The result set of the flowing query shows the data returned by row_number() function :

SELECT empid, ROW_NUMBER() over (PARTITION BY empid ORDER BY empid) as rowNum
 FROM [MySampleDB].[dbo].[emp]

The result set looks like :

To delete only duplicate records from the table, we have to delete only those rows for which the rowNumber is more than 1.

The following query will remove duplicates from the table :

WITH
temp_table AS
(SELECT empid, ROW_NUMBER() over (PARTITION BY empid ORDER BY empid) as rowNum
 FROM [MySampleDB].[dbo].[emp]
 )
DELETE FROM temp_table
WHERE rowNum > 1

Find the longest palindrome in a given string (C#)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string str = "paliniladrome";
            string longestPalindrome = GetMaxPalindromeString(str);
            Console.WriteLine(longestPalindrome);
            Console.ReadKey();
          
        }
        public static string GetMaxPalindromeString(string testingString)
        {
            int stringLength = testingString.Length;
            int maxPalindromeStringLength = 0;
            int maxPalindromeStringStartIndex = 0;
            for (int i = 0; i < stringLength; i++)
            {
                int currentCharIndex = i;
                for (int lastCharIndex = stringLength - 1; lastCharIndex > currentCharIndex; lastCharIndex--)
                {
                    if (lastCharIndex - currentCharIndex + 1 < maxPalindromeStringLength)
                    {
                        break;
                    }
                    bool isPalindrome = true;
                    if (testingString[currentCharIndex] != testingString[lastCharIndex])
                    {
                        continue;
                    }
                    else
                    {
                        int matchedCharIndexFromEnd = lastCharIndex - 1;
                        for (int nextCharIndex = currentCharIndex + 1; nextCharIndex < matchedCharIndexFromEnd; nextCharIndex++)
                        {
                            if (testingString[nextCharIndex] != testingString[matchedCharIndexFromEnd])
                            {
                                isPalindrome = false;
                                break;
                            }
                            matchedCharIndexFromEnd--;
                        }
                    }
                    if (isPalindrome)
                    {
                        if (lastCharIndex + 1 - currentCharIndex > maxPalindromeStringLength)
                        {
                            maxPalindromeStringStartIndex = currentCharIndex;
                            maxPalindromeStringLength = lastCharIndex + 1 - currentCharIndex;
                        }
                        break;
                    }
                }
            }
            if (maxPalindromeStringLength > 0)
            {
                return testingString.Substring(maxPalindromeStringStartIndex, maxPalindromeStringLength);
            }
            return null;
        }
    }
}
  

Find the factorial on a whole number (C#)

Calculating factorials opens the doors to a discussion about stack overflow (if done recursively) and integer overflow (if the param is too large) as well as discussions around how to handle and catch errors.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Factorial
{
    class Program
    {
        static void Main(string[] args)
        {      
              //Find the factorial of a given integer
            int n = 6;
            int factor = RecursiveFactorial(n);
            Console.WriteLine("Recursive factorial result : {0}",factor);
            int factorInterative = IterativeFactorial(n);
            Console.WriteLine("Iterative factorial result : {0}", factorInterative);
            Console.ReadKey();
    
        }
        public static int RecursiveFactorial(int n)
        {
            if (n <= 1)
                return 1;
            else if (n>=1)
            {
                try
                {
                    return n * RecursiveFactorial(--n);
                   
                }
                catch (Exception ex)
                {

                }
            }
           return 1;
        }
        public static int IterativeFactorial(int n)
        {
            if (n <= 0)
                return 0;
            int factor = 1;
            try
            {
                for (int i = 1; i <= n; i++)
                {
                    factor *= i;
                }
                return factor;
            }
            catch (Exception ex)
            {

            }
            return 1;
        }
    }
}

How to find records for the last 2 days from a database? (SQL)

//Find records for the last 2 days

SELECT * FROM tableName
WHERE LogDateTime >= DATEADD (DD, -2, GETDATE());

//Find records for the last 2 hours

SELECT * FROM tableName
WHERE LogDateTime >= DATEADD (HOUR, -2, GETDATE());

Finding the 3rd highest salary of an employee. (SQL)

Question

From the employee table, find the employee who earns the 3rd highest salary.

Solution

SELECT TOP 1 * FROM dbo.emp E
WHERE E.Salary IN
(SELECT TOP 3 Salary FROM dbo.emp
ORDER BY Salary desc)
ORDER BY E.Salary asc


 

Cricket Match score keeping (C#)

Question

Given a list of 30 whole numbers, each number representing the runs scored by a player in cricket per ball, write a C# program to find the following:

1. Run rate, which is the runs per over. 6 balls make an over.
2. Assuming that every 2 overs was played be a new batsman, calculate the batting average for each batsman. Batting average is the number of runs scored per ball played by that batsman.

Solution

//Calculate the run rate
public static double ranRateCalculator(List runs)
{
int totalRuns = 0;
foreach (int i in runs)
{
totalRuns += i;
}

//Total overs are the number of times the runs are entered in the list divided by 6 since 6 balls make an over.
int totalOvers = runs.Count / 6;

//runRate is the total runs per over
double ranRate = (double)totalRuns / totalOvers;
return runRate;
}

//Calculate the batting average for each batsman
 public static List ranRateCalculator(List runs)
 {
//Total overs are the number of times the runs are entered in the list divided by 6 since 6 balls make an over.
int totalOvers = (runs.Count)/6;
//total number of batsman are the total number of overs divided by 2 since the batsman change every 2 overs. If the total over is not exactly divisible by 2, then the number of batsman increase by one to play the remaining overs.
 int batsmanNum = totalOvers / 2;
 if (totalOvers%2 != 0)
 batsmanNum = batsmanNum + 1;
//Store the average scores per batsman in a list
 List avgPerBatsman = new List();
 for (int I-0; i < batsmanNum; i++)
 {
// find the runs scored by the first batsman, the second etc.
 var totalRunsPerBatsman = runs.Skip(i*12).Take(12);
 int total =0;
 foreach (var n in totalRunsPerBatsman)
 {
total += n;
}
double avg = (double) total / 12;

avgPerBatsman.Add(avg);
}

}
return avgPerBatsman;

//Calling the functions
public void Main()
{
List runs = new List{3,2,0,4,6,2,1,0,6,5,4,3,2,1,4,5,3,2,1,0,0,5,6,3,4,3,2,0,4,6};
// Finding the total run rate
 double runRate = runRateCalculator(runs);
 Console.Write("the run rate is {0}", runRate);
 Console.ReadKey();
//Finding the run rate for each batsman
 List avg = batsmanAvg(runs);
 foreach (var v in avg)
 {
 Console.WriteLine("The batsman average score are : {0}", v);
 }
 Console.ReadKey();
 }
 

Finding Fibonicci sequence (C#)

Question

Each new term in the Fibonacci sequence is generated by adding the previous two terms. By starting with 1 and 2, the first 10 terms will be: 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, ... Find the sum of all the even-valued terms in the sequence which do not exceed four million.

Solution

//The method that returns a list of Fibonacci number sequence up to an input number.

public static List Fibonacci(int n)
{
//The first 2 numbers are given and stored in long variables. Add these numbers to the list
long a =1;
long b = 2;

List <int> sequence = new List<int>();

sequence.Add(a);
sequence.Add(b);

int i= sequence.Count;

if (n > 0)
{
while (i< n)
{
long temp = a;
a=b;
b = temp + a;
sequence.Add(b);
i ++ ;
}
}
return sequence;
}

//Calling the method to generate a list of Fibonacci number up to 4000000
Static void Main()
{
int n=4000000;

List list = Fibonacci(n);

//Iterate through the elements of the list and if it is a positive number, keep the sum of the numbers in an long variable and return the sum

int sum =0;

foreach (var v in list)
{
if (v%2 == 0)
{
sum += v;
}
}
Console.WriteLine(sum);
Console.ReadKey();
}