Tuesday, 15 January 2013

Find nth Highest salary(value) from table

Presume: Let there is a table name Employee and it has a field name salary(bigint).
Problem: I want to get 5th(nth) highest salary.
Solution:Here is the query to retrieve 5th highest salary from employee table


                      SELECT TOP 1 Salary FROM Employee WHERE Salary IN
                              (SELECT TOP 5 DISTINCT( Salary) FROM Employee ORDER BY  Salary DESC )
                                ORDER BY Salary ASC

Note: Above query can be generalized to retrieve nth highest or lowest record from any table.

Tuesday, 20 November 2012

Delete duplicate row from table

To delete duplicate row from table execute this query.

DELETE FROM MyTable WHERE AUTOID NOT IN 
(SELECT MIN(AUTOID) 
 FROM MyTable  GROUP BY col1,col2) 

Where autoId is  auto increment id.

Thursday, 26 July 2012

Static polymorphism Vs. Dynamic polymorphism

Static polymorphism Vs. Dynamic polymorphism
What is polymorphism? Poly means many. So how can we take this definition in .NET context. Well we can apply this to class’s ability to share the same methods (actions) but implement them differently.
Suppose we have a method in a class to add numbers,
public class calculation
{
public int add(int x, int y)
{
return x+y;
}
}
So to perform addition of three numbers, we need similar add method but with different parameter
public class calculation
{
public int add(int x, int y)
{
return x+y;
}
public int add(int x, int y,int z)
{
return x+y+z;
}
}
So we can that class is sharing the same methods (actions) but implement them differently.
Now this is an example when we are sharing method name and implementing them differently, let’s take a scenario where implementation is in some derived class.
For instance, say we create a class called Shape and this class has a method called .Area () which calculates area. Then we create two subclasses, using inheritance, of this Shape class. One called Square, the other called Circle. Now obviously a square and circle are two entirely different shapes, yet both classes have the .Area() method. When the Square.Area() method is called it will calculate area of  a square. When the Circle.Area() method is called, it will calculate area of a circle. So both classes can use the same methods but implement them differently.
Now let’s dive little deeper and understand what we discussed above in more technical terms.
Types of Polymorphism
1) Static or Compile time Polymorphism
Which method is to be called is decided at compile-time only. Method Overloading is an example of this. Method overloading is a concept where we use the same method name many times in the same class, but different parameters. Depending on the parameters we pass, it is decided at compile-time only. The same method name with the same parameters is an error and it is a case of duplication of methods which c# does not permit. In Static Polymorphism decision is taken at compile time.
public Class StaticDemo
{
public void display(int x)
{
Console.WriteLine(“Area of a Square:”+x*x);
}
public void display(int x, int y)
{
Console.WriteLine(“Area of a Square:”+x*y);
}
public static void main(String args[])
{
StaticDemo spd=new StaticDemo();
Spd.display(5);
Spd.display(10,3);
}
}
2) Dynamic or Runtime Polymorphism.
Run time Polymorphism also known as method overriding. In this Mechanism by which a call to an overridden function is resolved at a Run-Time (not at Compile-time) if a base Class contains a method that is overridden. Method overriding means having two or more methods with the same name, same signature but with different implementation. In this process, an overridden method is called through the reference variable of a superclass, the determination of the method to be called is based on the object being referred to by reference variable.
Class BaseClass
{
Public void show ()
{
Console.WriteLine(“From base class show method”);
}
}
Public Class DynamicDemo : BaseClass
{
Public void show()
{
Console.WriteLine(“From Derived Class show method”);
}
Public static void main(String args[])
{
DynamicDemo dpd=new DynamicDemo ();
Dpd.show();
}
}
Here memory allocation will be at the run time for that particular method.
What is Virtual Function: They implement the concept of polymorphism are the same as in C#, except that you use the override keyword with the virtual function implementation in the child class. The parent class uses the same virtual keyword. Every class that overrides the virtual method will use the override keyword.
Why to use them:
1)    It is not compulsory to mark the derived/child class function with Override KeyWord while base/parent class contains a virtual method
2)    Virtual methods allow subclasses to provide their own implementation of that method using the override keyword
3)    Virtual methods can’t be declared as private.
4)    You are not required to declare a method as virtual. But, if you don’t, and you derive from the class, and your derived class has a method by the same name and signature, you’ll get a warning that you are hiding a parent’s method
5)    A virtual property or method has an implementation in the base class, and can be overriden in the derived classes.
6)    We will get a warning if we won’t use Virtual/New keyword.
7)    Instead of Virtual we can use New Keyword
class A 
{
public void M()
{
Console.WriteLine(“A.M() being called”);
}
public virtual void N()
{
Console.WriteLine(“A.N() being called”);
}
}

class B : A
{
public new void M()
{
Console.WriteLine(“B.M() being called”);
}
public override void N()
{
Console.WriteLine(“B.N() being called”);
}
}
say
A a = new B();
a.M();
a.N();
The results would be
A.M() being called
B.N() being called


Override Keyword: method overriding is modifying or replacing the implementation of the parent class with a new one. Parent classes with virtual or abstract members allow derived classes to override them.
class Shape
{
    public virtual void Draw()
    {
        Console.WriteLine(“Shape.Draw”)    ;
    }
}

class Rectangle : Shape
{
    public override void Draw()
    {
        Console.WriteLine(“Rectangle.Draw”);
    }
}

 à¤šीयर्स जय हो.............

Saturday, 21 April 2012

Script for swapping gender in sql server through CASE (Without Using Cursor)

I want to share you my experience about an interview where i was asked to write script for swapping gender using cursor and without using cursor.
   In my previous post I mentioned "Script for swapping gender through CURSOR" .Here I am sharing swapping gender using CASE .

Step 1: First of all create a table ,here "genderTest"

            CREATE TABLE  genderTest (id nvarchar(20) PRIMARY KEY,empname nvarchar(20),gender           nvarchar(1))

Step 2:Now insert some values in table

       INSERT INTO gendertest VALUES ('1','ashish','m')
       INSERT INTO gendertest VALUES ('2','nandani','f')
       INSERT INTO gendertest VALUES ('3','chandrasen','m')
       INSERT INTO gendertest VALUES ('4','priynka','f')
       INSERT INTO gendertest VALUES ('5','sonam','f')
       INSERT INTO gendertest VALUES ('6','guru','m')
       INSERT INTO gendertest VALUES ('7','faisal','m')
       INSERT INTO gendertest VALUES ('8','priti','f')

Step 3: Execute following query


           UPDATE genderTest 
           SET gender=
          CASE gender
          WHEN 'f' THEN 'm'
          ELSE 'f'
      END
   Above script will swap gender.
    





Thursday, 19 April 2012

Technology Improver: Script for swapping gender in sql server through Cursor

Script for swapping gender in sql server through Cursor


Step 1:Create table ,here GenderConvert

--create table GenderConvert(name nvarchar(20),gender nvarchar(1))

Step 2:Insert some Values in table

--insert into genderconvert values('ashish', 'm' )
--insert into genderconvert values('sonam', 'f' )
--insert into genderconvert values('priya', 'f' )
--insert into genderconvert values('sonu', 'm' )
--insert into genderconvert values('anshu', 'f' )
--insert into genderconvert values('vinod', 'm' )
--insert into genderconvert values('pappu ', 'm' )
--insert into genderconvert values('pavan', 'm' )
--insert into genderconvert values('priynka', 'f' )
--insert into genderconvert values('sonali', 'f' )
--insert into genderconvert values('guru', 'm' )

STEP 3:Execute this script (cursor)

DECLARE @name nvarchar(20)
DECLARE @gender nvarchar(1)
DECLARE @MyCursor CURSOR

--Assign table to cursor variable

SET @MyCursor = CURSOR FOR 
SELECT name,gender FROM genderconvert

--Open cursor

OPEN @MyCursor

--Fetch cursor row on by one

FETCH NEXT 
FROM @MyCursor INTO @name , @gender
WHILE @@FETCH_STATUS = 0
BEGIN

if(@gender = 'm' )
BEGIN 
UPDATE  genderconvert set gender='f' where name=@name
END
else
BEGIN 
UPDATE  genderconvert set gender='m' where name=@name
END
FETCH NEXT FROM @MyCursor into  @name,@gender
END


           After executing this script gender will be swap.Please give your valuable review if this post helpful for you.

Thursday, 19 January 2012

CURSOR IN SQL SERVER 2008


-----------     Cursor    -------------------
            Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.
            Some time we need to do operation on a particular row of result set or small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.
           
To use cursors in SQL procedures, you need to do the following:
1:Declare a cursor that defines a result set.
2:Open the cursor to establish the result set.
3:Fetch the data into local variables as needed from the cursor, one row at a time.
4:Close the cursor when done
5:Deallocate the cursor




1:DECLARE CURSOR(T-SQL)
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

LOCAL:
         Scope of Local cursor is only to batch , stored procedure , or trigger in   which it is declared. And LOCAL cursor automatically deallocated when container block ,sp or trigger terminated.



GLOBAL:
         Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is     onl   implicitly    deallocated at disconnect.

FORWARD_ONLY

          - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. It is the fastest cursor that you can update.
             The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor.                                                                
 Note: If cursor is not declared forward-only OR scroll  , then by default it is forward-only.
STATIC: Defines a cursor that makes a temporary copy of the data in tempdb to be used by the cursor. Therefore modification made to base table does not reflect to temporary table , and this cursor does not allow modification.
DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The FETCH ABSOLUTE option is not supported with dynamic cursors.
FAST_FORWARD: Specify forward-only , read-only cursor. FAST_FORWARD can not be applied if SCROLL or  FOR_UPDATE is specified.
READ_ONLY:  Prevent updates made through this cursor.
SCROLL_LOCK: specify that updation  or deletion made through this cursor guarantee to succeed. Because SQL server lock the corresponding row.  
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor.
TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.

FOR UPDATE [OF column_name [,...n]]
            Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.


2:OPEN   CURSOR 
Syntax for opening cursor is
Open <cursor_name>
3:FETCH ROW FROM CURSOR
FETCH [NEXT | PRIOR | FIRST | LAST |ABSOLUTE{n}|RELATIVE{n}]                                                                      [FROM]    <cursor_name>   [INTO @variable_name[1,2,…n variable]]
Let us define the terms used in syntax---
NEXT: Returns the immediate  next row .And increment current row pointer to  that row.
PRIOR: Returns the immediate  previous row .And increment current row pointer to  that row. 
FIRST: Returns the first row in the cursor and makes it the current row.
LAST: Returns the last row in the cursor and makes it the current row.
ABSOLUTE{ n}          :Return nth row of result set. If n is positive, counting start from top of result set .If n is negative , counting start from bottom of result set. In both situation returned row is made as new current row. If n is 0 no row is returned.
REALTIVE { n }: Same           functionality as absolute , but only difference is that counting start from current row ,forward of backward ,according to sign of n.                                               
NOTE:
J If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.
JIf DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.
JDYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.
4: CLOSE CURSOR
 Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE is not allowed on cursors that have only been declared or are already closed.

SYNTAX:
       CLOSE   <cursor_name>

5: DEALLOCATE CURSOR
Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server.
SIMPLE EXAMPLE:
DECLARE @a  nvarchar(50)
DECLARE @myCursor  CURSOR
SET  @myCursor=CURSOR FOR SELECT TOP 10  propertyid FROM properties

OPEN @myCursor
FETCH NEXT
FROM  @myCursor into @a
WHILE @@FETCH_STATUS =0
BEGIN
PRINT @a
FETCH NEXT
FROM  @myCursor into @a
END
CLOSE @myCursor
DEALLOCATE @myCursor





WHAT @@FETCH_STATUS DO HERE?
            @@FETCH_STATUS              is called cursor function, which is in build function.
It return the status  of last cursor FETCH statement.
If it is 0; the FETCH statement was successful
If it is -1; failed or row was beyond the result set
If it is -2;the row fetched is missing