Cover | Table of Contents

Table of Contents

Chapter 1: SQL Pocket Guide

Content preview·Buy reprint rights for this chapter

This book is an attempt to cram the most useful information about SQL into a pocket-size guide. It covers data manipulation and transaction control statements, datatypes, and table creation and modification for the following platforms:

Oracle Database 10g Release 2
(including Oracle Express Edition)
IBM DB2 Universal Database Release 8.2
Microsoft SQL Server 2005
MySQL 5.0
PostgreSQL 8.1

Not all syntax will work on all platforms, and some features may not be available in earlier releases of these products. Whenever possible, I've tried to note any product or release dependencies.

This book cannot possibly cover all SQL syntax for all products. Its goal is to cover the syntax most commonly used by developers. If you need comprehensive and authoritative syntax, refer to the SQL reference manual for your database platform.

After this introduction, topics are organized alphabetically, with section names carefully chosen to correspond to relevant SQL keywords. If you need help writing a join, for example, you should be able to quickly flip through the book and find the section titled "" between the sections "" and "." Consult the table of contents and/or the index if you don't immediately find what you're looking for.

Many thanks to all of the readers of the first edition who took the time to provide feedback and suggestions. As the result of an overwhelming number of requests, I've added coverage of CREATE TABLE statements and datatypes to this edition. I've also included a discussion of the powerful and increasingly significant PostgreSQL platform, as well as coverage of the very potent window functions (which are sometimes called analytic functions).

I hope there will be a third edition someday, and I would really appreciate your feedback on this second edition so that the third can be even better. It's tough to take a large topic such as SQL and cram it into a small pocket guide. My hope is to include all of the many little things (e.g., function parameters) that you tend to forget, and thus have to look up, when writing SQL. Please let me know how I've succeeded, or not succeeded, in reaching that goal. If you find yourself looking in vain for a piece of information that you think belongs in this book, or if I've wasted space by including not-so-useful information, please let me know by visiting the contact/feedback section at

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

This book is an attempt to cram the most useful information about SQL into a pocket-size guide. It covers data manipulation and transaction control statements, datatypes, and table creation and modification for the following platforms:

Oracle Database 10g Release 2
(including Oracle Express Edition)
IBM DB2 Universal Database Release 8.2
Microsoft SQL Server 2005
MySQL 5.0
PostgreSQL 8.1

Not all syntax will work on all platforms, and some features may not be available in earlier releases of these products. Whenever possible, I've tried to note any product or release dependencies.

This book cannot possibly cover all SQL syntax for all products. Its goal is to cover the syntax most commonly used by developers. If you need comprehensive and authoritative syntax, refer to the SQL reference manual for your database platform.

After this introduction, topics are organized alphabetically, with section names carefully chosen to correspond to relevant SQL keywords. If you need help writing a join, for example, you should be able to quickly flip through the book and find the section titled "" between the sections "" and "." Consult the table of contents and/or the index if you don't immediately find what you're looking for.

Many thanks to all of the readers of the first edition who took the time to provide feedback and suggestions. As the result of an overwhelming number of requests, I've added coverage of CREATE TABLE statements and datatypes to this edition. I've also included a discussion of the powerful and increasingly significant PostgreSQL platform, as well as coverage of the very potent window functions (which are sometimes called analytic functions).

I hope there will be a third edition someday, and I would really appreciate your feedback on this second edition so that the third can be even better. It's tough to take a large topic such as SQL and cram it into a small pocket guide. My hope is to include all of the many little things (e.g., function parameters) that you tend to forget, and thus have to look up, when writing SQL. Please let me know how I've succeeded, or not succeeded, in reaching that goal. If you find yourself looking in vain for a piece of information that you think belongs in this book, or if I've wasted space by including not-so-useful information, please let me know by visiting the contact/feedback section at

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Analytic Functions

Content preview·Buy reprint rights for this chapter

Analytic function is the term Oracle uses for what the SQL standard refers to as a window function. See the section "," later in this book, for more on this extremely useful class of functions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CASE Expressions

Content preview·Buy reprint rights for this chapter

CASE expressions let you implement if-then-else functionality in your SQL statements.

Simple CASE expressions let you correlate a list of values to a list of alternatives. For example:

SELECT u.name,
   CASE u.open_to_public
      WHEN 'y' THEN 'Welcome!'
      WHEN 'n' THEN 'Go Away!'
      ELSE 'Bad code!'
   END AS column_alias
FROM upfall u;

Simple CASE expressions are useful when you can directly link an input value to a WHEN clause by means of an equality condition. If no WHEN clause is a match, and no ELSE is specified, the expression returns null.

Searched CASE expressions let you associate a list of alternative return values with a list of true/false conditions. For example:

SELECT u.name,
   CASE
      WHEN u.open_to_public = 'y' THEN 'Welcome!'
      WHEN u.open_to_public = 'n' THEN 'Go Away!'
      ELSE 'Bad code!'
   END AS column_alias
FROM upfall u;

Null is returned in the event that no condition is TRUE and no ELSE is specified. If multiple conditions are TRUE, only the first such condition is executed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Datatypes

Content preview·Buy reprint rights for this chapter

Database systems support far more datatypes than this small book can cover adequately. This section describes some commonly used types that will meet many of your needs. Consult your vendor documentation if you need to store data that cannot be accommodated by the following types. See "," later in the text, for examples of datatype usage.

For all platforms except Oracle, use the VARCHAR type to store character data:

VARCHAR(max_bytes)

MySQL allows TEXT as a synonym for VARCHAR:

TEXT (max_bytes)

In Oracle, append a 2 to get VARCHAR2:

VARCHAR2(max_bytes)

Oracle Database 9i and higher allow you to specify explicitly whether the size refers to bytes or characters:

VARCHAR2(max_bytes BYTE)
VARCHAR2(max_characters CHAR)

Using Oracle's CHAR option means that all indexing into the string (such as with SUBSTR) is performed in terms of characters, not bytes. For more on Oracle's CHAR option, which is actually quite important, see http://gennick.com/char.html.

Maximums are: 4,000 bytes (Oracle), 32,672 bytes (DB2), 8,000 bytes (SQL Server), 65,532 bytes (MySQL), and 10,485,760 bytes (PostgreSQL).

All platforms support the use of DECIMAL for storing numeric, base-10 data (such as monetary amounts):

DECIMAL
DECIMAL(precision)
DECIMAL(precision, scale)

In Oracle, DECIMAL is a synonym for NUMBER, and you should generally use NUMBER instead.

DECIMAL(precision) is a decimal integer of up to precision digits. DECIMAL(precision, scale) is a fixed-point decimal number of precision digits with scale digits to the right of the decimal point. For example, DECIMAL(9,2) can store values up to 9,999,999.99.

In Oracle, declaring a column as DECIMAL without specifying precision or scale results in a decimal floating-point column. In DB2, the same declaration is interpreted as DECIMAL(5,0). In SQL Server, the effect is the same as DECIMAL(18,0).

Maximum precision/scale values are: 38/127 (Oracle), 31/31 (DB2), 38/38 (SQL Server), 65/30 (MySQL), and 1,000/1,000 (PostgreSQL).

Except for Oracle, the platforms support the following binary integer types:

SMALLINT
INTEGER
BIGINT

These types correspond to 2-byte, 4-byte, and 8-byte integers, respectively. Ranges are −32,768 to 32,767; −2,147,483,648 to 2,147,483,647; and −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, respectively.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Dataype Conversion

Content preview·Buy reprint rights for this chapter

Some platforms allow for the implicit conversion from one datatype to another. In Oracle, for example:

SELECT * FROM upfall WHERE id = '1';

However, it's often better to use explicit type conversion so that you know for sure which value is getting converted and how.

Use the CAST function to convert explicitly a value to a new type:

SELECT * FROM upfall u
WHERE u.id = CAST('1' AS INTEGER);

When converting from text to numeric or date types, CAST offers little flexibility in dealing with different input data formats. For example, if the value you are casting is a string, the contents must conform to your database's default text representation of the target datatype.

Oracle, MySQL, and PostgreSQL support the standard EXTRACT function to retrieve specific elements from a datetime value. In MySQL, for example:

SELECT EXTRACT(DAY FROM CURRENT_DATE);

The result will be a number. Valid elements are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.

Oracle supports the following additional elements: TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ REGION, and TIMEZONE_ABBR. The latter two Oracle elements are exceptions to the rule and return string values.

PostgreSQL also supports additional elements: CENTURY, DECADE, DOW (day of week), DOY (day of year), EPOCH (number of seconds in an interval, or since 1-Jan-1970 for a date), MICROSECONDS, MILLENNIUM, MILLISECONDS, QUARTER, TIMEZONE (offset from UTC, in seconds), TIMEZONE_HOUR (hour part of UTC offset), TIMEZONE_MINUTE (minute part of offset), and WEEK.

You can convert to and from datetime types in Oracle by using the following functions:

TO_CHAR({datetime|interval}, format)
TO_DATE(string, format)
TO_TIMESTAMP(string, format)
TO_TIMESTAMP_TZ(string, format)
TO_DSINTERVAL('D HH:MI:SS')
TO_YMINTERVAL('Y-M')
NUMTODSINTERVAL(number, 'unit_ds')
NUMTOYMINTERVAL(number, 'unit_ym')

unit_ds ::= {DAY|HOUR|MINUTE|SECOND}
unit_ym ::= {YEAR|MONTH}

Some functions take a format mask argument, which gives you great control over the text representation. For example, to convert a date to its text representation, specify:

SELECT name,
   TO_CHAR(confirmed_date, 'dd-Mon-yyyy') cdate
FROM upfall;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Deleting Data

Content preview·Buy reprint rights for this chapter

Use the DELETE statement to delete rows from a table:

DELETE
FROM data_source
WHERE predicates

Most often, you'll write a WHERE clause to identify one or more specific rows to delete. For example, you may want to delete states for which you don't know the population:

DELETE FROM state s
WHERE s.population IS NULL;

Note that SQL Server, MySQL, and PostgreSQL do not allow the alias on the target table. See the later section "" for more details on the different kinds of predicates that you can write.

When you delete multiple rows from a table on which a self-referential foreign key constraint is defined, you should include an ORDER BY clause in your DELETE statement to ensure that child rows are deleted before their parents. Because MySQL checks for constraint violations during statement execution, this is a MySQL-only issue.

The ISO SQL standard allows constraint checking to be done either at the end of each statement's execution or at the end of a transaction, but never during statement execution.

In the later section "," you will find an INSERT INTO . . . SELECT FROM statement that creates a new tour in the trip table called J's Tour. If you wish to delete J's Tour, you must issue a statement such as:

DELETE FROM trip WHERE name = 'J''s Tour'
ORDER BY CASE stop
       WHEN 1 THEN 1
       WHEN 2 THEN 2
       WHEN 6 THEN 3
       WHEN 4 THEN 4
       WHEN 3 THEN 5
       WHEN 5 THEN 6
       END DESC;

The CASE expression in this statement's ORDER BY clause hardcodes a child-first delete order. Obviously, this completely defeats the purpose of a multi-row DELETE statement. If you're lucky, you'll have a sortable column that will yield a child-first delete order without its having to be hardcoded. In the case of this book's example schema and data, I wasn't so lucky.

You can use the DELETE statement without a WHERE clause to remove all rows from a table:

DELETE FROM township;

However, such a DELETE can be time-consuming and input/output-intensive because database systems must generally log each row deletion. Many database systems also implement a TRUNCATE TABLE statement that empties a table more quickly and without logging:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Functions

Content preview·Buy reprint rights for this chapter

The following sections describe some of the more useful scalar functions, which return one value per row when invoked from a SQL statement. Conversion functions are described earlier in the "" section. Aggregate functions, which combine values from many rows into one result, are described later in "." Window functions, which return aggregate values in detail rows, are described later in the "" section.

MySQL requires the leading parenthesis of the argument list to immediately follow a function's name. For example, UPPER (name) will generate an error message because of the space between UPPER and (name).

Oracle implements a wide variety of helpful functions for working with dates and times.

Section 1.7.1.1: Getting the current date and time in Oracle

It's common to need the current date and/or time of day. SYSDATE is often used for that purpose:

SELECT SYSDATE FROM dual;

2006-02-07 09:32:32.0

You can use ALTER SESSION to specify a default date format for your session using the date format elements described earlier in .

ALTER SESSION
   SET NLS_DATE_FORMAT = 'dd-Mon-yyyy hh: mi:ss';

Following is a complete list of Oracle functions to return current datetime information:

CURRENT_DATE

Returns the current date in the session time zone as a value of type DATE.

CURRENT_TIMESTAMP[( precision )]

Returns the current date and time in the session time zone as a value of type TIMESTAMP WITH TIME ZONE. The precision is the number of decimal digits used to express fractional seconds, and it defaults to 6.

LOCALTIMESTAMP[( precision )]

Is the same as CURRENT_TIMESTAMP, but it returns a TIMESTAMP value with no time zone offset.

SYSDATE

Returns the server date and time as a DATE.

SYSTIMESTAMP[( precision )]

Returns the current server date and time as a TIMESTAMP WITH TIME ZONE value.

DBTIMEZONE

Returns the database server time zone as an offset from UTC in the form

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Grouping and Summarizing

Content preview·Buy reprint rights for this chapter

SQL enables you to collect rows into groups and to summarize those groups in various ways, ultimately, returning just one row per group. You do this using the GROUP BY and HAVING clauses, as well as various aggregate functions.

An aggregate function takes a group of values, one from each row in a group of rows, and returns one value as output. One of the most common aggregate functions is COUNT, which counts non-null values in a column. For example, to count the number of waterfalls associated with a county, specify:

SELECT COUNT(u.county_id) AS county_count
FROM upfall u;

14

Add DISTINCT to the preceding query to count the number of counties containing waterfalls:

SELECT COUNT(DISTINCT u.county_id) AS county_count
FROM upfall u;

6

The ALL behavior is the default, counting all values: COUNT(expression) is equivalent to COUNT(ALL expression).

COUNT is a special case of aggregate functions because you can pass the asterisk (*) to count rows rather than column values:

SELECT COUNT(*) FROM upfall;

Nullity is irrelevant when COUNT(*) is used because the concept of null applies only to columns, not to entire rows as a whole. All other aggregate functions ignore nulls.

lists some commonly available aggregate functions. However, most database vendors implement aggregate functions beyond those shown. Check your documentation if you need an aggregation beyond those listed in .

Table 11: Common aggregate functions

Function	Description
AVG(x)	Returns the average (mean) of a group of numbers.
COUNT(x)	Counts the number of non-null values in a group of values.
MAX(x)	Returns the greatest value in a group.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Hierarchical Queries

Content preview·Buy reprint rights for this chapter

Direct support for querying hierarchical data falls into two camps: SQL Server and DB2. Both support the use of the ISO SQL standard's recursive WITH clause. Oracle implements non-standard CONNECT BY syntax.

MySQL does not implement specific syntax in support of recursive, hierarchical queries. In PostgreSQL, support for WITH is planned for the 8.2 release.

SQL Server and DB2 support the recursive use of WITH as defined in the ISO SQL standard for querying hierarchical and recursive data. For example:

WITH recursiveGov
   (level, id, parent_id, name,
    type) AS
   (SELECT 1, parent.id, parent.parent_id,
           parent.name, parent.type
    FROM gov_unit parent
    WHERE parent.parent_id IS NULL
    UNION ALL
    SELECT parent.level+1, child.id,
           child.parent_id, child.name,
           child.type
    FROM recursiveGOV parent, gov_unit child
    WHERE child.parent_id = parent.id)
SELECT level, id, parent_id, name, type
FROM recursiveGOV;

Most of this statement consists of a subquery named recursiveGOV that is specified using the WITH clause. The subquery consists of two SELECTs unioned together. Consider the first SELECT as the union query's starting point. It includes a predicate to treat rows having null parent_ids as the tree roots. Consider the second SELECT as defining the recursive link between parent and child rows.

The second SELECT brings in the children of the first. Because the second SELECT references the named subquery that it is part of (itself), it recursively brings back children of the rows it returned (and so forth until the end). The main SELECT kicks off all this recursion by simply selecting from the named subquery.

For a more in-depth explanation of what happens when a recursive WITH executes, read the article "Understanding the WITH Clause" at http://gennick.com/with.

The output from the preceding query will look like this:

LEVEL ID PARENT_ID NAME     TYPE
----- -- --------- -------- --------
1     3  -         Michigan state
2     2  3         Alger    county
2     6  3         Baraga   county
 . . .

DB2 returns recursive results in the following breadth-first order, which differs from the order you'll get using Oracle's CONNECT BY syntax (described in the next section):

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Indexes, Creating

Content preview·Buy reprint rights for this chapter

The basic CREATE INDEX statement syntax is:

CREATE INDEX falls_name ON upfall
   (name, open_to_public);

In this syntax, falls_name is the name of the index. The table to be indexed is upfall. The index is on the combined values of name and open_to_public.

Oracle and PostgreSQL allow you to assign an index to a tablespace:

CREATE INDEX falls_name ON upfall
   (name, open_to_public)
   TABLESPACE users;

Oracle and PostgreSQL also allow you to index column expressions:

CREATE INDEX falls_name ON upfall
   (UPPER(name), open_to_public);

This particular index is useful for resolving queries in which the WHERE clause predicates involve the expression UPPER(name).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Indexes, Removing

Content preview·Buy reprint rights for this chapter

In Oracle, DB2, and PostgreSQL, you remove an index by naming it in a DROP INDEX statement:

DROP INDEX falls_name;

SQL Server and MySQL require you to also specify the table name:

DROP INDEX falls_name ON upfall;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Inserting Data

Content preview·Buy reprint rights for this chapter

Use the INSERT statement to insert new rows into a table. You can insert one row, many rows (DB2 and MySQL), or the results of a subquery.

The following example adds a county to the gov_unit table. The values in the VALUES clause correspond to the columns listed after the table name:

INSERT INTO gov_unit
   (id, parent_id, name, type)
VALUES (13, 3, 'Chippewa', 'County');

Any columns you omit from an INSERT statement take on their default values specified at table-creation time, with the default value defaulting to null.

Use the DEFAULT keyword to specify explicitly that a column should take on its default value. Use the null keyword to insert a null value explicitly into a column that might otherwise default to a non-null value. For example:

INSERT INTO gov_unit
   (id, parent_id, name, type)
VALUES (14, DEFAULT, 'Mackinac', NULL);

If your VALUES list contains a value for each of the table's columns in the order specified at table creation, you can omit the column list:

INSERT INTO gov_unit
VALUES (15, DEFAULT, 'Luce', 'County');

For anything other than an ad-hoc insert (in other words, for inserts you embed in your scripts and programs), it's safer to specify a list of columns. Otherwise, such queries will fail the moment a new column is added to the target table.

DB2 and MySQL provide the ability to insert multiple rows via repeated value lists in the VALUES clause:

INSERT INTO gov_unit
   (id, parent_id, name, type)
VALUES (16, 3, 'Menominee', 'County'),
       (17, 3, 'Iron', 'County'),
       (18, 3, 'Keweenaw', 'County');

All platforms allow inserts into a view. Oracle and DB2 also allow inserts into subqueries (or inline views):

INSERT INTO (SELECT id, name, type FROM gov_unit)
   (id, name, type)
VALUES (16, 'Keweenaw', 'County');

PostgreSQL requires views that are the targets of inserts to have an associated ON INSERT DO INSTEAD rule.

Using a subquery to feed an INSERT statement, it's possible to insert a number of rows at one time. For example, to create a duplicate of the Munising tour, but with a different name, specify:

INSERT INTO trip (name, stop, parent_stop)
   (SELECT 'J''s Tour', stop, parent_stop
    FROM trip
    WHERE name = 'Munising');

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Joining Tables

Content preview·Buy reprint rights for this chapter

Joins allow you to combine data from multiple tables into a single result-set row. There are two fundamental types of join: inner and outer. There are also two join syntaxes—the syntax introduced in the 1992 SQL standard, which depends on a JOIN clause, and an older syntax in which you separate table names with commas.

The concept of a join is best explained by beginning with the earlier syntax. To join related rows from two tables, begin by listing two table expressions separated by a comma in your FROM clause. For example, to retrieve a list of waterfalls and their county names, you could begin by writing:

SELECT u.name AS fall, c.name AS county
FROM upfall u, county c;

FALL            COUNTY
--------------- ----------
Munising Falls  Alger
Munising Falls  Baraga
Munising Falls  Ontonogan
 . . .

This result is a Cartesian product, which is all possible combinations of rows from the two tables. Conceptually, all joins begin as Cartesian products. From there, it's up to you to supply conditions to narrow down the results to only those rows that make sense. Using the older join syntax, you supply those conditions in the WHERE clause:

SELECT u.name AS fall, c.name AS county
FROM upfall u, county c
WHERE u.county_id = c.id;

FALL            COUNTY
--------------- ----------
Munising Falls  Alger
Tannery Falls   Alger
Alger Falls     Alger
 . . .

These results are much more useful.

The process I've just described is purely conceptual; database systems will rarely or never form a Cartesian product when executing a join. However, thinking in these conceptual terms will help you write correct join queries and understand their results. Regardless of how the join operation is optimized, join results must match the conceptual results in the end.

The SQL standard uses the term cross join to describe a Cartesian product. Generate a cross join as follows:

SELECT *
FROM upfall CROSS JOIN county;

DB2 does not support the CROSS JOIN keywords, but you can achieve the same effect by using a comma-delimited list of tables, as shown in the previous section.

One case in which cross joins

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Literals

Content preview·Buy reprint rights for this chapter

All database systems make provisions for embedding literal values in SQL statements. Text and numeric literals are usually quite simple, but there are some nuances of which you should be aware. Date and time literals tend to be more complex.

The ISO SQL standard for text literals is to enclose them within single quotes:

'This is a text literal'

Use two adjacent single quotes when you need to embed a single quote within a string:

'Isn''t SQL fun?'

SQL will treat the two adjacent single quotes as a single quote within the literal:

Isn't SQL fun?

Oracle Database 10g allows you to specify alternative quoting delimiters, which are always two characters and always include leading and trailing single quotes. For example, to use '[ and ]' as delimiters, specify:

Q'[This isn't as bad as it looks]'
q'[This isn't as bad as it looks]'

The (, [, and { characters are special cases in that their corresponding closing delimiters must be ), ], and }, respectively. Otherwise, use the same character to close the string that you use to open it:

Q'|This string is delimited by vertical bars|'

You can't use space, tab, or return characters to delimit a string in this manner.

PostgreSQL allows you to specify alternative quoting delimiters using a dollar-sign syntax, producing a dollar-quoted string constant. For example:

$tag$This is a dollar-quoted string constant$tag$

Replace tag with any desired sequence of characters. Your quoting delimiter is then $tag$. If you like, you can even use $$ without any intervening tag text. Escape sequences (see ) do not have any effect in dollar-quoted string constants; they are treated as literal character sequences. $$\t$$ yields the string \t, not a tab character.

MySQL allows you to include, in string literals, the escape sequences shown in . PostgreSQL allows the escape sequences shown in .

Table 12: MySQL string literal escape sequences

Escape	Description
\0

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Merging Data (Oracle, DB2)

Content preview·Buy reprint rights for this chapter

Oracle and DB2 support the use of the MERGE statement for updating or inserting rows, depending on whether they already exist in the target table. The basic syntax is:

MERGE INTO table alias
USING datasource ON (exists_test)
WHEN MATCHED THEN UPDATE
   SET column = value, column = value,  . . .
WHEN NOT MATCHED THEN INSERT
   (column, column,  . . . )
   VALUES (value, value,  . . . )

datasource ::= {table|view|(subquery)}

For example, to merge potentially new waterfall data into the upfall table, specify:

MERGE INTO upfall u
USING (SELECT * FROM new_falls) nf
   ON (u.id = nf.id)
WHEN MATCHED THEN UPDATE
   SET u.name = nf.name,
       u.open_to_public = nf.open_to_public
WHEN NOT MATCHED THEN INSERT
   (id, name, datum, zone, northing, easting,
    lat_lon, county_id, open_to_public,
    owner_id, description, confirmed_date)
   VALUES (nf.id, nf.name, nf.datum, nf.zone,
      nf.northing, nf.easting, nf.lat_lon,
      nf.county_id, nf.open_to_public,
      nf.owner_id, nf.description,
      nf.confirmed_date);

This statement updates only name and open_to_public for existing waterfalls, although you could choose to update all columns if you wanted to do so. For new falls, all columns are inserted into the upfall table.

You can place WHERE conditions on both the UPDATE and INSERT operations. In addition, Oracle allows you to specify rows to be deleted following an UPDATE operation:

MERGE INTO upfall u
USING (SELECT * FROM new_falls) nf
   ON (u.id = nf.id)
WHEN MATCHED THEN UPDATE
   SET u.name = nf.name,
       u.open_to_public = nf.open_to_public
   WHERE nf.name IS NOT NULL
   DELETE WHERE u.open_to_public = 'n'
WHEN NOT MATCHED THEN INSERT
   (id, name, datum, zone, northing, easting,
    lat_lon, county_id, open_to_public,
    owner_id, description, confirmed_date)
   VALUES (nf.id, nf.name, nf.datum, nf.zone,
      nf.northing, nf.easting, nf.lat_lon,
      nf.county_id, nf.open_to_public,

      nf.owner_id, nf.description,
      nf.confirmed_date)
   WHERE nf.open_to_public = 'y';

This statement uses WHERE nf.name IS NOT NULL to prevent updating any name to a null. The subsequent DELETE WHERE clause then deletes any updated rows that no longer represent publicly accessible falls.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Nulls

Content preview·Buy reprint rights for this chapter

When writing SQL, it's critical to understand nulls and three-valued logic. With few exceptions, the result of any expression involving a null will be either null or unknown, and this has ramifications for any expression (comparison or otherwise) that you write.

You should not compare a null to any other value using the standard comparison operators. For example, the following query will not return all rows from the upfall table:

SELECT u.id, u.name, u.datum
FROM upfall u
WHERE u.datum = 'NAD1927'
   OR u.datum <> 'NAD1927';

You'd think that any given datum would either be NAD1927 or not be NAD1927, but this is not the case. A null datum is not NAD1927, nor is it not not NAD1927.

SQL provides the IS NULL and IS NOT NULL predicates to detect the presence or absence of null values. To find all datum values other than NAD1927, including those that are null, specify:

SELECT u.id, u.name, u.datum
FROM upfall u
WHERE u.datum IS NULL
   OR u.datum <> 'NAD1927';

Similarly, you can use IS NOT NULL to match non-null values explicitly.

CASE expressions can sometimes be helpful when working with potentially null data. For example, you can use CASE to ensure that you always get a non-null datum in your result set:

SELECT u.id, u.name,
  CASE WHEN u.datum IS NULL THEN
     '*None!*'
  ELSE u.datum END
FROM upfall u;

Most databases also provide functions to do this type of thing more succinctly.

All platforms support the standard SQL COALESCE function. It takes a series of values and returns the first non-null value encountered. For example, to return a list of waterfall descriptions that show name when a description is null and show Unknown! when even the name is null, specify:

SELECT id, COALESCE(description, name, '*Unknown!*')
FROM upfall;

You can provide any number of arguments, but you should ensure that at least one will be non-null. If all arguments are null, COALESCE returns null as well.

Oracle supports CASE and COALESCE. It also provides several other functions for dealing with nulls.

NVL is similar to COALESCE. It is supported for backward compatibility and allows only two arguments:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

OLAP Functions

Content preview·Buy reprint rights for this chapter

Online analytical processing (OLAP) function is the term DB2 uses for what the SQL standard refers to as a window function. See "," later in this book, for more on this extremely useful class of functions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Predicates

Content preview·Buy reprint rights for this chapter

Predicates are conditions you write in the WHERE, ON, and HAVING clauses of a SQL statement that determine which rows are affected, or returned, by that statement. For example, use the predicate name = 'Wagner Falls' to return data for only that particular waterfall:

SELECT u.zone, u.northing, u.easting
FROM upfall u
WHERE name = 'Wagner Falls';

lists the available comparison operators. Some operators, such as IN and EXISTS, are more fully described in upcoming subsections. Regular-expression operators are described later in the "" section. Operators for dealing with nulls are described in "."

Table 14: Comparison operators

Operator	Description
!=, <>	Tests for inequality
<	Tests for less than
<=	Tests for less than or equal to
<=>	Null-safe test for equality; supported only by MySQL
=	Tests for equality
>	Tests for greater than
>=	Tests for greater than or equal to
BETWEEN	Tests whether a value lies within a given range
EXISTS	Tests whether rows exist matching conditions that you specify
IN

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Recursive Queries

Content preview·Buy reprint rights for this chapter

See "," earlier in this book.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Regular Expressions

Content preview·Buy reprint rights for this chapter

Oracle, SQL Server, MySQL, and PostgreSQL support regular expressions (regexes). SQL Server and MySQL support them only for string comparison, whereas PostgreSQL adds support for a regular expression substring function, and Oracle provides support for that and much more. DB2 Version 8.2 does not support regular expressions at all.

Oracle Database 10g implements the following regular-expression functions:

REGEXP_INSTR(source_string, pattern
            [, position [, occurrence
            [, return_option
            [, match_parameter]]]])

REGEXP_LIKE (source_string, pattern
            [, match_parameter])

REGEXP_REPLACE(source_string, pattern
              [, replace_string
              [, position [, occurrence
              [, match_parameter]]]])

REGEXP_SUBSTR(source_string, pattern
             [, position [, occurrence
             [, match_parameter]]])

Parameters are as follows:

source_string

The string you wish to search.

pattern

A regular expression describing the text pattern you are searching for. This expression may not exceed 512 bytes in length.

replace_string

The replacement text. Each occurrence of pattern in source_string is replaced by replace_string, which can use backreferences to refer to values that match subexpressions in the pattern.

position

The character position at which to begin the search. This defaults to 1 and must be positive.

occurrence

The occurrence of pattern you are interested in finding. This defaults to 1. Specify 2 if you want to find the second occurrence of the pattern, 3 for the third occurrence, and so forth.

return_option

Specify 0 (the default) to return the pattern's beginning character position. Specify 1 to return the ending character position.

match_parameter

A set of options, in the form of a character string, that changes the default manner in which regular-expression pattern matching is performed. You may specify any, all, or none of the following options, in any order:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Selecting Data

Content preview·Buy reprint rights for this chapter

Use a SELECT statement, or query, to retrieve data from a database—typically from a table or view or from a combination of tables and views:

SELECT expression_list
FROM data_source
WHERE predicates
GROUP BY expression_list
HAVING predicates
ORDER BY expression_list

DB2, Oracle, and SQL Server support factoring out subqueries using a WITH clause. See the earlier section "" and the upcoming section "" for some examples of this technique.

Each expression in the SELECT clause becomes a column in the result set returned by the query. Expressions may be simple column names, may generate a new value using a column value as input, or may have nothing to do with any columns at all.

Section 1.21.1.1: Listing the columns to retrieve

The SELECT clause specifies the individual data elements you want the statement to return. The simple case is to specify a comma-delimited list of one or more column names from the tables listed in the FROM clause:

SELECT id, name
FROM owner;

The result set for this query will contain the following columns:

ID           NAME
------------ ---------------
1            Pictured Rocks
2            Michigan Nature
3            AF LLC
4            MI DNR
5            Horseshoe Falls

Section 1.21.1.2: Taking shortcuts with the asterisk

To return all columns from a table, you can specify a single asterisk rather than write out each column name:

SELECT *
FROM owner;

ID           NAME            PHONE        TYPE
------------ --------------- ------------ -------
1            Pictured Rocks  906.387.2607 public
2            Michigan Nature 517.655.5655 private
3            AF LLC                       private
4            MI DNR          906-228-6561 public
5            Horseshoe Falls 906.387.2635 private

The asterisk is a helpful shortcut when executing queries interactively because it can save you a fair bit of typing. However, it's a risky proposition to use the asterisk in program code because the columns in a table may change over time, causing your program to fail when more or fewer columns than expected are returned.

Section 1.21.1.3: Writing expressions

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Subqueries

Content preview·Buy reprint rights for this chapter

Subject to various platform restrictions, subqueries may be used in most SQL statements as follows:

In the SELECT list of a SELECT statement

See the earlier subsection "."

In the FROM clause of a SELECT statement

See the earlier subsection "."

In the WHERE clause of a SELECT statement

See the earlier section "" and the earlier subsection "."

In the ORDER BY clause of a SELECT statement

See the earlier subsection "."

In an INSERT . . . SELECT . . . FROM statement

See the earlier subsection "."

In the SET clause of an UPDATE statement

See the later subsection "."

A subquery in the FROM clause of a SELECT statement functions like a view and replaces a table as a data source. You can use subqueries—just as you can use views—as targets of INSERT, DELETE, and UPDATE statements. For example, for all platforms except MySQL, you can specify:

DELETE
FROM (SELECT * FROM upfall u
      WHERE u.open_to_public = 'n') u2
WHERE u2.owner_id IS NOT NULL;

This statement deletes waterfalls that are not open to the public and for which an owner is known.

The SQL standard defines a WITH clause that you can use to factor out a subquery so that you don't need to repeat it in your SELECT statement. Oracle, DB2, and SQL Server (beginning in SQL Server 2005) support WITH. Currently, MySQL and PostgreSQL do not, but support for WITH is currently planned for the PostgreSQL 8.2 release.

The following SELECT repeats two subqueries twice to generate a list of counties containing more than the average number of waterfalls per county:

SELECT c.name,
       (SELECT COUNT(*) FROM upfall u2
        WHERE u2.county_id = c.id) fall_count,
       (SELECT AVG(fall_count)
         FROM (SELECT COUNT(*) fall_count
               FROM upfall u3
               GROUP BY u3.county_id) x1) avg_count
FROM county c
WHERE (SELECT COUNT(*) FROM upfall u2
        WHERE u2.county_id = c.id)
      >
      (SELECT AVG(fall_count)
         FROM (SELECT COUNT(*) fall_count
               FROM upfall u3
               GROUP BY u3.county_id) x2);

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tables, Creating

Content preview·Buy reprint rights for this chapter

You create a new table in a database by issuing a CREATE TABLE statement. The syntax varies widely among vendors, but the following subsections show reasonable examples for each platform. Bear in mind the following points:

• At a minimum, all you need is a list of column names and their datatypes:

  CREATE TABLE simple_example (
     id NUMERIC,
     name VARCHAR(15),
     last_changed DATE
  );
  

• The examples give explicit names for many of the constraints, which I consider a best practice, but the CONSTRAINT constraint_name syntax is optional and is often omitted (especially on column constraints such as the NOT NULL constraint).
• You can usually declare constraints that involve a single column as part of that column's definition. Multi-column constraints must be declared as table-level elements. The examples demonstrate both approaches.

See the "" section, earlier in this book, for a list of valid datatypes by platform.

The following is a typical CREATE TABLE statement for Oracle:

CREATE TABLE oracle_example (
   id NUMBER(6),
   name VARCHAR2(15) NOT NULL,
   country VARCHAR2(2) DEFAULT 'CA'
      CONSTRAINT country_not_null NOT NULL
      CONSTRAINT country_check
      CHECK (country IN ('CA','US')),
   indexed_name VARCHAR2(15),
   CONSTRAINT oracle_example_pk
      PRIMARY KEY (id),
   CONSTRAINT oracle_example_fk01
      FOREIGN KEY (name, country)
      REFERENCES parent_example (name, country),
   CONSTRAINT oracle_example_u01
      UNIQUE (name, country),
   CONSTRAINT oracle_example_index_upper
      CHECK (indexed_name = UPPER(name))
) TABLESPACE users;

This statement assigns the table to the users tablespace. The TABLESPACE clause is optional. If you aren't certain which tablespace to specify, you can either omit the clause to accept your default tablespace assignment or ask your database administrator's advice.

If you want the ID column to be an automatically generated sequential ID number, you can begin by creating an Oracle sequence:

CREATE SEQUENCE oracle_example_pk
   NOCYCLE MAXVALUE 999999 START WITH 1;

Then, create a trigger to derive a new id value from the sequence whenever a new row is inserted:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tables, Modifying

Content preview·Buy reprint rights for this chapter

You can change the columns and other attributes of a table using the ALTER TABLE statement. The syntax varies significantly among vendors. The following subsections show the same sequence of common table alterations. Many other types of changes are possible; consult your vendor documentation for details.

Use ALTER TABLE . . . ADD to add columns and table constraints:

ALTER TABLE oracle_example ADD (
   lower_name VARCHAR2(15),
   CONSTRAINT lower_name
      CHECK (lower_name = LOWER(name))
);

Use MODIFY to change a column's datatype, default value, or nullability. You may also add new constraints to a column. Anything you do not specify is left unchanged:

ALTER TABLE oracle_example MODIFY (
   name VARCHAR2(30) DEFAULT 'Missing!'
      CONSTRAINT name_canbe_null NULL,
   country DEFAULT NULL,
   indexed_name varchar2(30) NOT NULL
      CONSTRAINT no_leading_space
         CHECK (indexed_name = LTRIM(indexed_name))
);

Use DROP to remove a column or constraint. Each drop must be specified separately, and no parentheses are used:

ALTER TABLE oracle_example
   DROP CONSTRAINT lower_name;

ALTER TABLE oracle_example
   DROP COLUMN lower_name;

Use ALTER TABLE's ADD clause to add a column or table constraint. You may add more than one item at a time:

ALTER TABLE db2_example
   ADD COLUMN lower_name VARCHAR(15)
   ADD CONSTRAINT lower_name
          CHECK(lower_name = LOWER(name));

Use the ALTER clause to change a column's datatype or default value. You can change only one item at a time, so you may need to use multiple ALTER clauses per column:

ALTER TABLE db2_example
   ALTER COLUMN name
      SET DATA TYPE VARCHAR(30)
   ALTER COLUMN name SET DEFAULT 'Missing!'
   ALTER COLUMN country SET DEFAULT NULL
   ALTER COLUMN indexed_name
      SET DATA TYPE VARCHAR(30);

You can add table constraints but not column constraints, so the no_leading_space constraint added at the column level for other brands must be added at the table level for DB2:

ALTER TABLE db2_example
   ADD CONSTRAINT no_leading_space
      CHECK (indexed_name = LTRIM(indexed_name));

To change the nullability of a column, you must drop and recreate the table. (Remember that columns participating in unique and primary key constraints cannot be nullable.) Unlike the case with the other brands, there is no easy way to make

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tables, Removing

Content preview·Buy reprint rights for this chapter

When you no longer need a table, you can drop it from your schema:

DROP TABLE table_name;

In Oracle, you can drop a table that is referenced by foreign key constraints using the following syntax:

DROP TABLE table_name CASCADE CONSTRAINTS;

In PostgreSQL, you can do the same thing using:

DROP TABLE table_name CASCADE;

Foreign key constraints that reference the table being dropped will be dropped themselves.

In DB2, referencing foreign key constraints are always dropped; no CASCADE clause is needed.

In all other cases, you must drop any referencing foreign key constraints manually before dropping the referenced table.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Transaction Management

Content preview·Buy reprint rights for this chapter

A transaction is a collection of operations treated as a unit. Either all operations in the unit are completed or none of them are. All commonly used databases make provisions for transactions.

When working in a transactional environment, you need to know how to begin and end a transaction. You also need to know how to specify various characteristics of a transaction—for example, whether it will update any data.

SQL Server, MySQL, and PostgreSQL default to an autocommit mode in which each statement you execute is treated as a transaction in and of itself.

You can disable autocommit in SQL Server with the following statement:

SET IMPLICIT_TRANSACTIONS ON

You can enable autocommit again using:

SET IMPLICIT_TRANSACTIONS OFF

You leave SQL Server's and PostgreSQL's autocommit mode whenever you issue an explicit BEGIN TRANSACTION (SQL Server) or BEGIN (PostgreSQL) statement. See the next section, "," for details.

In MySQL, you can disable autocommit with:

SET AUTOCOMMIT=0

And you can enable it again with:

SET AUTOCOMMIT=1

You automatically leave autocommit mode whenever you issue a BEGIN or BEGIN WORK statement.

Databases differ in the syntax they support to begin a transaction. The following subsections show you how to begin transactions in Oracle, SQL Server, MySQL, and PostgreSQL. DB2 does not implement a SQL statement to explicitly begin a transaction.

Section 1.26.2.1: Starting a transaction (Oracle)

Within Oracle, for all practical purposes, you're always in a transaction. The first SQL statement you execute after you connect begins an implicit transaction, as does the first SQL statement you execute following the end of a transaction. Oracle's default transaction type: read/write with statement-level read consistency.

You can begin a transaction using SET TRANSACTION explicitly:

SET TRANSACTION options [NAME 'tran_name']

options ::=
   {READ {ONLY|WRITE}
   |ISOLATION LEVEL {SERIALIZABLE|READ COMMITTED}
   |USE ROLLBACK SEGMENT segment_name

The options and parameters are as follows:

NAME 'tran_name '

Specifies a name for the transaction of up to 255 bytes. Upon COMMIT, the name will be saved as the transaction comment, overriding any COMMIT comment. It's especially helpful to name distributed transactions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Union Queries

Content preview·