SQL tricky questions and answers for interview

SQL tricky questions and answers 

(Microsoft SQL server database management system)

- Database testing interview questions 

- ETL testing interview questions 

* What is the difference between concat and +

If any one column have null, when performing plus(+) operation then all would be null

Select 'daily' + null+ 'updates' 

O/p - null

But in concat function ignore that null and gives the result with not null values 

Select concat('daily' , null,'updates')

O/p - dailyupdates

-----------------------------------------------------------------------------------------------------------

* What is the difference between len() and datalength()

-> len() ignore the trailing spaces 

len(' ss ')

O/p- 3

-> datalength() counts the trailing spaces as well

datalength(' ss ')

O/p- 4

---------------------------------------------------------------------------------------------------------

* What is the difference between DATENAME() and DATEPART()?

DATENAME() returns string

DATEPART() returns integer 

---------------------------------------------------------------------------------------------------------

* What is the difference between CURRENT_TIMESTAMP() and GETDATE()?

CURRENT_TIMESTAMP()  returns the Current date and time in YYYY-MM-DD hh:mm:ss.mmm

GETDATE() returns the current database system 

date and time in YYYY-MM-DD hh:mm:ss.mmm

-------------------------------------------------------------------------------------------------------------

* How to calculate the accurate age in years

SELECT 

((DATEDIFF(DAY, '2000/07/25', '2064/08/01')-DATEDIFF(YEAR, '2000/07/25', '2064/07/26')/4)/365)

----------------------------------------------------------------------------------------------------------------

* What are the difference among Ceiling(), round(), floor()

Ceiling() function works to produce +1 number 

SELECT CEILING(25.75) 

O/p: 26

Round() gives the result of rounding to the number given as second parameter

SELECT round(25.75,1) 

O/p: 25.80

Floor() function works to produce only number 

SELECT floor(25.75) 

O/p: 25