Introduction to SQL (
Page 1 of 6 )
Interested in learning more about the SQL language? Read on to learn some of the useful features of this language. This article is excerpted from Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN 1590593006).
In this introductory chapter, we explore the
origin and utility of the SQL language, demonstrate some of the more useful
features of the language, and define a simple database design from which most
examples in the book are derived.
What Is SQL?
SQL is a special-purpose language used to define, access, and
manipulate data. SQL is nonprocedural, meaning that it describes the
necessary components (i.e., tables) and desired results without dictating
exactly how those results should be computed. Every SQL implementation sits atop
a database engine, whose job it is to interpret SQL statements and
determine how the various data structures in the database should be accessed to
accurately and efficiently produce the desired outcome.
The SQL language includes two distinct sets of commands: Data
Definition Language (DDL) is the subset of SQL used to define and
modify various data structures, while Data Manipulation Language (DML) is
the subset of SQL used to access and manipulate data contained within the data
structures previously defined via DDL. DDL includes numerous commands for
handling such tasks as creating tables, indexes, views, and constraints, while
DML is comprised of just five statements:
INSERT
Adds data to a database.
UPDATE
Modifies data in a database.
DELETE
Removes data from a database.
MERGE
Adds and/or modifies data in a database. MERGE is part of the 2003
ANSI SQL standard.
SELECT
Retrieves data from a database.
Some people feel that DDL is the sole property of database
administrators, while database developers are responsible for writing DML
statements, but the two are not so easily separated. It is difficult to
efficiently access and manipulate data without an understanding of what data
structures are available and how they are related; likewise, it is difficult to
design appropriate data structures without knowledge of how the data will be
accessed.That being said, this book deals almost exclusively with DML, except
where DDL is presented to set the stage for one or more DML examples. The
reasons for focusing on just the DML portion of SQL include:
- DDL is well represented in various books on database design
and administration as well as in SQL reference guides.
- Most database performance issues are the result of
inefficient DML statements.
- Even with a paltry five statements, DML is a rich enough
topic to warrant not just one book, but a whole series of books.
Anyone who writes SQL in an Oracle environment should be armed with the
following three books: a reference guide to the SQL language, such as
Oracle
in a Nutshell (O’Reilly); a performance-tuning guide, such as
Optimizing
Oracle Performance (O’Reilly); and the book you are holding, which shows how
to best utilize and combine the various features of Oracle’s SQL implementation.
So why should you care about SQL? In this age of Internet
computing and n-tier architectures, does anyone even care about data access
anymore? Actually, efficient storage and retrieval of information is more
important than ever:
- Many companies now offer services via the Internet. During
peak hours, these services may need to handle thousands of concurrent requests,
and unacceptable response times equate to lost revenue. For such systems, every
SQL statement must be carefully crafted to ensure acceptable performance as data
volumes increase.
- We can store a lot more data today than we could just a few
years ago. A single disk array can hold tens of terabytes of data, and the
ability to store hundreds of terabytes is just around the corner. Software used
to load or analyze data in these environments must harness the full power of SQL
to process ever-increasing data volumes within constant (or shrinking) time
windows.
Hopefully, you now have an appreciation for what SQL is and why it
is important. The next section will explore the origins of the SQL language and
the support for the SQL standard in Oracle’s products.
 This article is excerpted from Mastering Oracle
SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN 1590593006). Check it out at your favorite
bookstore today. Buy
this book now. |
