Counting null and non-null values in SQL Server

Counting null and non-null values in a single query

--create a table

create table employee(

emp_name varchar(50),

salary int)

--insert data in table

insert into employee values ('Rohit',10000)

insert into employee values ('Rahul',NULL)

insert into employee values ('Ravi',NULL)

--To see the data

select * from employee

--count null and not null value

SELECT count(*) FROM employee WHERE salary IS NULL

UNION ALL

SELECT count(*) FROM employee WHERE salary IS NOT NULL 

************************************************************      

                                            or

************************************************************

SELECT COUNT(*), 'null_tally' AS narrative 

  FROM employee 

 WHERE salary IS NULL 

UNION

SELECT COUNT(*), 'not_null_tally' AS narrative 

  FROM employee 

 WHERE salary IS NOT NULL;

How To Delete a null record in SQL Server

How To Delete a null record

--create a table

create table employee(

emp_name varchar(50),

salary int)

--insert data in table

insert into employee values ('Rohit',10000)

insert into employee values ('Rahul',NULL)

insert into employee values ('Ravi',NULL)

--To see the data

select * from employee

--delete all null record

delete employee where salary=null

--result after above code - (0 row(s) affected)

*******************************************************

--for delete null record we use 'is'

delete employee where salary is null

--result after above code - (2 row(s) affected)

Self-join in SQL Server 2005-08

Self-join in SQL Server 2005-08

A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.

For self join in sql you can try the following example:

Create table employees:

emp_id	emp_name	emp_manager_id
1	John	Null
2	Tom	1
3	Smith	1
4	Albert	2
5	David	2
6	Murphy	5
7	Petra	5

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:

manager	employee
John	Tom
John	Smith
Tom	Albert
Tom	David
David	Murphy
David	Petra

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Singleton class in C#

Singleton in C#

The Singleton pattern ensures that a class only has one instance and provides a global point of access to it from a well-known access point. The class implemented using this pattern is responsible for keeping track of its sole instance rather than relying on global variables to single instances of objects.


Examples :

using System;

public class Singleton
{
   private static Singleton instance;

   private Singleton() {}

   public static Singleton Instance
   {
      get 
      {
         if (instance == null)
         {
            instance = new Singleton();
         }
         return instance;
      }
   }
}