6.3.1 SELECT Command

The SELECT command allows you to extract rows from a table, according to optional selection rules.

Syntax:

SELECT [DISTINCT]
 <field-list-1>
 FROM <table-list>
 [WHERE <condition>]
 [GROUP BY <field-list-2>]
 [HAVING <condition-2>]
 [ORDER BY <field-list-3>]

<field-list-1>

is the list of fields that must be selected. Every field must be written according to the following syntax:

[<table-name>.]<field-name>
      [,[<table-name>.]<field-name>][, ...]]

Finally, you may replace <field-list-1> with the character "*" (star), which means "all the fields of every table present in <table-list>."

<table-list>

is a list of tables containing fields specified in <field-list-1> and/or in <condition>.

The format is:

<table-name>
      [,<table-name> [, ...]]

In other words, all the rows of a table are associated with each row of the other tables present in <table-list>. So if you have two tables, the first containing 100 rows and the second one with ten, the result of a SELECT statement, without the WHERE clause, is a table containing 1000 rows (100 * 10).

If you had three tables, the first with 100 rows, the second with 1000, and the third with 10, the result would be a table with 1,000,000 rows (100 * 1000 * 10).

AcuODBC also supports a variety of join statements in <table-list>, including inner joins, left outer joins, right outer joins, and self-joins.

An inner join is a SELECT statement that joins two or more tables together. The format for <table-list> when creating inner joins is:

<table-name-1> INNER
  JOIN <table-name-2>
  ON <field-name-1>=<field-name-2>

SELECT *
 FROM customer INNER JOIN orders
  ON customer.cs_code = orders.or_customer_code;

<table-name-1> LEFT
  OUTER JOIN <table-name-2>
 ON <field-name-1>=<field-name-2>

Note that the "OUTER" clause is required when making outer joins with AcuODBC. For example:

SELECT *
 FROM customer LEFT OUTER JOIN orders
 ON customer.cs_code = orders.or_code

A right outer join is the opposite of a left outer join; namely, it is a SELECT statement that retains unmatched rows from Table 2 when joining two tables, but discards unmatched rows from Table 1.

The format is the same for right outer joins as for left outer joins:

<table-name-1> RIGHT
  OUTER JOIN <table-name-2>

 ON <field-name-1>=<field-name-2>

The format for <table-list> when you are creating self-joins is:

<table-name>[,<table-name> AS <table-alias>]
   [,<table-name>[,<table-name> AS <table-alias>][,
   ...]]

SELECT DISTINCT
 orders1.or_date, orders1.or_quantity
 FROM orders AS orders1, orders AS orders2
  WHERE orders1.or_date=960409 and orders1.or_code=orders2.or_code

<condition-1>

consists of a logical expression aimed at identifying a useful set of lines among all the lines generated by the product of the tables. It can be expressed in the following way:

[NOT]
 <field>|<literal> <operator>
 <field>|<literal>

[AND|OR <condition>]

is a field present in one of the tables listed in <table-list>.

<literal>

is a numeric or alphanumeric constant. If it is alphanumeric it must be enclosed between quotes (' or ").

<operator>

is a logic operator included in the following list:

Logic Operator	Meaning
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	Contains
BETWEEN	Included between two values
IN	Included in a list of values
NOT IN	Not included in a list of values

Conditions can be joined with logic operators "AND" and "OR." According to normal precedence rules, "AND" conditions are evaluated before "OR" conditions. Using parentheses, you can change these rules.

<field-list-2>

Using the GROUP BY clause, you can group records that have identical values in columns. The list of fields specified after the GROUP BY clause specifies which columns have to be considered in order to form groups.

<condition-2>

is used to qualify groups the way <condition-1> works to qualify records. Syntax and operators are the same specified for <condition-1>.

<field-list-3>

is a list of fields belonging to tables present in <table-list>. These fields must be written according to the following syntax:

[<table-name>.]<field-name> [DESC]

   [[<table-name>.]<field-name> [DESC][, ...]]

The option DESC specifies that fields must be ordered in reverse ordering.

Standard Aggregate Functions

AcuODBC supports a series of standard aggregate functions that are specified inside a SELECT command on selected fields. The field-names to which these functions apply must follow the name of the function and be enclosed in parentheses.

The result of an aggregate function is unique for each selected group. If a group is not selected (i.e., the GROUP BY clause is not used), the result is computed on the full table.

The standard aggregate functions are:

Function	Description
COUNT	Counts the number of values present in a column.
COUNT(*)	Returns the number of rows of a table.
COUNT(field-name)	Returns the number of not null values stored in the column "field-name".
SUM	Computes the sum of values present in a column.
AVG	Computes the arithmetic average of the values present in a column.
MAX	Returns the highest value present in a column.
MIN	Returns the lowest value present in a column.
UPPER	Returns column values in upper case (for conditions only).
LOWER	Returns column values in lower case (for conditions only).

Examples:

The following examples are designed to illustrate SQL concepts. They are not made from the sample data that is shipped with AcuODBC.

Input table number 1 -> CUSTOMERS

id_cust	Company	Address	City
1	Acucorp Inc	8515 Miralani Drive	San Diego
2	Acucorp Germany	Otto-Hahn-Strasse 9	Friedrichsdorf Koeppern
3	Acucorp Italia	Via I Maggio 3	Piacenza
4	Acucorp Scandinavia	Rasundavagen 101	Solna

Input table number 2 -> ORDERS

id_order	id_ordcust	Date	Quantity
1	2	15/01/96	15
2	4	16/01/96	23
3	2	19/01/96	12
4	4	19/01/96	33
5	3	22/01/96	16
6	3	25/01/96	21
7	4	27/01/96	14
8	2	27/01/96	25

The relation between these two tables is represented by the fields "id_cust" in table one and "id_ordcust" in table two.

Example 1. If you execute the following SQL command:

SELECT company, id_order, date,
 quantity

 FROM customers, orders

 WHERE id_cust = id_ordcust

Company	id_order	Date	Quantity
Acucorp Germany	1	15/01/96	15
Acucorp Germany	3	19/01/96	12
Acucorp Germany	8	27/01/96	25
Acucorp Italia	5	22/01/96	16
Acucorp Italia	6	25/01/96	21
Acucorp Scandinavia	2	16/01/96	23
Acucorp Scandinavia	4	19/01/96	33
Acucorp Scandinavia	7	27/01/96	14

The WHERE clause has established a relation between the two tables. Customer #1, Acucorp, Inc., is not included in the output table, because there are no orders placed by this client in the ORDERS table.

Example 2. If you execute the following SQL command:

SELECT company, id_order, date,
 quantity
 FROM customers, orders

Company	id_order	Date	Quantity
Acucorp Inc	1	15/01/96	15
Acucorp Inc	2	16/01/96	23
Acucorp Inc	3	19/01/96	12
Acucorp Inc	4	19/01/96	33
Acucorp Inc	5	22/01/96	16
Acucorp Inc	6	25/01/96	21
Acucorp Inc	7	27/01/96	14
Acucorp Inc	8	27/01/96	25
Acucorp Germany	1	15/01/96	15
Acucorp Germany	2	16/01/96	23
Acucorp Germany	3	19/01/96	12
Acucorp Germany	4	19/01/96	33
Acucorp Germany	5	22/01/96	16
Acucorp Germany	6	25/01/96	21
Acucorp Germany	7	27/01/96	14
Acucorp Germany	8	27/01/96	25
Acucorp Italia	1	15/01/96	15
Acucorp Italia	2	16/01/96	23
Acucorp Italia	3	19/01/96	12
Acucorp Italia	4	19/01/96	33
Acucorp Italia	5	22/01/96	16
Acucorp Italia	6	25/01/96	21
Acucorp Italia	7	27/01/96	14
Acucorp Italia	8	27/01/96	25
Acucorp Scandinavia	1	15/01/96	15
Acucorp Scandinavia	2	16/01/96	23
Acucorp Scandinavia	3	19/01/96	12
Acucorp Scandinavia	4	19/01/96	33
Acucorp Scandinavia	5	22/01/96	16
Acucorp Scandinavia	6	25/01/96	21
Acucorp Scandinavia	7	27/01/96	14
Acucorp Scandinavia	8	27/01/96	25

In this case, because the SELECT statement does not contain any WHERE clause, the output table contains 32 rows--that is, the product of the rows of the two input tables, as explained above.

Example 3. If you execute the following SQL command:

SELECT company, id_order, date,
 quantity
 FROM customers, orders
 WHERE id_cust = id_ordcust AND id_cust = 4
 ORDER BY quantity

Company	id_order	Date	Quantity
Acucorp Scandinavia	7	27/01/96	14
Acucorp Scandinavia	2	16/01/96	23
Acucorp Scandinavia	4	19/01/96	33

Example 4. This example shows an SQL command used to extract the total amount of orders grouped by company.

SELECT company, Sum(quantity)
 FROM  customers, orders
 WHERE id_cust = id_ordcust
 GROUP BY company

Example 5. This example shows an SQL command used to extract the total number and amount of orders for each company, as well as the lowest and highest order.

SELECT company, Count(quantity)
 AS '#Ord', Sum(quantity) AS 'Tot_Ord',
Min(quantity) AS 'Min', Max(quantity) AS 'Max'
 FROM customers, orders
 WHERE id_cust = id_ordcust
 GROUP BY company

Example 6. This example shows an SQL command used to extract companies which have more than two orders, and to compute the average of orders.

SELECT company, Avg(quantity)
 AS Avrg', Count(quantity) AS 'Count'
FROM customers, orders
WHERE id_cust = id_ordcust
GROUP BY company
HAVING (Count(*)>2)