Sql Query (Date,Distinct,Constraint,When,Group By,Having Clause)

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

How to get  Date from sql Sever  in  different  way………………………………………………….

v  print getdate()                                  Aug  3 2012 11:31PM

v  select sysdatetime()                          2012-08-03 23:31:11.6113785 

          

v  Select Cast(Sysdatetime() as Date)     2012-08-03,

v  Select convert(date,Sysdatetime())     2012-08-03,

v  Select Datepart("yy",getdate())           2012

v  Select Datepart("mm",getdate())          8

v  Select Datepart("dd",getdate())           3

v Select Datepart("yyyy",getdate())        2012

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

  Use Group By in Sql Server.....................................


 Create Database SqlQuery

 Create Database SqlQuery

  Create table  items  using Database SqlQuery

  use SqlQuery

  create table items (Id int ,item int)

  insert item in items table

  insert into items values(2,49)

  select * from items

    select id ,Sum(item) as ToalItem from items group by id

Note: where clause is not support for  filtering data from table with aggregate function, it provides  by Having clause in sql server

select id ,sum(item) from items group by id having sum(item)>=20

  How to get difference  dob with current date…………….with using

 Create table  Stu_Dob  using Database SqlQuery

  use SqlQuery

  create table Stu_Dob (Id int,Name nvarchar(50) ,dob date)

  Insert data into table  Stu_Dob 

  insert into Stu_Dob values(3,'Udayan Sir','04/23/1985')

  Select data from table  Stu_Dob

 How to calculate  age  with help date of birth current with using  datediff(),getdate() function

  select id,Name,Age=datediff("yy",dob,getdate()) from Stu_Dob

    select id,Name from Stu_Dob where month(dob)=4

     select id,Name from Stu_Dob where year(dob)=1987

     select id,Name from Stu_Dob where day(dob)=11

Problem :   suppose  we  have  a table  of  Emp  where  three  field  are  labled as follows

             a.    Id

             b.    Gender

             c.    Age

v  If the age is above 40 it will be replaced  by ‘Old’  otherwise  ‘New’

v  If  the gender   is  ‘Male’  the  query  will replace it  by  ‘Femail’  

v  When  we executed  this  query  reputation of age is  remove by  distinct keyword

First  we create  a table Emp inside  database  test  apply  CONSTRAINT  key word on Gender     you can only insert 'Male','Female'  after applying constraint  

use test

create table Emp(Id int  IDENTITY(1,1) ,Gender nvarchar(10)CONSTRAINT Gender_CONSTRAINT CHECK (Gender IN ('Male','Female')),Age int)

insert record in table Emp

insert into Emp values('Male',65)

select record from table Emp

Select * from Emp

Case:1

Select Id,Age_Type=case when Age>40 then 'Old' else 'New' end from Emp

Case:2

update Emp set Gender=case when Gender='Male' then 'Female' else 'Male' end

Case:3

Reputation  will be  removed  by  distinct keyword in Sql Server

Select distinct age from Emp

  

Read More

Constructor in C# with Example

What   is   Constructor: A special method of the class that will be automatically invoked when an instance of the class is created is called as constructor. It is special type function having following characteristics. 

1.    Name  of constructor should be same name of  class

2.    It  is  used to initialized  Data Member of Class

3.    Constructor  have no return type

4.    Automatic calling at the time of object creation

5.    Constructor  always return an object or  a memory  block, or we can say that Constructor behaves likes factor to produce  object

     

Types of   Constructor:

1. Default Constructor
2. Parameterized Constructor
3. Copy Constructor
4. Static Constructor
5. Private Constructor

Default Constructor: 

A constructor without any parameters is called as default constructor. Drawback of default constructor is every instance of the class will be initialized to same values and it is not possible to initialize each instance of the class to different values.

Parameterized Constructor:

 A constructor with at least one parameter is called as parameterized constructor. Advantage of parameterized constructor is you can initialize each instance of the class to different values.

Copy Constructor: 

A parameterized constructor that contains a parameter of same class type is called as copy constructor. Main purpose of copy constructor is to initialize new instance to the values of an existing instance.

Static Constructor: 

You can create a constructor as static and when a constructor is created as static, it will be invoked only once for any number of instances of the class and it is during the creation of first instance of the class or the first reference to a static member in the class. Static constructor is used to initialize static fields of the class and to write the code that needs to be executed only once.

Private Constructor: 

You can also create a constructor as private. When a class contains at least one private constructor, then it is not possible to create an instance for the class. Private constructor is used to restrict the class from being instantiated when it contains every member as static. 

First   we create  a class……………Student.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace Construstor

{

    class Student

    {

        public int id;

        public string name;

        //Default Constructor

        public Student()

        {

            MessageBox.Show("Default Constructor is  called");

        }

        //Static Constructor

        static Student()

        {

            MessageBox.Show("Static  Constructor is  called");

        }

        //Parameterized Constructor

        public Student(int i, string fn)

        {

            id = i;

            name = fn;

          MessageBox.Show("Paramerizied Constructor:" + " " + id.ToString() + " " + name);

        }

       

        //private Student()

        // {

        // }

        //Copy Constructor

        public Student(Student st1)

        {

            id = st1.id;

            name = st1.name;

            MessageBox.Show(id.ToString() + " " + name);

        }

    }

}

After   we  take  a  windows form and  taking 5  button  for  each constructor ……..

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace Construstor

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        //Invoking Default Constructor

        private void bDefault_Click(object sender, EventArgs e)

        {

            Student st = new Student();

        }

        //Invoking Parameterized Constructor

        private void bParameterized_Click(object sender, EventArgs e)

        {

            Student st = new Student(26, "Kush Tiwari");

        }

        private void bPrivate_Click(object sender, EventArgs e)

        {

        }

        private void bCopy_Click(object sender, EventArgs e)

        {

         

         

            Student st = new Student(11, "Udayan Sir");

            //Invoking copy constructor

            Student st1 = new Student(st);

        }

        private void bStatic_Click(object sender, EventArgs e)

        {

            //Static Constructor and instance constructor, both are invoked for first instance.

            Student st = new Student();

            //Only instance constructor is invoked.

            Student st1 = new Student();

        }

    }

}

Read More