>
> ## The SELECT statement
> ### Ordering result sets:
> Allows ORDER BY in contexts other than cursor definitions.
> NULLs are considered lower than any non-NULL value.

NULLS FIRST is the default behaviour, but see NULLS LAST if required.

>
> ### Limiting result sets:
> Doesn't support ROW_NUMBER(), supports cursors in all contexts.
> Alternative to ROW_NUMBER():
>
> SELECT FIRST x columns
> FROM tablename
> ORDER BY key ASC
>

I am not aware of "key". I know there is an internal field
RDB$DB_KEY, but this is not a safe value to reference as it is not
persistent. If you want a row number, create a field in your table
and number it (a generator can be used for this purpose).

> ### Top-n query:
> Supports the slow standard SQL query variant only.
>
> ### Limit-with-offset
> Doesn't support ROW_NUMBER(), provides alternative syntax:
>
> SELECT FIRST x SKIP y columns
> FROM tablename
> ORDER BY key ASC
>

Firebird 2 will introduce rows syntax (I think that is the keyword)

> ## The INSERT statement
> ### Inserting several rows at a time: NO

See INSERT INTO .... SELECT FROM ..... syntax.
Also can be done with a stored procedure.

>
> ## Data types
> ### The BOOLEAN type
> Doesn't support BOOLEAN type, possible alternative CHAR(1) with
> constraints.

CREATE DOMAIN BOOLCHAR AS CHAR(1)
check( (value in ('T','F')) or value is null );

You can then declare fields as a "BOOLCHAR".

>
> ### The CHAR type
> Follows the standard.
>
> ### The TIMESTAMP type
> Follows the standard, but doesn't implement WITH TIMEZONE.
> Standard sanity checks pass.
>
> ## SQL Functions
> ### CHARACTER_LENGTH
> Firebird is extremely poor on built-in SQL functions (this situation
> is remedied in latest 2.0-alpha). CHARACTER_LENGTH() function

doesn't

> exist and there's no similar functionality.
>

This is a matter of opinion and I disagree. Distributed with Firebird
1.5 is a UDF called IB_UDF.DLL. There is a corresponding .sql script
in the UDF folder.

There are also third party, including many free dll/so libraries that
can add pretty much everything below. Failing that, any language that
allows you to compile a dll/so can be used to make up your own
function.

> ### SUBSTRING
> Only ordinary SUBSTRING variant exists.
>
> ### REPLACE
> No such functionality exists.
>
> ### TRIM
> No such functionality exists.

See comment above, you have LTRIM, RTRIM, LPAD, RPAD etc

>
> ### LOCALTIMESTAMP
> No such functionality exists.
>
> ### Concatenation
> Automatically casts the concatenated values into types compatible
> with concatenation. If an operand is NULL then the result is NULL.

See also the Coalesce operator, that allows you to substitute a NULL
for another value.

Eg Coalesce(Amount, 0) returns 0 if Amount is null, Coalesce
(MiddleName,'') returns an empty string if the middle name is null.

>
> ## Constraint handling
> ### UNIQUE constraint
> Follows the standard, including the optional NULLs allowed feature.
>
> ## Mixture of type and operators
> ### Automatic key generation
> Doesn't support standard IDENTITY attribute, but allows simulation

of

> BY DEFAULT or even ALWAYS behaviour with triggers and Generators:
>
> CREATE GENERATOR EMPNO_GEN;
> CREATE TRIGGER CREATE_EMPNO FOR EMPLOYEES
> BEFORE INSERT
> AS BEGIN
> NEW.EMPNO = GEN_ID (EMPNO_GEN, 1);
> END;
>

I would change this procedure to

IF (NEW.EMPNO IS NULL) THEN
BEGIN
NEW.EMPNO = GEN_ID (EMPNO_GEN, 1);
END

Then you have a best of both worlds.

> ## Command-line procedures
> Run:
> isql <dbname>
>
> Doesn't have command-line completion, but has history. Can be
> fussy on some terminal types.
>
> Firebird databases are usually self-contained in a single file
> residing anywhere in the filesystem.

There are risks associated with hosting the database on a drive that
is not under the direct control of the system, such as a network
share etc. Apart from the normal flakiness of such solutions, it
makes it very difficult if not impossible to prevent multiple servers
from using the same file and as such can cause corruption.

> This database file can be
> manipulated with usual file-related utilities, but there are several
> special commands such as the "gbak" which produces a backup-ready
> snapshot of the database.
>
> ## Get list of tables
> Doesn't support standard information schema but has its own version
> of data dictionary.
>
> In the isql utility,
> SHOW TABLE;
> will list existing user tables, and
> SHOW INDEX
> will list existing user indexes.
>
>
> ## Getting a table description
> Doesn't support standard information schema, in isql you can run:
> SHOW TABLE "tablename";
> to examine table structure, or
> SHOW INDEX "indexname"
> to examine an index.
>
> Any user-creatable objects can be examined with "SHOW <TYPE>

<ITEMNAME>"

> command.
>

There are lots of third party database explorers, there is a link on
firebirdsql.org to a lot of them. Some are free, some are commercial.

>
> ## Telling the DBMS to collect statistics
> Can only manually compute statistics on indexes, with
> SET STATISTICS INDEX "name";
> command.
>
>
> ## Getting a query explanation
> In isql, use
> SET PLAN;
> or
> SET PLANONLY;
> to set/reset display of query executor plan;
>

Most of the tools will return you the plan when you execute a query,
some even graphically which is a big help to me.

>
> ## Turning on query timing
> No such functionality exists.