Friday 3 August 2012

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






  



1 comments: