InterBase Quick Start: Part II - An Overview of SQL
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
DROP. For example,
CREATE TABLEdefines a table,
ALTER TABLEmodifies an existing table, and
DROP TABLEdeletes a table.
- DML statements manipulate data within these data structures. The four fundamental DML statements are
INSERTadds data to a table,
UPDATEmodifies existing data, and
DELETEremoves data. The
SELECTstatement 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 VIEW, and
Using IBConsole to Execute SQL
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.
To execute a statement, do one of the following:
Loading an SQL Script
- To load a script in the Interactive SQL window, do one of the following:
- Navigate to the location the script files and open
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
- 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:
- Use the
Performing a Rollback
To perform a Rollback, do one of the following:
- Use the
To check the status of the auto commit feature and other session settings, follow these steps:
- Use the Interactive SQL button () to open the Interactive SQL window.
- Choose Edit > Options to display the SQL Options dialog.