Advertisements

Tag Archives: SUM of grouped COUNT in SQL Query

SQL Puzzle | Multiple ways to Split a string and get nth ROW (XML) | Advanced SQL

15 Friday Jun 2018

Posted by 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 | Multiple ways to Split a string and get nth ROW | Advanced SQL

In this puzzle you have to split the string and get me the third value from the list. The challenge is to do that using a single SELECT. For more details please see the sample input and expected output.

Sample Input

(No column name)
Pawan1,Pawan2,Pawan4,Pawan3

Expected Output

(No column name)
Pawan4

Script – DDL and INSERT Sample Data

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 

--

SOLUTION – 1

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 
SELECT VALUE FROM 
(
	SELECT VALUE , ROW_NUMBER() OVER (ORDER BY (SELECT null)) rnk FROM STRING_SPLIT(@, ',')
)x where rnk = 3
GO

--

Output – 1

--

VALUE
--------------
Pawan4

(1 row affected)

--

SOLUTION – 2

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 
SELECT CAST('<p>' + REPLACE(@ , ',','</p><p>') + '</p>' AS XML).value('/p[3]','VARCHAR(10)')
GO

--

Output – 2

--


----------
Pawan4

(1 row 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

Advertisements

SQL Puzzle | The classic Multi-Column SEARCHING Puzzle | SQL Interview Question

14 Thursday Jun 2018

Posted by in Download SQL Interview Q's, 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 Puzzle | The classic Multi-Column SEARCHING Puzzle | SQL Interview Question

In this puzzle you have to find out rows where we have pawan in any of the column. The restriction is that you cannot use the OR statement as we have N number of columns as it looks bad if we use some many columns with OR condition. For more details please see the sample input and expected output.

Sample Input

a b c d e f
Pawan P K Manmeet NULL Avtaar
A Avtaar Pawan NULL Sharlee NULL
NULL Pawan A Manmeet Sharlee Avtaar
Manmeet Pawan NULL Sharlee A Pawan
NULL Avtaar Z M Z K
NULL Pawan Q Manmeet T Sharlee
Manmeet T Avtaar NULL Sharlee Z

Expected Output

a b c d e f rnk
Pawan P K Manmeet NULL Avtaar 1
A Avtaar Pawan NULL Sharlee NULL 2
NULL Pawan A Manmeet Sharlee Avtaar 3
Manmeet Pawan NULL Sharlee A Pawan 4
NULL Pawan Q Manmeet T Sharlee 6

Script – DDL and INSERT Sample Data

--

CREATE TABLE TheMultiCheckingCondition 
(	
	  a VARCHAR(10)
	, b VARCHAR(10)
	, c VARCHAR(10)
	, d VARCHAR(10)
	, e VARCHAR(10)
	, f VARCHAR(10)

)
GO

INSERT INTO TheMultiCheckingCondition values 
('Pawan','P','K','Manmeet',NULL,'Avtaar'),
('A','Avtaar','Pawan',NULL,'Sharlee',NULL),
(NULL,'Pawan','A','Manmeet','Sharlee','Avtaar'),
('Manmeet','Pawan',NULL,'Sharlee','A','Pawan'),
(NULL,'Avtaar','Z','M','Z','K'),
(NULL,'Pawan','Q','Manmeet','T','Sharlee'),
('Manmeet','T','Avtaar',NULL,'Sharlee','Z')
GO

SELECT * FROM TheMultiCheckingCondition
GO


--

SOLUTION – 1

--

;WITH CTE AS
(
	SELECT *, ROW_NUMBER() OVER (Order by (SELECT NULL)) rnk
	FROM TheMultiCheckingCondition
)
SELECT c.* FROM CTE c
CROSS APPLY 
(
	SELECT * FROM CTE c1 WHERE c1.rnk = c.rnk 
	FOR XML RAW('w')
)z(t)
WHERE z.t LIKE '%pawan%'

--Note - Here I am using row_number column, normally you have Id column in your table. You need to use that column
--instead of row_number.

--

Output – 1

--

a          b          c          d          e          f          rnk
---------- ---------- ---------- ---------- ---------- ---------- --------------------
Pawan      P          K          Manmeet    NULL       Avtaar     1
A          Avtaar     Pawan      NULL       Sharlee    NULL       2
NULL       Pawan      A          Manmeet    Sharlee    Avtaar     3
Manmeet    Pawan      NULL       Sharlee    A          Pawan      4
NULL       Pawan      Q          Manmeet    T          Sharlee    6

(5 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 Conditional JOIN Puzzle | SQL Interview Question

13 Wednesday Jun 2018

Posted by 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 Conditional JOIN Puzzle | SQL Interview Question

In this puzzle you have to JOIN the two tables based on the condition. The condition is like if the Id column in the OtherDetails IS NOT NULL then you have to JOIN on the ID column, If the Id column in the OtherDetails IS NULL then you have to JOIN on the Val column. Columns required in the output are – Id, Val and FullName. For more details please see the sample input and expected output.

Sample Input

Table 1 : SCodes

Id Val
1 MH
2 MH
6 HR
3 JK
5 JK
4 JK

Table 2 : OtherDetails

Id Val FullName
NULL MH Maharashtra
2 MH Mumbai
NULL HR Haryana
NULL JK Jammu & Kashmir
3 JK J & K
4 JK Vadi

Expected Output

Id Val FullName
1 MH Maharashtra
2 MH Mumbai
3 JK J & K
4 JK Vadi
5 JK Jammu & Kashmir
6 HR Haryana

Script – DDL and INSERT Sample Data

--

CREATE TABLE SCodes 
(
	 Id INT
	,Val VARCHAR(2)
)
GO

CREATE TABLE OtherDetails
(
	 Id INT
	,Val VARCHAR(2)
	,FullName VARCHAR(50)
)
GO

INSERT INTO SCodes VALUES
(1,'MH'),
(2,'MH'),
(6,'HR'),
(3,'JK'),
(5,'JK'),
(4,'JK')
GO

INSERT INTO OtherDetails VALUES
(NULL,'MH','Maharashtra'),
(2,'MH','Mumbai'),
(NULL,'HR','Haryana'),
(NULL,'JK','Jammu & Kashmir'),
(3,'JK','J & K'),
(4,'JK','Vadi')
GO

SELECT * from SCodes a
GO

SELECT * from OtherDetails a
GO

--

SOLUTION – 1

--

;WITH CTE AS 
(
	SELECT a.*,b.FullName
	FROM SCodes a
	INNER JOIN OtherDetails b ON a.Id = b.Id
)
,CTE1 AS
(
	SELECT a.*,b.FullName
	FROM SCodes a
	INNER JOIN OtherDetails b ON b.Val = a.Val AND b.Id IS NULL
	WHERE NOT EXISTS ( SELECT NULL FROM OtherDetails x WHERE x.ID = a.ID )
)
SELECT * FROM CTE
UNION 
SELECT * FROM CTE1

--

Output – 1

--

Id          Val  FullName
----------- ---- --------------------------------------------------
1           MH   Maharashtra
2           MH   Mumbai
3           JK   J & K
4           JK   Vadi
5           JK   Jammu & Kashmir
6           HR   Haryana

(6 rows affected)

--

SOLUTION – 2

--

;WITH CTE AS 
(
	SELECT a.*,b.FullName , ROW_NUMBER() OVER (PARTITION BY a.Id,a.Val ORDER BY b.Id DESC) rnk
	FROM SCodes a
	INNER JOIN OtherDetails b 
	ON ((a.Id = b.Id AND b.Id IS NOT NULL) OR (b.Val = a.Val AND b.Id IS NULL))

)
SELECT Id,Val,FullName FROM CTE
WHERE rnk = 1

--

Output – 2

--

Id          Val  FullName
----------- ---- --------------------------------------------------
1           MH   Maharashtra
2           MH   Mumbai
3           JK   J & K
4           JK   Vadi
5           JK   Jammu & Kashmir
6           HR   Haryana

(6 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 Without OR Puzzle | SQL Interview Question

12 Tuesday Jun 2018

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

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Without OR Puzzle | SQL Interview Question

In this puzzle you have to find out rows where we have Pune in any of the column. The restriction is that you cannot use the OR statement. For more details please see the sample input and expected output.

Sample Input

a b c d
NULL Pune Nasik NULL
Nagar NULL Mumbai Lonavala
Pune Nagar Nagpur Lonavala
Pune Nagar Nagpur NULL

Expected Output

a b c d
NULL Pune Nasik NULL
Pune Nagar Nagpur Lonavala
Pune Nagar Nagpur NULL

Script – DDL and INSERT Sample Data

--

CREATE TABLE WithoutOR 
(	
	  a VARCHAR(10)
	, b VARCHAR(10)
	, c VARCHAR(10)
	, d VARCHAR(10)
)
GO

INSERT INTO WithoutOR values 
(NULL, 'Pune', 'Nasik',Null),
('Nagar', NULL, 'Mumbai','Lonavala'),
('Pune', 'Nagar', 'Nagpur','Lonavala'),
('Pune', 'Nagar', 'Nagpur',NULL)
GO

SELECT * FROM WithoutOR
GO

--

SOLUTION – 1

--

SELECT * 
FROM WithoutOR x 
WHERE EXISTS 
(
	SELECT NULL FROM WithoutOR WHERE CONCAT(x.a,x.b,x.c,x.d) LIKE '%Pune%'
)

--

Output – 1

--

a          b          c          d
---------- ---------- ---------- ----------
NULL       Pune       Nasik      NULL
Pune       Nagar      Nagpur     Lonavala
Pune       Nagar      Nagpur     NULL

(3 rows affected)

--

SOLUTION – 2

--

SELECT * FROM WithoutOR 
WHERE CONCAT(a,b,c,d) LIKE '%Pune%'

--

Output – 2

--

a          b          c          d
---------- ---------- ---------- ----------
NULL       Pune       Nasik      NULL
Pune       Nagar      Nagpur     Lonavala
Pune       Nagar      Nagpur     NULL

(3 rows affected)

--

SOLUTION – 3

--

SELECT *
FROM WithoutOR t
WHERE EXISTS 
(
	SELECT NULL
	FROM 
	(
		VALUES (a),(b),(c),(d)
	) x(m)
	WHERE m = 'Pune'
)

--

Output – 3

--

a          b          c          d
---------- ---------- ---------- ----------
NULL       Pune       Nasik      NULL
Pune       Nagar      Nagpur     Lonavala
Pune       Nagar      Nagpur     NULL

(3 rows affected)

--

SOLUTION – 4

--

SELECT t.*
FROM WithoutOR t
CROSS APPLY
(
	SELECT COUNT(*) cnt
	FROM 
	(
		VALUES (a), (b), (c),(d)
	) x(m)
	WHERE m = 'Pune'
)x
WHERE x.cnt > 0

--

Output – 4

--

a          b          c          d
---------- ---------- ---------- ----------
NULL       Pune       Nasik      NULL
Pune       Nagar      Nagpur     Lonavala
Pune       Nagar      Nagpur     NULL

(3 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 Infamous ROW_NUMBER Puzzle | Advanced SQL

09 Saturday Jun 2018

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

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Infamous ROW_NUMBER Puzzle | Advanced SQL

In this puzzle you have to ignore the countinues duplicate values and and get the row number for the non duplicate values. For more details please see the sample input and expected output.

Sample Input

Id City
1 Pune
2 Nasik
3 Pune
4 Pune
5 Nasik
6 Nasik

Expected Output

City Rank
Pune 1
Nasik 2
Pune 3
Nasik 4

Script – DDL and INSERT Sample Data

--

CREATE TABLE Cities
(
	 Id INT
	,City VARCHAR(50)
)
GO

INSERT INTO Cities VALUES 
 (1,'Pune')
,(2,'Nasik')
,(3,'Pune')
,(4,'Pune')
,(5,'Nasik')
,(6,'Nasik')
GO

SELECT * FROM Cities
GO

--

SOLUTION – 1

--

SELECT City, x1 rank from 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY x1 order by Id) rnk from 
	(
		SELECT *, SUM(P) OVER(ORDER BY Id)+1 x1 FROM 
		(
			SELECT * , CASE WHEN City =  ISNULL(LAG(City) OVER (ORDER BY Id),City) THEN 0 ELSE 1 END p	
			FROM Cities 
		)x
	)k
)p WHERE rnk  = 1	 

--

Output – 1

--

City                                               rank
-------------------------------------------------- -----------
Pune                                               1
Nasik                                              2
Pune                                               3
Nasik                                              4

(4 rows affected)

--

SOLUTION – 1

--

SELECT x.City , ROW_NUMBER() OVER (Order by Id) Rank
FROM
(
	SELECT * , LAG(City) OVER (ORDER BY Id) PrevCity
	FROM Cities 
)x WHERE x.PrevCity IS NULL OR X.City != PrevCity	 

--

Output – 2

--

City                                               Rank
-------------------------------------------------- --------------------
Pune                                               1
Nasik                                              2
Pune                                               3
Nasik                                              4

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