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

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

v  print getdate()                                  Aug  4 2014 11:31PM

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

          

v  Select Cast(Sysdatetime() as Date)     2014-08-04,

v  Select convert(date,Sysdatetime())     2014-08-04,

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

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

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

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

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

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

 Create Database SqlDatabe

 Create Database Sqldatabase

  Create table  student  using Database  Sqldatabase

  use  Sqldatabase

  create table student (Id int ,item int)

  insert item in student table

  insert into student values(2,49)

 insert into student values(1,55),(3,44),(4,45)  

  select * from student

 select id ,Sum(item) as ToalItem from student 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) as totalitem from student group by id having sum(item)>=45

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

 Create table  Stu_Dob  using Database SqlDatabase

  use SqlDatabase

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

  Insert data into table  Stu_Dob 

  insert into Stu_Dob values(1,'sunil kumar','08/10/1990'),(2,'Naveen kumar','07/1/1985'),(3,'mohit singh','11/2/1996'),(4,'rohit kumar','5/10/1990')

  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)=8

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

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

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:1Select Id,Age_Type=case when Age>40 then 'Old' else 'New' end from Emp

Case:2update 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