По поводу написания селекта.... сорри я тут выдержку приведу с хелка по IB
Retrieves data from one or more tables. Available in SQL, DSQL, and isql.
Syntax SELECT [TRANSACTION transaction]
[DISTINCT | ALL]
{* | <val> [, <val> …]}
[INTO :var [, :var …]]
FROM <tableref> [, <tableref> …]
[WHERE <search_condition>]
[GROUP BY col [COLLATE collation] [, col [COLLATE collation] …]
[HAVING <search_condition>]
[UNION <select_expr> [ALL]]
[PLAN <plan_expr>]
[ORDER BY <order_list>]
[FOR UPDATE [OF col [, col …]]];
<val> = {
col [<array_dim>] | :variable
| <constant> | <expr> | <function>
| udf ([<val> [, <val> …]])
| NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation] [AS alias]
<array_dim> = [[x:]y [, [x:]y …]]
<constant> = num | 'string' | charsetname 'string'
<function> = COUNT (* | [ALL] <val> | DISTINCT <val>)
| SUM ([ALL] <val> | DISTINCT <val>)
| AVG ([ALL] <val> | DISTINCT <val>)
| MAX ([ALL] <val> | DISTINCT <val>)
| MIN ([ALL] <val> | DISTINCT <val>)
| CAST (<val> AS <datatype>)
| UPPER (<val>)
| GEN_ID (generator, <val>)
<tableref> = <joined_table> | table | view | procedure
[(<val> [, <val> …])] [alias]
<joined_table> = <tableref> <join_type> JOIN <tableref>
ON <search_condition> | (<joined_table>)
<join_type> = [INNER] JOIN
| {LEFT | RIGHT | FULL } [OUTER]} JOIN
<search_condition> = <val> <operator> {<val> | (
<select_one>)}
| <val> [NOT] BETWEEN <val> AND <val>
| <val> [NOT] LIKE <val> [ESCAPE <val>]
| <val> [NOT] IN (<val> [, <val> …] | <select_list>)
| <val> IS [NOT] NULL
| <val> {>= | <=}
| <val> [NOT] {= | < | >}
| {ALL | SOME | ANY} (<select_list>)
| EXISTS (<select_expr>)
| SINGULAR (<select_expr>)
| <val> [NOT] CONTAINING <val>
| <val> [NOT] STARTING [WITH] <val>
| (<search_condition>)
| NOT <search_condition>
| <search_condition> OR <search_condition>
| <search_condition> AND <search_condition>
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}
<plan_expr> =
[JOIN | [SORT] [MERGE]] ({<plan_item> | <plan_expr>}
[, {<plan_item> | <plan_expr>} …])
<plan_item> = {table | alias}
{NATURAL | INDEX (<index> [, <index> …])| ORDER <index
>}
<order_list> =
{col | int} [COLLATE collation]
[ASC[ENDING] | DESC[ENDING]]
[, <order_list> …]
Argument Description
expr A valid SQL expression that results in a single value
select_one A SELECT on a single column that returns exactly one value
select_list A SELECT on a single column that returns zero or more rows
select_expr A SELECT on a list of values that returns zero or more rows
Notes on SELECT syntax
n When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:
my_array = varchar(6)[5,5]
Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 10 and ends at 20:
my_array = integer[20:30]
n In SQL and isql, you cannot use val as a parameter placeholder (like "?").
n In DSQL and isql, val cannot be a variable.
n You cannot specify a COLLATE clause for Blob columns.
Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.
Argument Description
TRANSACTION transaction Name of the transaction under control of which the statement is executed; SQL only
SELECT [DISTINCT | ALL] Specifies data to retrieve. DISTINCT prevents duplicate values from being returned. ALL, the default, retrieves every value
{*|val [, val …]} The asterisk (*) retrieves all columns for the specified tablesval [, val …] retrieves a list of specified columns, values, and expressions
INTO :var [, var …] Singleton select in embedded SQL only; specifies a list of host-language variables into which to retrieve values
FROM tableref
[, tableref …] List of tables, views, and stored procedures from which to retrieve data; list can include joins and joins can be nested
table Name of an existing table in a database
view Name of an existing view in a database
procedure Name of an existing stored procedure that functions like a SELECT statement
alias Brief, alternate name for a table, view, or column; after declaration in tableref, alias can stand in for subsequent references to a table or view
joined_table A table reference consisting of a JOIN
join_type Type of join to perform. Default: INNER
WHERE search_condition Specifies a condition that limits rows retrieved to a subset of all available rows
GROUP BY col [, col …] Partitions the results of a query into groups containing all rows with identical values based on a column list
COLLATE collation Specifies the collation order for the data retrieved by the query
HAVING search_condition Used with GROUP BY; specifies a condition that limits grouped rows returned
UNION [ALL] Combines two or more tables that are fully or partially identical in structure; the ALL option keeps identical rows separate instead of folding them together into one
PLAN plan_expr Specifies the access plan for the InterBase optimizer to use during retrieval
plan_item Specifies a table and index method for a plan
ORDER BY order_list Specifies columns to order, either by column name or ordinal number in the query, and the order (ASC or DESC) in which rows to return the rows
Description SELECT retrieves data from tables, views, or stored procedures. Variations of the SELECT statement make it possible to:
n Retrieve a single row, or part of a row, from a table. This operation is referred to as a singleton select.
In embedded applications, all SELECT statements that occur outside the context of a cursor must be singleton selects.
n Retrieve multiple rows, or parts of rows, from a table.
In embedded applications, multiple row retrieval is accomplished by embedding a SELECT within a DECLARE CURSOR statement.
In isql, SELECT can be used directly to retrieve multiple rows.
n Retrieve related rows, or parts of rows, from a join of two or more tables.
n Retrieve all rows, or parts of rows, from union of two or more tables.
All SELECT statements consist of two required clauses (SELECT, FROM), and possibly others (INTO, WHERE, GROUP BY, HAVING, UNION, PLAN, ORDER BY). The following table explains the purpose of each clause, and when they are required:
TABLE 6 SELECT statement clauses
Clause Purpose Singleton SELECT Multi-row SELECT
SELECT Lists columns to retrieve Required Required
INTO Lists host variables for storing retrieved columns Required Not allowed
FROM Identifies the tables to search for values Required Required
WHERE Specifies the search conditions used to restrict retrieved rows to a subset of all available rows; a WHERE clause can contain its own SELECT statement, referred to as a subquery Optional Optional
GROUP BY Groups related rows based on common column values; used in conjunction with HAVING Optional Optional
HAVING Restricts rows generated by GROUP BY to a subset of those rows Optional Optional
UNION Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows Optional Optional
ORDER BY Specifies which columns to order, either by column name or by ordinal number in the query, and the sort order of rows returned: ascending (ASC) [default] or descending (DESC) Optional Optional
PLAN Specifies the query plan that should be used by the query optimizer instead of one it would normally choose Optional Optional
FOR UPDATE Specifies columns listed after the SELECT clause of a DECLARE CURSOR statement that can be updated using a WHERE CURRENT OF clause — Optional
Because SELECT is such a ubiquitous and complex statement, a meaningful discussion lies outside the scope of this reference. To learn how to use SELECT in isql, see the Operations Guide. For a complete explanation of SELECT and its clauses, see the Embedded SQL Guide.
Examples The following isql statement selects columns from a table:
SELECT JOB_GRADE, JOB_CODE, JOB_COUNTRY, MAX_SALARY FROM PROJECT;
The next isql statement uses the * wildcard to select all columns and rows from a table:
SELECT * FROM COUNTRIES;
The following embedded SQL statement uses an aggregate function to count all rows in a table that satisfy a search condition specified in the WHERE clause:
EXEC SQL
SELECT COUNT (*) INTO :cnt FROM COUNTRY
WHERE POPULATION > 5000000;
The next isql statement establishes a table alias in the SELECT clause and uses it to identify a column in the WHERE clause:
SELECT C.CITY FROM CITIES C
WHERE C.POPULATION < 1000000;
The following isql statement selects two columns and orders the rows retrieved by the second of those columns:
SELECT CITY, STATE FROM CITIES
ORDER BY STATE;
The next isql statement performs a left join:
SELECT CITY, STATE_NAME FROM CITIES C
LEFT JOIN STATES S ON S.STATE = C.STATE
WHERE C.CITY STARTING WITH 'San';
The following isql statement specifies a query optimization plan for ordered retrieval, utilizing an index for ordering:
SELECT * FROM CITIES
PLAN (CITIES ORDER CITIES_1);
ORDER BY CITY
The next isql statement specifies a query optimization plan based on a three-way join with two indexed column equalities:
SELECT * FROM CITIES C, STATES S, MAYORS M
WHERE C.CITY = M.CITY AND C.STATE = M.STATE
PLAN JOIN (STATE NATURAL, CITIES INDEX DUPE_CITY,
MAYORS INDEX MAYORS_1);
The next example queries two of the system tables, RDB$CHARACTER_SETS and RDB$COLLATIONS to display all the available character sets, their ID numbers, number of bytes per character, and collations. Note the use of ordinal column numbers in the ORDER BY clause.
SELECT RDB$CHARACTER_SET_NAME, RDB$CHARACTER_SET_ID,
RDB$BYTES_PER_CHARACTER, RDB$COLLATION_NAME
FROM RDB$CHARACTER_SETS JOIN RDB$COLLATIONS
ON RDB$CHARACTER_SETS.RDB$CHARACTER_SET_ID =
RDB%COLLATIONS.RDB$CHARACTER_SET_ID
ORDER BY 1, 4;
