SQL Exercises - Revision history

Lsokolowski1: /* DDL, DML */

2025-11-21T14:37:27Z

DDL, DML

← Older revision		Revision as of 14:37, 21 November 2025
Line 204:		Line 204:
	# Delete only not active spies		# Delete only not active spies
	#* discussion (-:		#* discussion (-:
	# Create '''simple''' and '''materialized''' view		# Create '''simple''' and '''materialized''' ''view''
	#* Should take all related data from '''EMP''' and '''DEPT2'''		#* Should take all related data from '''EMP''' and '''DEPT2'''
	#* Drop table '''DEPT2''' and check if the views still work. What can we say? (-;		#* Drop table '''DEPT2''' and check if the views still work. ''What can we say? (-;''
	# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14		# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise '''no 14'''
	#* Query the view and show also job and grade		#* Query the view and show also '''job''' and '''grade'''
	DEP NAZ SAL JOB GRADE		DEP NAZ SAL JOB GRADE
	---------------------------------------- ----------------- ---------- --------- ----------		---------------------------------------- ----------------- ---------- --------- ----------

Lsokolowski1: /* DDL, DML */

2025-11-21T14:36:08Z

DDL, DML

← Older revision		Revision as of 14:36, 21 November 2025
Line 204:		Line 204:
	# Delete only not active spies		# Delete only not active spies
	#* discussion (-:		#* discussion (-:
			# Create '''simple''' and '''materialized''' view
			#* Should take all related data from '''EMP''' and '''DEPT2'''
			#* Drop table '''DEPT2''' and check if the views still work. What can we say? (-;
	# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14		# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14
	#* Query the view and show also job and grade		#* Query the view and show also job and grade
	~~#* Create '''simple''' and '''materialized''' view taking all related data from '''EMP''' and '''DEPT2'''. Drop table '''DEPT2''' and check if the views still work. What can we say? (-;~~
	DEP NAZ SAL JOB GRADE		DEP NAZ SAL JOB GRADE
	---------------------------------------- ----------------- ---------- --------- ----------		---------------------------------------- ----------------- ---------- --------- ----------

Lsokolowski1: /* DDL, DML */

2025-11-21T10:13:40Z

DDL, DML

← Older revision		Revision as of 10:13, 21 November 2025
Line 206:		Line 206:
	# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14		# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14
	#* Query the view and show also job and grade		#* Query the view and show also job and grade
	#* Create simple and materialized view taking all related data from EMP and DEPT2. Drop table DEPT2 and check if the views still work. What can we say? (-;		#* Create '''simple''' and '''materialized''' view taking all related data from '''EMP''' and '''DEPT2'''. Drop table '''DEPT2''' and check if the views still work. What can we say? (-;
	DEP NAZ SAL JOB GRADE		DEP NAZ SAL JOB GRADE
	---------------------------------------- ----------------- ---------- --------- ----------		---------------------------------------- ----------------- ---------- --------- ----------

Lsokolowski1: /* DDL, DML */

2025-11-21T10:12:56Z

DDL, DML

← Older revision		Revision as of 10:12, 21 November 2025
Line 206:		Line 206:
	# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14		# Create read-only ''view'' called '''SUMS_REPORT''' with the result of query from additional exercise no 14
	#* Query the view and show also job and grade		#* Query the view and show also job and grade
			#* Create simple and materialized view taking all related data from EMP and DEPT2. Drop table DEPT2 and check if the views still work. What can we say? (-;
	DEP NAZ SAL JOB GRADE		DEP NAZ SAL JOB GRADE
	---------------------------------------- ----------------- ---------- --------- ----------		---------------------------------------- ----------------- ---------- --------- ----------

Lsokolowski1: /* hierarchyid (mssql) */

2024-09-09T07:00:20Z

hierarchyid (mssql)

← Older revision		Revision as of 07:00, 9 September 2024
Line 251:		Line 251:
	## Get '''ancestors''' (first parent)		## Get '''ancestors''' (first parent)
	## Get '''root''' node		## Get '''root''' node
	## Find all '''descendants''' of ~~Caroline~~ (-:		## Find all '''descendants''' of Coraline (-:
	# '''HOMEWORK''': Make it '''better''', accordingly to below instructions		# '''HOMEWORK''': Make it '''better''', accordingly to below instructions
	#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>		#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>

Lsokolowski1: /* hierarchyid (mssql) */

2024-08-21T12:42:26Z

hierarchyid (mssql)

← Older revision		Revision as of 12:42, 21 August 2024
Line 254:		Line 254:
	# '''HOMEWORK''': Make it '''better''', accordingly to below instructions		# '''HOMEWORK''': Make it '''better''', accordingly to below instructions
	#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>		#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>
			# '''Homework2''': Improve '''emp''' table same way (--:
	<!--		<!--
	CREATE TABLE spies (		CREATE TABLE spies (

Lsokolowski1: /* hierarchyid (mssql) */

2024-08-21T12:40:30Z

hierarchyid (mssql)

← Older revision		Revision as of 12:40, 21 August 2024
Line 226:		Line 226:
	#* supports '''searching''' hierarchies with '''built-in functions'''		#* supports '''searching''' hierarchies with '''built-in functions'''
	#* related docs		#* related docs
	#** in general - <small>https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16</small>		#** in '''general''' - <small>https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16</small>
	#** supported functions(methods) - <small>https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16</small>		#** supported '''functions'''(methods) - <small>https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16</small>
	# Create this table with hierarchy:<syntaxhighlight lang="sql">		# Create this table with hierarchy:<syntaxhighlight lang="sql">
	CREATE TABLE Emps (		CREATE TABLE Emps (

Lsokolowski1: /* hierarchyid (mssql) */

2024-08-21T12:40:18Z

hierarchyid (mssql)

← Older revision		Revision as of 12:40, 21 August 2024
Line 227:		Line 227:
	#* related docs		#* related docs
	#** in general - <small>https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16</small>		#** in general - <small>https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16</small>
	#** supported ~~fucntions~~(methods) - <small>https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16</small>		#** supported functions(methods) - <small>https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16</small>
	# Create this table with hierarchy:<syntaxhighlight lang="sql">		# Create this table with hierarchy:<syntaxhighlight lang="sql">
	CREATE TABLE Emps (		CREATE TABLE Emps (

Lsokolowski1: /* hierarchyid (mssql) */

2024-08-20T14:16:37Z

hierarchyid (mssql)

← Older revision		Revision as of 14:16, 20 August 2024
Line 347:		Line 347:
	FROM emps		FROM emps
	WHERE ManagerID = hierarchyid::GetRoot();		WHERE ManagerID = hierarchyid::GetRoot();


			DECLARE @Manager hierarchyid
			SELECT @Manager = ManagerID FROM emps
			WHERE EmpID = 2;
			SELECT * FROM emps
			WHERE ManagerID.IsDescendantOf(@Manager) = 1;
	-->		-->
	<!-- 121 -->		<!-- 121 -->

Lsokolowski1: /* hierarchyid (mssql) */

2024-08-20T14:13:27Z

hierarchyid (mssql)

← Older revision		Revision as of 14:13, 20 August 2024
Line 252:		Line 252:
	## Get '''root''' node		## Get '''root''' node
	## Find all '''descendants''' of Caroline (-:		## Find all '''descendants''' of Caroline (-:
	# HOMEWORK: Make it better, accordingly to below instructions		# '''HOMEWORK''': Make it '''better''', accordingly to below instructions
	#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>		#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small>
	<!--		<!--