P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML
SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0
DDL 359that are stored in different tables—such as salaried em-
ployee wages and hourly employee wages. It makes no
sense to have number of tickets and ticket price joined via
a union, but SQL will probably allow it because they are
both numeric columns. The syntax is:select statement 1
UNION
select statement 2Minus
The minus operation, also directly ported from set the-
ory, removes matching rows from multiple result sets.
This is useful in determine what is different between these
results—or “what didn’t sell while it was under a promo-
tion.” The syntax is:select statement 1
MINUS
select statement 2DDL
Create Table
Thecreate tablestatement allocates storage for data to
be stored. This storage has a structure (columns with
datatypes) and optional constraints defined. Once the ta-
ble is created, the table is ready to receive data via the
INSERT statement (see below). The syntax is straightfor-
ward:create table <TABLE_NAME> (
<column_element> | <table_constraint> )
For example, to create the movie table located in Ap-
pendix A, the following syntax was used:CREATE TABLE MOVIE (
MOVIE_ID NUMBER NOT NULL,
MOVIE_TITLE VARCHAR2(50) NOT NULL,
INVENTORY_ID NUMBER NOT NULL,
MOVIE_RATING CHAR(5) NOT NULL,
PASS_ALLOWED CHAR(1) NOT NULL);Keys
When a database is modeled using entity–relationship
techniques, identifying attribute(s) are identified for all
fundamental entities (tables), along with those attributes
that relate two or more tables. The identifying attribute(s)
uniquely distinguish one row of data from all others in the
table. In SQL these identifying attribute(s) are identified
to the database engine as a primary key. Many database
engines use this information to enforce the uniqueness
property that is inherent in the definition. Attributes that
tie relations together are known as foreign keys. Keys can
be identified either when a table is created (with the create
table command) or after (via the alter table command).Create Index
The create index statement allows the database engine to
create an index structure. An index structure provides amapping to data in a table based upon the values stored
in one or more columns. Indexes are used by the database
engine to improve a query’s performance by evaluating the
whereclause components and determining if an index is
available for use. If an index is not available for use during
query processing, each and every row of the table will have
to be evaluated against the query’s criteria for a match.
The syntax is not part of the SQL language specification
(Groff & Weinberg, 1999, p. 387) though most DBMSs
have a version of the create index statement.
For example, to create an index on the MOVIETITLE
of the movie table located in the Appendix, the following
syntax was used:create index MOVIE_NAME_IDX on MOVIE
(MOVIE_TITLE);Create View
“According to the SQL-92 standard, views are virtual ta-
bles that act as if they are materialized when their name
appears” (Celko, 1999, p.55). The termvirtualis used
because the only permanent storage that a view uses is
the data dictionary (DD) entries that the RDBMS defines.
When the view is accessed (by name) in a SQLfromclause,
the view is materialized. This materialization is simply the
naming, and the storing in the RDBMS’s temporary space,
of the result set of the view’s select statement. When the
RDBMS evaluates the statement that used the view’s name
in itsfromclause, the named result set is then referenced
in the same fashion as a table object. Once the statement
is complete the view is released from the temporary space.
This guarantees read consistency of the view. A more per-
manent form of materialized views is now being offered
by RDBMS vendors as a form of replication, but is not
germane to this discussion.
The syntax for creating a database view is below:create view <name> [<column list>] as
<select statement>The power of a view is that the select statement (in
the view definition) can be almostanyselect statement,
no matter how simple or complex—various vendors may
disallow certain functions being used in the select state-
ment portion of a view’s definition.
Views have proven to be very useful in implementing
security (restricting what rows and/or columns may be
seen by a user); storing queries (especially queries that are
complex or have specialized formatting, such as a report);
and reducing overall query complexity (Slazinski, 2001).Constraints
Constraints are simple validation fragments of code. They
are (from our perspective) either the first or last line of
defense against bad data. For example, we could validate
that a gender code of “M” or “F” was entered into an em-
ployee record. If any other value is entered, the data will
not be allowed into the database.