Friday, May 17, 2019

C# : Data Structure : Queue


Queue Implementation in C#

class Queue
    {
        int[] item;
        int rear;
        int front;
        readonly int capacity;

        public Queue()
        {
            capacity = 100;
            rear = -1;
            front = 0;
            item = new int[capacity];
        }
        public Queue( int capacity)
        {
            this.capacity = capacity;
            rear = -1;
            front = 0;
           
            item = new int[capacity];
        }

        public void Enqueue(int _item)
        {
            if (rear == capacity - 1)
            {
                Console.WriteLine("Queue is full");
                return;
            }
            item[rear + 1] = _item;
            rear++;
        }
        public int Dequeue()
        {
            if (front - 1==rear  )
            {
                Console.WriteLine("Queue is empty");
                return -1;
            }
            int itemtoReturn = item[front];
            front = front + 1;
            return itemtoReturn;
        }
    }
    public class QueueTest
    {
        //public static void Main()
        //{
        //    Queue que = new Queue(5);
        //    que.Enqueue(1);
        //    que.Enqueue(2);
        //    que.Enqueue(3);
        //    que.Enqueue(4);
        //    que.Enqueue(5);
        //    que.Enqueue(6);

        //    Console.WriteLine("items in queue :");
        //    Console.WriteLine(que.Dequeue());
        //    Console.WriteLine(que.Dequeue());
        //    Console.WriteLine(que.Dequeue());
        //    Console.WriteLine(que.Dequeue());
        //    Console.WriteLine(que.Dequeue());
        //                Console.WriteLine(que.Dequeue());
        //                Console.Read();
        //}

    }

C# : Data structure : Stack


Stack Implementation in C#

public class Stack
    {
        readonly int  _capacity;
        int top;
        int[] _stack ;
        public int Top
        {
            get { return top; }
        }
        public Stack()
        {
            _capacity = 1000;
            top = -1;
        }
        public Stack( int capacity)
        {
            _capacity = capacity;
            _stack = new int[_capacity];
            top = -1;
        }
        public void Push(int item)
        {
            if (top < _capacity - 1)
            {
                _stack[top + 1] = item;
                top++;
            }
            else
            {
                Console.WriteLine("Stack is full");
            }
        }
        public int Pop()
        {
            int itemToReturn=-1;
            if (top == -1)
            {
                Console.WriteLine("Stack empty!!");
             
            }
            else
            {
                 itemToReturn= _stack[top];
                top--;
             
            }
            return itemToReturn;
        }
        public int Peek()
        {
            if (top != -1)
            {
                return _stack[top];
            }
            else
            {
                Console.Write("Stack is empty!!!");
                return -1;

            }

         

        }

C# : Data Structure : Selection Sort



Selection Sort Algorithm in C#:

class SelectionSort
    {
        ///
        /// Selection Sort :
        //We will consider that at the first position element in array is minium,
        //and then serach in entire array smaller than
        // selected min we will exachange the array element
        //( for ex: at first itiration we consider that 0th element is min. if any other let's say 3rd element
        // is smaller then min.then we will exchange element of 0 and 3.now we have smallest elemnt of            //array at 0th position.similiarly we will do for first element and otehr in array.
        ///
        ///
        public static void Main()
        {
            int[] unSoretedArray = new int[] { 1, 8, 6, 4, 4, 2, 5 };
            PrintArray(unSoretedArray);
            for (int i = 0; i <= unSoretedArray.Length - 2; i++)
            {
                int min = unSoretedArray[i];

                int indexOfMin = i;
                for (int j = i + 1; j <= unSoretedArray.Length - 1; j++)
                {

                    if (min > unSoretedArray[j])
                    {
                        min = unSoretedArray[j];
                        indexOfMin = j;

                    }
                }
                unSoretedArray[indexOfMin] = unSoretedArray[i];
                unSoretedArray[i] = min;

                Console.Write("                                                           ");
                PrintArray(unSoretedArray);
                Console.WriteLine($"Changing position of elements from {indexOfMin} to {i}");

            }

            Console.Read();
        }
        public static void PrintArray(int [] unSoretedArray)
        {
            foreach (int i in unSoretedArray)
            {
                Console.Write($" {i}  " );
            }
            Console.WriteLine();

        }

C# : Data Structure : Insertion Sort



Insertion Sort Algorithm :

class InsertionSort
    {

        public static void Main()
        {
            int[] unSoredArray = new int[] { 4, 2, 3, 6, 9, 1, 2 };

            for (int i = 0; i <= unSoredArray.Length - 1; i++)
            {
                int val = unSoredArray[i];//4
                int j = i ;
                while(j>0 && unSoredArray[j-1] >val)//4- 2,2-4
                {
                    unSoredArray[j] = unSoredArray[j-1];
                    j--;
                }
                unSoredArray[j] = val;
            }
            PrintArray(unSoredArray);
            Console.Read();
        }
        public static void PrintArray(int[] unSoretedArray)
        {
            foreach (int i in unSoretedArray)
            {
                Console.Write($" {i}  ");
            }
            Console.WriteLine();
        }
    }

C# : Data Structure : Linked list implementation in C#



Linked List in C#:

 class LinkedListImpl
    {
        Node temp, head,last;
        int count = 0;
        public void InsertAtEnd(int value)
        {
            temp = new Node() { data = value, Next = null };
            if (head == null)
            {
                head = temp;
                last = temp;
            }
         
            last.Next = temp;
            last = temp;
         
            count++;
        }
        public void PrintLinkedList()
        {
            temp = head;
            if (temp == null) throw new Exception("Empty linked list!");

            while (temp != null)
            {
                Console.Write($"{temp.data}=>");
                temp = temp.Next;
            }

            Console.WriteLine("First element of List is " + head.data);
            Console.WriteLine("Last element of List is " + last.data);
        }
        public void InsertAtStart(int value)
        {
            temp = new Node() { data = value, Next = head };
            head = temp;

        }
        public void Insert(int insertAfter,int value)
        {
            temp = head;
            while (temp != null)
            {
                if (temp.data == insertAfter)
                {
                    Node temp2 = new Node { data = value, Next = temp.Next };
                    temp.Next = temp2;

                    // if inserted at last then need to Move last pointer.
                    if (temp2.Next is null)
                    {
                        last = temp2;
                    }
                    break;
                 
                }
                temp = temp.Next;
            }
        }

        public bool Find(int value)
        {
            temp = head;
            while (temp != null)
            {
                if (temp.data == value)
                {
                    return true;
                }
             
                temp = temp.Next;
            }
            return false;
        }
        public bool Delete(int value)
        {
            if (head.data == value)// if data is available at head node
            {
                head = head.Next;
                return true;
            }
            temp = head;
            while (temp != null)// moving till( desired node -1). i.e. before the desired node (1=>2=>5=>6=>7=>8 and we want to delete 6, so need to move till 5 th only.)
            {
             
                if (temp.Next?.data == value)
                {
                    temp.Next = temp.Next.Next;
                }

                if (temp.Next is null)// to set last node.
                {
                    last = temp;
                }
                temp = temp.Next;
             
            }
            return false;
        }
        class Node
        {
            public int data { get; set; }
            public Node Next { get; set; }
        }
/*
        public static void Main()
        {
            LinkedListImpl linkedList = new LinkedListImpl();

            linkedList.InsertAtEnd(2);
            linkedList.InsertAtEnd(4);
            linkedList.InsertAtEnd(6);

            linkedList.InsertAtStart(1);
            linkedList.InsertAtStart(0);

            linkedList.Insert(4, 5);
            linkedList.Insert(6, 7);
            linkedList.Insert(7, 8);


            Console.WriteLine(linkedList.Find(3));
            Console.WriteLine(linkedList.Find(0));
            Console.WriteLine(linkedList.Find(7));

            linkedList.PrintLinkedList();
            linkedList.Delete(7);
            linkedList.Delete(0);
            linkedList.Delete(8);
            linkedList.PrintLinkedList();
            Console.Read();

        }
        */

    }

Wednesday, May 15, 2019

SQL Server : Some common queries on Employee and Department table


Schema of Employee and Department table are as follows :





























Create Department :

Create table Department (DepartmentId integer not null primary key identity, DepartmentName varchar(50));


Create Employee :

Create table Employee(
EmployeeId integer not null identity primary key
,FirstName varchar(50)
,LastName varchar(50)
,DepartmentId integer
);

---------------Some common queries on above tables-----------------

--Department wise employees name and other details 
select d.DepartmentName, e.FirstName,e.LastName,e.Salary
from Employee e left join Department d on e.departmentId=d.DepartmentId

-- Find out no. of Employees in Each Deaprtment.
---------------------------------------------------------
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
group by d.departmentID,d.departmentName
 --------------------------------------------------------
-- find out departments where no.of employee more than 3
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
where d.departmentname is not null
group by d.departmentID,d.departmentName having  count(e.employeeid)>3

-----------------------------------------------------
-- Find out name of employee,salary and departmentName, who has max salary in their department .
--i.e. departmentname and name of employee who has max salary in their department.

SELECT DepartmentName, firstName,LastName, Salary, Salaryrank from
(select d.DepartmentName, e.firstName,e.LastName, e.Salary, row_number() over (partition by e.departmentID order by salary desc)  as Salaryrank
from Employee e
left join Department d on e.departmentId=d.DepartmentId) as result
where result.Salaryrank=1
-- Result will contains department name ,name of employee who has highest salary in their department, salary




SQL Server : How to create and alter table & columns



Create table in SQL Server, firstly you need to choose right database where you want to create table.
you can change database by below query :

USE  DB_NAME;

How to create table

Create table Employee(
                                           EmployeeId integer 
                                           ,FirstName varchar(50)
                                           ,LastName varchar(50)
                                           ,DepartmentId integer
);

above Employee table doesn't have primary key, we can add primary key via above query itself
or we can add primary key constraints, see below examples :

 -- Add constraints

  Alter table Employee add constraint Emp_pk primary key (EmployeeId); 

but when you will try to execute above query you will get "Cannot define PRIMARY KEY constraint on nullable column in table 'Employee" error message. because to create primary key on a column, column should be defined as 'NOT NULL'

So, we need to alter 'Employee table's 'EmployeeId' column like:

  Alter table Employee alter column EmployeeId integer NOT NULL;

(similarly, we can change any column's attributes like( name,datatype,etc.) Now, we can run previous statement to add constraint to add primary key.

We can also mention primary key, not null, identity column declaration with create table statement.
like :

Create table Employee(
EmployeeId integer not null identity primary key
,FirstName varchar(50)
,LastName varchar(50)
,DepartmentId integer
);

We can declare NOT NULL, CHECK and UNIQUE constraints at the time of table creation same like above.

C# Record type: Something to remember while using record types

  Record in c# provide a concise and expressive way to create immutable data types, record is a keyword in c#, we can use this keyword with ...