:Revision=1
:HTML
<<
128.129
SQL-Object|SQL
$NoSort
--
The IP-Object SQL enables the access to data bases with SQL (Structured Query Language)

All data bases which can be accessed via BDE can be used. Especially dBase, Paradox and all data base systems which can be reached via ODBC (thus e.g. Access) are included.

At the moment only a single data base can be opened at a time, but it is planned to allow more than one data bases to be accessed simultaneously. 
Therefore, for every access, the data base number must be given as the first parameter. For the moment this number is always 1 for the only data base that is available.

The access is made as follows:

<ol>
<li>The SQL data base is <i>assigned</i> to a server or a file path - this is normally a static stiing in the basic setup. At this point the server or the path doesn't need to exist.</li>
<li>The SQL data base is <i>connected</i> to the the server or the path. Now the server resp. the path must be available, since the SQL data base searches for avaialble tables on the server resp. path. This is in fact similar to the opening of an I/O interface. The connection can be automatic if AutoConnect is activated in the basic setup. </li>
<li>A SQL command is made towards the SQL data base (<!SW>SELECT, <!SW>INSERT, <!SW>UPDATE, <!SW>DELETE ...). In case of a successful <!SW>SELECT command, an open dataset is generated, quasi a small table in memory, which contains the result of the <!SW>SELECT command.</li>
<li>The data can be accessed record by record. For this purpose the <!SW>SQL.First and <!SW>SQL.Next commands can be used. <!SW>SQL.EOF can be used to check if the end of the data is reached.</li>
<li>The fields in the selected rows can be accessed with the <!SW>SQL.Field command.</li>
</ol>

Example:
<!CODE>
if SQL.IsActive (1) then begin
  SQL.Connect (1);
  if SQL.IsConnected (1) then begin
    SQL.Execute (1, 'SELECT Articlename FROM Orders');
    SQL.First (1);
    while not SQL.EOF (1) do begin
      debug.show (1, SQL.Field (1, 'Articlename'));
      SQL.Next(1);
    end;
    SQL.Disconnect (1);
  end;
end;
<!TXT>
>>

<<
128.129.1
Functions of the SQL Object|Functions
--
>>

<<
128.129.1.1
SQL.IsActive|IsActive
--
<!DEF>
function <!TW>SQL.IsActive : real;        
<!TXT>
Returns <!RW>TRUE if the SQL functions are globally activated in the basic setup.


<!DEF>
function <!TW>SQL.IsActive (rDBNr : real) : real;
<!TXT>
Returns <!RW>TRUE if the SQL data base <!PW>rDBNr is activated in the basic setup.
>>

<<
128.129.1.2
SQL.IsConnected|IsConnected
--
<!DEF>
function <!TW>SQL.IsConnected (rDBNr : real) : real;
<!TXT>
Returns <!RW>TRUE if the SQL data base <!PW>rDBNr is connected to the data base (server or file).

A data base can be 'Connected' only if it is activated in the basic setup.

SQL commands can be made only towards data bases which are in 'Connected' state.
>>

<<
128.129.1.3
SQL.IsOpen|IsOpen
--
<!DEF>
function <!TW>SQL.IsOpen (rDBNr : real) : real;
<!TXT>
Returns <!RW>TRUE if there is an open dataset for the SQL data base <!PW>rDBNr. I.e. if a SQL SELECT command has been executed successfuly and the obtained data can be accessed.
>>

<<
128.129.1.4
SQL.Error|Error
--
<!DEF>
function <!TW>SQL.Error (rDBNr : real) : string;
<!TXT>
Returns an error text if an error occured during the last command to the data base <!PW>rDBNr. Returns an empty string '' if no error occured. The function can be called several times. An error text is deleted with the next errorless command.
>>

<<
128.129.1.5
SQL.EOF|EOF
--
<!DEF>
function <!TW>SQL.EOF (rDBNr : real) : real;
<!TXT>
Returns <!RW>TRUE, if 
<ul indent=12>
<li>the data base rDBNr is not active</li>
<li>the data base rDBNr is not Connected</li>
<li>the data base rDBNr has no open dataset</li>
<li>the data base rDBNr has an open dataset, but it is empty</li>
<li>the <!RW>SQL.Next has reached the end of the open dataset</li>
</ul>

in other words:

Returns <!RW>FALSE, as long as data can be read from the open dataset.
>>

<<
128.129.1.5
SQL.Field|Field
--
<!DEF>
function <!TW>SQL.Field (rDBNr : real; sFieldName : string) : string;
<!TXT>
Returns the content of the field <!PW>sFieldName from the current record of the open dataset.

 <!PW>sFieldname is case independant. If the dataset is not open, or if the end of the dataset was reached, or if there is no field with the name <!PW>sFieldname, '**SQLError**' is returned. 


<!DEF>
function <!TW>SQL.Field (rDBNr : real; rFieldNr : real) : string;
<!TXT>
Returns the content of the rFieldNr-th of the current record of the open dataset. Counting starts with 1.

If the dataset is not opened, or the end of the dataset is reached, or if the rFieldNr is bigger than the number of fields, '**SQLError**' is returned.
>>

<<
128.129.1.5.1
SQL.Field.Count|Count
--
<!DEF>
function <!TW>SQL.Field.Count (rDBNr : real) : real;
<!TXT>
Returns the number of fields (= number of columns) in the open dataset of the data base <!RW>rDBNr.

If there is no open dataset, -1 is returned.
>>

<<
128.129.1.5.2
SQL.Field.Name|Name
--
<!DEF>
function <!TW>SQL.Field.Name (rDBNr, rIdx : real) : string;
<!TXT>
Returns the name of the rIdx-th field (= the rIdx-th column) in the open dataset of the data base <!PW>rDBNr. Counting starts with 1.

If there is no open dataset or if <!PW>rIdx is bigger than the number of fields, '**SQLError**' is returned.
>>

<<
128.129.1.5.3
SQL.Field.Number|Number
--
<!DEF>
function <!TW>SQL.Field.Number (rDBNr : real; sFieldName : string) : real;
<!TXT>
Returns the index of the field with the name <!PW>sFieldName in the open dataset of the data base <!PW>rDBNr. Counting starts with 1.

If there is no open dataset or if there is no field with the name <!PW>sFieldName, then 0 (=<!RW>FALSE) is returned.

The function can be used both for the inquiry of the field index:
<!CODE>
nIndex:=SQL.Field.Number (1, 'Barcode');
<!TXT>
and to check only its existance :
<!CODE>
if SQL.Field.Number (1, 'Barcode') then begin
<!TXT>
>>

<<
128.129.2
Procedures of the SQL Object|Procedures
--
>>

<<
128.129.2.1
SQL.Execute|Execute
--
<!DEF>
procedure <!TW>SQL.Execute (rDBNr : real; sSQLCommand : string);
<!TXT>
Executes the SQL-command <!PW>sSQLCommand towards the data base <!PW>rDBNr. Therefore, the data base must be in 'Connected' state.

If a SELECT command is successful, an open dataset is generated which can be accessed with <!RW>SQL.First, <!RW>SQL.Next, <!RW>SQL.EOF, <!RW>SQL.Field etc. 

Kind and extent of the possible SQL commands depend on the SQL-Server.
>>

<<
128.129.2.2
SQL.First|First
--
<!DEF>
procedure <!TW>SQL.First (rDBNr : real);
<!TXT>
Selects the first record of an open dataset.

If there is no data package open, nothing happens.
>>

<<
128.129.2.3
SQL.Next|Next
--
<!DEF>
procedure <!TW>SQL.Next (rDBNr : real);
<!TXT>
Selects the next record of an open dataset.

If there is no open dataset or the the end is reached, nothing happens.
>>

<<
128.129.2.4
SQL.Connect|Connect
--
<!DEF>
procedure <!TW>SQL.Connect (rDBNr : real);
<!TXT>
Connects the data base <!PW>rDBNr with its data dource.
>>

<<
128.129.2.5
SQL.DisConnect|DisConnect
--
<!DEF>
procedure <!TW>SQL.DisConnect (rDBNr : real);
<!TXT>
Disconnects the data base <!PW>rDBNr from its data source.
>>

<<
128.129.3
SQL-Basics
$NoSort
--
<h2>What is SQL?</h2>
<ul indent=10>
<li><b>SQL</b> stands for <i><B>S</B>tructured <B>Q</B>uery <B>L</B>anguage</i></li>
<li><B>SQL</B> enables the access to a data base</li>
<li><B>SQL</B> can retrieve data from a data base</li>
<li><B>SQL</B> can introduce new records in a data base</li>
<li><B>SQL</B> can modify records in a data base</li>
<li><B>SQL</B> can delete records in a data base</li>
</ul>

<h3>SQL is a standard - but...</h3>

SQL was defined by ANSI (American National Standards Institute) as a standard language to be used for the access and modification of data base systems.

Unfortunately the manufacturers who implement SQL as access language for their data bases have implemented manufacturer specific extensions of the standard language. Even the standard language elements may differ in minor but nasty details.

<h3>Tables</h3>
Each data base contains one ore more tables.
Each table has a unique name ("customers", "orders"),
Tables contain records (rows) with data.

An example for a table with the name "Persons"
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><td colspan=4>Tabelle <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Joachim<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Hamburg<!>
<!ETBL>

The table contains 3 records (rows - one per person) and 4 fields (columns) (FirstName, LastName, Age, Place).

<h3>SQL queries</h3>
With SQL one can make queries to data bases and receive datasets in return.

Example:
<!CODE>
SELECT LastName FROM Persons
<!TXT>

The result is:
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=1>Result table<!>
<!><B>LastName</B><!>
<!>Meier<!>
<!>Klein<!>
<!>Schmidt<!>
<!ETBL>

<!REM>
Note: 
some SQL systems expect a semicolon at the end of a command!
<!TXT>

<h3>SQL Data Manipulation Language (DML)</h3>
SQL can be used not only for queries but also for data manipulation in the data base.

The query and manipulation commands form the DML part of SQL:
<ul indent=12>
<li><!SW>SELECT - extract data</li>
<li><!SW>UPDATE - modify data</li>
<li><!SW>DELETE - delete records</li>
<li><!SW>INSERT <!SW>INTO - insert new records</li>
</ul>

<h3>SQL Data Definition Language (DDL)</h3>
The commands of the Data Definition Language (DDL) enable the modification of the structure of the data base itself.

The most important commands of the DDL are:
<ul indent=12>
<li><!SW>CREATE TABLE - create new tables</li>
<li><!SW>ALTER TABLE - alter table structure</li>
<li><!SW>DROP TABLE - remove entire tables</li>
<li><!SW>CREATE INDEX - create index</li>
<li><!SW>DROP INDEX - delete index</li>
</ul>
>>

<<
128.129.3.1
SELECT (SQL Command)|SELECT
--
The <!SW>SELECT command is used to extract data from the data base. The data is returned under the form of a table (dataset).

Syntax
<!CODE>
SELECT columnname/s FROM tablenname
<!TXT>
<h4>Select single columns</h4>
To select only the columns 'FirstName' and 'LastName' the following SQL command is used:
<!CODE>
SELECT FirstName,LastName FROM Persons
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!>
<!>Walter<!>Meier<!>
<!>Werner<!>Klein<!>
<!>Petra<!>Schmidt<!>
<!ETBL>

<h4>Select All Columns</h4>
To get all the columns a * is specified instead of the names of the columns.
<!CODE>
SELECT * FROM Persons
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<h4>The result-table</h4>
The access to the data in the result table is no longer part of SQL. For this purpose, the interpreter-Object SQL provides the <!RW>SQL.First, <!RW>SQL.Field and <!RW>SQL.Next commands.
>>

<<
128.129.3.1.1
DISTINCT (SQL keyword)|DISTINCT
--
The DISTINCT key word is used if only different values should be returned.

Syntax
<!CODE>
SELECT DISTINCT columnname/s FROM tablename
<!TXT>

If all the customers from a orders table should be displayed the following SQL command can be used:
<!CODE>
SELECT customer FROM orders
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Original table <b>Orders</b><!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>234234<!>
<!>AlphaOmega<!>034643<!>
<!>SoftySoft<!>904787<!>
<!>Hardliners<!>847646<!>
<!ETBL>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=1>Result Table<!>
<!><b>Customer</b><!>
<!>SoftySoft<!>
<!>AlphaOmega<!>
<!>SoftySoft<!>
<!>Hardliners<!>
<!ETBL>

Poblem:
SoftySoft appears twice!

To get each return value only once, DISTINCT is used:
<!CODE>
SELECT DISTINCT customer FROM orders
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=1>Result Table<!>
<!><b>Customer</b><!>
<!>SoftySoft<!>
<!>AlphaOmega<!>
<!>Hardliners<!>
<!ETBL>

Now SoftySoft appears only once.
>>

<<
128.129.3.1.2
ORDER BY (SQL keyword)|ORDER BY
--
The ORDER BY key word is used for sorting the outputs.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Original table <b>Orders</b><!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>904787<!>
<!>AlphaOmega<!>034643<!>
<!>SoftySoft<!>234234<!>
<!>Hardliners<!>847646<!>
<!ETBL>

<!CODE>
SELECT * FROM Orders
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>904787<!>
<!>AlphaOmega<!>034643<!>
<!>SoftySoft<!>234234<!>
<!>Hardliners<!>847646<!>
<!ETBL>

To sPlace the result alphabetically by customers:
<!CODE>
SELECT * FROM Orders ORDER BY Customer
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>AlphaOmega<!>034643<!>
<!>Hardliners<!>847646<!>
<!>SoftySoft<!>904787<!>
<!>SoftySoft<!>234234<!>
<!ETBL>

To sPlace the result alphabetically by customers and numerically by the contract number:
<!CODE>
SELECT * FROM Orders ORDER BY Customer,OrderID
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>AlphaOmega<!>034643<!>
<!>Hardliners<!>847646<!>
<!>SoftySoft<!>234234<!>
<!>SoftySoft<!>904787<!>
<!ETBL>
>>

<<
128.129.3.1.2.1
ASC (SQL keyword)|ASC
--
The standard sPlaceing order is ascending; if ASC is specified, this can be requested explicitely.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Original table <b>Orders</b><!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>904787<!>
<!>AlphaOmega<!>034643<!>
<!>SoftySoft<!>234234<!>
<!>Hardliners<!>847646<!>
<!ETBL>

<!CODE>
SELECT * FROM Orders ORDER BY Customer ASC
<!TXT>
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>AlphaOmega<!>034643<!>
<!>Hardliners<!>847646<!>
<!>SoftySoft<!>234234<!>
<!>SoftySoft<!>904787<!>
<!ETBL>
>>

<<
128.129.3.1.2.2
DESC (SQL keyword)|DESC
--
The standard sPlaceing order is ascending; if DESC (descending) is specified, a descending sequence can be obtained.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Original table <b>Orders</b><!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>904787<!>
<!>AlphaOmega<!>034643<!>
<!>SoftySoft<!>234234<!>
<!>Hardliners<!>847646<!>
<!ETBL>

<!CODE>
SELECT * FROM Orders ORDER BY Customer DESC
<!TXT>
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Customer</b><!><b>OrderID</b><!>
<!>SoftySoft<!>234234<!>
<!>SoftySoft<!>904787<!>
<!>Hardliners<!>847646<!>
<!>AlphaOmega<!>034643<!>
<!ETBL>
>>

<<
128.129.3.1.3
Aliasse (SQL)|Aliasse
--
Alioasses can be used in SQL if a column or table names must be repeated several times.

<h3>Column-Alias</h3>
Syntax
<!CODE>
SELECT column AS column_alias FROM table
<!TXT>

Example:

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!>Hubert<!>Maler<!>32<!>Hamburg<!>
<!ETBL>

Replace LastName with an alias:
<!CODE>
SELECT FirstName, LastName AS Nn FROM Persons WHERE Nn LIKE 'M%'
<!TXT>
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!>
<!>Walter<!>Meier<!>
<!>Hubert<!>Maler<!>
<!ETBL>

<h3>Table-Alias</h3>
Syntax
<!CODE>
SELECT column FROM table AS table_alias
<!TXT>

The table alias is frequently used in case of JOINs.
>>

<<
128.129.3.1.4
Joins and Keys (SQL)|JOIN
--
To link data from more than one table a <!SW>JOIN must be made.

The tables are linked to each other by a key. The primary key is a column with a unique value for each row. The rows in the data bases can be assigned to each other by such keys.

In the following customer table the <!SW>CustomerID is the primery key. Two records never have the same <!SW>CustomerID.

The same is true for the column <!SW>ContractID in the contract table.

The <!SW>CustomerID in the contract table is the link between the tables.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Original table <b>Customers</b><!>
<!><b>CustomerID</b><!><b>Name</b><!>
<!>01<!>OutOfCashLtd.<!>
<!>02<!>PayLateTrust<!>
<!>03<!>WillBuyIfCheap<!>
<!>04<!>MeMyselfAndI<!>
<!ETBL>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=3>Original table <b>Orders</b><!>
<!><b>OrderID</b><!><b>Product</b><!><b>CustomerID</b><!>
<!>001<!>Table<!>01<!>
<!>002<!>Bed<!>03<!>
<!>003<!>Chair<!>03<!>
<!>004<!>Cooker<!>05<!>
<!ETBL>

The data from both tables can be combined with a SQL-command:

Example: Which product was ordered by whom?
<!CODE>
SELECT Customers.Name, Orders.Product
FROM Customers, Orders
WHERE Customers.CustomerID=Orders.CustomerID
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Name</b><!><b>Product</b><!>
<!>OutOfCashLtd.<!>Table<!>
<!>WillBuyIfCheap<!>Bed<!>
<!>WillBuyIfCheap<!>Chair<!>
<!ETBL>

Example: Who ordered the table?
<!CODE>
SELECT Customers.Name
FROM Customers,Orders
WHERE Customers.CustomerID=Orders.CustomerID AND Orders.Product='Table'
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=1>Result table<!>
<!><b>Name</b><!>
<!>OutOfCashLtd.<!>
<!ETBL>

Another method is to use the <!SW>JOIN key word to join the tables:
There are 3 possibilties to do this:
<li>Inner Join</li>
<li>Left Join</li>
<li>Right Join</li>
>>

<<
128.129.3.1.4.1
INNER JOIN (SQL keyword)|INNER JOIN
--
Syntax
<!CODE>
SELECT column/s FROM table1 
INNER JOIN table2 ON table1.key = table2.foreignkey
<!TXT>

Who ordered which product?
<!CODE>
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
<!TXT>
The <!SW>INNER <!SW>JOIN will find all records in both columns, that have a match. Records in Customers, without a match will not be listed.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Name</b><!><b>Product</b><!>
<!>OutOfCashLtd.<!>Table<!>
<!>WillBuyIfCheap<!>Bed<!>
<!>WillBuyIfCheap<!>Chair<!>
<!ETBL>
>>

<<
128.129.3.1.4.2
LEFT JOIN (SQL keyword)|LEFT JOIN
--
Syntax
<!CODE>
SELECT column/s FROM table1 
LEFT JOIN table2 ON table1.key = table2.foreignkey
<!TXT>

Show all customers and their orders - if any
<!CODE>
SELECT Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
<!TXT>

The LEFT JOIN finds all the records in the left table, even if there is no concordance found in the right table. Those rows from Customers, without a match in orders will be returned too.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Name</b><!><b>Product</b><!>
<!>OutOfCashLtd.<!>Table<!>
<!>PayLateTrust<!><font size=-9 color=#dfdfff>.</font><!>
<!>WillBuyIfCheap<!>Bed<!>
<!>WillBuyIfCheap<!>Chair<!>
<!>MeMyselfAndI<!><font size=-9 color=#dfdfff>.</font><!>
<!ETBL>
>>

<<
128.129.3.1.4.3
RIGHT JOIN (SQL keyword)|RIGHT JOIN
--
Syntax
<!CODE>
SELECT column/s FROM table1 
RIGHT JOIN table2 ON table1.key = table2.foreignkey
<!TXT>

Show all orders and the orderers
<!CODE>
SELECT Customers.Name, Orders.Product
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
<!TXT>

The RIGHT JOIN returns all the rows from the right table even if there is no corespondance in the left table. Those rows in orders, without a match in Customers, will be returned too.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=2>Result table<!>
<!><b>Name</b><!><b>Product</b><!>
<!>OutOfCashLtd.<!>Table<!>
<!>WillBuyIfCheap<!>Bed<!>
<!>WillBuyIfCheap<!>Chair<!>
<!><font size=-9 color=#dfdfff>.</font><!>Cooker<!>
<!ETBL>
>>

<<
128.129.3.1.5
WHERE (SQL keyword)|WHERE
--
The <!SW>WHERE clause is used to select certain records.

Syntax
<!CODE>
SELECT column/s FROM table WHERE column operator value
<!TXT>

The following operators are possible:
<!STBL>
<tr bgcolor=f0f0f0 align=center><TD><b>Operator</b><!><b>Description</b><!>
<!c>=<!>Equality<!>
<!c>&lt;&gt;<!>Inequality<!>
<!c>&gt;<!>Greater then<!>
<!c>&lt;<!>Less then<!>
<!c>&gt;=<!>Greater or equal<!>
<!c>&lt;=<!>Less or equal<!>
<!c>BETWEEN .. AND<!>between two values<!>
<!c>LIKE<!>similar to a value<!>
<!ETBL>
<!REM>
Attention: 
some systems use != instead of &lt;&gt;
<!TXT>

<h4>Example</h4>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

In order to select from the Persons table only those persons who live in Stuttgart the following command is used:
<!CODE>
SELECT * FROM Persons WHERE Place='Stuttgart'
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>


SQL uses single quotaion marks for text values (some systems allow double quotation marks too). Numeric values are written without quotation marks.

<h4>For text values:</h4>

Correct:
<!CODE>
SELECT * FROM Persons WHERE Place='Stuttgart'
<!TXT>
Incorrect:
<!CODE>
SELECT * FROM Persons WHERE Place=Stuttgart
<!TXT>

<h4>For mumeric values:</h4>

Correct:
<!CODE>
SELECT * FROM Persons WHERE Age>40
<!TXT>
Incorrect:
<!CODE>
SELECT * FROM Persons WHERE Age>'40'
<!TXT>
>>


<<
128.129.3.1.5.1
LIKE (SQL keyword)|LIKE
--
The <!SW>LIKE operator is used to search for patterns (partial concordance).

Syntax
<!CODE>
SELECT column/s FROM table WHERE column LIKE sample
<!TXT>

The <!CW>"%" character is a wildcard and replaces any characters before and after the pattern

The following command will find persons whose first name starts with <!CW>'W':
<!CODE>
SELECT * FROM Persons WHERE FirstName LIKE 'W%'
<!TXT>

The following command will find persons whose first name ends with <!CW>'er':
<!CODE>
SELECT * FROM Persons WHERE FirstName LIKE '%er'
<!TXT>

The following command will find persons whose first name contains the letter <!CW>'r':
<!CODE>
SELECT * FROM Persons WHERE FirstName LIKE '%r%'
<!TXT>
>>

<<
128.129.3.1.5.2
BETWEEN (SQL keyword)|BETWEEN
--
The <!SW>BETWEEN ... <!SW>AND operator selects values between 2 basic values; these basic values can be texts, numbers or dates.

<!CODE>
SELECT column/s FROM table WHERE column BETWEEN value1 AND value2
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!>Hubert<!>Maler<!>32<!>Hamburg<!>
<!ETBL>

List all persons whose LastName is between 'Klein' (inclusive) and 'Meier' (exclusive):
<!CODE>
SELECT * FROM Persons WHERE LastName BETWEEN 'Klein' AND 'Meier'
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Hubert<!>Maler<!>32<!>Hamburg<!>
<!ETBL>

<!REM>
Attention! 
Depending on the data base system, BETWEEN...AND is handled differently in regard of the inclusive/ exclusive state of basic values!
<!TXT>
>>

<<
128.129.3.1.5.3
AND & OR (SQL keyworde)|AND & OR
--
<!SW>AND and <!SW>OR are used to combine several partial conditions for <!SW>WHERE.

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

Search all persons who live in Stuttgart <b>and</b> are older than 35:
<!CODE>
SELECT * FROM Persons WHERE Place='Stuttgart' AND Age>35
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

Search of all persons who live in Stuttgart <b>or</b> are older than 35:
<!CODE>
SELECT * FROM Persons WHERE Place='Stuttgart' OR Age>35
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

AND and OR can be also used togehter; the partial conditions are enclosed in brackets ():
<!CODE>
SELECT * FROM Persons 
WHERE (FirstName LIKE 'W%' AND Age>35) OR Place='Stuttgart'
<!TXT>
>>

<<
128.129.3.1.5.4
NOT (SQL keyword)|NOT
--
The <!SW>NOT operator negates a condition.

<!CODE>
SELECT column/s FROM table WHERE NOT condition
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!>Hubert<!>Maler<!>32<!>Hamburg<!>
<!ETBL>

List all persons younger than 35 or older than 40
<!CODE>
SELECT * FROM Persons 
WHERE NOT Age BETWEEN 35 AND 40
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table<!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Hubert<!>Maler<!>32<!>Hamburg<!>
<!ETBL>
>>

<<
128.129.3.2
UPDATE (SQL Command)|UPDATE
--
The <!SW>UPDATE command is used to modify existing data in a data base. 

Syntax
<!CODE>
UPDATE table 
SET column = newvalue 
WHERE condition
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<h4>Modify a field</h4>
<!CODE>
UPDATE Persons SET LastName = 'Meyer' WHERE LastName = 'Schmidt'
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Meyer<!>36<!>Stuttgart<!>
<!ETBL>

<h4>Modify several columns</h4>
<!CODE>
UPDATE Persons SET FirstName = 'Klaus', Place = 'Hamburg' WHERE Age < 40
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Hamburg<!>
<!>Petra<!>Schmidt<!>36<!>Hamburg<!>
<!ETBL>
>>

<<
128.129.3.3
INSERT INTO (SQL Command)|INSERT INTO
--
The <!SW>INSERT <!SW>INTO command is used to insert new records into a table.

Syntax
<!CODE>
INSERT INTO table VALUES (value1, value2,....)
<!TXT>

The columns where the records should be inserted can be specified:
<!CODE>
INSERT INTO table (column1, column2,...) VALUES (value1, value2,....)
<!TXT>

<h4>Insert into all columns</h4>

If this table is queried
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!ETBL>

with this command
<!CODE>
INSERT INTO Persons VALUES ('Petra', 'Schmidt', 36, 'Stuttgart')
<!TXT>

it produces
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<h4>Insertions when columns are specified</h4>

If the following command is executed on the previuous table:
<!CODE>
INSERT INTO Persons (FirstName, Age) VALUES ('Werner', 33)
<!TXT>

The result is the following:
<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Result table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!>Werner<!><font size=-9 color=#dfdfff>.</font><!>33<!><font size=-9 color=#dfdfff>.</font><!>
<!ETBL>
>>

<<
128.129.3.4
DELETE (SQL Command)|DELETE
--
The <!SW>DELETE command deletes records from a data base.

Syntax
<!CODE>
DELETE FROM table WHERE condition
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Werner<!>Klein<!>33<!>Stuttgart<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<h4>Delete a record</h3>

Delete the records for "Werner Klein"
<!CODE>
DELETE FROM Persons WHERE LastName = 'Klein'
<!TXT>

<!STBL bgcolor=#efefff>
<tr bgcolor=dfdfff><TD colspan=4>Original table <b>Persons</b><!>
<!><b>FirstName</b><!><b>LastName</b><!><b>Age</b><!><b>Place</b><!>
<!>Walter<!>Meier<!>45<!>Frankfurt<!>
<!>Petra<!>Schmidt<!>36<!>Stuttgart<!>
<!ETBL>

<h4>Delete all records</h4>

It is possible to delete all records. The result is an intact table with a complete table structure and indices, but without data:
<!CODE>
DELETE FROM tabelle
<!TXT>
or
<!CODE>
DELETE * FROM tabelle
<!TXT>
>>
