3. Introduction to the SQL Language

In this section of the chapter, we present the first SQL commands for creating and working with a single table. We generally provide a simplified version of these commands. Their full syntax is available in the Firebird reference guides (see Section 2.2).

A database is used by people with various skill sets:

the database administrator is generally someone proficient in SQL and databases. They are the ones who create the tables, as this operation is usually performed only once. Over time, they may need to modify the structure. A database is a collection of tables linked by relationships. The database administrator defines these relationships. They also grant permissions to the database’s various users. For example, they may specify that a particular user has the right to view the contents of a table but not to modify it.
The database user is the person who brings the data to life. Depending on the permissions granted by the database administrator, they will add, modify, and delete data in the database’s various tables. They will also analyze the data to extract information useful for the smooth operation of the business, the administration, etc.

In section 2.6, we introduced the SQL editor of the [IB-Expert] tool. This is the tool we will be using. Let’s review a few points:

The SQL Editor can be accessed via the [Tools/SQL Editor] menu option or by pressing the [F12] key

This opens an [SQL Editor] window where we can type an SQL command:

The screenshot above is often represented by the text below:

SQL> select * from BIBLIO

3.1. Firebird Data Types

When creating a table, you must specify the data type that a table column can contain. Here, we present the most common Firebird data types. Note that these data types may vary from one DBMS to another.

`SMALLINT`	integer in the range [-32768, 32767]: 4
`INTEGER`	integer in the range [–2,147,483,648, 2,147,483,647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	real number with n digits, m of which are decimal NUMERIC(5,2): -100.23, +027.30
`FLOAT`	real number approximated to 7 significant digits: 10.4
`DOUBLE PRECISION`	real number approximated to 15 significant digits: -100.89
`CHAR(N) CHARACTER(N)`	A string of exactly N characters. If the stored string has fewer than N characters, it is padded with spaces. CHAR(10): 'ANGERS ' (4 trailing spaces)
`VARCHAR(N) CHARACTER VARYING(N)`	string of up to N characters VARCHAR(10): 'ANGERS'
`DATE`	a date: '2006-01-09' (YYYY-MM-DD format)
`TIME`	a time: '16:43:00' (HH:MM:SS format)
`TIMESTAMP`	both date and time: '2006-01-09 16:43:00' (format YYYY-MM-DD HH:MM:SS)

The CAST() function allows you to convert from one type to another when necessary. To convert a value V declared as type T1 to type T2, you write: CAST(V,T2). You can perform the following type conversions:

number to string. This type conversion is implicit and does not require the use of the CAST function. Thus, the operation 1 + '3' does not require conversion of the character '3'. Its result is the number 4.
DATE, TIME, TIMESTAMP to strings and vice versa. Thus
TIMESTAMP to TIME or DATE and vice versa

In a table, a row may have columns with no value. We say that the value of the column is the NULL constant. You can check for the presence of this value using the operators

IS NULL / IS NOT NULL

3.2. Creating a table

To learn how to create a table, we’ll start by creating one in [Design] mode with IBExpert. To do this, we’ll follow the method described in section 2.3. This will create the following table:

This table will be used to record the books purchased by a library. The meaning of the fields is as follows:

Name	Type	Constraint	Meaning
`ID`	`INTEGER`	`Primary Key`	`Book ID`
`TITLE`	`VARCHAR(30)`	`NOT NULL UNIQUE`	`Book Title`
`AUTHOR`	`VARCHAR(20)`	`NOT NULL`	`Author`
`GENRE`	`VARCHAR(30)`	`NOT NULL`	`Genre (Novel, Poetry, Mystery, Comic Book, etc.)`
`PURCHASE`	`DATE`	`NOT NULL`	`Date the book was purchased`
`PRICE`	`NUMERIC(6,2)`	`NOT NULL`	`Price`
`AVAILABLE`	`CHAR(1)`	`NOT NULL`	`Is it available? Y (yes), N (no)`

This table, which was created using the IBEXPERT wizard, could have been created directly using SQL statements. To view these, simply check the [DDL] tab for the table:

The SQL code used to create the [BIBLIO] table is as follows:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITLE VARCHAR(30) NOT NULL,
    AUTHOR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   PURCHASE_DATE NOT NULL,
   PRICE NUMERIC(6,2) NOT NULL,
   AVAILABLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITLE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

line 1: owner Firebird - indicates the SQL dialect level used
line 2: Firebird-specific - specifies the character set used
lines 6–14: SQL standard: creates the BIBLIO table by defining the name and data type of each of its columns.
line 16: SQL standard: creates a constraint specifying that the TITLE column does not allow duplicates
line 17: SQL standard: specifies that the [ID] column is the table’s primary key. This means that no two rows in the table can have the same ID. This is similar to the [UNIQUE NOT NULL] constraint on the [TITLE] column, and in fact the TITLE column could have served as the primary key. The current trend is to use primary keys that have no specific meaning and are generated by the DBMS.

The syntax for the [CREATE TABLE] command is as follows:

syntax

CREATE TABLE table (column_name1 column_type1 column_constraint1, column_name2 column_type2 column_constraint2, ..., column_nameN column_typeN column_constraintN, other constraints)

action

creates the table table with the specified columns

`column_name_i`	name of column i to be created
`column_type_i`	data type of column i: char(30) numeric(6,2) date timestamp ...
`column_constraint_i`	Constraint that the data in column i must satisfy. Here are a few examples: PRIMARY KEY: the column is a primary key. This means that no two rows in the table can have the same value in this column, and furthermore, a value is required in this column. A primary key is primarily used to uniquely identify a row. NOT NULL : No null values are allowed in the column. UNIQUE : no value can appear more than once in the column. CHECK (condition): The value in the column must satisfy the condition.
`other constraints`	You can specify here - constraints on multiple columns: check(col1>col2) - foreign key constraints

The [BIBLIO] table could also have been created with the following SQL statement:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITLE VARCHAR(30) NOT NULL UNIQUE,
    AUTHOR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   PURCHASE_DATE NOT NULL,
   PRICE NUMERIC(6,2) NOT NULL,
   AVAILABLE  CHAR(1) NOT NULL
);

Let's demonstrate this. Let's open this query in an SQL editor (F12) to create a table we'll call [BIBLIO2]:

After execution, you must commit the transaction to see the result in the database:

Once this is done, the table appears in the database:

By double-clicking on its name, we can view its structure:

We can see the definition we created for the [BIBLIO2] table

3.3. Deleting a table

The SQL statement to delete a table is as follows:

`syntax`	DROP TABLE table
`action`	Deletes [table]

To delete the [BIBLIO2] table we just created, we now execute the following SQL command:

and confirm it with [Commit]. The [BIBLIO2] table is deleted:

3.4. Filling a Table

Let’s insert a row into the [BIBLIO] table we just created:

Confirm the addition of the row with [Commit], then right-click on the added row:

and, as shown above, copy the inserted row to the clipboard as an INSERT SQL statement. Next, open any text editor and paste what we just copied. We get the following SQL code:

INSERT INTO BIBLIO (ID,TITLE,AUTHOR,GENRE,PURCHASE_DATE,PRICE,AVAILABLE) VALUES (1,'Candide','Voltaire','Essay','18-OCT-1985',140,'o');

The syntax for an SQL INSERT statement is as follows:

`syntax`	insert into table [(column1, column2, ..)] values (value1, value2, ....)
`action`	adds a row (value1, value2, ..) to the table. These values are assigned to column1, column2, ... if they exist; otherwise, to the table's columns in the order in which they were defined.

To insert new rows into the [BIBLIO] table, we will type the following INSERT statements into the SQL editor. We will execute and commit these statements one by one. We will use the [New Query] button to move on to the next INSERT statement.

insert into biblio(id,title,author,genre,purchase_date,price,available) values (2,'Les fleurs du mal','Baudelaire','Poem','Jan-01-78',120,'n');
insert into biblio(id,title,author,genre,purchase_date,price,available) values (3,'Tintin in Tibet','Hergé','Comic Book','Nov 10, 1990',70,'o');
insert into biblio(id,title,author,genre,purchase,price,available) values (4,'Swann's Way','Proust','Novel','Dec 8, 1978',200,'o');
insert into biblio(id,title,author,genre,purchase,price,available) values (5,'The Earth','Zola','novel','Jun 12, 1990',50,'n');
insert into biblio(id,title,author,genre,purchase,price,available) values (6,'Madame Bovary','Flaubert','Novel','12-Mar-88',130,'o');
insert into biblio(id,title,author,genre,purchase,price,available) values (7,'Manhattan Transfer','Dos Passos','Novel','Aug 30, 1987',320,'o');
insert into biblio(id,title,author,genre,purchase,price,available) values (8,'Tintin in America','Hergé','Comic book','May 15, 1991',70,'o');

After committing [Commit] the various SQL statements, we obtain the following table:

3.5. Querying a table

3.5.1. Introduction

In the SQL editor, type the following command:

and execute it. We get the following result:

The SELECT statement is used to retrieve data from database tables. This statement has a very rich syntax. Here, we will focus on the syntax for querying a single table. We will cover querying multiple tables simultaneously at a later time. The syntax for the SQL [SELECT] statement is as follows:

syntax

SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...]

FROM table

action

displays the values of expression1 for all rows in the table. expression1 can be a column or a more complex expression. The * symbol denotes all columns. By default, all rows in the table (ALL) are displayed. If DISTINCT is present, identical selected rows are displayed only once. The values of expression1 are displayed in a column titled expression1 or alias1 if the latter was used.

Examples:

SQL > select title, author from library

SQL> select title, price from library

SQL> select title BOOK_TITLE, price PURCHASE_PRICE from biblio

In the examples above, we have assigned aliases (BOOK_TITLE, PURCHASE_PRICE) to the requested columns.

3.5.2. Displaying rows that meet a condition

`syntax`	SELECT .... WHERE condition
`action`	Only rows that meet the condition are displayed

Examples

SQL> select title, price from library where price > 100

SQL> select title, price, genre from library where genre='Novel'

One of the books has the genre 'novel' and not 'Novel'. We use the UPPER function, which converts a string to uppercase, to get all the novels.

SQL> select title, price, genre from library where upper(genre) = 'ROMAN'

We can combine conditions using logical operators

`AND`	logical AND
`OR`	Logical OR
`NOT`	Logical negation

SQL> select title,price,genre from biblio where upper(genre)='NOVEL' and price<100

SQL> select title, genre from library

SQL> select title, genre from library where upper(genre) = 'NOVEL' or upper(genre) = 'COMIC'

SQL> select title, genre from library where not(upper(genre) = 'NOVEL' or upper(genre) = 'COMIC')

SQL> select title,purchase_date from library

SQL>select title,purchase_date from library where purchase_date > '31-Dec-1987'

SQL> select title, price from library where price between 100 and 150

3.5.3. Displaying rows in a specific order

To the previous syntaxes, you can add an ORDER BY clause specifying the desired display order:

`syntax`	SELECT .... ORDER BY expression1 [asc\|desc], expression2 [asc\|desc], ...
`action`	The result rows of the selection are displayed in the order of 1: ascending (asc / ascending, which is the default) or descending (desc / descending) order of expression1 2: if expression1 is equal, the display is based on the values of expression2 etc.

Examples:

SQL>select title, genre, price, purchase_date from library order by purchase_date desc

SQL>select title, genre, price, purchase from library order by price

SQL>select title, genre, price, purchase from library order by genre desc

SQL>select title, genre, price, purchase from library order by genre desc, price

SQL>select title, genre, price, purchase from library order by genre desc, price desc

3.6. Deleting rows from a table

`syntax`	DELETE FROM table [WHERE condition]
`Action`	Deletes table rows that meet the condition. If no condition is specified, all rows are deleted.

Examples:

SQL> select title from library

The two commands below are executed one after the other:

SQL> delete from biblio where title='Candide'

SQL> select title from biblio

3.7. Modifying the contents of a table

`syntax`	update table set column1 = expression1, column2 = expression2, ... [where condition]
`action`	For table rows that satisfy the condition (all rows if there is no condition), column1 is set to the value of expression1.

Examples:

SQL> select genre from biblio

We capitalize all genres:

SQL> update biblio set genre=upper(genre)

We check:

SQL> select genre from biblio

Display the prices:

SQL> select genre, price from biblio;

The price of novels increases by 5%:

SQL> update library set price=price*1.05 where genre='NOVEL';

Let's check:

SQL> select genre, price from biblio

3.8. Permanently updating a table

When changes are made to a table, Firebird actually applies them to a copy of the table. These changes can then be made permanent or rolled back using the COMMIT and ROLLBACK commands.

`Syntax`	COMMIT
`action`	Makes the updates made to the tables since the last COMMIT permanent.

`syntax`	ROLLBACK
`action`	Reverts all changes made to the tables since the last COMMIT.

Note

A COMMIT is performed implicitly at the following times:

a) Upon logging out of Firebird

b) After each command affecting the structure of tables: CREATE, ALTER, DROP.

Examples

In the SQL editor, you can restore the database to a known state by committing all operations performed since the last COMMIT or ROLLBACK:

SQL> commit

We retrieve the list of titles:

SQL> select title from library

Deleting a title:

SQL> delete from biblio where title='The Earth'

Verification:

SQL> select title from biblio

The title has been successfully deleted. Now we'll roll back all changes made since the last COMMIT / ROLLBACK:

SQL> rollback

Verification:

SQL> select title from library

The deleted title has reappeared. Now let’s retrieve the list of prices:

SQL> select price from biblio

Let's set all prices to zero.

SQL> update biblio set price=0

Let's check the prices:

SQL> select price from biblio

Let's undo the changes made to the database:

SQL> rollback

and check the prices again:

SQL> select price from biblio

We have restored the original prices.

3.9. Adding rows from one table to another

It is possible to add rows from one table to another when their structures are compatible. To demonstrate this, let’s start by creating a table [BIBLIO2] with the same structure as [BIBLIO].

In the IBExpert database explorer, double-click the [BIBLIO] table to access the [DDL] tab:

In this tab, you’ll find the list of SQL statements used to generate the [BIBLIO] table. Copy all of this code to the clipboard (CTRL-A, CTRL-C). Then open a tool called [Script Executive] that allows you to execute a list of SQL statements:

A text editor opens, into which we can paste (CTRL-V) the text previously copied to the clipboard:

A list of SQL commands is often called an SQL script. [Script Executive] will allow us to execute such a script, whereas the SQL editor only allowed the execution of a single command at a time. The current SQL script creates the [BIBLIO] table. Let’s make it create a table named [BIBLIO2]. To do this, simply change [BIBLIO] to [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITLE       VARCHAR(30) NOT NULL,
    AUTHOR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    PURCHASE_DATE       DATE NOT NULL,
    PRICE        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    AVAILABLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITLE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Let's run this script using the [Run Script] button below:

The script is executed:

and we can see the new table in the database explorer:

If we double-click on [BIBLIO2] to check its contents, we find that it is empty, which is normal:

A variant of the SQL INSERT statement allows you to insert rows from one table into another:

`syntax`	INSERT INTO table1 [(column1, column2, ...)] SELECT column1, column2, ... FROM table2 WHERE condition
`action`	The rows from table2 that satisfy the condition are added to table1. The columns column1, column2, ... from table2 are assigned in order to column1, column2, ... in table1 and must therefore be of compatible types.

Let's go back to the SQL editor:

and execute the following SQL statement:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

which inserts into [BIBLIO2] all rows from [BIBLIO] corresponding to a novel. After executing the SQL statement, let’s commit it with a [Commit]:

SQL> commit

Now, let’s view the data in the [BIBLIO2] table:

SQL> select * from BIBLIO2

3.10. Deleting a table

`syntax`	DROP TABLE table
`action`	deletes table

Example: Dropping the BIBLIO2 table

SQL> drop table BIBLIO2

Confirm the change:

SQL> commit

In the database explorer, refresh the table view:

We see that the [BIBLIO2] table has been deleted:

3.11. Modifying a table's structure

`Syntax`	ALTER TABLE table [ ADD column_name1 column_type1 column_constraint1] [ALTER column_name2 TYPE column_type2] [DROP column_name3] [ADD constraint] [DROP CONSTRAINT constraint_name]
`action`	allows you to add (ADD), modify (ALTER), and drop (DROP) table columns. The syntax column_name1 column_type1 column_constraint1 is the same as that of CREATE TABLE. You can also add or drop table constraints.

Example: Execute the following two SQL commands sequentially in the SQL editor

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

In the database explorer, let’s check the structure of the [BIBLIO] table:

The changes have been applied. Let’s see how the table’s contents have changed:

SQL> select * from biblio

The new [NB_PAGES] column has been created but has no values. Let’s drop this column:

SQL> alter table biblio drop nb_pages

SQL> commit

Let's check the new structure of the [BIBLIO] table:

The [NB_PAGES] column has indeed disappeared.

3.12. Views

It is possible to have a partial view of a table or multiple tables. A view behaves like a table but does not contain data. Its data is extracted from other tables or views. A view has several advantages:

A user may be interested only in certain columns and rows of a given table. The view allows them to see only those rows and columns.
The owner of a table may wish to grant only limited access to other users. A view allows them to do so. The users they have authorized will only have access to the view they have defined.

3.12.1. Creating a view

`syntax`	CREATE VIEW view_name AS SELECT column1, column2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	creates the view view_name. This is a table with the structure column1, column2, ... from table and, for rows, the rows from table that satisfy condition (all rows if there is no condition)
`WITH CHECK OPTION`	This optional clause specifies that inserts and updates to the view must not create rows that the view could not select.

Note The syntax of CREATE VIEW is actually more complex than that presented above and allows, in particular, the creation of a view from multiple tables. To do this, the SELECT statement simply needs to reference multiple tables (see the following chapter).

Examples

We create a view from the biblio table that includes only novels (row selection) and only the title, author, and price columns (column selection):

SQL> create view novels as select title, author, price from biblio where upper(genre)='NOVEL';

SQL> commit

In the database explorer, refresh the view (F5). A view appears:

We can view the SQL statement associated with the view. To do this, double-click on the [ROMANS] view:

A view is like a table. It has a structure:

and content:

A view is used like a table. You can run SQL queries on it. Here are a few examples to try in the SQL editor:

SQL> select * from novels

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Novel','01-Sep-91',200,'o')

Is the new novel visible in the [ROMANS] view?

SQL> select * from novels

Let’s add something other than a novel to the [BIBLIO] table:

SQL> insert into biblio(id,title,author,genre,purchase,price,available) values (11,'Poèmes saturniens','Verlaine','Poem','02-Sep-92',200,'o');

Let's check the [BIBLIO] table:

SQL> select title, author from BIBLIO

Let's check the [ROMANS] view:

SQL> select title, author from NOVELS

The added book is not in the [ROMANS] view because it did not have upper(genre)='ROMAN'.

3.12.2. Updating a view

You can update a view just as you would a table. All tables from which the view’s data is extracted are affected by this update. Here are a few examples:

SQL> insert into biblio(id,title,author,genre,purchase_date,price,available) values (13,'The Red and the Black','Stendhal','Novel','Oct 3, 1992',110,'o')

SQL> select * from novels

SQL> select title, author from biblio

We delete a row from the [ROMANS] view:

SQL> delete from NOVELS where title='The Red and the Black'

SQL> select * from novels

SQL> select author, title from BIBLIO

The row deleted from the [NOVELS] view has also been deleted from the [BIBLIO] table. We will now increase the price of the books in the [NOVELS] view:

SQL> update novels set price=price*1.05

Let's check in [NOVELS]:

SQL> select * from novels

What was the impact on the [BIBLIO] table?

SQL> select title, author, price from biblio

The prices of the novels have indeed been increased by 5% in [BIBLIO] as well.

3.12.3. Deleting a view

`syntax`	DROP VIEW view_name
`action`	deletes the view named

Example

SQL> drop view novels

SQL> commit

In the database explorer, you can refresh the view (F5) to see that the [ROMANS] view has disappeared:

3.13. Using group functions

There are functions that, instead of operating on each row of a table, operate on groups of rows. These are essentially statistical functions that allow us to calculate the mean, standard deviation, etc., of the data in a column.

`syntax1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	Calculates the statistical functions fi on all rows of the table that satisfy the condition.

syntax2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

The GROUP BY keyword divides the table rows into groups. Each group contains the rows for which the expressions expr1, expr2, ... have the same value.

Example: GROUP BY genre groups together books of the same genre. The GROUP BY author,genre clause would group together books with the same author and the same genre. The WHERE condition first removes rows from the table that do not meet the condition. Then, groups are formed by the GROUP BY clause. The aggregate functions are then calculated for each group of rows.

`syntax3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING group_condition]
`action`	The HAVING clause filters the groups formed by the GROUP BY clause. It is therefore always associated with the presence of the GROUP BY clause. Example: GROUP BY genre HAVING genre!='NOVEL'

The available statistical functions are as follows:

`AVG(expression)`	average of expression
`COUNT(expression)`	number of rows for which expression has a value
`COUNT(*)`	total number of rows in the table
`MAX(expression)`	Maximum value of expression
`MIN(expression)`	min of expression
`SUM(expression)`	sum of expression

Examples

SQL> select price from library

Average price? Maximum price? Minimum price?

SQL> select avg(price), max(price), min(price) from library

SQL> select title, price, genre from library

Average price of a novel? Maximum price?

SQL> select avg(price) as average, max(price) as max_price from biblio where upper(genre) = 'NOVEL'

How many comic books?

SQL> select count(*) from biblio where upper(genre)='COMIC'

How many novels cost less than 100 F?

SQL> select count(*) from biblio where upper(genre)='NOVEL' and price<100

SQL> select genre, price from library

Number of books and average price per book for books of the same genre?

SQL> select upper(genre) as GENRE, avg(price) as AVG_PRICE, count(*) as COUNT from biblio group by upper(genre)

Same question, but only for books that are not novels:

SQL>
select uppercase(genre) GENRE,avg(price) AVERAGE_PRICE,count(*) COUNT
from biblio
group by UPPER(genre)
where upper(GENRE) != 'NOVEL'

Same query, but only for books under 150 F:

SQL> 
select uppercase(genre) as GENRE, average(price) as AVERAGE_PRICE, count(*) as COUNT
from library
where price<150
group by upper(genre)
where upper(GENRE) != 'NOVEL'

Same query, but we only keep groups with an average book price >100 F

SQL> 
select uppercase(genre) as GENRE, average(price) as AVERAGE_PRICE, count(*) as COUNT
from biblio
group by upper(genre)
where avg(price) > 100

3.14. Creating an SQL script for a table

SQL is a standard language that can be used with many DBMSs. To be able to switch from one DBMS to another, it is useful to export a database or simply certain elements of it in the form of an SQL script which, when re-run in another DBMS, will be able to recreate the elements exported in the script.

Here, we will export the [BIBLIO] table. Let’s select the [Extract Metadata] option:

Note that you must be in the database from which you want to export elements. The option launches a wizard:

`1`	where to generate the SQL script: in a file (File) to the Clipboard in the Script Executive tool
`2`	file name if the [File] option is selected
`3`	what to export
`4`	Buttons to select (->) or deselect (<-) the objects to export

If we wanted to export the entire database, we would check the [Extract All] option above. We simply want to export the BIBLIO table. To do this, using [4], we select the [BIBLIO] table, and using [2], we specify a file:

If we stop here, only the structure of the [BIBLIO] table will be exported. To export its contents, we need to use the [Data Tables] tab:

Use [1] to select the [BIBLIO] table:

Use [2] to generate the SQL script:

Let’s accept the prompt. This allows us to view the generated script in the [biblio.sql] file:

/******************************************************************************/
/****         Generated by IBExpert 2004.06.17 01/22/2006 15:06:13         ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\work\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITLE       VARCHAR(30) NOT NULL,
AUTHOR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
PURCHASE_DATE       DATE NOT NULL,
PRICE        NUMERIC(6,2) DEFAULT 10 NOT NULL,
AVAILABLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POEM', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (3, 'Tintin in Tibet', 'Hergé', 'COMIC', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (4, 'Swann's Way', 'Proust', 'NOVEL', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (5, 'La terre', 'Zola', 'NOVEL', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE, PRICE, AVAILABLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'NOVEL', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (7, 'Manhattan Transfer', 'Dos Passos', 'NOVEL', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (8, 'Tintin in America', 'Hergé', 'COMIC', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (10, 'Father Goriot', 'Balzac', 'Novel', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITLE, AUTHOR, GENRE, PURCHASED, PRICE, AVAILABLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poetry', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITLE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

Lines 1 through 3 are comments
Lines 5 through 12 are Firebird-specific SQL
the remaining lines are standard SQL that should be executable in a DBMS that supports the data types declared in the BIBLIO table.

Let’s run this script within Firebird to create a BIBLIO2 table that will be a clone of the BIBLIO table. To do this, use [Script Executive] (Ctrl-F12):

Let’s load the [biblio.sql] script we just generated:

Modify it to keep only the table creation and row insertion parts. The table is renamed [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITLE       VARCHAR(30) NOT NULL,
    AUTHOR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    PURCHASE_DATE       DATE NOT NULL,
    PRICE        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    AVAILABLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITLE, AUTHOR, GENRE, PURCHASE_DATE, PRICE, AVAILABLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POEM', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Let's run this script:

We can verify in the database explorer that the [BIBLIO2] table has been created and that it has the expected structure and content: