Understanding the Structure of SQL Statements

When using SQL to send commands to the DBMS, you first tell the DBMS what you want to do and then describe the data (or structure) on which you want the DBMS to take the action. SQL is similar to the German language in that you put the action word (the verb) at the beginning of the sentence (the SQL statement) and then follow the verb with one or more clauses that describe the subject (the database object, or set of rows) on which you want the DBMS to act. Figure 35.1 shows the basic form of SQL statements.

Figure 35.1: Basic structure of an SQL statement

As shown in Figure 35.1, each SQL statement begins with a keyword that describes what the statement does. Keywords you'll find at the beginning of SQL statements include: SELECT, INSERT, UPDATE, DELETE, CREATE, or DROP. After you tell the DBMS what you want done, you tell it the columns of interest and the table(s) in which to look. You normally identify the columns and tables you want to use by listing the columns after the verb (at the start of the SQL statement) and by listing the table(s) after the keyword FROM.

After you tell the DBMS what to do and identify the columns and tables to which to do it, you finish the SQL statement with one or more clauses that either further describe the action the DBMS is to take, or give a description of the data values that identify tables rows on which you want you want to DBMS to act. Typical descriptive clauses begin with the keywords: HAVING, IN, INTO, LIKE, ORDER BY, WHENEVER, WHERE, or WITH.

ANSI/ISO SQL-92 has approximately 300 reserved words of which you will probably use about 30 to do the majority of your work with the database. Table 35.1 lists some of the most commonly used keywords. Although some of the keywords are applicable only to MS-SQL Server, you will find keywords that perform similar functions if you are using another vendor's DBMS.

Table 35.1: Commonly Used SQL and MS-SQL Server Keywords

Keyword	Description
Data Definition Language (DML)
CREATE DATABASE	(MS-SQL Server). Creates a database and transaction log. A database has one or more schemas, which contain database objects such as tables, views, domains, constraints, procedures, triggers, and so on.
DROP DATABASE	(MS-SQL Server). Erases a database and transaction log.
CREATE SCHEMA	Adds a named container of database objects to the database. A database may have more than one schema. All database objects (tables, views, domains, constrains, procedures, triggers, and so on) reside in one of the schemas within the database.
DROP SCHEMA	Removes a schema from a database.
CREATE DOMAIN	Creates a named list of allowable values for columns in database tables. You can use domains as data types for columns in multiple tables.
DROP DOMAIN	Removes a domain definition from the database.
CREATE TABLE	Creates a structure (table) of columns and rows to hold data.
ALTER TABLE	Adds columns to a table, removes columns from a table, changes column data types, or adds column constraints to a table.
DROP TABLE	Removes a table from the database.
CREATE VIEW	Creates a database object that displays rows of one or more columns from one or more tables. Some views allow you to update the base tables.
DROP VIEW	Drops a database view.
CREATE INDEX	Creates a structure with values from a table column, which speeds up the DBMS's ability to find specific rows within the table.
DROP INDEX	Removes an INDEX from the database.
Data Manipulation Language (DML)
INSERT	Adds one or more rows to a table.
SELECT	Retrieves database data.
UPDATE	Updates data values in a table.
DELETE	Removes one or more rows from a table.
TRUNCATE	(MS-SQL Server). Removes all rows from a table.
Data Control Language (DCL)
ROLLBACK	Undoes changes made to database objects, up to the last COMMIT or SAVEPOINT.
COMMIT	Makes proposed changes to the database permanent. (COMMITTED changes cannot be undone with a ROLLBACK.)
SAVEPOINT	Marks points in a transaction (set of actions) that can be used to ROLLBACK (or undo) a part of a transaction without having to undo the entire transaction.
GRANT	Gives access to database objects or SQL statements.
REVOKE	Removes access privileges to database objects or executes specific SQL statements.
Programmatic SQL
DECLARE	Reserves server resources for use by a cursor.
OPEN	Creates a cursor and fills it with data values selected from columns in one or more rows in one or more database tables.
FETCH	Passes data values from a cursor to host variables.
CLOSE	Releases the resources used to hold the data copied from the database into a cursor.
DEALLOCATE	Releases server resources reserved for use by a cursor.
CREATE PROCEDURE	(MS-SQL Server). Creates a named list of SQL statements that a user (with the correct access rights) can execute by using the name as he or she would any other SQL keyboard.
ALTER PROCEDURE	(MS-SQL Server). Changes the sequence of SQL statements that the DBMS will perform when the user calls a procedure.
DROP PROCEDURE	(MS-SQL Server). Removes a procedure from the database.
CREATE TRIGGER	(MS-SQL Server, DB2, PL/SQL). Creates a named sequence of SQL statements that the DBMS will execute automatically when a column has a specific data value or when a user attempts a specific database command (the triggering event).
ALTER TRIGGER	(MS-SQL Server, DB2, PL/SQL). Changes the SQL statements executed when the DBMS detects the triggering event, or changes the nature of the event.
DROP TRIGGER	(MS-SQL Server, DB2, PL/SQL). Removes a trigger from the database.
DESCRIBE INPUT	Reserves an input area an application program will use to pass values to the DBMS during a dynamic SQL statement.
GET DESCRIPTOR	Tells the DBMS to use the DESCRIPTOR area to retrieve data values placed there by an application program during a dynamic SQL statement.
DESCRIBE OUTPUT	Reserves an output area the DBMS will use to pass data from the database to an application program during a dynamic SQL statement.
SET DESCRIPTOR	Tells the DBMS to place data into the DESCRIPTOR area for retrieval by an application program during a dynamic SQL statement.
PREPARE	Tells the DBMS to create an execution plan or compile the SQL statement(s) in a dynamic SQL statement.
EXECUTE	Tells the DBMS to execute a dynamic SQL statement.

You will find several tips on each of the common SQL statements (and others that are important, though not commonly used), throughout this book. The important thing to know now is that all SQL statements begin with a keyword (verb), have a list of objects on which to act, and may have one or more clauses that further describe the action or identify the rows on which to act at the end of the statement. If the SQL statement does not contain clauses that limit the action to rows with specific column data values, the DBMS will take action on all of the rows in a table (or multiples tables through a VIEW).