Commonly Used SQL Statements (SQL99)
The following provides a reference to the SQL statements that you might use on a day-to-day basis. It does not intend to provide a complete reference, and it also does not aim to illustrate vendor implementation-specific syntax. For a fuller reference or an exact guide to your chosen RDBMS, please refer to your vendor's documentation.
ALTER TABLE
ALTER TABLE table_name {
ADD [COLUMN] column_name datatype attributes
| ALTER COLUMN column_name SET DEFAULT default_value
| ALTER COLUMN column_name DROP DEFAULT
| ALTER COLUMN column_name ADD SCOPE table_name
| ALTER COLUMN column_name DROP SCOPE [RESTRICT | CASCADE]
| DROP COLUMN column_name [RESTRICT | CASCADE]
| ADD table_constraint_name
| DROP CONSTRAINT table_constraint_name [RESTRICT | CASCADE]
}
Description:
The ALTER TABLE statement alters the columns and constraints of a table. As you can see, this statement provides a number of ways to add, modify, or drop a column from a table. Statements that utilize RESTRICT and CASCADE allow you to dictate how the database should behave if either dropping or modifying a column impacts on foreign or primary keys. Specifically, RESTRICT forces the database to throw an exception, or simply abort the command, if the database contains foreign keys that reference a primary key that you want to drop or modify. CASCADE instructs the database to drop any foreign keys that reference a primary key that you want to drop or modify.
The constraints options allow you to place table-wide restrictions on the type of data a user can enter into a table's rows. In the context of constraints, RESTRICT and CASCADE have the same meaning as with columns.
CREATE TABLE
CREATE [GLOBAL TEMPORARY | LOCAL TEMPORARY] TABLE table_name
[ON COMMIT [PRESERVE ROWS | DELETE ROWS] ] {
column_name datatype [(length)] [NULL | NOT NULL],...n
| [LIKE table_name]
| [table_constraint][,...n] ]
}
Description:
The CREATE TABLE statement creates a new table. Both the TEMPORARY options are optional, and they both create temporary tables that the database discards at the end of a session. The GLOBAL option creates a table that is available to all user sessions; whereas, the LOCAL option creates a table that is only available to the user session that creates it. The ON COMMIT clause is used only with temporary tables, and it has two options. The first preserves any modifications to a temporary table when you issue a COMMIT statement. The second, DELETE ROWS, deletes the rows of the temporary table when you issue a COMMIT statement; this is the default behavior that SQL99 defines.
The LIKE option allows you to create a new table based on the definition of an existing table. For example, you create a new table that has the same column names as an existing table.
The optional table constraints clause allows you to place constraints, or tests, on the data types that a user can insert into a column. For example, you could stipulate that a column is the primary key for the table or that it must contain a unique value.
CREATE VIEW
CREATE VIEW view_name [(column [,...])] AS SELECT_statement [WITH [CASCADE | LOCAL] CHECK OPTION]
Description:
The CREATE VIEW statement creates a new view based on a query. The list of columns is optional. It allows you to assign names to each of the columns of the view. If you do not provide a list of columns, the statement uses the column names returned by the SELECT statement.
The optional WITH CHECK OPTION option ensures that a view can only insert, modify, or delete data that it can read from the view's base table. The LOCAL option only applies the check to the current view, whereas the CASCADE option applies the check to the current view and all the views on which it is built.
DELETE
DELETE FROM [owner.]table_name [WHERE clause]
Description:
The DELETE statement deletes rows from a table. If you use the statement without the optional WHERE clause, it deletes all the rows in the table. For an explanation of the WHERE clause, see the “Commonly Used SQL Clauses” section later in this appendix.
DROP TABLE
DROP TABLE table_name [RESTRICT | CASCADE]
Description:
The DROP TABLE statement drops a table—including constraints, indexes, and triggers—from a database. RESTRICT forces the database to throw an exception or abort the operation if any views or constraints reference the table. In contrast, CASCADE drops all views or constraints that reference the table.
DROP VIEW
DROP VIEW view_name [RESTRICT | CASCADE]
Description:
The DROP VIEW statement drops a view. RESTRICT forces the database to throw an exception or abort the operation if any views or constraints reference the table. In contrast, CASCADE drops all views and constraints that reference the table.
INSERT
INSERT INTO [database_name.]owner. (table_name | view_name) [(column [,...])]
{[DEFAULT] VALUES | VALUES (value[,...]) | SELECT_statement }
Description:
The INSERT statement inserts new rows of data into either a table or view.
The list of columns is optional, but if you omit it, the statement will either assume the order of the columns and insert data on this assumption (inserting either null or default values into the remaining columns, or the RDMBS will throw an exception, for example, MySQL throws a Column Count Does Not Match Value Count exception. The statement accepts data in three ways. The first is using the DEFAULT VALUES method, which inserts the database implementation's default values, if the implementation supports default values (most do), into the table. The second, VALUES, contains a list of comma-separated values that insert into the specified columns. Finally, you can use a SELECT statement, which is described next in this appendix.
SELECT
SELECT [ALL | DISTINCT] [select[,...]] FROM table_name [,...] [JOIN join_condition] [WHERE search_condition] [GROUP BY group_by_expression[,...]] [HAVING search_condition [,...]] [ORDER BY order_expression [ASC | DESC] ]
Description:
The SELECT statement retrieves data from a table. The basic form of the statement is shown in the first line of the syntax. The list of select items can consist of column names, expressions, local and global variables (where supported), mathematical calculations, or a wildcard * (returns all columns). In addition, you can specify an alias for the returned columns. For example, to select the column first_name but reference it as fore_name, you use the syntax SELECT first_name AS fore_name. You can also apply this syntax to a table name by using the FROM clause. The default behavior of the statement is to return all records, including duplicates and those containing only default values. If you use the DISTINCT option, the statement does not return duplicate records.
Note
The final section of this appendix, “Commonly Used SQL Commands,” describes the use of the WHERE, GROUP BY, HAVING, and ORDER BY clauses.
The JOIN operation allows you to modify a SELECT statement so that it returns a result set based on a relationship you define (a join) between columns in two different tables. There are five main types of join.
Inner Join
The Inner Join, which is also known as an EquiJoin, joins two tables where a Boolean expression you specify returns true. For example, the following JOIN operation joins the products table and suppliers table by their supplier_id columns. In this instance, the statement will only return the records where supplier IDs correspond in both tables:
Note
Although the SQL99 standard syntax for an inner join is JOIN, many RDMBS use the syntax INNER JOIN.
CROSS JOIN
The CROSS JOIN joins two tables by Cartesian Product—each record in the first table joins to all the records in the second table. Because a CROSS JOIN joins every record in two tables, you do not specify columns to join. Thus, the syntax for this join appears as follows:
SELECT * FROM products CROSS JOIN suppliers
Caution
Using a CROSS JOIN on a live database can result in an enormous result set. You can very easily place an excessive load on a system using this command, and this can adversely affect the normal operation of your database server.
Left Outer Join
The LEFT JOIN joins two tables and returns all the values from the left table and only the matching records from the right table. Where no match occurs, a NULL value is returned as the value of the right table. For example, the following statement returns all records that have supplier IDs in the products table, but only those records from the suppliers table that have a corresponding supplier ID in the products table:
Right Outer Join
The RIGHT JOIN joins two tables and returns all the values from the right table and only the matching records from the left table. Where no match occurs, a NULL value is returned as the value of the left table. For example, the following statement returns all the supplier IDs from the suppliers table, but only those from the products table that have a corresponding entry in the suppliers table:
FULL JOIN
The FULL JOIN joins two tables, and returns all the rows from both tables. For example, the following statement returns all the supplier IDs from both tables regardless of whether they match:
SELECT * FROM products FULL JOIN suppliers ON products.supplier_id=suppliers.supplier_id
UPDATE
UPDATE {table_name | view_name}
SET {column_name | variable_name}= {DEFAULT | expression} [,...n]
WHERE conditions
Description:
The UPDATE statement changes data in a table or view. The statement centers on the SET clause that allows you to set a named column to a new data value. You can express the data value either as DEFAULT—the column's default value —or as an expression. The expression can be an expression, such as age+10; or a value, such as 'New Value'. The WHERE clause is optional, but if you omit it, the statement updates all the records in the column.