Important Topics :
1) Primary key
2) Candidate key
3) Super key
4) Foreign key
5) Constraints
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
6) Joins
Types of join
7) Normalization
8) View
Types of view
9) Procedure
10) Function
Types of Function
11) Trigger
Types of trigger
12) Jobs
13) Cursor
Types of cursor
14) Indexes
Types of indexes
15) Group by
16) Order by
Q1) What is the difference between clustered and a non-clustered index?
Q2) What's the difference between a primary key and a unique key?
Q3) How to implement one-to-one, one-to-many and many-to-many relationships while
designing tables?
Q4) What is a NOLOCK?
Q5) What is difference between DELETE & TRUNCATE commands?
Q6) Difference between Function and Stored Procedure?
Q7) What types of Joins are possible with Sql Server?
Q8) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Q9) What is sub-query?
Q10) What is SQL Profiler?
Q11) What is a Scheduled Jobs or What is a Scheduled Tasks?
Q12) What is a "constraint"?
Q14) How is ACID property related to Database?
Q15) What is a Linked Server?
Q16) What is Collation?
Q17) Query to find Nth highest salary ?
Q18) Query to Delete duplicated rows from table?
Q19) SET ANSI_NULLS ON
Q20) SET QUOTED_IDENTIFIER ON
Q21) SET ANSI_PADDING ON
Q22) SET ANSI_NULLS ON
Q23) SET QUOTED_IDENTIFIER ON
Q24) SET NOCOUNT ON
functions on sql :
len :
select len('aaaa') o/p 4
SubString :
if CHARINDEX('INSERT',@qry) > 0
Check null :
IF @Expression IS NULL OR @Delimiter IS NULL
Get Current Date(in UTC) :
getutcdate()
If in Sql
IF @Expression IS NULL OR @Delimiter IS NULL
While :
WHILE (@IntVar > 0 AND @Var1 < @Var2) OR @ConVar3 = 0
BEGIN
SET @Var1 = @ConVar3
SET @Var2 = @ConVar3
END
Function Returns varchar ;
ALTER FUNCTION [dbo].[fnRetVarchar]
(
@Var1 VARCHAR(max)
, @Var2 VARCHAR(max)
, @Var3 INT
)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @RETURN VARCHAR(max)
SET @RETURN='aaaa'+'bbbb'
Function Example:
IST to UTC
ALTER function [dbo].[FromISTtoUTC] (@IST datetime)
returns datetime
as
begin
return DateAdd(mi,-330,@IST)
end
UDF currency convert to INR
ALTER function [dbo].[UDFConvertToINR] (@amount money,
@roe money) returns money
as
begin
declare @INRAmount money
if(@roe=0.00)
begin
set @INRAmount=@amount
end
else
begin
set @INRAmount=@amount*@roe
end
return @INRAmount
end
Remove Unique constraint from table
SELECT name,* FROM sys.objects
WHERE type = 'UQ'
This will show all unique constraints along with Constraint name now
ALTER TABLE <Table name>
DROP CONSTRAINT <Name from above query>
Add Unique Constraint
ALTER TABLE <Table name>
ADD CONSTRAINT <Any name of Constraint> UNIQUE (<Column name coma separated>)
Function to separate alphabet from alphanumeric string
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
Separate string by coma[Input D001S005P452 Output D001,S005,P452]
DECLARE @a varchar(64)
declare @Str varchar(5)
declare @b int
declare @len int
declare @Count int
set @a = 'D12563S2F3M455'
set @Str= dbo.fn_StripCharacters(@a, '^a-z')
set @len =len(@str)
set @Count=2
while @Count<=@len
begin
set @b = CHARINDEX(SUBSTRING ( @Str ,@Count , 1 ), @a)
set @a= STUFF(@a, @b, 0, ',')
set @Count =@Count+1
end
select @a
Use of Cursor Example
DECLARE @city varchar(50), @source varchar(50),@query varchar(max),@tempCity varchar(50),@Total numeric,
@NoofBook numeric, @NoOfSearch numeric;
CREATE TABLE #LocalTempTable(city varchar(50),TotalSearches numeric,Source varchar(50),TotalBook varchar(5))
set @tempCity='';
DECLARE report_cursor CURSOR FOR
select final1.city,HotelSource.hotelsource as source,final1.NoofBook,final1.NoOfSearch from
(
select sum(NoofSearch) as NoofSearch,sum(NoofBook) as NoofBook,city,source from
(
select null as NoofSearch,count(*) as NoofBook,cityref as city,source from
HotelItinerary where createdon<='2013-03-18 13:19:36.290' and createdon>='2013-03-01 05:15:56.930' and cityref <> '' group by cityref,source
union all
select count(*) as NoofSearch,null as NoofBook,searchcity as city,null as source from
HotelSearchHistory where searchedon<='2013-03-18 13:19:36.290' and searchedon>='2013-03-01 05:15:56.930' group by searchcity
)
as Final group by final.city,final.source
) as Final1
left outer join HotelSource on HotelSource.HotelSourceid=final1.source order by final1.city
OPEN report_cursor
FETCH NEXT FROM report_cursor
INTO @city, @source,@NoofBook,@NoOfSearch
WHILE @@FETCH_STATUS = 0
BEGIN
if @city = @tempCity
begin
insert into #LocalTempTable values('',null,@source,@NoofBook)
if @NoofBook is not null
set @Total=@Total+@NoofBook;
end
else
begin
insert into #LocalTempTable values('',null,'',@Total)
insert into #LocalTempTable values(@city,@NoOfSearch,'Sources','Book')
set @tempCity = @city
set @Total=0;
if @NoofBook is not null
set @Total=@Total+@NoofBook;
end
FETCH NEXT FROM report_cursor
INTO @city, @source,@NoofBook,@NoOfSearch
END
CLOSE report_cursor;
DEALLOCATE report_cursor;
set @query='select * from #LocalTempTable'
delete top(1) from #LocalTempTable
exec(@query)
drop table #LocalTempTable
Parse XML IN SQL
declare @FailedtempTable table (FirstName varchar(50), lastName varchar(50),origin varchar(20),
destination varchar(50),ArrDate varchar(50),DepDate varchar(50),flightNo varchar(5)
)
declare @FirstName varchar(50)
declare @lastName varchar(50)
declare @origin varchar(50)
declare @destination varchar(50)
declare @ArrDate varchar(50)
declare @DepDate varchar(50)
declare @flightNo varchar(50)
DECLARE @Names VARCHAR(max)
Declare @hotels XML
DECLARE report_cursor CURSOR FOR
select requestxml from airfailedbookingstest(nolock) where
source = 'Indigo' and eventtime between DATEADD(day,-90,getdate()) and getdate()
and (remarks like '%Response was null%' or remarks like '%communication%')
order by eventtime desc
OPEN report_cursor
FETCH NEXT FROM report_cursor
INTO @Names
WHILE @@FETCH_STATUS = 0
BEGIN
if @Names is not null
begin
set @Names = replace(@Names,'Web Exception returned from Navitaire Response was null|request xml','')
set @Names = replace(@Names,'<?xml version="1.0" encoding="utf-8"?>','')
set @Names = replace(@Names,'SOAP-ENV:','')
set @Names = replace(@Names,'ns1:','')
set @Names = '<newData>' + @Names +'</newData>'
set @hotels = @Names
DECLARE @docHandle1 int
EXEC sp_xml_preparedocument @docHandle1 OUTPUT, @hotels
select @FirstName=FirstName,@lastName=lastName,@origin=origin,@destination=destination,@ArrDate=ArrDate,@DepDate=DepDate,@flightNo=flightNo
FROM OPENXML (@docHandle1,'/newData/Envelope/Body/bookreservation/input/Reservation',2)
with
(FirstName varchar(50) './PassengerList/item/FirstName' ,
lastName varchar(50) './PassengerList/item/LastName' ,
origin varchar(20) './AirComponents/item/Flights/item/Origin',
destination varchar(50) './AirComponents/item/Flights/item/Destination',
ArrDate varchar(15) './AirComponents/item/Flights/item/ArrivalDateTime' ,
DepDate varchar(50) './AirComponents/item/Flights/item/DepartureDateTime',
flightNo varchar(100) './AirComponents/item/Flights/item/FlightNumber' )
declare @TempDate datetime
set @TempDate=convert(datetime,@DepDate)
if @TempDate>=getdate() and @TempDate<=DATEADD(day,2,getdate())
begin
INSERT INTO @FailedtempTable values(@FirstName,@lastName,@origin,@destination,@ArrDate,@DepDate,@flightNo)
end
end
FETCH NEXT FROM report_cursor INTO @Names
end
CLOSE report_cursor;
DEALLOCATE report_cursor;
select * from @FailedtempTable
Split CSV String
ALTER function [dbo].[FnSplit]
(
@CSVString varchar(8000) ,
@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
declare @i int ,
@j int
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select ltrim(rtrim(substring(@CSVString, @i, @j - @i)))
select @i = @j + len(@Delimiter)
end
return
end
Iterate Rows Using While Loop in SQL
declare @FirstName varchar(50)declare @lastName varchar(50)
declare @origin varchar(50)
declare @destination varchar(50)
declare @ArrDate varchar(50)
declare @DepDate varchar(50)
declare @flightNo varchar(50)
declare @msg varchar(max)
DECLARE @ReqXmlStr VARCHAR(max)
Declare @ReqXML XML
declare @max int --Loop termination point
declare @count int --Use for iteration
declare @FailedBookingIDs varchar(max)=''
declare @temp varchar(10)
select requestxml,failedbookingid,ROW_NUMBER() OVER(ORDER BY eventtime DESC) AS Row into #temp from xyz(nolock)
where status <> 2 and
source = 'Indigo' and eventtime between DATEADD(day,-90,getdate()) and getdate()
and (remarks like '%Response was null%' or remarks like '%communication%')
order by eventtime desc
select @max = max(Row) from #temp
set @count=1;
set @msg='Indigo - Failed Transactions : <br/><br/>';
if @max is not null and @max>0
BEGIN
set @msg='<table border=/"1/"><tr><th>First Name</th><th>Last Name</th><th>Origin</th><th>Destination</th><th>Arrival</th><th> Departure</th><th>Flight Code</th></tr>';
while @count < =@max
begin
select @ReqXmlStr=requestxml,@temp=failedbookingid from #temp where Row=@count
set @count=@count+1;
if @ReqXmlStr is not null
begin
set @ReqXmlStr = replace(@ReqXmlStr,'Web Exception returned from Navitaire Response was null|request xml','')
set @ReqXmlStr = replace(@ReqXmlStr,'<?xml version="1.0" encoding="utf-8"?>','')
set @ReqXmlStr = replace(@ReqXmlStr,'SOAP-ENV:','')
set @ReqXmlStr = replace(@ReqXmlStr,'ns1:','')
set @ReqXmlStr = '<newData>' + @ReqXmlStr +'</newData>'
set @ReqXML = @ReqXmlStr
DECLARE @docHandle1 int
EXEC sp_xml_preparedocument @docHandle1 OUTPUT, @ReqXML
select @FirstName=FirstName,@lastName=lastName,@origin=origin,@destination=destination,@ArrDate=ArrDate,@DepDate=DepDate,@flightNo=flightNo
FROM OPENXML (@docHandle1,'/newData/Envelope/Body/bookreservation/input/Reservation',2)
with
(FirstName varchar(50) './PassengerList/item/FirstName' ,
lastName varchar(50) './PassengerList/item/LastName' ,
origin varchar(20) './AirComponents/item/Flights/item/Origin',
destination varchar(50) './AirComponents/item/Flights/item/Destination',
ArrDate varchar(15) './AirComponents/item/Flights/item/ArrivalDateTime' ,
DepDate varchar(50) './AirComponents/item/Flights/item/DepartureDateTime',
flightNo varchar(100) './AirComponents/item/Flights/item/FlightNumber' )
declare @TempDate datetime
set @TempDate=convert(datetime,@DepDate)
if @TempDate>=getdate() and @TempDate<=DATEADD(day,2,getdate())
--if @TempDate>='2013-06-10 10:50:49.570' and @TempDate<='2013-06-13 10:50:49.570'
begin
set @FailedBookingIDs=@FailedBookingIDs+@temp+',';
set @msg=@msg+'<tr>';
set @msg=@msg+'<td>'+@FirstName+'</td>';
set @msg=@msg+'<td>'+@lastName+'</td>'
set @msg=@msg+'<td>'+@origin+'</td>'
set @msg=@msg+'<td>'+@destination+'</td>'
set @msg=@msg+'<td>'+@ArrDate+'</td>'
set @msg=@msg+'<td>'+@DepDate+'</td>'
set @msg=@msg+'<td>'+@flightNo+'</td>'
set @msg=@msg+'</tr>';
end
end
end
set @msg=@msg+'</table>';
end
else
begin
set @msg=@msg+'No data exist';
end
drop table #temp
update airfailedbookings set status=2 where col1 in (select * from dbo.fnsplit(@FailedBookingIDs,','))
exec msdb.dbo.usp_SendDBMail 'abc@abc.com','subject', @msg
Send Email
ALTER procedure [dbo].[usp_SendDBMail]
(
@cmpEMail varchar(max),
@subject varchar(500),
@messagebody varchar(max)
)
As
Begin
EXEC sp_send_dbmail
@profile_name='abc',
@recipients=@cmpEMail,
@subject=@subject,
@body=@messagebody ,@body_format='HTML'
End
Last Successfull Job Run Time
--manage date rangeDECLARE @jobId binary(16)
declare @rundate int
declare @runtime int
declare @LastRunDate datetime
SELECT @jobId=job_id FROM msdb.dbo.sysjobs WHERE (name = 'test')
select top 1 @rundate=run_date,@runtime=run_time from msdb.dbo.sysjobhistory where job_id =@jobId and run_status=1 order by instance_id desc
if @rundate is not null and @runtime is not null
begin
set @LastRunDate=msdb.dbo.agent_datetime(@rundate,@runtime)
set @LastRunDate=DATEADD(day,2,@LastRunDate)
end
else
set @LastRunDate=getdate()
--End
Use Partition on columm :
USE AdventureWorks2012; GO SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1), ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0 ORDER BY TerritoryName;
----------------------------------------------------------------------------------------------------------------
Update XML data saved as ntext column in SQL
/*
Following query will update commission amount using TotalEnoPnsPremium and commission percentage
We only need to change POlicy NUmber for which we need to update
Created on : 11/02/2016
Created by : Rahul Saini
*/
-- Initialize temp table by policy data
select CAST(XMLData AS XML) as xmldata,policynumber,quoteid,historyid,
row_number() over(order by historyid) as row into #temp from History(nolock)
where PolicyNumber='88896P160APL'
--select * from #temp
declare @max int --Loop termination point
declare @count int --Use for iteration
declare @commissionPercent decimal
declare @commissionAmount decimal
declare @TotalENOAndPNSPremium decimal
declare @ChosenENOPremium decimal
declare @NewcommissionAmount int
select @max = max(Row) from #temp
set @count=1;
if @max is not null and @max>0
BEGIN
while @count < =@max
begin
select
@commissionPercent = cast(xmlData as xml).value('(/session/data/policy/line/CommissionPercentage)[1]','decimal'),
@commissionAmount = cast(xmlData as xml).value('(/session/data/policy/line/CommissionAmount)[1]','decimal'),
@TotalENOAndPNSPremium = cast(xmlData as xml).value('(/session/data/policy/line/TotalENOAndPNSPremium)[1]','decimal')
from #temp where row = @count
--print @commissionPercent
--print @commissionAmount
--print @TotalENOAndPNSPremium
-- Update will only happen if commissionamount, commission percentage and enopremium will exist
if @TotalENOAndPNSPremium is not null and @TotalENOAndPNSPremium>0 and @commissionAmount is not null and @commissionPercent is not null and @commissionPercent>0
begin
set @NewcommissionAmount= round(((@TotalENOAndPNSPremium*@commissionPercent)/100),0)
--print @NewcommissionAmount
update #temp
set xmlData.modify('replace value of (/session/data/policy/line/CommissionAmount/text())[1] with sql:variable("@NewcommissionAmount")')
where row=@count
end
set @count = @count+1
--print @count
end
end
-- Update history table using temp table
-- Ensure changes before update actual history table, comment following update command and sun select on temp table
update history
set history.XmlData =cast(replace(cast(b.xmlData as nvarchar(max)), 'utf-16', 'utf-8') as ntext)
from history h
inner join #Temp b on h.historyid =b.historyid
-- droping temp table
drop table #Temp
No comments:
Post a Comment