SQL Puzzle | The Input Order Puzzle | SQL Interview Question

26 Tuesday Jun 2018

Posted by Pawan Kumar Khowal in 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 Input Order Puzzle | SQL Interview Question

In this puzzle you have to get records based on the Input comma separated values. The challenge here is to get the records in the same order the input values are specified in the string. For more details please see the sample input and expected output.

Sample Input

Id	Val
1	a
2	b
3	c
4	d
5	e

Expected Output

–OUTPUT for Declare @Input varchar(max)=’3,2′

Id	Val
3	c
2	b

Script – DDL and INSERT Sample Data

--

CREATE TABLE theOrder
(
	 Id INT
	,Val Varchar(10)
)
GO

INSERT INTO theOrder VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e')
GO

SELECT * FROM theOrder
GO

--

SOLUTION – 1 | STRING_SPLIT

--

Declare @Input varchar(max)='3,2'

SELECT a.*
FROM theOrder a INNER JOIN 
				( SELECT Value , Row_Number() OVER (ORDER BY (SELECT NULL)) rnk 
				FROM STRING_SPLIT(@Input,',') )z
ON z.value = a.Id
ORDER BY z.rnk

--

OUTPUT – 1

--

Id          Val
----------- ----------
3           c
2           b

(2 rows affected)



--

SOLUTION – 2 | using XML

--


Declare @Input varchar(max)='3,2'

SELECT a.*
FROM theOrder a
INNER JOIN 
(
	Select   m.value('(./text())[1]', 'int') Vals
			,Row_Number() OVER (ORDER BY (SELECT NULL)) rnk
	From 
	(
	    VALUES (CAST('<a>' + REPLACE(@Input,',','</a><a>')+'</a>' AS XML))
	) x(r)
	CROSS APPLY r.nodes('a') node(m)
)z on a.id = z.Vals
ORDER BY rnk

--

OUTPUT – 2

--

Id          Val
----------- ----------
3           c
2           b

(2 rows affected)

--

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 Update XML puzzle using SQL

24 Saturday Feb 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 Update XML puzzle using SQL

In this puzzle you have to update the value present in XMLs for Id = 1 and Id = 3 from existing values to Avtaar and Pawan respectively.

Please check the sample input and the expected output.

Sample Input


Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">1</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">1</a><b>23</b></Data>

Expected Output


Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">Avtaar</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">Pawan</a><b>23</b></Data>

(3 rows affected)

--

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

--

CREATE TABLE XMLUpdate 
( 
	  Id INT
	, XmlVal XML 
)
GO

INSERT  INTO XMLUpdate VALUES  
( 1, N'<Data><a id="1">1</a><b>21</b><c>31</c></Data>' ),
( 2, N'<Data><a id="1">1</a><b>22</b><c>32</c></Data>' ),
( 3, N'<Data><a id="2">1</a><b>23</b></Data>' )
GO

SELECT * FROM XMLUpdate
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

--

UPDATE  XMLUpdate
SET     XmlVal.modify('replace value of (/Data/a[@id="1"]/text())[1] with "Avtaar"')
WHERE Id = 1

UPDATE  XMLUpdate
SET     XmlVal.modify('replace value of (/Data/a[@id="2"]/text())[1] with "Pawan"')
WHERE   ID = 3

SELECT  * FROM XMLUpdate
GO

--

Output-1

--                            

Id          XmlVal
----------- ------------------------------------------------------------
1           <Data><a id="1">Avtaar</a><b>21</b><c>31</c></Data>
2           <Data><a id="1">1</a><b>22</b><c>32</c></Data>
3           <Data><a id="2">Pawan</a><b>23</b></Data>

(3 rows affected)

--

References

https://docs.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml

https://docs.microsoft.com/en-us/sql/t-sql/xml/modify-method-xml-data-type

Full SET of related XML Puzzles

1	https://msbiskills.com/2018/02/24/sql-puzzle-check-node-existence-in-xml-using-sql/
2	https://msbiskills.com/2018/02/24/sql-puzzle-read-xml-data-flat-no-inner-elements-using-sql/
3	https://msbiskills.com/2018/02/22/sql-puzzle-read-and-pivot-xml-data-while-reading-in-sql/
4	https://msbiskills.com/2018/02/22/sql-puzzle-multiple-ways-to-read-xml-datawith-attributes-single-multi-using-sql-2/
5	https://msbiskills.com/2018/02/21/sql-puzzle-read-data-from-xml-1/
6	https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
7	https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
8	https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
9	https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
10	https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
11	https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
12	https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
13	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
14	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/

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

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 | Check Node existence in XML using SQL

24 Saturday Feb 2018

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

≈ 1 Comment

Tags

SQL Puzzle | Check Node existence in XML using SQL

In this puzzle you have to check whether a node exists in the XML or not

Please check the sample input and the expected output.

Sample Input


<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>

Expected Output

OUTPUT
1

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

--

DECLARE @XML AS XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'

--

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

--

DECLARE @XML AS XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'

SELECT @XML.exist('(/Master/a)') OUTPUT

--

Output-1

--                            

OUTPUT
------
1

(1 row affected)


--

Note –

1. The exists method returns 1 or 0. If it returns 1 then the node is present in the XML. If the method returns then the node is not present in the XML.

2. This method needs a path to check for the specific node. If the path is also incorrect it will return 0.

3. This method will return NULL if the input is NULL

Remark from MS –

The exist() method returns 1 for the XQuery expression that returns a nonempty result. If you specify the true() or false() functions inside the exist() method, the exist() method will return 1, because the functions true() and false() return Boolean True and False, respectively. That is, they return a nonempty result). Therefore, exist() will return 1 (True), as shown in the following example:

Refer more from Microsoft from below URL-

https://docs.microsoft.com/en-us/sql/t-sql/xml/exist-method-xml-data-type

Related XML Puzzles

0	https://msbiskills.com/2018/02/24/sql-puzzle-read-xml-data-flat-no-inner-elements-using-sql/
1	https://msbiskills.com/2018/02/22/sql-puzzle-read-and-pivot-xml-data-while-reading-in-sql/
2	https://msbiskills.com/2018/02/22/sql-puzzle-multiple-ways-to-read-xml-datawith-attributes-single-multi-using-sql-2/
3	https://msbiskills.com/2018/02/21/sql-puzzle-read-data-from-xml-1/
4	https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
5	https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
6	https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
7	https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
8	https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
9	https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
10	https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
11	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
11	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/

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

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 | Read XML Data Flat (No Inner Elements) using SQL

24 Saturday Feb 2018

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

≈ 2 Comments

Tags

SQL Puzzle | Read XML Data Flat (No Inner Elements) using SQL

In this puzzle you have to read XML which has no inner elements

Please check the sample input and the expected output.

Sample Input


<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>

Expected Output

OUTPUT
Pawan
Avtaar
Kishan
Ishu
Sharlee
Kabir

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

--

DECLARE @XML AS XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'

--

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

--

DECLARE @Xml XML = 
'<Master Id="10">
  <a Vals="Name">Pawan</a>
  <a Vals="Name">Avtaar</a>
  <a Vals="Name">Kishan</a>
  <a Vals="Name">Ishu</a>
  <a Vals="Name">Sharlee</a>
  <a Vals="Name">Kabir</a>
</Master>'
SELECT
    x.v.value('.','VARCHAR(100)') OUTPUT
FROM @Xml.nodes('/Master/a') x(v)

--

Output-1

--                            

OUTPUT
-----------------------
Pawan
Avtaar
Kishan
Ishu
Sharlee
Kabir

(6 rows affected)


--

Related XML Puzzles

1	https://msbiskills.com/2018/02/22/sql-puzzle-read-and-pivot-xml-data-while-reading-in-sql/
2	https://msbiskills.com/2018/02/22/sql-puzzle-multiple-ways-to-read-xml-datawith-attributes-single-multi-using-sql-2/
3	https://msbiskills.com/2018/02/21/sql-puzzle-read-data-from-xml-1/
4	https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
5	https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
6	https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
7	https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
8	https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
9	https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
10	https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
11	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
11	https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/

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

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 XML PUZZLE

SQL Puzzle | The Input Order Puzzle | SQL Interview Question

SQL Puzzle | The Update XML puzzle using SQL

SQL Puzzle | Check Node existence in XML using SQL

SQL Puzzle | Read XML Data Flat (No Inner Elements) using SQL

Share this

Share this

Share this

Share this