Search This Blog

Tuesday 21 April 2015

EXCEPT VS NOT IN AND INTERSECT VS IN in SQL Server

EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table.

"NOT IN" operator will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result

--STEP 1 :-

create table #Table1
(
       NAME varchar(50)
)

create table #Table2
(
       NAME varchar(50)
)

--STEP 2 :-

--INSERT VALUES TO #Table1
insert into #Table1 values ('Name 1')
insert into #Table1 values ('Name 2')
insert into #Table1 values ('Name 3')
insert into #Table1 values ('Name 4')

--INSERT VALUES TO #Table2
insert into #Table2 values ('Name 1')
insert into #Table2 values ('Name 2')

--SELECT QUERY
select * from #Table1
select * from #Table2

-----------EXCEPT VS NOT IN OPERATOR-----------------------------

--STEP 3 :-

--USING EXCEPT OPERATOR
select * from #Table1
except
select * from #Table2

--USING NOT IN OPERATOR
select * from #Table1
where NAME NOT IN(select * from #Table2)

--STEP 4 :-

--INSERT DUBLICATE VALUE IN #Table1
insert into #Table1 values ('Name 3')
insert into #Table1 values ('Name 4')

--SELECT QUERY
select * from #Table1

--STEP 5 :-

--USING EXCEPT OPERATOR
select * from #Table1
except
select * from #Table2

--USING NOT IN OPERATOR
select * from #Table1
where NAME NOT IN(select * from #Table2)

-----------INTERSECT VS IN OPERATOR-----------------------------

INTERSECT operator returns all distinct rows from left hand side table which does exist in right hand side table.

"IN" operator will return all rows from left hand side table which are present in right hand side table but it will not remove duplicate rows from the result

--STEP 6 :-

--USING INTERSECT OPERATOR
select * from #Table1
INTERSECT
select * from #Table2

--USING IN OPERATOR
select * from #Table1
where NAME IN(select * from #Table2)

--STEP 7:-

--INSERT DUBLICATE VALUE IN #Table1
insert into #Table1 values ('Name 1')
insert into #Table1 values ('Name 2')

--SELECT QUERY
select * from #Table1

--STEP 8:-

--USING INTERSECT OPERATOR
select * from #Table1
INTERSECT
select * from #Table2

--USING IN OPERATOR
select * from #Table1
where NAME IN(select * from #Table2)

drop table #Table1,#Table2


Wednesday 8 April 2015

Check whether the Entered Number is Even or Odd in C#

If a given number is divisible by 2 with the remainder 0 then the number is an Even number.
If the number is not divisible by 2 then that number will be an Odd number.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            int i;

            Console.Write("Enter a Number : ");

            i = int.Parse(Console.ReadLine());

            if (i % 2 == 0)
            {
                Console.Write("Entered Number is an Even Number");

                Console.Read();
            }

            else
            {
                Console.Write("Entered Number is an Odd Number");
            }
       

            Console.ReadLine();
        }
    }
}

Example:-

2)10(5
  10
--------
   0


 2)11(5
   10
--------
    1


 2)25(12
   2
--------
   05
    4
--------
    1


Saturday 4 April 2015

OOPS Concepts

OOPS Concepts
Class
Object
Encapsulation
Abstraction
Inheritance
Polymorphism

Class
It is a collection of objects. Or it is the blueprint design.
Example: Let’s take our home
Home is our Class
Kitchen, bedroom, hall is our method
Kitchen, bedroom and hall contain its own properties.
Kitchen has set of dishes, gas etc.
Bedroom has bed, table lamp, television etc.
Hall have sofa, dining table etc.

public class Home
    {
        #region Kitchen Properties
        public int setOfDishes = 0;
        public int noOfGasCylinder = 0;
        #endregion

        #region Bedroom Properties
        public int noOfBed = 0;
        public int noOfTableLamp = 0;
        public int noOfTelevision = 0;
        #endregion

        #region Hall Properties
        public int noOfSofa = 0;
        public int noOfDiningTable = 0;
        public int noOfChair = 0;
        #endregion

        public void Kitchen()
        {
            setOfDishes = 10;
            noOfGasCylinder = 2;

            Console.WriteLine("Set Of Dishes = {0}", setOfDishes);
            Console.WriteLine("No Of Gas Cylinder = {0}", noOfGasCylinder);
        }

        public void Bedroom()
        {
            noOfBed = 1;
            noOfTableLamp = 1;
            noOfTelevision = 1;

            Console.WriteLine("No Of Bed = {0}", noOfBed);
            Console.WriteLine("No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("No Of Television = {0}", noOfTelevision);

        }

        public void Hall()
        {
            noOfSofa = 2;
            noOfDiningTable = 1;
            noOfChair = 6;

            Console.WriteLine("No Of Sofa = {0}", noOfSofa);
            Console.WriteLine("No Of Dining Table = {0}", noOfDiningTable);
            Console.WriteLine("No Of Chair = {0}", noOfChair);
        }
    }
Object
Object is an instance of a class.
Instances of classes are created by using the new operator.

Now we want to access the Kitchen method of Home class
So, we can create the Instances of home class to access the Kitchen method

Home objHome = new Home();
objHome.Kitchen();

Encapsulation
In our house example, I have already told that House security guard limitation is the entrance of the house. The security guard doesn’t need to know about what is happening inside the house. Therefore, the House Owner will hide from the Security guard all the happenings for more safety for his safety. The hiding and limitation are called as Encapsulation.
For example, we have two Classes the first one is “Home” class and the other class as “HomeSecurity” class Here we can see all the variables are wrap into a class where “HomeSecurity” class is set as public, so the “Home” class can access that, but “Home” class has both Public and private variable where the private variable of a class cannot be accessed outside of the class.

    public class HomeSecurity
    {
        public int NoOfSecurity;
        public string SecurityName = string.Empty;
    }

    public class Home
    {
        private int NoOfLockerInHosue = 2;
        public string OwnerName = string.Empty;
    }

Abstraction
1.       Abstraction is to show and share some common information to the user.
2.       Let’s take our House example, in our house we will have servant, servants can go to all rooms and do cleaning and other works.
3.       The house owner can give full rights or some partial rights to the servant for accessing his house.
4.      How to abstract: - By using Access modifiers

There are 5 different access modifiers in c#.
1. Private
2. Protected
3. Internal
4. Protected Internal
5. Public

Private                           - Only with in containing class
Protected                     - With the containing class and the class derived from the containing class
Internal                         - Anywhere within the containing assembly
Protected Internal    - Anywhere within the containing assembly, and from within a derived class in any
                                         another assembly
Public                              - Anywhere

Inheritance
1.       In simple word Inheritance means something we got from our ancestor.
2.       Inheritance is nothing but accessing and using all base class variable and methods in the Derived Class.
3.       The class whose members are inherited is called the base class, and the class that inherits those members is called the derived class.

    public class Home
    {
        public Home()
        {
            Console.WriteLine("Home class constructor");
        }

        public virtual void Kitchen(int setOfDishes, int noOfGasCylinder)
        {
            Console.WriteLine("Home - Set Of Dishes = {0}", setOfDishes);
            Console.WriteLine("Home - No Of Gas Cylinder = {0}", noOfGasCylinder);
        }

        public virtual void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Home - No Of Television = {0}", noOfTelevision);
        }

        public virtual void Hall(int noOfSofa, int noOfDiningTable, int noOfChair)
        {
            Console.WriteLine("Home - No Of Sofa = {0}", noOfSofa);
            Console.WriteLine("Home - No Of Dining Table = {0}", noOfDiningTable);
            Console.WriteLine("Home - No Of Chair = {0}", noOfChair);
        }

        public virtual void RentedHome()
        {
            Console.WriteLine("Home is not Rented Home.");
        }
    }

    public class Ram_Home : Home
    {
        public Ram_Home()
        {
            Console.WriteLine("Ram_Home class constructor");
        }

        public override void Kitchen(int setOfDishes, int noOfGasCylinder)
        {
            Console.WriteLine("Ram_Home - Set Of Dishes = {0}", setOfDishes);
            Console.WriteLine("Ram_Home - No Of Gas Cylinder = {0}", noOfGasCylinder);
        }

        public override void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Ram_Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Ram_Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Ram_Home - No Of Television = {0}", noOfTelevision);
        }

        public new void Hall(int noOfSofa, int noOfDiningTable, int noOfChair)
        {
            Console.WriteLine("Ram_Home - No Of Sofa = {0}", noOfSofa);
            Console.WriteLine("Ram_Home - No Of Dining Table = {0}", noOfDiningTable);
            Console.WriteLine("Ram_Home - No Of Chair = {0}", noOfChair);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("-----------------------------Home class object");

            Home objHome = new Home();
            Console.WriteLine("---------------Kitchen");
            objHome.Kitchen(10, 2);
            Console.WriteLine("---------------Bedroom");
            objHome.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objHome.Hall(1, 1, 6);
            Console.WriteLine("---------------RentedHome");
            objHome.RentedHome();

            Console.WriteLine("-----------------------------Ram_Home class object");

            Ram_Home objRam_Home = new Ram_Home();
            Console.WriteLine("---------------Kitchen");
            objRam_Home.Kitchen(20, 1);
            Console.WriteLine("---------------Bedroom");
            objRam_Home.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objRam_Home.Hall(2, 1, 4);
            Console.WriteLine("---------------RentedHome");
            objRam_Home.RentedHome();

            Console.WriteLine("-----------------------------Home class object references an object of Ram_Home");

            Home objHome1 = new Ram_Home();
            Console.WriteLine("---------------Kitchen");
            objHome1.Kitchen(10, 2);
            Console.WriteLine("---------------Bedroom");
            objHome1.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objHome1.Hall(1, 1, 6);
            Console.WriteLine("---------------RentedHome");
            objHome1.RentedHome();

            Console.ReadLine();
        }
    }
Step 1:- Home class object
1.        Now create the object of home class i.e. also called base or parent class.

            Home objHome = new Home();
            Console.WriteLine("---------------Kitchen");
            objHome.Kitchen(10, 2);
            Console.WriteLine("---------------Bedroom");
            objHome.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objHome.Hall(1, 1, 6);
            Console.WriteLine("---------------RentedHome");
            objHome.RentedHome();

Output

Home class constructor
---------------Kitchen
Home - Set Of Dishes = 10
Home - No Of Gas Cylinder = 2
---------------Bedroom
Home - No Of Bed = 1
Home - No Of Table Lamp = 1
Home - No Of Television = 1
---------------Hall
Home - No Of Sofa = 1
Home - No Of Dining Table = 1
Home - No Of Chair = 6
---------------RentedHome
Home is not Rented Home.

2.        Home class constructor getting called first and then after it will print all the method  of home class as  expected.


Step 2:- Ram_Home class object
1.       Now create the object of Ram_Home class i.e. also called Derived or Child class.

            Ram_Home objRam_Home = new Ram_Home();
            Console.WriteLine("---------------Kitchen");
            objRam_Home.Kitchen(20, 1);
            Console.WriteLine("---------------Bedroom");
            objRam_Home.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objRam_Home.Hall(2, 1, 4);
            Console.WriteLine("---------------RentedHome");
            objRam_Home.RentedHome();

Output

Home class constructor
Ram_Home class constructor
---------------Kitchen
Ram_Home - Set Of Dishes = 20
Ram_Home - No Of Gas Cylinder = 1
---------------Bedroom
Ram_Home - No Of Bed = 1
Ram_Home - No Of Table Lamp = 1
Ram_Home - No Of Television = 1
---------------Hall
Ram_Home - No Of Sofa = 2
Ram_Home - No Of Dining Table = 1
Ram_Home - No Of Chair = 4
---------------RentedHome
Home is not Rented Home.

2.       Base class constructor getting called first and then derived class constructor getting called because Ram_Home class Inherited the Home class
3.       It will print all the method of a derived class as expected except Rented_Home() method because we have not implemented the Rented_Home() method in derived class.
4.       In Inheritance, when we are creating the object of Derived class it will search respective method in derived class first and then went to base class.
5.       If any method not present in the derived class, then it will search that method in the base class and print the base class method.
            
Step 3:- Home class object references an object of Ram_Home
1.       Now create the object of Home class, but it references an object of Ram_Home class.

              Home objHome1 = new Ram_Home();
            Console.WriteLine("---------------Kitchen");
            objHome1.Kitchen(10, 2);
            Console.WriteLine("---------------Bedroom");
            objHome1.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Hall");
            objHome1.Hall(1, 1, 6);
            Console.WriteLine("---------------RentedHome");
            objHome1.RentedHome();

Output

Home class constructor
Ram_Home class constructor
---------------Kitchen
Ram_Home - Set Of Dishes = 10
Ram_Home - No Of Gas Cylinder = 2
---------------Bedroom
Ram_Home - No Of Bed = 1
Ram_Home - No Of Table Lamp = 1
Ram_Home - No Of Television = 1
---------------Hall
Home - No Of Sofa = 1
Home - No Of Dining Table = 1
Home - No Of Chair = 6
---------------RentedHome
Home is not Rented Home.

2.       Base class constructor getting called first followed by the derived class constructor.
3.       It will print the Kitchen(), Bedroom() method of a derived class because Kitchen(), Bedroom() method overridden in derived class.
4.       Hall() method print from base class because we have use new keyword, new keyword explicitly hides a member or method of derived class that is inherited from a base class
5.       we have not implemented the Rented_Home() method in derived class so it will called from base class.
           

Polymorphism
Using same method name with different parameter is an example for the polymorphism.
Polymorphisms have two types:
1.       Method Overloading  is also called Compile Time Polymorphism
2.       Method Overriding is also called Run time Polymorphism

Method Overloading (Compile Time Polymorphism) (Early Binding):

1.       Method Overloading means same method name using more than one method with different argument in the same class.
public class Home
    {
        public void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Home - No Of Television = {0}", noOfTelevision);
        }

        public void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision, string WallColour)
        {
            Console.WriteLine("Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Home - No Of Television = {0}", noOfTelevision);
            Console.WriteLine("Home - Wall Colour = {0}", WallColour);
        }

        static void Main(string[] args)
        {
            Home objHome = new Home();
            objHome.Bedroom(1, 1, 1);
            objHome.Bedroom(1, 1, 1, "White");

            Console.ReadLine();
        }
    }
Output

Method Argument with 3 Parameter

Home - No Of Bed = 1
Home - No Of Table Lamp = 1
Home - No Of Television = 1

Method Argument with 4 Parameter

Home - No Of Bed = 1
Home - No Of Table Lamp = 1
Home - No Of Television = 1
Home - Wall Colour = White

Method Overriding (Run time Polymorphism) (Late Binding):

1.       Method overriding occurs when child class declares a method that has the same type arguments as a method declared by one of its superclass.
2.       Method overriding can only be used in the derived class.
3.       Method Overriding cannot be done in the same class.
4.       Method Overriding can be used in Abstract Method, Virtual Method and in Sealed Method

Note:  By default functions are not virtual in C# and so you need to write “virtual” explicitly.

What is Virtual Method:-
By declaring base class function as virtual, we allow the function to be overridden in any of derived class.

public class Home
    {
        public virtual void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Home - No Of Television = {0}", noOfTelevision);
        }

        public virtual void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision, string WallColour)
        {
            Console.WriteLine("Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Home - No Of Television = {0}", noOfTelevision);
            Console.WriteLine("Home - Wall Colour = {0}", WallColour);
        }
    }

    public class Ram_Home : Home
    {
        public override void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Ram_Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Ram_Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Ram_Home - No Of Television = {0}", noOfTelevision);
        }

        public override void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision, string WallColour)
        {
            Console.WriteLine("Ram_Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Ram_Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Ram_Home - No Of Television = {0}", noOfTelevision);
            Console.WriteLine("Ram_Home - Wall Colour = {0}", WallColour);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("-----------------------------Home class object references an object of Ram_Home");
            Home objHome = new Ram_Home();
            Console.WriteLine("---------------Bedroom() method with 3 argument");
            objHome.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Bedroom() method with 4 argument");
            objHome.Bedroom(1, 1, 1, "White");

            Console.ReadLine();       
         }
    }

Abstract class:-

1.       The Abstract class is a special type of class that should be declared with the abstract keyword.
2.       It should contain one or more abstract methods or not abstract methods, which should contain only method definitions.
3.       It won't have any method body (in the form of curly braces), so the method definition is followed by a semicolon instead of a normal method block.
4.       An abstract class cannot be instantiated.
5.       The purpose of an abstract class is to provide a common definition of a base class that multiple derived classes can share.
6.       Derived classes of the abstract class must implement all abstract methods.

  public abstract class Home
    {
        public abstract void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision);

        public abstract void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision, string WallColour);
    }

    public class Ram_Home : Home
    {
        public override void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision)
        {
            Console.WriteLine("Ram_Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Ram_Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Ram_Home - No Of Television = {0}", noOfTelevision);
        }

        public override void Bedroom(int noOfBed, int noOfTableLamp, int noOfTelevision, string WallColour)
        {
            Console.WriteLine("Ram_Home - No Of Bed = {0}", noOfBed);
            Console.WriteLine("Ram_Home - No Of Table Lamp = {0}", noOfTableLamp);
            Console.WriteLine("Ram_Home - No Of Television = {0}", noOfTelevision);
            Console.WriteLine("Ram_Home - Wall Colour = {0}", WallColour);
        }
    }

    class Program : Ram_Home
    {
        static void Main(string[] args)
        {
            Program objHome = new Program();
            Console.WriteLine("---------------Bedroom() method with 3 argument");
            objHome.Bedroom(1, 1, 1);
            Console.WriteLine("---------------Bedroom() method with 4 argument");
            objHome.Bedroom(1, 1, 1, "White");

            Console.ReadLine();
        }
    }