Tuesday, January 19, 2010

1.1 What Is SQL?











 < Day Day Up > 







1.1 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.

















     < Day Day Up > 



    No comments: