Friday 29 March 2013

self joins examples in sql


How To Use Self Join In Sql Server


Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.
For self join in sql you can try the following example:

Create table employees:
emp_idemp_nameemp_manager_id
1JohnNull
2Tom1
3Smith1
4Albert2
5David2
6Murphy5
7Petra5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:
select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:
manageremployee
JohnTom
JohnSmith
TomAlbert
TomDavid
DavidMurphy
DavidPetra

Views in SQL Server


View

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
Once you have defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Example

Consider the Publishers table below. If you want users to see only two columns in the table, you can create a view called vwPublishers that will refer to the Publishers table and the two columns required. You can grant Permissions to users to use the view and revoke Permissions from the base Publishers table. This way, users will be able to view only the two columns referred to by the view. They will not be able to query on the Publishers table.
Publishers
Publd
PubName
City
State
Country
0736
New Moon Books
Boston
MA
USA
0877
Binnet & Hardly
Washington
DC
USA
1389
Algodata Infosystems
Berkeley
CA
USA
1622
Five Lakes Publishing
Chicago
IL
USA
VW Publishers
Publd
PubName
0736
New Moon Books
0877
Binnet & Hardly
1389
Algodata Infosystems
1622
Five Lakes Publishing
Views ensure the security of data by restricting access to the following data:
  • Specific rows of the tables.
  • Specific columns of the tables.
  • Specific rows and columns of the tables.
  • Rows fetched by using joins.
  • Statistical summary of data in a given tables.
  • Subsets of another view or a subset of views and tables.
Some common examples of views are:
  • A subset of rows or columns of a base table.
  • A union of two or more tables.
  • A join of two or more tables.
  • A statistical summary of base tables.
  • A subset of another view, or some combination of views and base table.


Creating Views

A view can be created by using the CREATE VIEW statement.

Syntax

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]
Where:
view_name specifies the name of the view and must follow the rules for identifiers.
column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.
WITH ENCRYPTION encrypts the text for the view in the syscomments table.
AS specifies the actions that will be performed by the view.
select_statement specifies the SELECT Statement that defines a view. The view may use the data contained in other views and tables.
WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.
The restrictions imposed on views are as follows:
  • A view can be created only in the current database.
  • The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
  • A view can be created only if there is a SELECT permission on its base table.
  • A SELECT INTO statement cannot be used in view declaration statement.
  • A trigger or an index cannot be defined on a view.
  • The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Example

CREATE VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone
FROM Customers
Creates a view called vwCustomer. Note that the view is a query stored as an object. The data is derived from the columns of the base table Customers.
You use the view by querying the view like a table.
SELECT *FROM vwCUSTOMER
The output of the SELECT statement is:
CustomerId
Company Name
Phone
ALFKI
Alfreds Futterkiste
030-0074321
ANTON
Antonio Moreno Taqueria
(5)555-3932
(91 rows affected)

[SOLVED] triggers in sql server 2008 with simple example


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. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.
Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(ii) Instead Of Triggers
(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers.

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);

I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. The main purpose of this audit table is to record the changes in the main table. This can be thought of as a generic audit trigger.

Now, create the audit table as:-
 Collapse | Copy Code
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
(a) AFTRE INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

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
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table.
To see the newly created trigger in action, lets insert a row into the main table as :

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

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:-

6   Chris  1500.00   Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700
(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-

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
The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from the update(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly checked the column Emp_Sal for an update.
Let’s update a record column and see what happens.

 update Employee_Test set Emp_Sal=1550 where Emp_ID=6
This inserts the row into the audit table as:-
 Collapse | Copy Code
6  Chris  1550.00  Updated Record -- After Update Trigger.  2008-04-26 12:38:11.843
(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let’s create the trigger as:-

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
In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table.
Let’s fire a delete on the main table.
A record has been inserted into the audit table as:-

 6  Chris 1550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867
All the triggers can be enabled/disabled on the table using the statement

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL
Specific Triggers can be enabled or disabled as :-

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table.
(ii) Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-

(a) INSTEAD OF INSERT Trigger.
(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

(a) Let’s create an Instead Of Delete Trigger as:-

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;

BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.
Now, let’s try to delete a record with the Emp_Sal >1200 as:-

delete from Employee_Test where Emp_ID=4
This will print an error message as defined in the RAISE ERROR statement as:-

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200

And this record will not be deleted.
In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables.

method overriding in c# with real time examples

same method names with same arguments and same return types associated in a class and its subclass. 

Examples:


class BC
{
  public virtual void Display()
  {
     System.Console.WriteLine("BC::Display");
  }
}

class DC : BC
{
  public override void Display()
  {
     System.Console.WriteLine("DC::Display");
  }
}

class TC : DC
{
  public override void Display()
  {
     System.Console.WriteLine("TC::Display");
  }
}

class Demo
{
  public static void Main()
  {
     BC b;
     b = new BC();
     b.Display();  

     b = new DC();
     b.Display();  

     b = new TC();
     b.Display();  
  }
}
Output

BC::Display
DC::Display
TC::Display

Abstract Classes and Abstract Methods in C#


Introduction

Abstract classes are one of the essential behaviors provided by .NET. Commonly, you would like to make classes that only represent base classes, and don’t want anyone to create objects of these class types. You can make use of abstract classes to implement such functionality in C# using the modifier 'abstract'.

An abstract class means that, no object of this class can be instantiated, but can make derivations of this.

An example of an abstract class declaration is:
abstract class absClass
{
}
An abstract class can contain either abstract methods or non abstract methods. Abstract members do not have any implementation in the abstract class, but the same has to be provided in its derived class.

An example of an abstract method:

abstract class absClass
{
  public abstract void abstractMethod();
}
Also, note that an abstract class does not mean that it should contain abstract members. Even we can have an abstract class only with non abstract members. For example:
abstract class absClass
{
    public void NonAbstractMethod()
    {
        Console.WriteLine("NonAbstract Method");
    }
}
A sample program that explains abstract classes:

using System;

namespace abstractSample
{
      //Creating an Abstract Class
      abstract class absClass
      {
            //A Non abstract method
            public int AddTwoNumbers(int Num1, int Num2)
            {
                return Num1 + Num2;
            }

            //An abstract method, to be
            //overridden in derived class
            public abstract int MultiplyTwoNumbers(int Num1, int Num2);
      }

      //A Child Class of absClass
      class absDerived:absClass
      {
            [STAThread]
            static void Main(string[] args)
            {
               //You can create an
               //instance of the derived class

               absDerived calculate = new absDerived();
               int added = calculate.AddTwoNumbers(10,20);
               int multiplied = calculate.MultiplyTwoNumbers(10,20);
               Console.WriteLine("Added : {0},
                       Multiplied : {1}", added, multiplied);
            }

            //using override keyword,
            //implementing the abstract method
            //MultiplyTwoNumbers
            public override int MultiplyTwoNumbers(int Num1, int Num2)
            {
                return Num1 * Num2;
            }
      }
}
In the above sample, you can see that the abstract class absClass contains two methods AddTwoNumbers and MultiplyTwoNumbers. AddTwoNumbers is a non-abstract method which contains implementation and MultiplyTwoNumbers is an abstract method that does not contain implementation.

The class absDerived is derived from absClass and the MultiplyTwoNumbers is implemented on absDerived. Within the Main, an instance (calculate) of the absDerived is created, and calls AddTwoNumbers and MultiplyTwoNumbers. You can derive an abstract class from another abstract class. In that case, in the child class it is optional to make the implementation of the abstract methods of the parent class.

Example
//Abstract Class1
abstract class absClass1
{
    public abstract int AddTwoNumbers(int Num1, int Num2);
    public abstract int MultiplyTwoNumbers(int Num1, int Num2);
}

//Abstract Class2
abstract class absClass2:absClass1
{
    //Implementing AddTwoNumbers
    public override int AddTwoNumbers(int Num1, int Num2)
    {
        return Num1+Num2;
    }
}

//Derived class from absClass2
class absDerived:absClass2
{
    //Implementing MultiplyTwoNumbers
    public override int MultiplyTwoNumbers(int Num1, int Num2)
    {
        return Num1*Num2;
    }
}
In the above example, absClass1 contains two abstract methods AddTwoNumbers and MultiplyTwoNumbers. The AddTwoNumbers is implemented in the derived class absClass2. The class absDerived is derived from absClass2 and the MultiplyTwoNumbers is implemented there.

Abstract properties

Following is an example of implementing abstract properties in a class.

//Abstract Class with abstract properties
abstract class absClass
{
    protected int myNumber;
    public abstract int numbers
    {
        get;
        set;
    }
}

class absDerived:absClass
{
    //Implementing abstract properties
    public override int numbers
    {
        get
        {
            return myNumber;
        }
        set
        {
            myNumber = value;
        }
    }
}
In the above example, there is a protected member declared in the abstract class. The get/set properties for the member variable myNumber is defined in the derived class absDerived.

Important rules applied to abstract classes

An abstract class cannot be a sealed class. I.e. the following declaration is incorrect.

//Incorrect
abstract sealed class absClass
{
}
Declaration of abstract methods are only allowed in abstract classes.

An abstract method cannot be private.

//Incorrect
private abstract int MultiplyTwoNumbers();
The access modifier of the abstract method should be same in both the abstract class and its derived class. If you declare an abstract method as protected, it should be protected in its derived class. Otherwise, the compiler will raise an error.

An abstract method cannot have the modifier virtual. Because an abstract method is implicitly virtual.
//Incorrect
public abstract virtual int MultiplyTwoNumbers();
An abstract member cannot be static.

//Incorrect
publpublic abstract static int MultiplyTwoNumbers();

[SLOVED] Delegate in c# with real examples


What is a Delegate?
Delegate is a type which  holds the method(s) reference in an object. It is also referred to as a type safe function pointer.
Advantages
Encapsulating the method's call from caller
Effective use of delegate improves the performance of application
Used to call a method asynchronously
Declaration 
public delegate type_of_delegate delegate_name()
Example:
public delegate int mydelegate(int delvar1,int delvar2)
Note
You can use delegates without parameters or with parameter list
You should follow the same syntax as in the method 
(If you are referring to the method with two int parameters and int return type, the delegate which you are declaring should be in the same format. This is why it is referred to as type safe function pointer.)
Sample Program using Delegate 
public delegate double Delegate_Prod(int a,int b);
class Class1
{
    static double fn_Prodvalues(int val1,int val2)
    {
        return val1*val2;
    }
    static void Main(string[] args)
    {
        //Creating the Delegate Instance
        Delegate_Prod delObj = new Delegate_Prod(fn_Prodvalues);
        Console.Write("Please Enter Values");
        int v1 = Int32.Parse(Console.ReadLine());
        int v2 = Int32.Parse(Console.ReadLine());
        //use a delegate for processing
        double res = delObj(v1,v2);
        Console.WriteLine ("Result :"+res);
        Console.ReadLine();
    }
}
Explanation
Here I have used a small program which demonstrates the use of delegate.
The delegate "Delegate_Prod" is declared with double return type and accepts only two integer parameters.
Inside the class, the method named fn_Prodvalues is defined with double return type and two integer parameters. (The delegate and method have the same signature and parameter type.)
Inside the Main method, the delegate instance is created and the function name is passed to the delegate instance as follows:
Delegate_Prod delObj = new Delegate_Prod(fn_Prodvalues);
After this, we are accepting the two values from the user and passing those values to the delegate as we do using method:
 delObj(v1,v2);
Here delegate object encapsulates the method functionalities and returns the result as we specified in the method.
Multicast Delegate
What is Multicast Delegate?
It is a delegate which holds the reference of more than one method.
Multicast delegates must contain only methods that return void, else there is a run-time exception.

Simple Program using Multicast Delegate

delegate void Delegate_Multicast(int x, int y);
Class Class2
{
    static void Method1(int x, int y)
    {
        Console.WriteLine("You r in Method 1");
    }

    static void Method2(int x, int y)
    {
        Console.WriteLine("You r in Method 2");
    }

    public static void <place w:st="on" />Main</place />()
    {
        Delegate_Multicast func = new Delegate_Multicast(Method1);
        func += new Delegate_Multicast(Method2);
        func(1,2);             // Method1 and Method2 are called
        func -= new Delegate_Multicast(Method1);
        func(2,3);             // Only Method2 is called
    }
}
Explanation
In the above example, you can see that two methods are defined named method1 and method2 which take two integer parameters and return type as void.
In the main method, the Delegate object is created using the following statement: 
Delegate_Multicast func = new Delegate_Multicast(Method1);
Then the Delegate is added using the += operator and removed using the -= operator.