Changes for page SQL Query

Summary

• Page properties (4 modified, 0 added, 0 removed)

Details

Page properties

Title

...	...	@@ -1,1 +1,1 @@
1		-intermediate-database-connectivity-sql-query
	1	+SQL Query

Author

...	...	@@ -1,1 +1,1 @@
1		-XWiki.ebakker
	1	+XWiki.dfirdausy

Default language

...	...	@@ -1,0 +1,1 @@
	1	+en

Content

...	...	@@ -1,13 +1,8 @@
1	1	{{container}}{{container layoutStyle="columns"}}(((
2		-= SQL Query =
	2	+In this microlearning, we will explore the fundamentals of SQL queries. By understanding these concepts, you will be equipped to write basic queries to retrieve and modify data in a database.
3	3
4		-In this microlearning, we will learn the basics of SQL queries. With the help of this information, you can start writing the correct queries to retrieve and write data from and to a database.
5		-
6	6	Should you have any questions, please get in touch with [[academy@emagiz.com>>mailto:academy@emagiz.com]].
7	7
8		-* Last update: September 7th, 2021
9		-* Required reading time: 6 minutes
10		-
11	11	== 1. Prerequisites ==
12	12
13	13	* Basic knowledge of the eMagiz platform
...	...	@@ -14,17 +14,8 @@
14	14
15	15	== 2. Key concepts ==
16	16
17		-Each type of external database will need specific queries to perform actions on the database level. Mostly we see that SQL queries are needed; therefore, we focus on them in this microlearning. In terms of CRUD operations on the database, the SQL language defines the following:
	12	+Each type of external database will need specific queries to perform actions on the database level. Mostly we see that SQL queries are needed; therefore, we focus on them in this microlearning.
18	18
19		-* INSERT = Create
20		-* SELECT = Read
21		-* UPDATE = Update
22		-* DELETE = Delete
23		-
24		-These basic operations on the database level should allow you to perform the action you want.
25		-
26		-
27		-
28	28	== 3. SQL Query ==
29	29
30	30	In this microlearning, we will learn the basics of SQL queries. With the help of this information, you can start writing the correct queries to retrieve and write data from and to a database. Just as with REST web services, the CRUD operations are represented within the SQL language. They are described as follows:
...	...	@@ -44,21 +44,21 @@
44	44	* The column names (id, created date, contents)
45	45	* The value per column (:headers[id], :headers[timestamp], :payload)
46	46
47		-When combining all of this you will end up with something like this: INSERT INTO mytable (id, created date, contents) values (:headers[id], :headers[timestamp], :payload). As you can see, we want to insert our row into the table called mytable. We want to insert three values in three separate columns (id, created date, contents). Furthermore, you should note that you can use the header values and (part of) the payload as dynamic input for those values. The notation as depicted above is paramount in making this work.
	33	+When combining all of this you will end up with something like this: {{code language="sql"}}INSERT INTO mytable (id, created date, contents) values (:headers[id], :headers[timestamp], :payload){{/code}}. As you can see, we want to insert our row into the table called mytable. We want to insert three values in three separate columns (id, created date, contents). Furthermore, you should note that you can use the header values and (part of) the payload as dynamic input for those values. The notation as depicted above is paramount in making this work.
48	48
49		-Note that when the primary key value already exists in the database, you will receive an error (duplicate key violation). Just as you would expect when calling a POST twice in a row with the same unique identifier.
	35	+{{info}}When the primary key value already exists in the database, you will receive an error (duplicate key violation). Just as you would expect when calling a POST twice in a row with the same unique identifier.{{/info}}
50	50
51	51	=== 3.2 SELECT ===
52	52
53	53	With a select statement, you read one or more records from the database. Within a select statement, you define the following:
54	54
55		-* The columns you want to be returned (\* in case of all columns)
	41	+* The columns you want to be returned (~* in case of all columns)
56	56	* The table from which to read
57	57	* Optional: A condition (WHERE x=x)
58	58
59		-When combining all of this, you will end up, in the simplest form, with something like this: SELECT \* from mytable. Expanding on that, we could define that we only want to retrieve the id and contents column. To do so, we slightly alter our SQL query to this: SELECT id,contents from mytable. Building on that further, we could add a condition to the statement, a so-called where clause. With the help of that clause, we can even further narrow down our result set. An example of that would be SELECT id,contents from mytable WHERE id = :headers[id].
	45	+When combining all of this, you will end up, in the simplest form, with something like this: SELECT ~* from mytable. Expanding on that, we could define that we only want to retrieve the id and contents column. To do so, we slightly alter our SQL query to this: SELECT id,contents from mytable. Building on that further, we could add a condition to the statement, a so-called where clause. With the help of that clause, we can even further narrow down our result set. An example of that would be SELECT id,contents from mytable WHERE id = :headers[id].
60	60
61		-Note that a SELECT statement never alters the state of the row in the database itself.
	47	+{{info}}A SELECT statement never alters the state of the row in the database itself.{{/info}}
62	62
63	63	=== 3.3 UPDATE ===
64	64
...	...	@@ -79,29 +79,26 @@
79	79
80	80	When combining all of this, you will end up with something as follows DELETE FROM mytable WHERE id = :headers[id]. This statement will delete the row within the table for which the id matches the id in my header. This way, you can clean up parts of your database table.
81	81
82		-Note that you should be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
	68	+{{warning}}You should be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!{{/warning}}
83	83
	70	+== 4. Key takeaways ==
84	84
	72	+* These examples cover the basic SQL operations: INSERT (Create), SELECT (Read), UPDATE (Update), and DELETE (Delete).
	73	+* SQL queries allow you to perform essential CRUD operations to manage data in a database.
	74	+* You can use dynamic values in SQL queries through SpEL expressions, making your queries more adaptable and flexible.
	75	+* While SQL is commonly used, other databases may require different query languages or syntax for similar operations.
	76	+* Always ensure conditions (like WHERE clauses) are applied to avoid unintentional modifications or deletions of data.
85	85
86		-== 4. Assignment ==
	78	+== 5. Suggested additional readings ==
87	87
88		-See if you can find any database implementation within the projects you can access. This assignment can be completed with the help of the (Academy) project that you have created/used in the previous assignment.
	80	+If you are interested in this topic and want more information on it, please read the help text provided by eMagiz and see the following links:
89	89
90		-== 5. Key takeaways ==
91		-
92		-* These examples cover the basics of the SQL language
93		-* The SQL language has its form of CRUD operations
94		-* You can dynamically fill the values of the SQL properties with the help of SpEL expressions
95		-* Other types of databases might require other queries
96		-
97		-
98		-
99		-If you are interested in this topic and want more information, please read the help text provided by eMagiz and read more information on the following link:
100		-
101		-* https://www.w3schools.com/sql/default.Asp
102		-
103		-== 7. Silent demonstration video ==
104		-
105		-As this is more of theoretical microlearning, there is no video accompanying the microlearning.
106		-
	82	+* [[eMagiz Store (Menu)>>doc:Main.eMagiz Store.Accelerators.WebHome||target="blank"]]
	83	+** [[Accelerators (Navigation)>>doc:Main.eMagiz Store.Accelerators.WebHome||target="blank"]]
	84	+*** [[Database Connectivity (Explanation)>>doc:Main.eMagiz Store.Accelerators.Database Connectivity.WebHome||target="blank"]]
	85	+* [[Expert Level (Menu)>>doc:Main.eMagiz Academy.Microlearnings.Expert Level.WebHome||target="blank"]]
	86	+** [[Solution Architecture (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Expert Level.Solution Architecture.WebHome||target="blank"]]
	87	+*** [[H2 Database for other applications (Explanation)>>doc:Main.eMagiz Academy.Microlearnings.Expert Level.Solution Architecture.expert-solution-architecture-h2-database-for-other-applications||target="blank"]]
	88	+* [[SQL Query (Search Results)>>url:https://docs.emagiz.com/bin/view/Main/Search?sort=score&sortOrder=desc&highlight=true&facet=true&r=1&f_space_facet=0%2FMain.&l_space_facet=10&f_type=DOCUMENT&f_locale=en&f_locale=&f_locale=en&text=%22sql+query%22||target="blank"]]
	89	+* [[SQL Explained (External)>>https://www.w3schools.com/sql/default.Asp||target="blank"]]
107	107	)))((({{toc/}}))){{/container}}{{/container}}