Top 6 SQL Tricky Interview Questions & Answers | sql server interview question and answers

Top 6 SQL Tricky Interview Questions & Answers | Accenture SQL Interview Questions | Part 49
Top SQL interview questions and answers for freshers and professionals in 2021. These questions have been designed to help you become more comfortable with the types of questions you might be asked during your SQL interview.
Accenture SQL Questions and Answers 2020
Find Us On RUclips- "Subscribe Channel to watch Database related videos" / @ssunitech6890
For SQL Quiz-
• sql server : Interview...
Find Us On FaceBook-
/ ss-unitech-18770538867...

[@ssunitech6890]

----Question--1
declare @input varchar(100)='abc,,,dddd,,,,,,dde,,2lmdl3emf,cewec,,,,,'
select @input,REPLACE(@input,',','.,'),replace(REPLACE(@input,',','.,'),',.',''),
replace(replace(REPLACE(@input,',','.,'),',.',''),'.,',',')
------Question--2
declare @source table(id int)
declare @dest table(id int)
insert @source values (1),(1)
select * from @source
Merge @dest as D
Using @Source as S
On s.id=d.id
When Matched Then
Update Set d.id=s.id
When Not Matched by Target then
Insert (id) Values (s.id);
select * from @dest
-------Question--3
SELECT LEN('SS UNITECH')-LEN(REPLACE('SS UNITECH','S',''))
-------Question--4
DECLARE @ATT TABLE (EMP INT,DATEPRESENT VARCHAR(50))
INSERT @ATT VALUES (1,'1,2,3'),(2,'2,4,5,6'),(3,'1,2,5,4,7,9')
SELECT *,LEN(DATEPRESENT)-LEN(REPLACE(DATEPRESENT,',','') )+1
FROM @ATT
-------Question--5
DECLARE @TBL TABLE(ID VARCHAR(100),NAME VARCHAR(100))
INSERT @TBL VALUES(1,'AA'),(2,'BB'),(3,'CC')
SELECT * FROM @TBL
UPDATE @TBL
SET ID=NAME,NAME=ID
SELECT * FROM @TBL
-------Question--6
DECLARE @I INT=100
;WITH CTE AS
(SELECT 1 AS ID
UNION ALL
SELECT ID+1
FROM CTE
WHERE ID

[@selvakumar2984]

Very Informative and crispy. Appreciated and Thanks for sharing the knowledge.

[@ssunitech6890]

Thanks for your appreciation.
Can you please share the videos to others?

[@sumitshitole1899]

Love this video.. thank you .. pls keep the good work going on

[@ssunitech6890]

Thanks Sumit..
Please share to others.
Thanks

[@sandhyasingh284]

Wow it's great video and very helpful...
Hope you will upload more videos

[@ssunitech6890]

Thanks Sandhya..

[@roshnisingh7661]

Nice video really helpful to get list of question and answer..
These are really very common question 2 questions were asked by me as well..
Keep it up..

[@ssunitech6890]

Thanks Roshani..

[@EnjoyYOu121]

Please create more videos like this , good work .....

[@ssunitech6890]

Thanks Vipul..

[@sandeepkhawas3367]

Appreciate your efforts.. keep it up!!

[@ssunitech6890]

Thanks for your appreciation

[@Naveen_Kumar_Garikipati]

Nice explanation never seen before and ever after...

[@ssunitech6890]

Nice to see your comment...
Thanks please share to others

[@premaliadkar5589]

To the point explanation... 👍Keep it up

[@ssunitech6890]

Thanks for your appreciation 😘

[@ChaitanyaKondaveeti]

Good work.. Thank you very much..

[@ssunitech6890]

Thanks for your appreciation 😚

[@BimleshKumar-iw5jn]

Great video...

[@ssunitech6890]

Thanks

[@themightyvk]

Very helpful...

[@ssunitech6890]

Thanks for your appreciation

[@RamRam-ce2yk]

Thank you so much Sir ❤️❤️

[@ssunitech6890]

Thanks 👍

[@shekharmulye1322]

Thank u for help

[@ssunitech6890]

Thanks

[@ujjwalchakraborty8811]

very helpfull.

[@ssunitech6890]

Thanks

[@asheetsinvhal2195]

Thanks for sharing

[@ssunitech6890]

Thanks 👍

[@arko1383]

very nice video. Thank you

[@ssunitech6890]

Thanks for your appreciation

[@ayushawasthinandu8401]

Nyc videos

[@ssunitech6890]

Thanks

[@THERANGERWOLF]

Hello Sir, I think for length function question we can directly use length(replace()), no need to use - operator then again needs to add 1

[@ssunitech6890]

Yes it may be, for a question we will have multiple solutions.
Thanks for your comment
Stay tuned and keep learning

[@es.kamalvanshi]

can you pls give an example of that?

[@Raj-ph1py]

Short & Precise
Completely understood in 12 mins.
Request you to please upload some videos of C# use in SSIS.

[@ssunitech6890]

Thanks Raj your continuous appreciation motivate me alot.. yes I have few scenarios where will use C# coding..
Thanks-SS Unitech

[@vaibhavsingh4043]

Nice video

[@ssunitech6890]

Thanks Vaibhav

[@vishnukant6287]

Nice

[@ssunitech6890]

Thanks Vishnu .

[@shivaroyal9292]

For column swapping no need to update we can go with case statement like below
SELECT (CASE WHEN ID=ID THEN NAME END) AS ID,
(CASE WHEN NAME =NAME THEN ID END) AS NAME FROM TABLE NAME;

[@ssunitech6890]

👍

[@yvasite]

Thanks for this. Very nice explanation - I Just thought, what if there are different combinations of number of commas existing in the Question. For Example - DECLARE @INPUT VARCHAR(100) = 'SAADD,,,,,,,,,,,,,SSFDSFDSF,,,,,,,SS,,SS,SS,,,SS,,,,,SS,,,,SS,,,,,,,,,,,,,,,,,,,,,S'. In such scenarios the below solution will be best fit - SELECT REPLACE(REPLACE(REPLACE(@INPUT , ',,,,' , ',') , ',,,' , ','), ',,', ',') - Just give a thought. All the best

[@ssunitech6890]

Thanks for your suggestions

[@user-rh5fx6hh8e]

Hi Sir
can you please share the complete SQL classes, i have not found in playlist, if not please starts classes sir.
most of the interviews they are asking SQL logic quries.

[@ssunitech6890]

I have don't have any playlist for SQL server tutorial. You can check SQL playlists there are 3 playlist. You can learn about query writing.

[@rafixa5300]

Thanks!
Could you please tell or make a video on recent accenture sql server application developer Online test and interview questions for 3 yers experience. It will be really helpful.

[@ssunitech6890]

Sure.
Will do that

[@rafixa5300]

@@ssunitech6890 thank u for that. I have subscribed your channel. Could u please mail me some questions, actually I am having the interview on next monday, so I need it before that if possible 🙂

[@ssunitech6890]

In channel, it's having around 60+ videos. Watch those videos

[@bellajoyrossa]

Second question regarding merge, first 1 will be not matched, so insert to destination, but 2nd 1 should be matched, thus it should be a update, so should it be only one row returns from @dest?

[@ssunitech6890]

Thanks for your suggestions but your thinking is not right.
What I have explained is correct

[@rajsamant1]

Thanks for the tricky sql interview questions and answers...
for q4....select EMP, len(replace(DATEPRESENT,',',''))
from @ATT....
will also work?

[@ssunitech6890]

Thanks for your appreciation.
Did you try this code?

[@himanshubajaj8536]

Q5 :- How to swap the column if the ID is int and Name is varchar ??

[@ssunitech6890]

Nope only swap if Id column is varchar.
If id is int then it will return an error.
Thanks

[@AmitKalshetti]

declare @name varchar(200) = 'SS aS UNITECH S'
select len(@name)- len( REPLACE( @name,'s',''))
Output : 5

[@ssunitech6890]

Keep learning

[@rrl1919]

NICE VIDEO...SIR HOW CAN I WRITE Q IF I HAVE TO FIND OCCURANCE OF S FROM 'NAME' COLUMN FROM TABLE --AND I HAVE TO STORE THAT RESULT

[@rrl1919]

BCOZ THIS QUERY IS HARD CODED FOR 'SS UNITECH'

[@rrl1919]

I GOT SOLUTION SIR THANKS

[@ssunitech6890]

Sounds good.
Please share to others

[@rrl1919]

SELECT NAME , LEN(NAME)AS TOTAL_LEN,
LEN(NAME)-LEN(REPLACE(NAME,'S','')) AS_LENGTH_OF_S_OCCOURANCE FROM OCCURANCE;

[@ssunitech6890]

Nice

[@shashikantvishwakarma9141]

Len() also counts the blank space or not?

[@ssunitech6890]

Yes Len() will count blank spaces.
Thanks

[@dhammsagar]

Hii sir plz give solution if id is int and name is varchar then how to swap in update statement

[@ssunitech6890]

Hi Dhammasagar,
If you have int data type for id column then you can't swap it. It will return an error

[@prameeSri143]

Question: 'ABC, ,,,,,BCA,, ,, ,,CDB, ,, BTBGB,, ,,VEDR '
Ans:
declare @input2 varchar(100) = 'ABC, ,,,,,BCA,, ,, ,,CDB, ,, BTBGB,, ,,VEDR '
select REPLACE(REPLACE(REPLACE(@input2,',',' ,'),', ', ''),' ','')
Small modification based on your question Sir. Thank you.

[@ssunitech6890]

Thanks 👍

[@bellajoyrossa]

Question 4, your answer only works when date presented is single digit numbers

[@bellajoyrossa]

I am thinking to use string_split function

[@ssunitech6890]

It was introduced in later version of SQL server management studio.
That's why I didn't use it

[@rohitsethi5696]

this is the wrong login thing of scenario where instead of this 1,2,5,4,7,9 the data is 1,10,2,5,4,7,9 then if we need max number in DATEPRESENT

[@ssunitech6890]

It's checking how many values are there not max value

[@pareshyadnik4108]

Hello Sir..thanks for sharing this..I am from mostly support background having SQL and SSIS skills and I want to switch to SQL and ssis development ..ho can I achieve this

[@ssunitech6890]

You can achieve it easily.
Watch SSIS videos on my channel.
Thanks

[@shekharmulye1322]

Dear sir I m a. Net developer but i want to get job in backend related work also with power bi... Along with. Net experience... But i m confuse how to crack interview what preparation i have to do... I want to get job base on sql and power bi... Pls guide

[@ssunitech6890]

You have.net experience so first learn SQL server stored procedure, functions, triggers, views, temporary table and then start learning power bi data modeling.

[@shekharmulye1322]

@@ssunitech6890 Ok thank you...are these given topics enough to get new job? Because in interviews what question will come no idea... Please advice me... I m ready to do paid course of Power bi... From ur tranning. Ur teaching is superb

[@ssunitech6890]

Stay tuned and keep learning

[@manifestationprayer]

can u share project related interview question and answe

[@ssunitech6890]

Project related SQL or SSIS interview questions and answers?

[@manifestationprayer]

@@ssunitech6890 ptoject related in reference to ssis

[@ssunitech6890]

Sure..
I have uploaded a video on SSIS project.
ruclips.net/video/PayvxG2om3s/видео.html
Please have a look and let me know

[@iliashterev38]

I think Q 5 should be wrong.
UPDATE @TBL
SET ID=NAME,NAME=ID
Once you set the ID column to have value of the NAME then the value of ID itself should be lost. Which means both columns now should have the NAME value.
Could you please help me (and other) on that.
Thank you.

[@ssunitech6890]

In same query, you can replace it.

[@iliashterev38]

@@ssunitech6890 I am sorry. I did not understand. Could you please try once more ??
I noticed one major error in my initial post. Instead of "of" I had written "if".
Thank you.

[@BornDude25]

for the third question,can't we use this ?
Select CHARINDEX('S','SS UNItech',2)

[@ssunitech6890]

In this question: we are checking for occurance of the characters.

[@gokulkrishnan7588]

4th Question
Declare @ATT table (Emp int,Datepresent varchar(50))
insert @ATT Values(1,'1,2,3'),(2,'2,4,5,6'),(3,'1,2,5,4,7,9')
select emp, Len(replace(Datepresent,',',''))
from @ATT

[@ssunitech6890]

Great man
Thanks

[@MaheshBabu-vq7qk]

But it will not produce correct result if we have double digit dates like 11, 12,...etc.

[@gokulpoovalagan2615]

Hope this works in your case Select emp, select count(*) from string_split(datepresent,',') from @ATT

[@ssunitech6890]

Keep learning guys

[@marliearcellia1289]

Hello, I try this in MySQL and I cannot get it run. Is this cannot be processed in a new query tab in MySQL?

[@ssunitech6890]

This query is used in SQL server

[@marliearcellia1289]

@@ssunitech6890 Thank you for a reply. Really love how you make SQL seems less difficult than it sounds :) All the best for you.

[@ssunitech6890]

Thanks for your appreciation 😘

[@ssunitech6890]

I mean, I don't have experience in MySQL, but you can not write in DB2.

[@ssunitech6890]

Yes

[@krutarthshah7426]

at ruclips.net/video/VGj7_C-GD7E/видео.html the logic is incorrect in case of the 10th date.

[@ssunitech6890]

On which question?

[@shivanshukumar893]

It is not dynamic lol

[@ssunitech6890]

Which question lol?

[@shivanshukumar893]

@@ssunitech6890 1 st one

[@THERANGERWOLF]

Hello Sir, I think for length function question we can directly use length(replace()), no need to use - operator then again needs to add 1

[@ssunitech6890]

Yes it may be, for a question we will have multiple solutions.
Thanks for your comment
Stay tuned and keep learning

[@THERANGERWOLF]

I have tried, it's working.

[@ssunitech6890]

Great 👍