The Structured Query Language, abbreviated SQL, is a universal language used to create and managed computer databases. It is used in all popular database environments, including Microsoft SQL Server, Oracle, Borland/Corel Paradox, Microsoft Access, etc.

SQL can be pronounced Sequel or S. Q. L. On this site, we will consider the Sequel pronunciation.

When using SQL, you write a relatively short sections of code and view its result. Code based on SQL is referred to as a SQL statement. When writing an expression, SQL is not case-sensitive. This means that Case, case, and CASE represent the same word. This applies to keywords of the SQL or words that you will add in your statements.

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to get a value to the user. After creating the SELECT expression, you can pass it as the first argument to the RecordsetClass.Open() method.

When creating a recordset, you can use the whole table, including all of its columns. An alternative is to select only one or more columns from a table. In this case, the formula used on the SELECT operator is:

SELECT What FROM WhatObject;

The What factor of our syntax is the name of the column(s) of a table. The WhatObject factor can be the name of a table. Here is an example:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName FROM Persons;",
                    conADO,
	            ADODB::CursorTypeEnum::adOpenDynamic,
		    ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

To consider more than one column in a statement, you can list them in the What factor of our formula, separating them with a comma except for the last column. The syntax you would use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT FirstName, LastName, Gender FROM Persons;",
                    conADO,
	            ADODB::CursorTypeEnum::adOpenDynamic,
		    ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

In the previous lesson, we saw that, to select everything, that is, all columns, from a table, you could pass the name of the column as the first argument to the RecordsetClass.Open() method. To get the same effect, you can use the asterisk in place of the What factor of our formula. This would be done as follows:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT * FROM Persons;",
                    conADO,
	            ADODB::CursorTypeEnum::adOpenDynamic,
		    ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

If you create a SELECT statement that specifies the name or names of columns, the name of each column is used to represent it. If you want, you can specify a different string, sometimes named a caption, that would represent the column. To do this, the formula to use is:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice. The caption can be made of a word but the word cannot be one of the SQL's keywords. If the Caption is made of more than one word, you can include between an opening and a closing square brackets. Here is an example:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName As [Last Name] FROM Persons;",
                   conADO,
	            ADODB::CursorTypeEnum::adOpenDynamic,
		    ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

In the same way, you can apply the AS keyword to as many columns as you want by separating them with commas. Here is an example:

System::Void btnPeople_Click(System::Object^  sender, System::EventArgs^  e)
{
    ADODB::Recordset ^ rstPeople = gcnew ADODB::Recordset();
    ADODB::Connection ^ conADO = gcnew ADODB::Connection();

    conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
                 L"Data Source='C:\\Programs\\People.mdb'", L"", L"", 0);
    rstPeople->Open(L"SELECT LastName As [Last Name], Gender AS Sex FROM Persons;",
                   conADO,
	            ADODB::CursorTypeEnum::adOpenDynamic,
		    ADODB::LockTypeEnum::adLockOptimistic, 0);

    rstPeople->Close();
}

In the same way, you can mix number-based and string-based columns.