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

19 Thursday Apr 2018

Posted by Pawan Kumar Khowal 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 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

--

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

(3 rows affected)

--

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

19 Thursday 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 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

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

18 Wednesday Apr 2018

≈ Leave a comment

Tags

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

In this you have to fetch the data from the XML. The catch here is that the position of Method type=”Email” is not fixed. It also may or may not come. We have to prepare for all the contingencies. If the Method type=”Email” tag is present we should fetch the value else we should get blank. 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

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532	Pawan@MSBISKills.com	MSBISKills.com

Sample-2 | POSITION Changed for Method type=”Email”

--

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

--

Expected output-2

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532	Pawan@MSBISKills.com	MSBISKills.com

Sample-3 | Method type=”Email” is not present

--

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

--

Expected output-3

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532		MSBISKills.com

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 @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]					
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--

Testing – 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 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--OUTPUT

UserName                 Cell          Email                      WebSite            Fax      
--------------------- -- ---------- -- ----------------------- -- ---------------- - -------- 
PawanWithSQLServer       3264532       Pawan@MSBISKills.com       MSBISKills.com              
                                                                                              
(1 row affected)  
--

Testing – 2 | POSITION Changed for Method type=”Email”

--


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

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE

GO

--OUTPUT


UserName                 Cell          Email                      WebSite            Fax      
--------------------- -- ---------- -- ----------------------- -- ---------------- - -------- 
PawanWithSQLServer       3264532       Pawan@MSBISKills.com       MSBISKills.com              
                                                                                              
(1 row affected) 
--

Testing – 3 | Method type=”Email” is not Present

--

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

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--OUTPUT

UserName              Cell         Email      WebSite             Fax     
--------------------  ---------- - -------- - ----------------- - ------- -
PawanWithSQLServer    3264532                 MSBISKills.com              
                                                                          
(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

SQL PUZZLE | How to INSERT XML file into SQL Server & fetch data? | SQL Interview Question

18 Wednesday Apr 2018

≈ Leave a comment

Tags

SQL PUZZLE | How to INSERT XML file into SQL Server & fetch data? | SQL Interview Question

In this puzzle you have to read the xml file from a location and insert the complete XML file into a SQL Server table. After that you have to fetch the data from SQL Server table. Please read the sample input and the expected output.

Sample Input

--

<?xml version="1.0"?>
<MyGate sVersion="0.20">
	<Header>
	<SenderID>A</SenderID>
	<ReceiverID>B</ReceiverID>
	<TypeID>MasterClass</TypeID>
	<Count>10</Count>
	<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
	</Header>
</MyGate>

--

Expected output

–Table 1 after Insertion

--
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>myXML</th><th>InsertionDate</th></tr></thead><tbody>
 <tr><td><MyGate sVersion="0.20"><Header><SenderID>A</SenderID><ReceiverID>B</ReceiverID><TypeID>MasterClass</TypeID><Count>10</Count><Dt type="SQLSERVER">12/12/2017 11:32</Dt></Header></MyGate></td><td>2018-04-18 12:52:54.873</td></tr>
</tbody></table>
--

–Fetched data

SenderId	ReceiverId	TypeID	Count	Header_Date
A	B	MasterClass	10	2017-12-12 11:32:00.000

Script – DDL and INSERT sample data

--

--Create a new table to insert the data
CREATE TABLE myXMLData
(
	 myXML XML
	,InsertionDate DATETIME DEFAULT GETDATE()
)
GO

--XML data--

<?xml version="1.0"?>
<MyGate sVersion="0.20">
	<Header>
	<SenderID>A</SenderID>
	<ReceiverID>B</ReceiverID>
	<TypeID>MasterClass</TypeID>
	<Count>10</Count>
	<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
	</Header>
</MyGate>

--

SOLUTION – 1 | Bulk loading XML data

--

--Create a new table to insert the data
CREATE TABLE myXMLData
(
	 myXML XML
	,InsertionDate DATETIME DEFAULT GETDATE()
)
GO

--Insert the complete XML file into the database table using Bulk loading XML data
INSERT INTO myXMLData(myXML)
SELECT xCol  
FROM    
(
	 SELECT *      
		FROM OPENROWSET (BULK 'E:\SampleXMLFile.xml', SINGLE_CLOB)   
	 AS xCol
)AS R(xCol)  
GO

--Read the Data
SELECT * FROM myXMLData
GO

--

OUTPUT – 1

--

myXML
--------
<MyGate sVersion="0.20"><Header>
<SenderID>A</SenderID>
<ReceiverID>B</ReceiverID><TypeID>MasterClass</TypeID>
<Count>10</Count>
<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
</Header></MyGate>
(1 row affected)

--

SOLUTION – Selecting Data

--

--SELECT the data from the SQL Server Table
;WITH CTE AS
(
    SELECT 
		    p1.value(N'(SenderID)[1]',N'nvarchar(30)') AS [SId]
		   ,p1.value(N'(ReceiverID)[1]',N'nvarchar(30)') AS [RId]
		   ,p1.value(N'(TypeID)[1]',N'nvarchar(30)') AS [TypeID]
		   ,p1.value(N'(Count)[1]',N'nvarchar(30)') AS [Ct]		      
		   ,p1.value(N'(Dt)[1]', 'DATETIME') AS [Dt]		   
    FROM myXMLData as xm  
	OUTER APPLY xm.myXML.nodes(N'/MyGate/Header') AS u1(p1)
)
SELECT * FROM CTE

--

OUTPUT

--

SenderId       ReceiverId    TypeID          Count    Header_Date
-------------- ------------- --------------- -------- -----------------------
A              B             MasterClass     10       2017-12-12 11:32:00.000

(1 row affected)

--

Reference-

https://docs.microsoft.com/en-us/sql/relational-databases/xml/load-xml-data?view=sql-server-2017

Related XML Puzzles

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

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 BASICS | What is the difference between SQL, PL-SQL/PL/pgSQL and T-SQL? | SQL Interview Question?

18 Wednesday Apr 2018

≈ Leave a comment

Tags

SQL BASICS | What is the difference between SQL, PL-SQL, PL/pgSQL and T-SQL? | SQL Interview Question?

Today I got a facebook message asking about the difference between SQL, PL-SQL/PL/pgSQL and T-SQL. So I thought of sharing the details with you guys also :).

SQL

1. The Full form of SQL is Structured Query Language. It focuses on managing relational databases. Consider this as an open format and it is used by almost all rdbms we have – SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc. It was developed by IBM for one of their product.

2. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

3. Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard.

4. This should work in all databases we have. It has complex syntax compared to vendor SQLs although is easy to migrate from one vendor database to another.

T-SQL

1. The full form of T-SQL is Transact-SQL.

2. It is Microsoft’s and Sybase’s proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc.

3. The implementation of DELETE and UPDATE statement in T-SQL is different from SQL.

4. Transact-SQL is central to using Microsoft SQL Server and Azure SQL Database.

PL/SQL

1. The full form is Procedural Language/Structured Query Language.

2. PL-SQL is a proprietary procedural language used by Oracle.

3. PL/SQL combine programming procedural instructions and allows the creation of programs that operates directly on database scenario.

4. This can be used to create Procedures, Functions, Packages, Types, Triggers, etc.

PL/pgSQL

1. This is a language used by PostgreSQL.

2. PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle’s PL/SQL language.

3. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Version 9 also implements some ISO SQL/PSM features, like overloading of SQL-invoked functions and procedures.

References

https://stackoverflow.com/questions/1043265/what-is-the-difference-between-sql-pl-sql-and-t-sql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

https://en.wikipedia.org/wiki/Transact-SQL

https://en.wikipedia.org/wiki/PL/pgSQL

https://www.w3schools.com/sql/sql_intro.asp

https://en.wikipedia.org/wiki/SQL

https://en.wikipedia.org/wiki/PL/SQL

Enjoy:)

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 Gro… on SQL SERVER Interview Questions…
	SQL PUZZLE \| The Gro… on SQL SERVER – Talking…
	SQL PUZZLE \| The Gro… on SQL Performance Tuning &…
	SQL PUZZLE \| The Gro… on List of T SQL Interview puzzle…
	SQL PUZZLE \| Fetch A… on SQL SERVER Interview Questions…
	SQL PUZZLE \| Fetch A… on SQL SERVER – Talking…
	SQL PUZZLE \| Fetch A… on SQL Performance Tuning &…
	SQL PUZZLE \| Fetch A… on List of T SQL Interview puzzle…
	Sankarganesh (@sanka… on SQL Server Query – 4 \| BirthDa…
	SQL PUZZLE \| The OPT… on SQL SERVER Interview Questions…
	SQL PUZZLE \| The OPT… on SQL SERVER – Talking…
	SQL PUZZLE \| The OPT… on SQL Performance Tuning &…
	SQL PUZZLE \| The OPT… on List of T SQL Interview puzzle…
	SQL PUZZLE \| How to… on SQL SERVER Interview Questions…
	SQL PUZZLE \| How to… on SQL SERVER – Talking…

Improving my SQL BI Skills

Tag Archives: SUM of grouped COUNT in SQL Query

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

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

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

SQL PUZZLE | How to INSERT XML file into SQL Server & fetch data? | SQL Interview Question

SQL BASICS | What is the difference between SQL, PL-SQL/PL/pgSQL and T-SQL? | SQL Interview Question?

April 2018
M	T	W	T	F	S	S
« Mar
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

Share this

Share this

Share this

Share this

Share this