SQL (Structured Query Language)

 SQL (Structured Query Language) is de facto standard for processing structured data. It is used in relational databases for reading and writing data from/to databases and at the same time it is used for defining database objects themselves (data structure). Check Wikipedia and w3schools for more details about SQL.

Let`s look at couple of examples.

Let`s assume following table called Orders.

Id	Order_no	Amount	Currency	Date	Vendor
1	4200000353	20.000	USD	1.10.2018	2
2	4200000354	5.000	EUR	3.10.2018	3
3	4200000355	2.100	USD	29.10.2018	3
4	4200000356	10.500	USD	3.11.2018	3

We will select three columns from the table.

SELECT Order_no, Amount, Curency
FROM Orders

This query will return these records.

Order_no	Amount	Currency
4200000353	20.000	USD
4200000354	5.000	EUR
4200000355	2.100	USD
4200000356	10.500	USD

Let`s assume now a table with data about vendors that is called Vendors. We have the data in various tables due to normalization that will ensure data integrity. We do not want to repeat at each order the same data about vendors and risk that we state different data about vendor at various orders for the same vendor. This is why we will state in the order just a reference (link) to the vendor (vendor Id) and all the vendor data will be stored in a single place only - in a single row of the table Vendors.

Id	Name	Address	Commodity	Segmentation
1	Fastcom, Inc.	Palute Way 74, Sacramento, 95670	IT	A
2	TOP Catering, Inc.	Ellis Ave 78 , Chicago, 60637	Catering	B
3	SoftFusion, GmbH	Seidenstraße 4, Stuttgart, 70174	IT	B
4	Paperless, Inc.	5th Avenue 154, New York, 10004	Consulting	B

We will add to the above example data from this second table which is linked with the first table. Please note that we provided tables with aliases ord a vend, that we can use in the query. We can see that table Orders refers in column Vendor to the table Vendors. Values in column Vendor (in the table Orders) correspond to values in column Id (in the table Vendors). This will allow us to link the data and get a view combining data from both tables.

SELECT ord.order_no, ord.amount, ord.currency, vend.name
FROM orders ord
LEFT JOIN vendors vend ON ord.vendor = vend.id

Note that we have stated orders and vendors instead of Orders and Vendors. This is ok since SQL is not case sensitive in names of objects (tables and columns).

Above query will return these records.

order_no	amount	currency	vendor
4200000353	20.000	USD	TOP Catering, Inc.
4200000354	5.000	EUR	SoftFusion, Gmbh
4200000355	2.100	USD	SoftFusion, Gmbh
4200000356	10.500	USD	SoftFusion, Gmbh

We want to select only orders in USD now. Therefore, we will restrict the data by means of clause WHERE.

SELECT ord.order_no AS Order_number, ord.amount AS Amount, ord.currency AS Currency, vend.name AS Vendor
FROM orders ord
LEFT JOIN vendors vend ON ord.vendor = vend.id
WHERE ord.currency = 'USD'

We have stated the text string USD in quotation marks in order the database engine recognizes that it is string and not a name of some object. We have stated name of the column, that the database engine should display, after the key word AS.

The above query will return these records.

Order_number	Amount	Currency	Vendor
4200000353	20.000	USD	TOP Catering, Inc.
4200000355	2.100	USD	SoftFusion, GmbH
4200000356	10.500	USD	SoftFusion, GmbH

In the next example we want to display an aggregation of all the orders at all the vendors and distinguish currency. We want to display list of all the vendors and the total sum of orders that we issued grouped by currency.

SELECT vend.name AS Name, ord.currency AS Currency, Sum (ord.amount) AS Total_amount
FROM vendors vend
LEFT JOIN orders ord ON ord.vendor = vend.id
GROUP BY vend.name, ord.currency

Note that we have started selecting in the table of vendors now and link to it data from the table with orders. If we used clauses FROM and LEFT JOIN as in the previous example, the results would not contain data about vendors that do not have any order.

Name	Currency	Total_amount
Fastcom, Inc.
TOP Catering, s.r.o.	USD	20.000
SoftFusion, GmbH	EUR	5.000
SoftFusion, Inc.	USD	12.600
Paperless, Inc.

You can use SQL ib ObjectGears queries or in a script (e.g. for direct write into the database). You can find illustrations of using SQL in ObjectGears in examples.

Explanation of used terms:

Database engine - Software, that represents database management system.

Database - System of arranged data in form of records.

Table - Structure containing records. Each record is represented by a row. Each column contains certain attribute of the record.

Select - SQL clause defining which data should be selected (displayed) from tables.

Join - SQL clause defining how the data from more tables should be linked in our processing.