Oracle SQL: The Essential Reference

Overview

SQL (Structured Query Language) is the heart of a relational database management system. It's the language used to query the database, to create new tables in the database, to update and delete database fields, and to set privileges in the database. Oracle SQL: The Essential Reference is for everyone who needs to access an Oracle database using SQL—developers, DBAs, designers, and managers.

SQL is based on research dating back to the late 1960s, but its first commercial release ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (32) from $1.99   
  • New (2) from $40.28   
  • Used (30) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$40.28
Seller since 2009

Feedback rating:

(847)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
1565926978 *BRAND NEW* Ships Same Day or Next!

Ships from: Springfield, VA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$50.00
Seller since 2014

Feedback rating:

(136)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

SQL (Structured Query Language) is the heart of a relational database management system. It's the language used to query the database, to create new tables in the database, to update and delete database fields, and to set privileges in the database. Oracle SQL: The Essential Reference is for everyone who needs to access an Oracle database using SQL—developers, DBAs, designers, and managers.

SQL is based on research dating back to the late 1960s, but its first commercial release was in the RDBMS announced by the fledgling Oracle Corporation in 1979. Since that time, every other database vendor has adopted SQL, and ANSI and the ISO have made it a standard. Although vendors diverge in their extensions to SQL, the core language is standard across vendor boundaries.

Despite SQL's long history and relative simplicity, few developers and database administrators are truly masters of SQL. The constant stream of vendor enhancements, the hard-won experience in tuning SQL for best performance, and the requirements of particular operational environments mean that there is always more to learn about SQL.

Oracle SQL: The Essential Reference delivers all the information needed to keep ahead of the learning curve on standard SQL and Oracle's extensions to it. This single, concise reference volume will hold its own against a stack of Oracle manuals and even yield insights and examples not available in those manuals.

There are chapters on basic SQL elements (naming requirements, column types, pseudo-types, data conversion rules, operators); Data Definition Language (DDL) and Data Manipulation Language (DML); common language elements (constraints, storage clause, predicates); SQL functions; PL/SQL (including procedures, functions, and packages); SQL*Plus, and Oracle SQL optimization and tuning. The book covers Oracle 8i, release 8.1.6.

Read More Show Less

Editorial Reviews

Booknews
Deals with using structured query language (SQL) to access an Oracle8i database. The author surveys SQL statements for definition or modification of database objects and for data manipulation and control, commands for the SQL*Plus interface, and structure and syntax for the basic elements of procedural language extensions to SQL (PL/SQL). Annotation c. Book News, Inc., Portland, OR (booknews.com)
Read More Show Less

Product Details

  • ISBN-13: 9781565926974
  • Publisher: O'Reilly Media, Incorporated
  • Publication date: 9/28/2000
  • Edition description: 1 ED
  • Pages: 418
  • Product dimensions: 6.99 (w) x 9.13 (h) x 0.83 (d)

Meet the Author

David Kreines is the Manager of Database Services for Rhodia, Inc., and author of Oracle SQL: The Essential Reference (O'Reilly Media, Inc., 2000) and coauthor of Oracle in a Nutshell (with Rick Greenwald) (O'Reilly Media, Inc., 2002), Oracle Database Administration: The Essential Reference (with Brian Laskey) (O'Reilly Media, Inc., 1999) and Oracle Scripts (with Brian Lomasky) (O'Reilly Media, Inc., 1998). Dave has worked with Oracle as a developer and database administrator since 1985, on a wide variety of platforms, from PCs to mainframes. He is an Oracle Certified Professional, is certified as a DBA, and has been a frequent contributor to Oracle conferences, user groups, and publications, both in the United States and in Europe. Dave served two terms as president of the International Oracle Users Group -Americas (IOUG-A), and spent ten years on the board of directors.

Read More Show Less

Read an Excerpt


Chapter 1: Elements of SQL

SQL was developed to provide easy access to relational databases, so it is able to perform the following kinds of actions:

  • Querying data from a database
  • Inserting data into a database
  • Deleting data from a database
  • Creating and manipulating database objects
  • Controlling access to the database

Strictly speaking, SQL is not a language at all, but rather a means of conveying instructions to the Oracle database. It differs from traditional programming languages in several important ways:

  • SQL provides automatic navigation to data.
  • SQL operates on sets of data, rather than on individual data elements.
  • SQL is declarative, not procedural, and does not provide procedural control.
  • SQL programming is done at the logical level; there is little need to deal with the details of implementation.

Simply put, when programming in SQL you tell Oracle what you want to do, but not how it should be done. However, this approach can be both a blessing and a curse. Consider the following SQL statement:

SELECT ename, deptno, sal, comm FROM scott.emp WHERE hiredate > '01-JAN-00'; 

This simple SQL statement tells the database to display a list consisting of name (ename), department number (deptno), salary (sal), and commission (comm) for each employee hired after January 1, 2000. Such a program might have taken hundreds of lines of code in an "old style" procedural language, but takes only three lines in SQL. At the same time, however, Oracle is not always too smart about how it retrieves data. Although Oracle's internal "query optimizer" has steadily improved, there are still many ways to improve SQL performance, and Chapter 8, SQL Statement Tuning, is dedicated to this subject.

TIP:  

The lack of procedural control was viewed by some as a disadvantage of SQL, so Oracle Corporation developed PL/SQL (Procedural Language/SQL), which is discussed in Chapter 7, PL/SQL.

SQL statements, also known as SQL commands, are combinations of the following:

Keywords

Reserved words with specific operational meaning to Oracle.

Variables

Data elements, which may be dynamically replaced with text or numeric values. In SQL these are the names of objects such as columns, tables, or views.

Literals

Constant data, including text strings and numbers.

Operators

Symbols or words that operate on one or more variables or literals.

SQL statements are composed of commands, variables, and operators, which are described in detail in this and subsequent chapters. A SQL statement is constructed from:

  • Characters A through Z (or the equivalent from your database character set)
  • Numbers 0 through 9
  • Spaces
  • The following special characters: + - * = ? ! @ ( ) _ . , < > | $ #
  • TIP:  

    Oracle strongly discourages the use of # and $.

    Other characters, such as &, are also used in SQL statements, but may be intercepted and interpreted by SQL*Plus if you are using that tool. See Chapter 6, SQL*Plus, for more information.

A SQL statement can contain one or more of the following items anywhere a single space can occur:

  • Tab
  • Carriage return
  • Multiple spaces
  • Comments
  • TIP: 

    Certain components of SQL statements (such as variable names and strings) may contain other characters, as long as they are enclosed in double quotes.

The following two SELECT statements, for example, are evaluated in exactly the same way by Oracle and both return the same result set:

SELECT ename,empno,sal FROM scott.emp WHERE sal>500;   SELECT ename,         empno, sal FROM   scott.emp WHERE  sal > 500; 

SQL is generally not case-sensitive, so case is not significant except in literals, which are enclosed in quotes.

WARNING: 

Be aware that a variable name enclosed in double quotes will be case-sensitive. This fact is especially important if you access your Oracle database using Microsoft Access, which creates objects using lowercase names.

Naming in SQL

Most naming requirements in SQL are actually requirements of the Oracle database; names that are acceptable for schema objects (defined in the next section) in the Oracle database are acceptable in SQL, and vice versa. The following rules apply to the names of schema objects in Oracle:

  • They may comprise 1 to 30 alphanumeric characters.
  • They must begin with a letter.
  • They may include an underscore ( _ ).
  • They may include a dollar ($) or pound sign (#), although Oracle discourages the use of these characters.
  • They may not be a reserved word.
  • They may not be the name of a SQL command.
  • TIP:  

    A name may begin with and/or contain any characters if it is enclosed in double quotes.

Schema Objects

A schema object is a logical collection of data or other objects that are owned by a user and stored in the database. The following types of objects are considered schema objects:

  • Clusters
  • Database links
  • Database triggers
  • Dimensions
  • External procedure libraries
  • Index-organized tables
  • Indexes
  • Index types
  • Materialized views/snapshots
  • Materialized view logs/snapshot logs
  • Nested table types
  • Object types
  • Operators
  • Packages
  • Sequences
  • Stored functions
  • Stored procedures
  • Synonyms
  • Tables
  • Varying array types
  • Views
  • Database links

General Syntax

Generally, you reference schema objects in SQL statements using the following syntax:

schema.object_name.object_ part@dblink 

These syntax elements have the following meaning:

schema

The name of the schema that owns the object. In Oracle, a schema corresponds one-to-one with a username; if the schema is omitted from a reference to a schema object, then the username that is currently logged in is used by default.

object_name

The name of the object being referenced, such as a table.

object_part

The name of a part of an object, for those schema objects that have a part, such as a column of a table.

dblink

The name of a database link referencing a remote database.

The syntax shown here, with a schema name followed by a period, then followed by an object name (for example, scott.emp) is commonly referred to as dot notation. Generally, if the schema. portion of a name is omitted; the schema of the user currently connected to the database will be used by default.

For example, the following SQL statement queries data from a table, which is a schema object named emp in the schema scott. This schema is located in a remote database and is referenced by the database link test:

SELECT ename, empno, sal FROM scott.emp@test  WHERE sal > 500; 

Partition Syntax

When referencing a specific partition or subpartition of a partitioned table, use the following syntax:

 

schema.table_name {PARTITION (partition) |
SUBPARTITION (subpartition)
)

These syntax elements have the following meaning:

schema

The name of the schema that owns the object. In Oracle, a schema corresponds one-to-one with a username, and if the schema is omitted from a reference to a schema object, then the username that is currently logged in is used by default.

table_name

The name of the table being referenced.

partition

The name of a partition of the table.

subpartition

The name of a subpartition of the table.

This construct is known as a partition-extended table name. A partition-extended table name may not have a database link associated with it. Therefore, if you want to access this object on a remote database, you must create a view that can be accessed using the general schema object syntax described previously.

Datatypes

Oracle stores data in the database in any of three basic families of datatypes: character, numeric, and date. Both the character and numeric families have several distinct datatypes associated with them, which are described in the following sections.

Character Data

Character data is any string of one or more bytes of data that will not be the direct target of an arithmetic operation. Oracle (and SQL) supports several types of character data, which are listed below with their usage syntax:

CHAR [(length)]

Fixed-length character data, with a maximum length of 2000 bytes. length specifies the maximum length of the character string to be stored.

VARCHAR2 [(length)]

Variable-length character data, with a maximum length of 4000 bytes. length specifies the maximum length of the character string to be stored.

NCHAR [(length)]

Fixed-length character data consisting of characters from a National Character Language (NLS) supported character set. Since a character may require more than one byte, the maximum length is 2000 bytes (which may allow fewer than 2000 characters). length specifies the maximum length of the character string to be stored.

NVARCHAR2 [(length)]

Variable-length character data consisting of characters from a National Language Support (NLS) character set. Since a character may require more than one byte, the maximum length is 4000 bytes (which may allow fewer than 4000 characters).length specifies the maximum length of the character string to be stored.

LONG

Variable-length character data with a maximum length of 2 gigabytes.

RAW

Raw binary data with a maximum length of 2000 bytes. RAW data will not be converted by Oracle when moving between systems with different character sets.

LONG RAW

Raw binary data with a maximum length of 2 gigabytes. LONG RAW data will not be converted by Oracle when moving between systems with different character sets.

The following character datatypes are also recognized for compatibility with ANSI SQL:

CHARACTER CHARACTER VARYING CHAR VARYING  NATIONAL CHARACTER  NATIONAL CHAR  NATIONAL CHARACTER VARYING  NATIONAL CHAR VARYING  NCHAR VARYING  

Large Objects

Oracle provides several datatypes that support storage of large amounts of data in a single column. These datatypes are often used to store images, sound, and other large objects:

BLOB

Large, raw binary data with a maximum length of 4 gigabytes. BLOB data will not be converted by Oracle when moving between systems with different character sets. When a BLOB column is referenced, a LOB locator is returned.

CLOB

Large character data with a maximum length of 4 gigabytes.

NCLOB

Large character data consisting of characters from a National Language Support (NLS) character set with a maximum length of 4 gigabytes. NCLOB data will not be converted by Oracle when moving between systems with different character sets.

BFILE

Provides access to a binary file stored in an operating system file external to the Oracle database. The file can have a maximum size of 4 gigabytes.

Numeric Data

Numeric data is data that can participate in an arithmetic operation directly without data conversion. Oracle has only a single type of numeric data: the NUMBER type....

Read More Show Less

Table of Contents

  • Dedication
  • Foreword
  • Preface
  • Chapter 1: Elements of SQL
  • Chapter 2: Data Definition Statements
  • Chapter 3: Data Manipulation and Control Statements
  • Chapter 4: Common SQL Elements
  • Chapter 5: SQL Functions
  • Chapter 6: SQL*Plus
  • Chapter 7: PL/SQL
  • Chapter 8: SQL Statement Tuning
  • SQL Resources
  • Colophon

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted April 25, 2001

    Don't buy this book

    This book is an excerpt from Oracle's reference book. The few examples it includes are extremely simple. I would recommend buying the books from Oracle instead of this book, even though Oracle recommends finding a decent SQL book other than their reference book. In my attempt to do that, I bought this book. It was a mistake. Do not buy this book.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)