Advertisements

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

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

–Solution and the explanation will be uploaded at 4 PM today. Good Luck. 🙂

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

--

SELECT NULL
UNION 
SELECT NULL
UNION ALL
SELECT NULL

--

SOLUTION

–Solution and the explanation will be uploaded at 4 PM today. Good Luck. 🙂

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

--

SELECT NULL
UNION 
SELECT NULL

--

SOLUTION

–Solution and the explanation will be uploaded at 4 PM today. Good Luck. 🙂

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 Blank RECORDS PUZZLE | Advanced SQL SERVER

Tags

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


SQL PUZZLE | THE Blank RECORDS PUZZLE | Advanced SQL SERVER

In this puzzle you have to get Blank Records from the Vals column. Please see the sample input and expected output.

Sample Input

Id Vals
1 Hello
2  
3  
4 Where r u ?

Expected output

Id Vals
2
3

Script – DDL and INSERT sample data

--

CREATE TABLE myData1
(
	 Id INT
	,Vals TEXT
)
Go

INSERT INTO myData1 VALUES
(1,'Hello'),
(2,''),
(3,''),
(4,'Where r u ?')
GO

SELECT * FROM myData1
GO


--

SOLUTION – 1

Solution will be uploaded at 4 PM today. Good Luck. 🙂

Output – 1

--

Id          Vals
----------- ------------------
2           
3           

(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 | Generate a comma separated list via XML | Advance SQL

Tags

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


SQL PUZZLE | Generate a comma separated list XML | Advance SQL

I got an email yesterday asking about this so I checked my wesbite for this article and understand that I have never blogged about this. So I thought why wait let’s share it today itself :). In this puzzle you have to read all the data from Vals column and get us a comma separated list of values from that column. Please see the sample input and expected output.

Sample Input

Id Vals
1 a
2 b
3 c
4 d

Expected output

(No column name)
a,b,c,d

Script – DDL and INSERT sample data

--

CREATE TABLE myData2
(
	 Id INT
	,Vals VARCHAR(100)
)
Go

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

SELECT * FROM myData2
GO

--

SOLUTION – 1

--

SELECT SUBSTRING((SELECT ','+Vals 
FROM myData2
FOR XML PATH('')),2,1000)

--

Output – 1

--

------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 2

--

SELECT STUFF((SELECT ','+Vals 
FROM myData2
FOR XML PATH('')), 1, 1, '')

--

Output – 2

--

------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 3

--

SELECT REPLACE(
	(
		SELECT Vals as 'data()' 
		FROM myData2 FOR XML PATH('')
	)
, ' ', ',')

--

Output – 3

--


------------------
a,b,c,d

(1 row affected)



--

SOLUTION – 4 | Applicable for SQL Server 2017

--

SELECT STRING_AGG(Vals, ',')
FROM myData2

--

Output – 4

--


------------------
a,b,c,d

(1 row affected)



--

Reference –

https://msbiskills.com/2018/01/10/new-t-sql-features-in-sql-server-2017-iii-string_agg/
https://davegugg.wordpress.com/2015/01/29/creating-a-comma-separated-list-with-for-xml-path-and-stuff/

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 Group Status Puzzle | A SINGLE SELECT Interview question

Tags

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


SQL PUZZLE | The Group Status Puzzle ? | A SINGLE SELECT Interview question

In this puzzle you have to group data based on the Id and QTY column & find out the status for them, if the status are multiple then we need “In progress in this column” otherwise you just need to place the same status if all the status are same. The challenge is to do this in a single SELECT. Please see the sample input and expected output.

Sample-1

Id A1 QTY Vals
1 A 25 OPEN
1 B 25 OPEN
1 C 25 OPEN
1 D 25 CLOSE
1 E 25 OPEN
1 F 25 OPEN
1 G 25 OPEN
2 A 43 OPEN
2 B 43 OPEN
2 C 43 OPEN
2 D 43 OPEN
2 E 43 OPEN
2 F 43 OPEN
2 G 43 OPEN
3 A 20 CLOSE
3 B 20 CLOSE
3 C 20 CLOSE
3 D 20 CLOSE
3 E 20 CLOSE
3 F 20 CLOSE
3 G 20 CLOSE

Expected output-1

Id QTY GrStatus
1 25 In-Progress
2 43 OPEN
3 20 CLOSE

Script – DDL and INSERT sample data

--

CREATE TABLE GroupedStatus
(
	   Id INT
	,  A1 VARCHAR(1)
	,  QTY INT   
	,  Vals VARCHAR(10)
)
GO

INSERT INTO GroupedStatus VALUES
(1,'A',25,'OPEN'),
(1,'B',25,'OPEN'),
(1,'C',25,'OPEN'),
(1,'D',25,'CLOSE'),
(1,'E',25,'OPEN'),
(1,'F',25,'OPEN'),
(1,'G',25,'OPEN'),
(2,'A',43,'OPEN'),
(2,'B',43,'OPEN'),
(2,'C',43,'OPEN'),
(2,'D',43,'OPEN'),
(2,'E',43,'OPEN'),
(2,'F',43,'OPEN'),
(2,'G',43,'OPEN'),
(3,'A',20,'CLOSE'),
(3,'B',20,'CLOSE'),
(3,'C',20,'CLOSE'),
(3,'D',20,'CLOSE'),
(3,'E',20,'CLOSE'),
(3,'F',20,'CLOSE'),
(3,'G',20,'CLOSE')
GO

SELECT * FROM GroupedStatus
GO

--

SOLUTION – 1

--

SELECT g.Id , g.QTY , IIF(COUNT(DISTINCT Vals)>1,'In-Progress',MAX(Vals)) GrStatus
FROM GroupedStatus g
GROUP BY g.Id , g.QTY
ORDER BY g.Id

--

Output – 1

--

Id          QTY         GrStatus
----------- ----------- -----------
1           25          In-Progress
2           43          OPEN
3           20          CLOSE

(3 rows affected)

--

SOLUTION – 2

--

SELECT g.Id , g.QTY , IIF(MIN(Vals)MAX(Vals),'In-Progress',MAX(Vals)) GrStatus
FROM GroupedStatus g
GROUP BY g.Id , g.QTY
ORDER BY g.Id

--

Output – 2

--

Id          QTY         GrStatus
----------- ----------- -----------
1           25          In-Progress
2           43          OPEN
3           20          CLOSE

(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 | Fetch ALL attributes & values dynamically from XML? | Advanced SQL

Tags

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


SQL PUZZLE | Fetch ALL attributes & values dynamically from XML? | Advanced SQL

In this you have to go through the XML and get all the attributes names and values without providing the node names. Please see the sample input and expected output.

Sample-1

--

<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>

--

Expected output-1

Nm Vals
specVersion 3.20
UserId PawanWithSQLServer
type Cell
type Email
type WebSite
type Fax

Script – DDL and INSERT sample data

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

--

SOLUTION – 1

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML
SELECT
		p.value('local-name(.)', 'VARCHAR(100)') As Nm,
		p.value('.','NVARCHAR(MAX)') As Vals		
FROM @XML.nodes('//node()//@*') AS u(p)

--

Output – 1

--

Nm                       Vals
------------------------ -------------------------
specVersion              3.20
UserId                   PawanWithSQLServer
type                     Cell
type                     Email
type                     WebSite
type                     Fax

(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