Sql Server Data Manipulation Language(DML) and Data Definition Language(DDL)

Data Manipulation Language (DML)
It is used to insert, delete, modify and retrieve data in database.
example : select, update, insert , delete statement

Select Statement
It is used to retrieve one or more rows from the table in database.
example 1: return all the rows from the table

select * from employeeMaster

example 2: return particular rows from table

select * from employeeMaster where Salary>35000

Insert statement
It is used to insert one or more rows in the table.
example 1 : Inserting single row

insert into employeeMaster (EmpName,DesignationId,LocationId,Salary)

Values('Tanuj', 3,3 ,67000)

select * from employeeMaster where EmpName='Tanuj'

example 2 : Inserting multiple rows

insert into employeeMaster (EmpName,DesignationId,LocationId,Salary)

Values('Swapnil', 3,3 ,57000),('Rahul',1,1,45000)

select * from employeeMaster where EmpName in ('Swapnil','Rahul')

Update Statement
It is used to update data of the table
example 1: update employeeMaster set Salary=50000 where EmpName in ('Swapnil','Rahul')

select * from employeeMaster where EmpName in ('Swapnil','Rahul')

Delete Statement
It is used to delete one or more rows from the table in database.

delete from employeeMaster where EmpName in ('Swapnil','Rahul')

select * from employeeMaster where EmpName in ('Swapnil','Rahul')
No rows will be returned

Data Definition Language
It is used to create, modify and remove the structure of database objects.
Example : Create, Alter and Drop statement

Create statement 
It is used to create the structure of table, function, procedures, user defined table type etc.

Create table test

(

testid int,

testName varchar(50)

)

to see the structure :sp_help test

Alter Statement

It is used to alter the structure of table, function, procedures etc.

example:

alter table test add testDate datetime

to see the structure :sp_help test

drop column: alter table test drop column testDate

Modify column: alter table test alter column testname varchar(100)

Drop Statement

It is used to drop the structure and data.

example : drop table test

Read More

Concatenate column values into single row using stuff and xml path

Stuff
It is used to insert a string into another string, It removes specified length of characters from the start of the string and then add second string at the start in the first string.

syntax:
stuff(expression, start, length, replace with these string)

Example

select stuff('SQL Server',5,6,'Database')

Output: 

XML Path()

Here it is used to convert column data into single row

Concatenate column values into single row

Suppose we have a situation in which we need to show column values concatenated on the basis of aggregate function applied on them.

for example:
show all the employee name whose salary sum is combined on the basis of there location and designation.

Creating tables

1. EmployeeMaster

2. EmployeeLocationDetails

3. EmployeeDesignationDetails

create table employeeMaster

(

EmpId int primary key,

EmpName varchar(100),

DesignationId int,

LocationId int,

Salary int

)

Create table employeeDesignationDetails

(

DesignationId int primary key identity(1,1),

DesignationName varchar(100)

)

Create table employeeLocationDetails

(

LocationId int primary key identity(1,1),

LocationName varchar(100)

)

 inserting into Tables

insert into employeeLocationDetails(LocationName)

values ('Delhi'),('Banglore')

insert into employeeDesignationDetails(DesignationName)

values ('Developer'),('Designer'),('Manager')

insert into employeeMaster(EmpName,DesignationId,LocationId,Salary)

values ('Ajay',2,1,45000),('Akhshay',2,1,35000),('Sanjay',1,2,34000),('Tarun',1,2,56000)

,('Anita',1,1,33000),('Aanchal',1,1,28000),('Aaina',2,2,27000),('Preeti',2,2,45000)

,('Rajiv',3,1,46000),('Neeraj',3,1,78000)

For achieving above goal we will be needing a temporary temporary

select a.EmpName,Salary,LocationName,a.LocationId,DesignationName,a.DesignationId into #temp from employeeMaster a

left outer join employeeLocationDetails b on a.LocationId=b.LocationId

left outer join employeeDesignationDetails c on c.DesignationId=a.DesignationId

select

stuff((select ', ' + EmpName from #temp a where a.LocationId=b.LocationId and a.DesignationId=b.DesignationId for xml path('')),1,2,'') as EmpName,

sum(Salary) 'Salary',DesignationName,LocationName

 from #temp b

 group By LocationName,DesignationName,LocationId,DesignationId

Read More

Concatenate column values on which aggregate function implemented in sql server

Suppose we have a situation in which we need to show column values concatenated on the basis of aggregate function applied on them.
for example:
show all the employee name whose salary sum is combined on the basis of there location and designation.

Creating tables

1. EmployeeMaster

2. EmployeeLocationDetails

3. EmployeeDesignationDetails

create table employeeMaster

(

EmpId int primary key,

EmpName varchar(100),

DesignationId int,

LocationId int,

Salary int

)

Create table employeeDesignationDetails

(

DesignationId int primary key identity(1,1),

DesignationName varchar(100)

)

Create table employeeLocationDetails

(

LocationId int primary key identity(1,1),

LocationName varchar(100)

)

 inserting into Tables

insert into employeeLocationDetails(LocationName)

values ('Delhi'),('Banglore')

insert into employeeDesignationDetails(DesignationName)

values ('Developer'),('Designer'),('Manager')

insert into employeeMaster(EmpName,DesignationId,LocationId,Salary)

values ('Ajay',2,1,45000),('Akhshay',2,1,35000),('Sanjay',1,2,34000),('Tarun',1,2,56000)

,('Anita',1,1,33000),('Aanchal',1,1,28000),('Aaina',2,2,27000),('Preeti',2,2,45000)

,('Rajiv',3,1,46000),('Neeraj',3,1,78000)

For achieving above goal we will be needing a temporary temporary table variable

First we need to define the User defined table type that will represent the definition of a table.

CREATE TYPE employeeDetails AS TABLE

(

EmpId int,

EmpName varchar(100),

Salary int,

DesignationId int,

DesignationName varchar(100),

LocationId int ,

LocationName varchar(100)

)

Creating procedure

CREATE proc A_employeeConcatNamefromFunc

as

begin

Declare   @tmp as employeeDetails  --declaring temporary varible of table type

select e1.EmpId,e1.EmpName,e1.Salary,e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

select  sum(e1.Salary) 'Salary',e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp1 from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

group by e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName

insert into @tmp select * from #temp --inserting into temporary varible

drop table #temp --droping temporary table

select dbo.func_EmployeeConcatName(a.DesignationId,a.LocationId,@tmp) as 'EmpName',DesignationName,LocationName,Salary 'Sum of Employee Salary' from #temp1 a

drop table #temp1

end

Creating function

CREATE function func_EmployeeConcatName(@DesignationId int,@LocationId int,@temp employeeDetails ReadOnly)--tempory table variable needs to be defined as read only.

returns varchar(max)

as

begin

declare @Name varchar(max)

select @Name= COALESCE(@Name+',' ,'') + EmpName from @temp where locationid=@LocationId and designationid=@DesignationId

return @Name

end

Read More

Passing temporary variable to function in sql server

First we need to define the User defined table type that will represent the definition of a table.

CREATE TYPE employeeDetails AS TABLE

(

EmpId int,

EmpName varchar(100),

Salary int,

DesignationId int,

DesignationName varchar(100),

LocationId int ,

LocationName varchar(100)

)

Creating tables

1. EmployeeMaster

2. EmployeeLocationDetails

3. EmployeeDesignationDetails

create table employeeMaster

(

EmpId int primary key,

EmpName varchar(100),

DesignationId int,

LocationId int,

Salary int

)

Create table employeeDesignationDetails

(

DesignationId int primary key identity(1,1),

DesignationName varchar(100)

)

Create table employeeLocationDetails

(

LocationId int primary key identity(1,1),

LocationName varchar(100)

)

 inserting into Tables

insert into employeeLocationDetails(LocationName)

values ('Delhi'),('Banglore')

insert into employeeDesignationDetails(DesignationName)

values ('Developer'),('Designer'),('Manager')

insert into employeeMaster(EmpName,DesignationId,LocationId,Salary)

values ('Ajay',2,1,45000),('Akhshay',2,1,35000),('Sanjay',1,2,34000),('Tarun',1,2,56000)

,('Anita',1,1,33000),('Aanchal',1,1,28000),('Aaina',2,2,27000),('Preeti',2,2,45000)

,('Rajiv',3,1,46000),('Neeraj',3,1,78000)

Creating procedure for passing variable to function

create proc A_employeeNamefromFunc

as

begin

Declare   @tmp as employeeDetails  --declaring temporary varible of table type

select e1.EmpId,e1.EmpName,e1.Salary,e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

insert into @tmp select * from #temp --inserting into temporary varible

drop table #temp --droping temporary table

select dbo.func_EmployeeName(1,1,@tmp) as 'EmpName' --executing function

end

Creating function

create function func_EmployeeName(@DesignationId int,@LocationId int,@temp employeeDetails ReadOnly)--tempory table variable needs to be defined as read only.

returns varchar(100)

as

begin

declare @Name varchar(100)

select @Name=  EmpName from @temp where locationid=@LocationId and designationid=@DesignationId and salary>30000

return @Name

end

Executing Proc

executing the above proc will generate the following output

Read More