SQL PUZZLE | THE IDENTITY & CONSTRAINT Puzzle – How well do you know them ? | SQL Interview Question

23 Monday Apr 2018

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

≈ Leave a comment

Tags

SQL PUZZLE | THE IDENTITY & CONSTRAINT Puzzle – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL query and provide us the output without really executing them at the SSMS. Here you need to provide the data we have in the TestIdentityWithConstraint table.

Q1. What will be the OUTPUT of the following statement?

--

CREATE TABLE TestIdentityWithConstraint
(
	 Id INT IDENTITY(1,1)
	,Vals INT 
)
GO

ALTER TABLE TestIdentityWithConstraint ADD CONSTRAINT ck_Vals CHECK (Vals >= 10)
GO

INSERT INTO TestIdentityWithConstraint(Vals) VALUES (9)
GO

INSERT INTO TestIdentityWithConstraint(Vals) VALUES (11)
GO

SELECT * FROM TestIdentityWithConstraint

--

SOLUTION

Id Vals
———– ———–
2 11

(1 row affected)

Reason – So clearly the first insert will be blocked by the constraint as the vals column cannot accept value below 11. But the Identity column till then created the identity for the column(Id). So when we insert the second row which was successfully inserted will have identity 2 as identity was already 1. So the values will be 2, 11. Please try and let me know if you see any difference.

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL OUTPUT PUZZLE | THE NULL and UNION – How well do you know them ? | SQL Interview Question

20 Friday Apr 2018

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

≈ Leave a comment

Tags

SQL OUTPUT PUZZLE | THE NULL and UNION – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL queries and provide us the output with really executing them at the SSMS.

Q1. What will be the OUTPUT of the following statement?

--

SELECT NULL
UNION ALL
SELECT NULL
UNION
SELECT NULL


--

SOLUTION

--

-----------
NULL

(1 row affected)

--

Reason – Here we will get a single NULL value because the operations will happen in top to bottom approach. So first UNION ALL will happen and after that UNION will happen and thats why we will get a single NULL value as the output.

Q2. What will be the OUTPUT of the following statement?

--

SELECT NULL
UNION 
SELECT NULL
UNION ALL
SELECT NULL

--

SOLUTION

--

-----------
NULL
NULL

(2 rows affected)

--

Reason – Here we will get two NULLs because the operations will happen in top to bottom approach. So first UNION will happen and after that UNION ALL will happen and thats why we will get two NULL values as the output.

Q3. What will be the OUTPUT of the following statement?

--

SELECT NULL
UNION 
SELECT NULL

--

SOLUTION

--

-----------
NULL

(1 row affected)

--

Reason – Here we a single NULL value. Although NULL is not equal to another NULL but in UNION all and UNION it behaves like they are equal. The same is the case with group by.

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL Puzzle | THE AMAZING NULL PROBLEM | SQL Interview Question

16 Friday Feb 2018

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

≈ 4 Comments

Tags

SQL Puzzle | THE AMAZING NULL PROBLEM | SQL Interview Question

In this puzzle we have to get distinct data from Id and Vals columns. Now if for any Id if you have data and NULL also then in that case we have to ignore the NULL row.

Please check the sample input and the expected output.

Sample Input

Id	Val
1	10
1	11
1	NULL
1	NULL
2	10
5	10
3	NULL
4	NULL
4	NULL

Expected Output

Name Id X 1 X 9 X 10 A 2 A 4 A 6 U 4 U 9 U 12 B 5 B 6 B 7 B 8

Use below script to create table and insert sample data into it.

--

CREATE TABLE NULLNOTNULL
(
	 Id INT
	,Val VARCHAR(10)
)
GO

INSERT INTO NULLNOTNULL VALUES
(1,10),
(1,11),
(1,NULL),
(1,NULL),
(2,10),
(5,10),
(3,NULL),
(4,NULL),
(4,NULL)
GO

SELECT * FROM NULLNOTNULL
GO

--

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 COUNT … OVER (PARTITION BY ..) Clause

--


SELECT Id,Val
FROM
(
	SELECT Id,Val,COUNT(*) OVER (PARTITION BY Id) cnts
	FROM NULLNOTNULL 
	GROUP BY Id,Val
)a WHERE cnts = 1 OR (cnts > 1 AND Val IS NOT NULL)

--

Output-1

--                            


Id          Val
----------- ----------
1           10
1           11
2           10
3           NULL
4           NULL
5           10

(6 rows affected)



--

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

https://pawankkmr.wordpress.com/2015/03/31/t-sql-query-group-by-remove-null-uom-puzzle/

https://pawankkmr.wordpress.com/2015/04/02/t-sql-query-count-null-values-puzzle/

https://pawankkmr.wordpress.com/2015/05/10/t-sql-query-the-previous-valuenon-null-puzzle/

https://msbiskills.com/2015/08/06/t-sql-query-the-remove-nulls-puzzle/

https://msbiskills.com/2016/02/07/sql-puzzle-the-remove-null-puzzle/

https://msbiskills.com/2016/05/18/sql-puzzle-the-null-columns-puzzle/

https://msbiskills.com/2016/07/26/sql-puzzle-remove-nulls-via-row-wise-pattern/

https://msbiskills.com/2017/02/15/sql-puzzle-the-isnull-puzzle/

https://msbiskills.com/2017/12/26/sql-puzzle-the-tricky-null-puzzle-1/

https://msbiskills.com/2017/12/26/sql-puzzle-the-tricky-null-puzzle-2concat/

https://msbiskills.com/2018/02/08/sql-puzzle-get-isnull-isnull-next-non-null-value-previous-non-null-value-0-puzzle/

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Improving my SQL BI Skills

Tag Archives: THE NULL PUZZLE

SQL PUZZLE | THE IDENTITY & CONSTRAINT Puzzle – How well do you know them ? | SQL Interview Question

SQL OUTPUT PUZZLE | THE NULL and UNION – How well do you know them ? | SQL Interview Question

SQL Puzzle | THE AMAZING NULL PROBLEM | SQL Interview Question

Share this

Share this

Share this