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%'