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