Interesting T - SQL Queries (T - SQL Interview Questions)

Question 1
Write a single DML statement to replace 0 with 1 & 1 with 0?

Answer
create table #temp

(nm varchar(10),

id int)



insert into #temp values

('one', 1), ('zero', 0), ('one', 1), ('zero', 0), ('one', 1),

('zero', 0), ('one', 1), ('zero', 0), ('one', 1), ('zero', 0)



select * from #temp



update #temp

set id = case when id = 1 then 0

when id = 0 then 1

end



select * from #temp


Question 2
Write a Query to get maximum 3rd salaried employee
create table #emp

(empid int,

empsal money)

insert into #emp values(1, 25000), (2, 30000), (3, 15000), (4, 40000), (5, 22000) ,(6, 18000)

select * from #emp order by empsal desc



--using top keyword
 select top 1 * from



(
  select top 3 * from #emp

order by empsal desc

)a

order by empsal asc



--using ranking function
 select empid, empsal



from
 (
  select empid, empsal, RANK() over (order by empsal desc) rnk

from #emp

)a

where a.rnk = 3



--using corelated subquery
 select *

from #emp e1

where (3-1) = (select COUNT(distinct (e2.empsal)) from #emp e2 where e2.empsal > e1.empsal)

Specifying the password of SSIS Package while executing it as SQL Job

Normally when we create SSIS packages, in connection managers we may specify the password for some connections. It can be SQL authentication for an OLEDB connection, FTP connection or something else.

When we specify such passwords which are sensitive information, either we should create a config file which stores these passwords or we have to save the package as "EncryptSensitiveWithPassword" and speficy the password for the package.

Whenever we specify a password for the package, and later when we want to execute that ETL using SQL job, we have to specify the password details even in the job.

Else, we may end up getting the below error:
Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

To avoide this error, in SQL job, under "Command Line" properties, choose the option "edit the command line manually" and then type password just after "/DECRYPT".

Command line may look as below:
/FILE "D:\TestETL.dtsx" /DECRYPT pwd123  /CHECKPOINTING OFF /REPORTING E


Here pwd123 is the password for the ETL TestETL.dtsx

 

Text-Qualifier only to the values which has a comma(seperator) in it

We generally get requirements to load data to a flat file destination or csv files.
When we load such data, we may see that value itself has a comma or a separator in it.

During such cases we usually specify text-qualifier in the flat file connection manager properties.
Whenever we specify text-qualifier like that, all column values have these text-qualifier, irrespective of whether the value has a comma in it or not.

InCase, if the customer asks us to text-qualify only to those values which has a comma in it, then below is the work-around solution for that.
Take a derived column transformation and add the below expression:

(FINDSTRING(column_name,",",1) == 0) ? column_name : "\"" + column_name+ "\""
 

Avoiding text-qualifier in the header rows while loading data to a flat file destination

Normally we get a requirement to process data from database and load the data to flat file destination.
Sometimes, we may have to add a text-qualifier as the value itself may have a separator in it.
Example: if its a csv file, we may have a value as SurfMyAds.com, Inc.

In such cases, we may have to enclose such values inside a text-qualifier like "SurfMyAds.com, Inc."

Now, when we specify text-qualifier in flat-file connection properties, even the column header also gets text-quailifer. May be something as shown below

"Name","Salary","DOJ","Department"
"Sachin","8000","2014-01-01", "Finance"
"Rahul","5000","2015-01-01","HR"



Now

But, we do not need text-qualifier in header rows.
To satisfy this requirement, we have to disable the option of "column names in the first row" in the flat file connection manager, as shown below

Later, in the data flow task, open the flat file destination. In the connection manager properties, under the section "Header" specify the column heading that is required. Sample has been shown below:

Once these settings are done, header rows in flat file destination will not have text-qualifiers