SQL tutorial for X++ / Dynamic AX

Hi,

SQL tutorial for AX buds.

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

--------------------------------------
Consider this below table as example for upcoming scenarios.

Student --------------------------------------
studentID	Name	Gender	Native
Stu001	Fathima	Female	IND
Stu002	Faridth	Male	IND
Stu003	Faheed	Male	KSA
Stu004	Fakrudeen	Male	KSA
Stu005	Firdose	Female	IND
Stu006	Fahima	Female	KSA

---------------------------------------

All the SQL syntax is not applicable for X++;

SQL Tutorial
SQL Select	Basic select concern record	while SELECT field_Name FROM table_name
SQL Distinct	List unique records	while SELECT DISTINCT field_Name FROM table_name
SQL Where	Where condition	while SELECT field_Name FROM table_name where Table_Name.Field_Name ==/=< conditions
SQL And & Or	Show only IND female student	while SELECT StudentID FROM Student where  Student.Gender == Gender::Female && Student.Native == "IND"
	Show all female record, IND native also	while SELECT StudentID FROM Student where  Student.Gender == Gender::Female || Student.Native == "IND"
SQL Order By	Sort the record	while SELECT DISTINCT field_Name FROM table_name ORDER BY table_name.field_Name ASC/DESC
SQL Insert Into		MyTable  MyTable;  ttsBegin;  select MyTable ;  MyTable.AccountNum = '1101'; MyTable.Name = 'MyName';  MyTable.insert();  ttsCommit;
SQL Update		Student Student; ttsBegin;  select forUpdate Student where Student.StudentID= "Stu0002"; Student.StudentID = 'Stu0009'; Student.Name = "Rahima"; Student.update();  ttsCommit;
SQL Delete		static void DeleteMultiRow1bJob(Args _args)
		{
		MyWidgetTable tabWidget; // extends xRecord.
		;
		ttsBegin;
		while select
		forUpdate
		tabWidget
		where tabWidget .quantity <= 100
		{
		tabWidget .delete();
		}
		ttsCommit;
		}
SQL Select Top	Code fastup the search and once found it will search for remaining data	select FirstOnly PersonnelNunber where HcmWorker.PersonnelNumber == "0000567";
SQL Between	There is no between in X++ but	while select Table_Name where Table_Name.Field_Name >= RangeFromValue && Table_Name.Field_Name <= RangeToValue
SQL Wildcards		while SELECT DISTINCT field_Name FROM table_name
SQL Not Null		while select Table_Name where Table_Name.Field_Name != Null

Regards,