SQL Puzzle | Handling Special characters with For XML PATH(”) Puzzle

20 Tuesday Feb 2018

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

Tags

SQL Puzzle | Handling Special characters with For XML PATH(”) Puzzle

In this puzzle you have to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator.

Notes -If you use FOR XML PATH(”) some entries having & shows [& amp ;] This is not what we require. To handle this we need to use the cool trick – TYPE directive. Example FOR XML PATH(”),TYPE).value(‘.’,’NVARCHAR(MAX)’). So after this you do not have to worry about the HTML characters any more.

Sample Example of XML PATH(”),TYPE).value(‘.’,’NVARCHAR(MAX)’)

In this example I am just printing the prime numbers below 1000 and using & as my separator.

--                            

SET QUOTED_IDENTIFIER ON;
DECLARE @Limit AS INT = 1000
DECLARE @ AS VARCHAR(MAX) = ''
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
),
CTE1 AS 
(
	SELECT DISTINCT Number
	FROM 
		Series num
	WHERE 
		Number >= 2 AND Number <= 1000 AND 
			NOT EXISTS 
	( 
			SELECT 1 FROM Series AS num1 WHERE num1.Number > 1
		AND  num1.Number < num.Number
		AND  num.Number % num1.Number = 0
	)	
)
,CTE2 AS
(
	SELECT
	(SELECT '&'+CAST(t2.Number AS VARCHAR(10))
	from CTE1 t2
	ORDER BY Number
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') a
)
SELECT @ = SUBSTRING(a,2,LEN(a)) FROM CTE2
PRINT @

--

OUTPUT

--                            

2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71&73&79&83&89&97&101&103&107&109&113&127&131&137&139&149
&151&157&163&167&173&179&181&191&193&197&199&211&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293
&307&311&313&317&331&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449&457&461
&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587&593&599&601&607&613&617&619&631&641
&643&647&653&659&661&673&677&683&691&701&709&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823
&827&829&839&853&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991&997

--

Read Related Puzzles –

https://msbiskills.com/2016/06/23/sql-puzzle-biggest-prime-number-divisor-of-an-input-number/

https://msbiskills.com/2016/06/15/sql-puzzle-the-prime-number-puzzle-v/

https://msbiskills.com/2016/06/01/sql-puzzle-5-prime-numbers-above-a-given-number/

https://msbiskills.com/2016/05/22/sql-puzzle-the-circular-prime-numbers-puzzle/

https://msbiskills.com/2015/10/30/sql-puzzle-check-prime-number-puzzle/

https://msbiskills.com/2015/10/25/sql-puzzle-the-prime-numbers-puzzle/

Pawan Khowal

Pawan is a SQL Server Expert. 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 | Search Capital Letters ONLY Puzzle ? | SQL Interview Question

20 Tuesday Feb 2018

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

≈ Leave a comment

Tags

SQL Puzzle | Search Capital Letters ONLY Puzzle ? | SQL Interview Question

In this puzzle you have to search captial P only from the vals column.

Please check the sample input and the expected output.

Sample Input

Id	Vals
1	Pawan
2	PAWAN
3	pawaK
4	pawa
5	PawanKhowal
6	paw
7	pAW

Expected Output

Id	Vals
1	Pawan
2	PAWAN
5	PawanKhowal

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

--

CREATE TABLE SearchCapitalLettersOnly
(
	 Id INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO SearchCapitalLettersOnly VALUES
(1,'Pawan'),
(2,'PAWAN'),
(3,'pawaK'),
(4,'pawa'),
(5,'PawanKhowal'),
(6,'paw'),
(7,'pAW')
GO

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

--

SELECT * FROM SearchCapitalLettersOnly
WHERE Vals LIKE 'P%' COLLATE Latin1_General_Bin
GO

--

Output-1

--                            


Id          Vals
----------- ---------------------------
1           Pawan
2           PAWAN
5           PawanKhowal

(3 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.

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 | A Conditional Aggregation – The ToCity Puzzle | SQL Interview Question

20 Tuesday Feb 2018

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

≈ Leave a comment

Tags

SQL Puzzle | A Conditional Aggregation – The ToCity Puzzle | SQL Interview Question

In this puzzle you have to find the create a new column called ToCity. The Value of this column will be the city for which the flag value is 2 for each Id. Now if you do not have any record with flag = 2 for any id then repeat the same city in the ToCity column.

Please check the sample input and the expected output.

Sample Input

Id	Flag	FromCity
1	1	Gurgaon
1	2	Pune
2	1	Gurgaon
2	2	Chicago
3	1	NY
3	2	Las Vegas
4	1	Gurgaon
5	1	Gurgaon
6	1	Gurgaon
7	1	Gurgaon

Expected Output

id	flag	FromCity	ToCity
1	1	Gurgaon	Pune
2	1	Gurgaon	Chicago
3	1	NY	Las Vegas
4	1	Gurgaon	Gurgaon
5	1	Gurgaon	Gurgaon
6	1	Gurgaon	Gurgaon
7	1	Gurgaon	Gurgaon

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

--

CREATE TABLE MoveRowTOColumn
(
	 ID INT
	,Flag INT
	,City VARCHAR(100)
)
GO

INSERT INTO MoveRowTOColumn VALUES
(1,1,'Gurgaon'),
(1,2,'Pune'),
(2,1,'Gurgaon'),
(2,2,'Chicago'),
(3,1,'NY'),
(3,2,'Las Vegas'),
(4,1,'Gurgaon'),
(5,1,'Gurgaon'),
(6,1,'Gurgaon'),
(7,1,'Gurgaon')
GO

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

--

SELECT * FROM
(
	SELECT Id, Flag , City FromCity 
		, ISNULL( MAX(CASE WHEN Flag = 2 THEN City ELSE NULL END) OVER(PARTITION BY ID) , City ) ToCity
	FROM MoveRowTOColumn
)u WHERE Flag = 1

--

Output-1

--                            


Id          Flag        FromCity        ToCity         
----------- ----------- --------------- -------------- 
1           1           Gurgaon         Pune           
2           1           Gurgaon         Chicago        
3           1           NY              Las Vegas      
4           1           Gurgaon         Gurgaon        
5           1           Gurgaon         Gurgaon        
6           1           Gurgaon         Gurgaon        
7           1           Gurgaon         Gurgaon        
                                                       
(7 rows affected)                                      
                                                       
                                   

--

SOLUTION – 2

--

SELECT distinct m1.id,m1.flag,m1.city FromCity ,COALESCE(m2.city,m1.city)  ToCity
from MoveRowTOColumn m1 LEFT join MoveRowTOColumn m2 ON m1.id = m2.ID and m2.flag>m1.flag
WHERE m1.flag !=2

--

Output-2

--                            


Id          Flag        FromCity        ToCity         
----------- ----------- --------------- -------------- 
1           1           Gurgaon         Pune           
2           1           Gurgaon         Chicago        
3           1           NY              Las Vegas      
4           1           Gurgaon         Gurgaon        
5           1           Gurgaon         Gurgaon        
6           1           Gurgaon         Gurgaon        
7           1           Gurgaon         Gurgaon        
                                                       
(7 rows affected)                                      
                                                       


--

SOLUTION – 3 | THE APPLY Operator

--

SELECT x.Id,x.Flag,x.City FromCity , ISNULL(a.City,x.City) ToCity 		
FROM MoveRowTOColumn x
OUTER APPLY 
(
	SELECT TOP 1 City FROM 
	MoveRowTOColumn y WHERE 
	x.Id = y.Id and y.Flag = 2
)a
WHERE a.City <> x.City OR a.City IS NULL

--

Output-3

--                            


Id          Flag        FromCity              ToCity          
----------- ----------- -------------- ------ --------------- 
1           1           Gurgaon               Pune            
2           1           Gurgaon               Chicago         
3           1           NY                    Las Vegas       
4           1           Gurgaon               Gurgaon         
5           1           Gurgaon               Gurgaon         
6           1           Gurgaon               Gurgaon         
7           1           Gurgaon               Gurgaon         
                                                              
(7 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.

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 | -Ve +Ve Pair Puzzle | SQL Interview Question

20 Tuesday Feb 2018

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

≈ 1 Comment

Tags

SQL Puzzle | -Ve +Ve Pair Puzzle | SQL Interview Question

In this puzzle you have to identify the positive and negative pairs for each Id value. Now the requirement is that we cancel and the positive and negative pairs for each Id and show the remaining data to the end user.

Please check the sample input and the expected output.

Sample Input

ID	Vals
101	1
101	-1
101	2
101	3
101	-2
101	-3
101	1
101	2
102	1
102	-1
103	1
104	1

Expected Output

Id	Vals
101	1
101	2
103	1
104	1

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

--

CREATE TABLE Pairs
(
	 ID INT
	,Vals INT
)
GO

INSERT INTO Pairs VALUES
(101,1),
(101,-1),
(101,2),
(101,3),
(101,-2),
(101,-3),
(101,1),
(101,2),
(102,1),
(102,-1),
(103,1),
(104,1)
GO

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

--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER(PARTITION BY ID,Vals ORDER BY Vals) rnk
	FROM Pairs
)
,CTE1 AS
(
	SELECT * , COUNT(*) OVER (PARTITION BY ID,REPLACE(Vals, '-', ''),rnk) cnt
	FROM CTE
)
SELECT Id,Vals
FROM CTE1 
WHERE cnt = 1

--

Output-1

--                            


Id          Vals
----------- -----------
101         1
101         2
103         1
104         1

(4 rows affected)



--

SOLUTION – 2

--

SELECT ID
	,CASE WHEN SUM(VALS) > 0 THEN MAX(vals) ELSE MIN(VALS) END Vals from pairs 
GROUP BY id,ABS(vals) HAVING (COUNT(*) > 1 and SUM(Vals) > 0) 
OR COUNT(*) = 1

--

Output-2

--                            


ID          Vals
----------- -----------
101         1
103         1
104         1
101         2

(4 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.

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

Daily Archives: February 20, 2018

SQL Puzzle | Handling Special characters with For XML PATH(”) Puzzle

SQL Puzzle | Search Capital Letters ONLY Puzzle ? | SQL Interview Question

SQL Puzzle | A Conditional Aggregation – The ToCity Puzzle | SQL Interview Question

SQL Puzzle | -Ve +Ve Pair Puzzle | SQL Interview Question

Share this

Share this

Share this

Share this