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

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

  1.Create Database SqlQuery 

    

    Create Database SqlQuery

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

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

field EmailId  is  unique............ using database SqlQuery

 use SqlQuery

create table CompanyDetails (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 CompanyDetails you can see design  of  table  

exec sp_columns CompanyDetails

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

insert data in  table CompanyDetails 

insert into CompanyDetails values('Manoj Yadav','Mr.Yadav','msaroj14@gmail.com',24,12000,'9718429281')

insert into CompanyDetails values('Rohit Tiwari','Jai Prakash Tiwari','rohittiwarig@gzpmail.com',24,22000,'9458668566')

insert into CompanyDetails values('Rahul Sharma','Mr.O P Sharma','rahulgniit11@gmail.com',21,000,'7503726403')

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

select data from  table CompanyDetails

Select * from CompanyDetails

Select * from CompanyDetails where SrNo=5

Select * from CompanyDetails where FatherName='Jai Prakash Tiwari'

Select * from CompanyDetails where Name='Kush Tiwari'

*  means select all data field  from table CompanyDetails

Select Name,FatherName,EmailId from CompanyDetails where Salary=12000

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

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

Select record from table Alias CompanyDetails 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 CompanyDetails

select command  with Alias for  example SrNo as SerialNo

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

update data from  table CompanyDetails  which SrNo=2

Update CompanyDetails set Name='Rohit',FatherName='J.P.Tiwari',EmailId='rohittiwarigzp@gmail.com',Age=20,Salary=21,ContactNo='9455494649' where SrNo=2

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

Detete data from  table CompanyDetails  which SrNo=14

Delete from CompanyDetails where SrNo=14

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

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

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

or

Select * from CompanyDetails  where age between 10 and 50

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

or

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

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

select data from  table CompanyDetails  with using  Like

keyword

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

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

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

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

Start with a to m and CharcherLength is  no matter

select * from CompanyDetails 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 CompanyDetails

Select Max(Salary) from CompanyDetails

Select 2^nd highest maximum salary from table CompanyDetails

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

Select  minimum salary from table CompanyDetails

Select Min(Salary) from CompanyDetails

Select 2^nd highest minimum salary from table CompanyDetails

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

Find Avg salary from table CompanyDetails

Select Avg(Salary) from CompanyDetails

Find Total salary from table CompanyDetails

Select Sum(Salary) from CompanyDetails

Find Count Name from table CompanyDetails

Select Count(Name) from CompanyDetails

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

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

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 CompanyDetails using order by clause:

Select * from CompanyDetails order by salary    by default asending order

Select * from CompanyDetails order by salary desc

Select * from CompanyDetails order by salary asc ,name desc

salary with asending and  name with desc order

Select * from CompanyDetails order by 5

where 5  is  index no table which is  age

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

Select record from table CompanyDetails using top Keyword:

Select Top(5) * from CompanyDetails

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

Select Top (7) Name,FatherName from CompanyDetails

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

How to  add other column in  table CompanyDetails  with using  alter

keyword

use SqlQuery

alter table CompanyDetails add CompanyName nvarchar(50) null

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

How to  delete   table CompanyDetails   from  database SqlQuery

use SqlQuery

drop table CompanyDetails

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

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 '%ramu@gmail.com%'

                                                                                                                                                       Next

Read More