SQL Advanced in MS SQL - Revision history

Lsokolowski1: /* Exercises */

2024-08-22T15:14:05Z

Exercises

2024-08-21T12:37:21Z

Hierarchy

← Older revision		Revision as of 12:37, 21 August 2024
Line 1,102:		Line 1,102:
	== Hierarchy ==		== Hierarchy ==
	* Using '''outer joins''' only		* Using '''outer joins''' only
	* Recursively with '''CTE''' (Common Table Expression)		* Recursively with '''CTE''' (''Common Table Expression'')
	* Built-in support - '''hierarchyid''' column type		* Built-in support - '''hierarchyid''' column type

2024-08-20T14:13:02Z

hierarchyid Example & Exercises

← Older revision		Revision as of 14:13, 20 August 2024
Line 1,182:		Line 1,182:

	=== hierarchyid ''Example & Exercises'' ===		=== hierarchyid ''Example & Exercises'' ===
	[SQL_Exercises#hierarchyid_%28mssql%29\|hierarchyid]		[[SQL_Exercises#hierarchyid_%28mssql%29\|hierarchyid]]
	<!--		<!--
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">

2024-08-20T14:10:51Z

hierarchyid Example & Exercises

← Older revision		Revision as of 14:10, 20 August 2024
Line 1,182:		Line 1,182:

	=== hierarchyid ''Example & Exercises'' ===		=== hierarchyid ''Example & Exercises'' ===
			[SQL_Exercises#hierarchyid_%28mssql%29\|hierarchyid]
	<!--		<!--
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
Line 1,188:		Line 1,189:

	=== hierarchyid Exercise ===		=== hierarchyid Exercise ===
	~~[SQL_Exercises#hierarchyid_%28mssql%29\|hierarchyid]~~
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
	-- TODO		-- TODO

2024-08-20T14:10:29Z

hierarchyid Example & Exercises

← Older revision		Revision as of 14:10, 20 August 2024
Line 1,188:		Line 1,188:

	=== hierarchyid Exercise ===		=== hierarchyid Exercise ===
			[SQL_Exercises#hierarchyid_%28mssql%29\|hierarchyid]
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
	-- TODO		-- TODO

2024-08-20T14:09:23Z

Hierarchy

← Older revision		Revision as of 14:09, 20 August 2024
Line 1,181:		Line 1,181:
	-->		-->

	=== hierarchyid Example ===		=== hierarchyid ''Example & Exercises'' ===
			<!--
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
	-- TODO		-- TODO
Line 1,190:		Line 1,191:
	-- TODO		-- TODO
	</syntaxhighlight>		</syntaxhighlight>
			-->

	== Analytic functions ==		== Analytic functions ==

2024-08-20T06:00:43Z

Hierarchy

← Older revision		Revision as of 06:00, 20 August 2024
Line 1,168:		Line 1,168:
	-->		-->
	<!-- Solution Ex3		<!-- Solution Ex3
	~~TODO~~
	with chL as (		with chL as (
	select id, ename, cast(ename as varchar(200)) as hierPath, 0 as lvl,		select id, ename, cast(ename as varchar(200)) as hierPath, 0 as lvl,

2024-08-18T23:52:32Z

FIRST, LAST

← Older revision		Revision as of 23:52, 18 August 2024
Line 1,269:		Line 1,269:
	-------------------------		-------------------------
	-- Exercise		-- Exercise
	---- Return the hire date of the last employee in each department for the given salary		---- Return the hire date of the last employee in each department for the given salary
			------ show department name and monies grade
	</syntaxhighlight>		</syntaxhighlight>
	<!-- Solution		<!-- Solution

2024-08-18T23:51:35Z

FIRST, LAST

@@ Line 1,267: / Line 1,267: @@
      ON e.deptno = d.deptno
 ORDER BY job;
 </syntaxhighlight>
 == Example 1, 'WITH' ==

2024-08-18T23:43:19Z

LAG, LEAD

@@ Line 1,253: / Line 1,253: @@
    ORDER BY hiredate;
 -->
 == Example 1, 'WITH' ==