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

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

03:28    2 comments

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

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

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

11:20    No comments

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

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