Database Reference
In-Depth Information
The
Update
statement facilitates update of rows of a table. Depending on the
condition specified (in the
Where-Clause
), the update might affect a single row, multiple
rows, or all the rows (if no condition is specified, or all the rows meet the condition
specified). The data to be used for the update can be specified in one of three ways:
•
By specifying literal column-values for the row(s) of the table that
meet(s) the condition specified
•
By specifying execution-time variables corresponding to stated
columns, for the row(s) of the specified table that meet(s) the
condition specified
•
By specifying a sub-query that includes columns corresponding
to the stated columns, for the row(s) of the specified table that
meet(s) the condition specified
The
Delete
statement facilitates deletion of rows from a specified table. Depending
on the condition specified (in the
Where-Clause
), the deletion might be for a single
row, multiple rows, or all the rows (if no condition is specified, or all the rows meet the
condition specified).
The
Commit
and
Rollback
statements facilitate transaction management, and are
useful in situations where it is desirable to have a transaction wholly committed, or not
recorded, in order to preserve the integrity of the database.
The
Select
statement is one of the most powerful and widely used statements in
SQL. It facilitates retrieval of data from one or more tables and presenting the information
to the end-user. This process is called querying. It is very flexible and can therefore be
used in several different ways:
•
Simple queries involve data retrieval (via the
Select
statement)
from one table only.
•
Queries involving multiple tables can be constructed in one of
two ways:
·
The traditional method requires you to specify the join
condition in the
Where-Clause
of the
Select
statement.
·
The American National Standard Institute (ANSI) method
requires you to specify the join using join keywords. The
ANSI method is more verbose but is also more flexible than
the traditional method.
•
Queries involving the use of functions provide additional
functionality and flexibility in data retrieval. The functions can be
classified into five categories: row functions, date functions, data
conversion functions, aggregation functions, and programmer-
defined functions.
•
Queries can be constructed using the special operators LIKE,
BETWEEN and IN.
•
You can have queries containing other queries - nested queries.
These queries may involve the use of keywords such as ANY, ALL,
EXISTS and NOT EXISTS.
