Difference between correlated subqueries and uncorrelated subqueries

Uncorrelated Subquery

If the SQL above looks scary to you, don’t worry – it’s still easy to understand for our purposes here. The subquery portion of the SQL above begins after the “NOT IN” statement. The reason that the query above is an uncorrelated subquery is that the subquery can be run independently of the outer query. Basically, the subquery has no relationship with the outer query.

Example:

Select  * from salesperson where salesperson.ID NOT IN (Select o.id from orders o,customer c where o.id=c.id)

Correlated Subquery

Now, a correlated subquery has the opposite property – the subquery can not be run independently of the outer query. You can take a look at this example of a correlated subquery below and easily see the difference yourself

Example:

Select * from  Employee e where (1)=(select count (distinct(e.salary)) from employee e1 where e1.salary>e.salary)

What you will notice in the correlated subquery above is that the inner subquery uses e.Salary, but the alias e is created in the outer query. This is why it is called a correlated subquery, because the subquery references a value in it’s WHERE clause (in this case, it uses a column belonging to e) that is used in the outer query.

Example

create table ##Emptemp(id int identity(1,1),Name Varchar(50),Dept varchar(50)) 

insert into ##Emptemp (name,dept) values ('Sendhil','ECE')

insert into ##Emptemp (name,dept) values ('Kumar','ECE')

insert into ##Emptemp (name,dept) values ('ARUN','EEE')

create table ##Deptemp(id int identity(1,1),DEP_Name Varchar(50)) 

insert into ##Deptemp (DEP_Name) values ('ECE')

insert into ##Deptemp (DEP_Name) values ('EEE')

select *  from ##Emptemp

select *  from ##Deptemp

Correlated Query:

select DEP_Name,(select COUNT(*) from ##Emptemp e where e.Dept=d.DEP_Name) TotalCount from ##Deptemp d

Uncorrelated Query:

select * from ##Emptemp e where e.Dept in (select d.DEP_Name  from ##Deptemp d )

Enter Username And Password Using VBscript

Call the Vbscript file using batch file

Batch File :

REM :Start
Pause
call  "C:\Users\sendhil\Desktop\Test\test2.vbs"
Pause

VBscript Code :

WScript.Quit Main

Function Main
  Set IE = WScript.CreateObject("InternetExplorer.Application", "IE_")
  IE.Visible = True
  IE.Navigate "http://default/Login.aspx"
  Wait IE
  With IE.Document
    .getElementByID("txtusername").value = "admin1"
    .getElementByID("txtpwd").value = "XXXX"
    .getElementsByName("btnok").Item(0).Click


  End With

 
End Function

Sub Wait(IE)
  Do
    WScript.Sleep 500
  Loop While IE.ReadyState < 4 And IE.Busy
  Do
    WScript.Sleep 500
  Loop While IE.ReadyState < 4 And IE.Busy
End Sub

Sub IE_OnQuit
  On Error Resume Next
  WScript.StdErr.WriteLine "IE closed before script finished."
  WScript.Quit
End Sub

ViewData VS ViewBag Vs TempData in MVC

ViewData and ViewBag are used for the same purpose to transfer data from controller to view. Both life lies only in current request. ViewData is nothing but dictionary of object and it is accessible by string as key. ViewData is property of controller that exposes an instance of the ViewDataDictionary class. ViewBag is very similar to ViewData. ViewBag is a dynamic property (dynamic keyword which is introduced in .net framework 4.0). ViewBag is able to set and get value dynamically and able to add any number of additional fields without converts it to strongly typed. ViewBag is just a wrapper around the ViewData.

 
ViewData Example

//Controller Code

public ActionResult Index()

{

      List<string> Student = new List<string>();

      Student.Add("Jignesh");

      Student.Add("Tejas");

      Student.Add("Rakesh");

      ViewData["Student"] = Student;

      return View();

}

//page code

<ul>

    <% foreach (var student in ViewData["Student"] asList<string>)

        { %>

    <li><%: student%></li>

    <% } %>

</ul>
 ViewBag Example

//Controller Code

public ActionResult Index()

{

      List<string> Student = new List<string>();

      Student.Add("Jignesh");

      Student.Add("Tejas");

      Student.Add("Rakesh");

      ViewBag.Student = Student;

      return View();

} 

//page code

<ul>

    <% foreach (var student in ViewBag.Student)

        { %>

    <li><%: student%></li>

    <% } %>

</ul>
 
TempData is a dictionary which is derived from TempDataDictionary class. TempData is stored data just like live session for short time. TempData Keep data for the time of HTTP Request it mean that it hold data between two consecutive requests. TempData help us to transfer data between controllers or between actions. TempData internally use Session variables. Note that TempData is only work during the current and subsequent request. It is generally used to store one time message. With the help of TempData.Keep() method we can keep value in TempData object after request completion.
 TempData Example
 

//Controller Code

public ActionResult Index()

{

    List<string> Student = new List<string>();

    Student.Add("Jignesh");

    Student.Add("Tejas");

    Student.Add("Rakesh");

    TempData["Student"] = Student;

    return View();

}

//page code

<ul>

    <% foreach (var student in TempData["Student"] asList<string>)

        { %>

    <li><%: student%></li>

    <% } %>

</ul>

ViewData VS ViewBag VS TempData
 

ViewData	ViewBag	TempData
It is Key-Value Dictionary collection	It is a type object	It is Key-Value Dictionary collection
ViewData is a dictionary object and it is property of ControllerBase class	ViewBag is Dynamic property of ControllerBase class.	TempData is a dictionary object and it is property of controllerBase class.
ViewData is Faster than ViewBag	ViewBag is slower than ViewData	NA
ViewData is introduced in MVC 1.0 and available in MVC 1.0 and above	ViewBag is introduced in MVC 3.0 and available in MVC 3.0 and above	TempData is also introduced in MVC1.0 and available in MVC 1.0 and above.
ViewData  is also work with .net framework 3.5 and above	ViewBag  is only  work with .net framework 4.0 and above	TempData  is also work with .net framework 3.5 and above
Type Conversion code is required while enumerating	In depth, ViewBag is used dynamic, so there is no need to type conversion while enumerating.	Type Conversion code is required while enumerating
It value become null if redirection is occurred.	Same as ViewData	TempData is used to pass data between two consecutive requests.
It lies only during the current request.	Same as ViewData	TempData is only work during the current and subsequent request

Event bubbling in Asp.Net

Handling(tracking) child controls events (only button type child controls) through parent control in data bind controls known as event bubbling.

Server controls like Datagrid, DataList, and Repeater can have other child controls inside them.

Example DataGrid can have combo box inside datagrid. These child control do not raise there events by themselves, rather they pass the event to the container parent (which can be a datagrid,
datalist, repeater), which passed to the page as "ItemCommand" event. As the child control send events to parent it is termed as event bubbling.

CODE:
Basically, to raise the event that you want bubbled up:

RaiseBubbleEvent(this, args);
And then to catch it:

protected override bool OnBubbleEvent(object source, EventArgs e) {
    bool handled = false;

    if (e is TemplatedListCommandEventArgs) {
        TemplatedListCommandEventArgs ce = (TemplatedListCommandEventArgs)e;

        OnItemCommand(ce);
        handled = true;
    }
    return handled;
}
As the code implies, if this method returns false, the event will continue to bubble up the control hierarchy

LINK

Virtual vs Override vs New Keyword in C#

Virtual Keyword

Virtual keyword is used for generating a virtual path for its derived classes on implementing method overriding. Virtual keyword is used within a set with override keyword. It is used as:

// Base Class
    class A
    {
        public virtual void show()
        {
            Console.WriteLine("Hello: Base Class!");
            Console.ReadLine();
        }
    }


Override Keyword

Override keyword is used in the derived class of the base class in order to override the base class method. Override keyword is used with virtual keyword, as:

// Base Class
    class A
    {
        public virtual void show()
        {
            Console.WriteLine("Hello: Base Class!");
            Console.ReadLine();
        }
    }

// Derived Class

    class B : A
    {
        public override void show()
        {
            Console.WriteLine("Hello: Derived Class!");
            Console.ReadLine();
        }
    }

New Keyword

New keyword is also used in polymorphism concept, but in the case of method overloading So what does overloading means, in simple words we can say procedure of hiding your base class through your derived class.

It is implemented as:

class A
    {
        public void show()
        {
            Console.WriteLine("Hello: Base Class!");
            Console.ReadLine();
        }
    }

    class B : A
    {
        public new void show()
        {
            Console.WriteLine("Hello: Derived Class!");
            Console.ReadLine();
        }
    }


Example:

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

namespace Generics
{
    class A
    {
        public void show()
        {
            Console.WriteLine("Hello: Base Class!");
            Console.ReadLine();
        }
    }

    class B : A
    {
        public void show()
        {
            Console.WriteLine("Hello: Derived Class!");
            Console.ReadLine();
        }
    }

    class Polymorphism
    {
        public static void Main()
        {
            A a1 = new A();
            a1.show();
            B b1 = new B();
            b1.show();
            A a2 = new B();
            a2.show();

        }
    }
}


Output :
Hello: Base Class!
Hello: Derived Class!
Hello: Base Class!

NULLIF Function in sql

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASEstatement:

SELECT CASE ("column_name")
  WHEN "expression 1 = expression 2 " THEN "NULL"
  [ELSE "expression 1"]
  END
FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

Store_Name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;

Result:

Store_Name	NULLIF (Actual, Goal)
Store A	NULL
Store B	40
Store C	25

Delete duplicate rows in sql using Single query

To delete the Newly inserted Duplicate record :

create table ##temp (id int identity(1,1) primary key  ,name varchar(100) )

insert into ##temp (name) values ('Kumar')
select * from ##temp


insert into ##temp (name) values ('Kumar')
select * from ##temp






Delete a from ##temp a,##temp  b where a.id>b.id and a.name=b.name
select * from ##temp

To delete the Old inserted Duplicate record :

create table ##temp (id int identity(1,1) primary key  ,name varchar(100) )

insert into ##temp (name) values ('Kumar')
select * from ##temp


insert into ##temp (name) values ('Kumar')
select * from ##temp






Delete a from ##temp a,##temp  b where a.id<b.id and a.name=b.name
select * from ##temp