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

Sql Query for Sql Server 2008R2 (Create,Alter,Drop,Insert,Update,Delete,Alias with Select,Top,Like,Order By, Aggregate Function etc

1.Create Database SqlDatabase 

    

    Create Database SqlDatabase

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

 2.Create table Companyinformatio where Srno is  auto increment and primary key and       

field EmailId  is  unique............ using database SqlDatabase

 use SqlQuery

create table Companyinformation (SrNo int IDENTITY(1,1) primary key,Name    nvarchar(50),FatherName nvarchar(50),EmailId nvarchar(50) unique,Age int,Salary int,ContactNo nvarchar(50))

After Creating above  table Companyinformation you can see design  of  table  

exec sp_columns Companyinformation

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

insert data in  table Companyinformation 

insert into Companyinformation values('naveen Yadav','Mr.Yadav','naveen@gmail.com',24,12000,'9718429281')

insert into Companyinformation values('Rohit ','Jai  kumar','rohit@gzpmail.com',34,2000,'9458668577')

insert into Companyinformation values('Rahul ','Mr.O P Shiv','rahul@gmail.com',21,1000,'7503726411')

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

select data from  table Companyinformation

Select * from Companyinformation

Select * from Companyinformation where SrNo=5

Select * from Companyinformation where FatherName='Jai kumar'

Select * from Companyinformation where Name='Naveen Yadav'

*  means select all data field  from table Companyinformation

Select Name,FatherName,EmailId from Companyinformation where Salary=1000

select command  with selected record for example Name,FatherName,EmailId only show

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

Select record from table Alias Companyinformation using  as Keyword:

select SrNo as SerialNo,Name as Emp_Name,FatherName as Emp_FatherName,EmailId as Emp_EmailId,Age as Emp_Age,Salary as MonthlySalary,ContactNo as Emp_ContactNo from Companyinformation

select command  with Alias for  example SrNo as SerialNo

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

update data from  table Companyinformation  which SrNo=2

Update CompanyDetails set Name='Rohit singh',FatherName='Jai kumar',EmailId='rohit@gmail.com',Age=20,Salary=21,ContactNo='9871234511' where SrNo=2

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

Detete data from  table Companyinformation  which SrNo=14

Delete from Companyinformation where SrNo=14

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

select data from  table Companyinformation  with help and,between,in keyword

Select * from Companyinformation  where age>20 and age<=70

or

Select * from Companyinformation  where age between 10 and 50

Select * from Companyinformation  where (age=21 or age=70)

or

Select * from Companyinformation  where age in(21,70)

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

select data from  table Companyinformation  with using  Like

keyword

select * from Companyinformation where Name Like 'R%'      Start with R

select * from Companyinformation where Name Like '%i'      End with i

select * from Companyinformation where Name Like 'R---'   Start with R and CharLen=4

select * from Companyinformation where Name Like '[a-m]%'  

Start with a to m and CharcherLength is  no matter

select * from Companyinformation where Name not Like 'R%'     Not Start with R 

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

aggregate functions in sql :

  

   1. Max

        2.Min

        3.Count

        4.Sum

        5. Avg

Select  maximum salary from table Companyinformation

Select Max(Salary) from Companyinformation

Select 2^nd highest maximum salary from table Companyinformation

Select Max(Salary) from Companyinformation where Salary < (Select Max(Salary) from Companyinformation)

Select  minimum salary from table Companyinformation

Select Min(Salary) from Companyinformation

Select 2^nd highest minimum salary from table Companyinformation

Select Min(Salary) from Companyinformation where Salary > (Select Min(Salary) from Companyinformation)

Find Avg salary from table Companyinformation

Select Avg(Salary) from Companyinformation

Find Total salary from table Companyinformation

Select Sum(Salary) from Companyinformation

Find Count Name from table Companyinformation

Select Count(Name) from Companyinformation

Find Total salary,No of Emp,Average of Salary Companyinformation using Sum,Count,Avg  keyword

Select Total=Sum(Salary),Count=Count(Name),Average=Avg(Salary) from Companyinformation

Note In aggregate functions left Count each keyword apply on  only those field which have must int Property Means   max,min,sum,avg are numeric  keyword

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

Select record from table Companyinformation using order by clause:

Select * from Companyinformation order by salary    by default asending order

Select * from Companyinformation order by salary desc

Select * from Companyinformation order by salary asc ,name desc

salary with asending and  name with desc order

Select * from Companyinformation order by 5

where 5  is  index no table which is  age

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

Select record from table Companyinformation using top Keyword:

Select Top(5) * from Companyinformation

Select Top(5) * from Companyinformation order  by age desc

Select Top (7) Name,FatherName from Companyinformation

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

How to  add other column in  table Companyinformation  with using  alter

keyword

use SqlDatabase

alter table Companyinformation add CompanyName nvarchar(50) null

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

How to  delete   table Companyinformation   from  database SqlDatabase

use SqlDatabase

drop table Companyinformation

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

Select record

 select SrNo,[EmailId_To]= substring ([EmailId_To],1,10),[Subject]=SUBSTRING([Subject],1,10),[Message]= substring([Message],1,10),Attach_File,
convert(varchar,DateTime,106)as DateTime  from Email_Message
where EmailId_To like '%sunil@gmail.com%'