SQL Puzzle | Inserted Rows Count Puzzle

16 Monday May 2016

Posted by Pawan Kumar Khowal in SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

Tags

SQL Puzzle | Inserted Rows Count Puzzle

This question was asked to me in one of the technical interviews I had recently attended. In this case they are inserting data in a table, Now this table has only one row and they are inserting data with single insert statement with GO.

Please check out the sample input values and sample expected output below.

Sample Input

For example we are inserting 100 rows in a table we should get 100 as output.

Expected Output

100

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Using Output Clause & Temp Table


--

CREATE TABLE #TempNew 
( 
	Cnt INT NOT NULL
)
GO

CREATE TABLE testOutputClause
(
	Id INT IDENTITY PRIMARY KEY
)
GO

INSERT INTO testOutputClause 
OUTPUT Inserted.Id INTO #TempNew
DEFAULT VALUES
GO 100

SELECT COUNT(*) FROM #TempNew

--

—

[/code]

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Server Interview Puzzles – III

16 Monday May 2016

Posted by Pawan Kumar Khowal in SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL Server Interview Puzzles – III

Here I come with few interview questions asked to me or some of friends last few years. They were either asked to us in interview or via SQL tests. Test your skills and enjoy !


--

--SQL Server Quiz

--Q1.


CREATE TABLE #testOutputClause
(
	Id INT IDENTITY PRIMARY KEY
)
GO

INSERT INTO #testOutputClause DEFAULT VALUES
GO 3

SELECT @@ROWCOUNT

--Q2


CREATE TABLE #testNull 
(	
	Id INT
)
GO

INSERT INTO #testNull VALUES (7),(9),(-1),(NULL)

SELECT Id FROM #testNull WHERE Id <> 1

--Q3. 


DECLARE @V VARCHAR(MAX)
SET @V = REPLICATE('$',100000) 
SELECT LEN(@V)


--Q4. 


DECLARE @ NVARCHAR(MAX)
SET @ = REPLICATE('d',16000) 
SELECT LEN(@)


--Q5.


DECLARE @strValue VARCHAR
SELECT ISNULL(@strValue,'I am NULL')


--Q6.


DECLARE @strValue1 VARCHAR
SELECT COALESCE(@strValue1,'I am NULL')


--Q7.


CREATE TABLE #Nulls1 
(	
	Id INT
)
GO

INSERT INTO #Nulls1 VALUES (7),(9),(-1),(NULL)

SELECT COUNT(1) , COUNT(*) , SUM(1) , COUNT(Id) 
FROM #Nulls1


--Q8. 


DECLARE @V1 VARCHAR(MAX)
SET @V1 = REPLICATE('$',100000) 
SELECT DATALENGTH(@V1)


--Q9. 


DECLARE @1 NVARCHAR(MAX)
SET @1 = REPLICATE('d',16000) 
SELECT DATALENGTH(@1)


--Q10. 

CREATE TABLE #Nulls2
(	
	Id INT
)
GO

SELECT SUM(Id) , COUNT(1) FROM #Nulls2

--

SQL Puzzle | Check input parameter value is integer or not – Inbuilt function & Custom Logic

16 Monday May 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL Puzzle | Check input parameter value is integer or not

This question was asked to me in one of the technical interviews I had recently attended. Here we have to accept an input string and check whether it is an integer value or not. I told him about the Try_Convert but he told me to come up the custom logic. Please check out the sample input values and sample expected output below.

Sample Input

DECLARE @ AS VARCHAR(100) = ‘2147483648.1’
DECLARE @ AS VARCHAR(100) = ‘F’
DECLARE @ AS VARCHAR(100) = ‘9F’
DECLARE @ AS VARCHAR(100) = ‘$’
DECLARE @ AS VARCHAR(100) = ’20’

Expected Output

Not an Integer
Not an Integer
Not an Integer
Not an Integer
An Integer

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Try_Convert


--


DECLARE @ AS VARCHAR(100) = '2147483648.1'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = 'F'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '9F'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '$'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '20'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO




--

SOLUTION 2 | Using Numbers Table


--

	 
DECLARE @ AS VARCHAR(100) = '2147483648.1'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = 'F'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '9F'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '$'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '20'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO	 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

M	T	W	T	F	S	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Improving my SQL BI Skills

Daily Archives: May 16, 2016

SQL Puzzle | Inserted Rows Count Puzzle

SQL Server Interview Puzzles – III

SQL Puzzle | Check input parameter value is integer or not – Inbuilt function & Custom Logic

Share this

Share this

Share this