InterBase Quick Start: Part II - An Overview of SQL

Go Up to InterBase Quick Start: Part II - Data Definition

SQL statements fall into two major categories: Data definition language (DDL) statements, and Data manipulation language (DML) statements:

DDL statements define, change, and delete structures that hold data. These include the database itself, tables, and other elements that are part of the database such as domains, indexes, triggers, stored procedures, roles, and shadows. Collectively, the objects defined with DDL statements are known as metadata. DDL statements begin with the keywords CREATE, ALTER, and DROP. For example, CREATE TABLE defines a table, ALTER TABLE modifies an existing table, and DROP TABLE deletes a table.

DML statements manipulate data within these data structures. The four fundamental DML statements are INSERT, UPDATE, DELETE, and SELECT. INSERT adds data to a table, UPDATE modifies existing data, and DELETE removes data. The SELECT statement retrieves or queries information from the database. It is the most important and most complex of all the SQL statements because it is the means by which you gain access to all the information that you have so meticulously stored.

Throughout the Quick Start you use several DDL statements to create data structures for your TUTORIAL database: CREATE DOMAIN, CREATE TABLE, ALTER TABLE, CREATE VIEW, and CREATE INDEX.

Using IBConsole to Execute SQL

IBConsole includes an Interactive SQL window that allows you to execute DDL and DML statements.

Entering SQL Statements in the SQL Statement Area

You enter SQL statements by typing them in the SQL statement area (top-right edit window).

You do not need to end statements with a terminator (such as a semicolon). However, the terminator is not prohibited and does not cause a problem. For example, you can copy-paste statements from scripts and execute them without removing the terminators.
InterBase SQL statements are not case sensitive. You can enter all SQL statements in lowercase if you prefer.
Anything inside quotation marks is case sensitive and must be entered as shown.
InterBase ignores line breaks within statements. They are for your convenience only.
You can use spaces or tabs to indent lines to make them easier to read. InterBase ignores spaces and tabs.
You must execute each statement before entering the next one.

Executing Statements

To execute a statement, do one of the following:

Use the Execute Query button ().
Select Query > Execute.
Use the F5 key.

Loading an SQL Script

To load a script in the Interactive SQL window, do one of the following:
- Select Query > Load Script.
- Use the Load SQL Script button ().
Navigate to the location the script files and open <filename>.SQL.

Committing Your Work

Until you commit your work, your transaction is considered to be active. Work associated with an active transaction is not yet visible to other users. For example, usually want to enter an entire group of related items before committing so that misleading intermediate states are never visible to other users. When you commit, your transaction changes to a committed state and the work you did in that transaction becomes visible to other users. When you create metadata, however, you usually want to commit each data structure as you complete the DDL statement.

InterBase has an auto commit feature that automatically commits any DDL statement when you execute it. The auto commit feature does not apply to DML statements such as INSERT, UPDATE, DELETE, and SELECT.

Note:
The auto commit feature is enabled by default in InterBase.

To commit a transaction (for example, after entering a DML statement), do one of the following: