oracle | Page 5

model.Other companies, such as Oracle, rallied around the
relational flag as well.By the mid 1980’s, SQL had gathered sufficient momentum in
the marketplace to warrant oversight by the American National Standards Institute
(ANSI).ANSI released its first SQL standard in 1986, followed by updates in 1989,
1992, and 1999.
Thirty years after the System/R team began prototyping a relational database, SQL is
still going strong.While there have been numerous attempts to dethrone relational
databases in the marketplace, well-designed relational databases coupled with well-
written SQL statements continue to succeed in handling large, complex data sets
where other methods fail.
Oracle’s SQL Implementation
Given that Oracle was an early adopter of the relational model and SQL, one might
think that they would have put a great deal of effort into conforming with the vari-
ous ANSI standards.For many years, however, the folks at Oracle seemed content
that their implementation of SQL was functionally equivalent to the ANSI standards
without being overly concerned with true compliance.Beginning with the release of
Oracle8i, however, Oracle has stepped up its efforts to conform to ANSI standards
and has tackled such features as the CASE statement and the left/right/full outer join
syntax.
Ironically, the business community seems to be moving in the opposite direction.A
few years ago, people were much more concerned with portability and would limit
their developers to ANSI-compliant SQL so that they could implement their systems
on various database engines.Today, companies tend to pick a database engine to use
across the enterprise and allow their developers to use the full range of available
options without concern for ANSI-compliance.One reason for this change in attitude
is the advent of n-tier architectures, where all database access can be contained within
a single tier instead of being scattered throughout an application.Another possible
reason might be the emergence of clear leaders in the DBMS market over the last five
years, such that managers perceive less risk in which database engine they choose.
Theoretical Versus Practical Terminology
If you were to peruse the various writings on the relational model, you would come
across terminology that you will not find used in this book (such asrelationsand
,ch01.8459 Page 3 Wednesday, March 27, 2002 2:18 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved. 4|
Chapter 1: Introduction to SQL
tuples).Instead, we use practical terms such as tables and rows, and we refer to the
various parts of an SQL statement by name rather than by function (i.e., “SELECT
clause” instead ofprojection).With all due respect to Dr.Codd, you will never
hear the wordtupleused in a business setting, and, since this book is targeted
toward people who use Oracle products to solve business problems, you won’t
find it here either.
A Simple Database
Because this is a practical book, it contains numerous examples.Rather than fabri-
cating different sets of tables and columns for every chapter or section in the book,
we have decided to draw from a single, simple schema for most examples.The sub-
ject area that we chose to model is a parts distributor, such as an auto-parts whole-
saler or medical device distributor, in which the business fills customer orders for
one or more parts that are supplied by external suppliers.Figure 1-1 shows the
entity-relationship model for this business.
If you are unfamiliar with entity-relationship models, here is a brief description of
how they work.Each box in the model represents anentity, which correlates to a
database table.
*The lines between the entities represents therelationshipsbetween
tables, which correlate to foreign keys.For example, the CUST_ORDER table holds
a foreign key to the employee table, which signifies the salesperson responsible for a
particular order.Physically, this means that the CUST_ORDER table contains a col-
umn holding employee ID numbers, and that, for any given order, the employee ID
number indicates the employee who sold that order.If you find this confusing, sim-
ply use the diagram as an illustration of the tables and columns found within our
database.As you work your way through the SQL examples in this book, return
occasionally to the diagram, and you should find that the relationships start mak-
ing sense.
DML Statements
In this section, we introduce the four statements that comprise the DML portion of
SQL.The information presented in this section should be enough to allow you to
start writing DML statements.As is discussed at the end of the section, however,
DML can look deceptively simple, so keep in mind while reading the section that
there are many more facets to DML than are discussed here.
* Depending on the purpose of the model, entities may or may not correlate to database tables.For example,
alogicalmodel depicts business entities and their relationships, whereas aphysicalmodel illustrates tables
and their primary/foreign keys. The model in Figure 1-1 is a physical model.
,ch01.8459 Page 4 Wednesday, March 27, 2002 2:18 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved.DML Statements|
5
The SELECT Statement
The SELECT statement is used to retrieve data from a database.The set of data
retrieved via a SELECT statement is referred to as aresult set.Like a table, a result set is
comprised of rows and columns, making it possible to