SQL Interview Questions

Leave a comment (0) Go to comments

We are here not to see simple theoretical interview questions, it’s the time to be practical… lets go thru few practical questions which can, only be solved, once you have clear understanding of theory…

Question 1)

DECLARE @TestVariable2 AS VARCHAR(50)
DECLARE @NextTestVariable2 AS CHAR(50)

SET @TestVariable2 = CONVERT(VARCHAR,’abc’)
SET @NextTestVariable2 = CONVERT(CHAR,’abc’)

SELECT DATALENGTH(@TestVariable2),
       DATALENGTH(@NextTestVariable2),
       DATALENGTH(CAST(‘abc’ AS VARCHAR)),
       DATALENGTH(CAST(‘abc’ AS CHAR))
GO

Answer: 3, 50, 3, 30

Explanation: The default datatype definition for VARCHAR is VARCHAR(n). “When n is not specified when using the CAST and CONVERT functions, the default length is 30.” However, DATALENGTH looks at storage space, so even though varchar has a default of 30, if you do not use 30 characters, data length only shows the length of the data stored.

Question 2) What is the result of below query

CREATE TABLE # (STRING CHAR(100))
GO
INSERT INTO # SELECT ‘ASDF’
GO
INSERT INTO # SELECT ‘ASDF’
GO 100
SELECT * FROM #
GO
DROP TABLE #

Answer: 102

Question 3) What is the outcome of following statement:


Declare @strSql varchar(2000)
Set @strSql = ”
Set @strSql = @strSql + ‘Create table #tmp (id int)’
Set @strSql = @strSql + ‘Insert into #tmp(id) values (1)’
Exec (@strSql)
Select * from #tmp

Answer: Throw an error: Invalid object name ‘#tmp’.

Explanation: The temp table (#tmp) created inside the dynamic query is out of scope of after the Exec (@strSql) statement. The table is within scope while the EXEC() executing.

Question 4) What is the outcome of following SQL code:

SELECT DATEPART(yyyy,OrderDate) AS [Year], SalesPersonID,
SUM(TotalDue) AS AverageOrderAmt
FROM Sales.SalesOrderHeader
GROUP BY [Year], SalesPersonID
ORDER BY [Year] ;

Answer: Error

Explanantion: You cannot use an alias in a GROUP BY clause.

Question 5)

DECLARE @inc int, @dec int;

SELECT @inc = 1, @dec = 10;

DECLARE @temp table (StringA varchar(14), StringB varchar(12));

WHILE @inc <= 10
BEGIN   
 INSERT INTO @temp   
 VALUES (‘Hello’ + SPACE(@inc),            ‘
  Hello’ + SPACE(@dec));   
 SET @inc = @inc + 1;   
 SET @dec = @dec – 1;
END

SELECT COUNT(*) FROM @temp
WHERE LEN(StringA) = LEN(StringB);

Answer 10 

Explanation:  SQL ignores trailing spaces when comparing strings. Therefore the two strings appear to be equal, no matter what the number of spaces beyond the characters are.

Question 6) When is a global temp table dropped?

Answer:  When the instance restatrs; When the session that created it ends if no other sessions are referencing it; When all active tasks have stopped referencing them;
When specifically dropped by name

Question 7)  What is the output of below SQL code:

declare @v1 varchar(20),@v2 varchar(20)
select @v1 = ‘NULL’

if @v1 is null and @v2 is null
    select ‘A’
     else
     select ‘B’

Answer: B

Explanantion: ‘NULL’ is not recognised by ‘is null’ when it is a character string. 

Question 8) What does NULL mean?

Answer: UNKNOWN. The value NULL means UNKNOWN; it does not mean ” (empty string). Let us assume that ANSI_NULLS are on in your database, which is a default property, any comparison to the value NULL will get the value NULL. One cannot compare any value with an UNKNOWN value or NULL and logically expect to get an answer. 

Question 9) What type of language is the SELECT command in SQL Server?
Answer: DML

Question 10) What is the data type conversion from char to datetime?

Answer: The data type conversion from a char to a datetime is implicit and doesn’t require the use of explicit CAST or CONVERT functions. The char must be in a valid date format or it will cause an error, but an implicit conversion takes place.

Watch this space for more questions. :)

EOF - SQL Interview Questions, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.