1. How to use sql query to copy only structure?
Ans: select * into table2 from table1 where 1 = 2
2. How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block
3. What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
4. How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'
5. How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ''
select @vName = @vName + ','+ [Name] from HumanResources.Shift
select @vName
6. How to update 'Yes' to 'No' and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
then 'No'else 'Yes' end)
7. Consider you have a table with columns ID(primary key), Country and State.
Now if you have some rows with combination of country and state repeating,
ie, two rows with combination India, Kerala. Write a query for deleting
duplicate records?
Ans: With T1 as
(Select *,Row_Number() over (partition by Country, State order by ID)
as 'RowNo' From TableName)
Delete from T1 where RowNo > 1;
8. How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?
Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.
1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)
2.
select
OfficeName
, officeid
, o.CustID
, AgentID -
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)
9. Another simple example for using temporary table.
Also how to split a single column in to multiple columns based on column value.
Here date_format function format date to yyyymm format.
period_diff function returns number of months between two date parameters passed. Also parameter expect value in either yymm or yyyymm format.
IN
MYSQL
CREATE
TEMPORARY TABLE monthlyoffer( MemberName VARCHAR(50) ,January INT DEFAULT
0 ,February
INT DEFAULT 0 ,March INT DEFAULT 0 ,April INT DEFAULT 0 ,May INT DEFAULT 0 ,June INT DEFAULT 0,July
INT DEFAULT 0 ,August INT DEFAULT 0 ,September INT
DEFAULT 0 ,October INT DEFAULT 0 ,November INT DEFAULT
0 ,December
INT DEFAULT 0 ,PaidDate date ,CurrentYearPaid INT ,TotalPaid INT ,Balance INT);
Insert
into monthlyoffer (MemberName
,January,February,March,April,May,June,July,August,September,October,November,December,PaidDate,CurrentYearPaid
,TotalPaid,Balance)
Select
mem.first_name,
sum(case when monthname(paid_date)='January'
and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
sum(case when monthname(paid_date)='February'
and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
sum(case when monthname(paid_date)='March'
and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
sum(case when monthname(paid_date)='April'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='May'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='June'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='July'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='August'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='September'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='October'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='November'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
sum(case when monthname(paid_date)='December'
and Year(mo.paid_date) = 2012 then mo.amount_paid
else 0 end),
max(paid_date),
sum(case when
Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
sum(mo.amount_paid),(mem.offer_amount*(1+PERIOD_DIFF(DATE_FORMAT(Now(),
'%Y%m'),DATE_FORMAT(mem.offer_start_date, '%Y%m'))))-sum(case when
mo.amount_paid is null then 0 else mo.amount_paid end)
from
members mem
left join monthly_offered_members_list
mo on mo.member_id = mem.member_id and mo.approved = 1
where
mem.offer_amount is not null and member_status = 'Active' and
Year(mem.offer_start_date) <= 2012
group
by mem.first_name;
Select
* from monthlyoffer;
