SQL Database Programming

by Chris Fehily

Home • Download SQL code • Sample database • Buy the paperback

Contents

Introduction

SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that’s stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;). You might already know how to extract this type of information by using a graphical query or reporting tool, but perhaps you’ve noticed that this tool becomes limiting or cumbersome as your questions grow in complexity—that’s where SQL comes in.

You also can use SQL to add, change, and delete data and database objects. All modern relational database management systems (DBMSs) support SQL, although support varies by product (more about that later in this introduction).

About SQL

SQL is:

• A programming language
• Easy to learn
• Declarative
• Interactive or embedded
• Standardized
• Used to change data and database objects
• Not an acronym

A programming language.SQL is a formal language in which you write programs to create, modify, and query databases. Your database system executes your SQL program, performs the tasks you’ve specified, and then displays the results (or an error message). Programming languages differ from natural (spoken) languages in that programming languages are designed for a specific purpose, have a small vocabulary, and are inflexible and unambiguous. So if you don’t get the results you expect, then it’s because your program contains an error—or bug—and not because the computer misinterpreted your instructions. (Debugging one’s programs is a cardinal programming task.)

SQL, like any formal language, is defined by rules of syntax, which determine the words and symbols you can use and how they can be combined, and semantics, which determine the actual meaning of a syntactically correct statement. Note that you can write a legal SQL statement that expresses the wrong meaning (good syntax, bad semantics). Chapter 3 introduces SQL syntax and semantics.

Easy to learn.Easy compared with other programming languages, that is. If you’ve never written a program before, then you’ll find the transition from natural to formal language to be frustrating. Still, SQL’s statements read like sentences to make things easy on humans. A novice programmer probably would understand the SQL statement SELECT au_fname, au_lname FROM authors ORDER BY au_lname; to mean “List the authors’ first and last names, sorted by last name,” whereas the same person would find the equivalent C or Perl program impenetrable.

Declarative.If you’ve never programmed, then you can skip this point without loss of continuity. If you’ve programmed in a language such as C or JavaScript, then you’ve used a procedural language, in which you specify the explicit steps to follow to produce a result. SQL is a declarative language, in which you describe what you want and not how to do it; your database system’s optimizer will determine the “how.” As such, standard SQL lacks traditional control-flow constructs such as if-then-else, while, for, and goto statements.

To demonstrate this difference, I’ve written programs that perform an equivalent task in Microsoft Access Visual Basic (VB, a procedural language) and SQL. Listing i.1 shows a VB program that extracts author names from a table that contains author information. You needn’t understand the entire program, but note that it uses a Do Until loop to define explicitly how to extract data. Listing i.2 shows how to do the same task with a single SQL statement (as opposed to about 20 lines of VB code). With SQL, you specify only what needs to be accomplished; the DBMS determines and performs internally the actual step-by-step operations needed to get the result.

Moreover, Listing i.2 is a trivial SQL query. After you add common operations such as sorts, filters, and joins, you might need more than 100 lines of procedural code to accomplish what a single SQL SELECT statement can do.

Interactive or embedded.In interactive SQL, you issue SQL commands directly to your DBMS, which displays the results as soon as they’re produced. DBMS servers come with both graphical and command-line tools that accept typed SQL statements or text files that contain SQL programs (scripts).

If you’re developing database applications, then you can “embed” SQL statements in programs written in a host language, which commonly is a general-purpose language (C++, Java, or COBOL, for example) or a scripting language (Python, R, or PHP). A PHP script can use an SQL statement to query a MySQL database, for example; MySQL will pass the query result back to a PHP variable for further analysis or webpage display. Drawing from the preceding examples, I’ve embedded an SQL statement in an Access Visual Basic program in Listing i.3.

This book covers interactive SQL. In general, any SQL statement that can be used interactively also can be used in a host language, although perhaps with slight syntactic differences, depending on your DBMS, host language, and operating environment.

Standardized.SQL isn’t “owned” by any particular firm. It’s an open standard defined by an international standards working group, under the joint leadership of the International Organization for Standardization (ISO) and the International Engineering Consortium (IEC). The American National Standards Institute (ANSI) participates in the working groups and has ratified the standard (Figure i.1). The terms ISO SQL, ANSI SQL, and standard SQL are synonymous. For more information, see “SQL Standards and Conformance” in Chapter 3.

All DBMS vendors add proprietary features to standard SQL to enhance the language. These extensions usually are additional commands, keywords, functions, operators, data types, and control-flow constructs such as if-then, while, and goto statements. Microsoft, Oracle, and IBM have added so many features to standard SQL that the resulting languages—Transact-SQL, PL/SQL, and SQL PL, respectively—are actually separate languages in their own right, rather than mere supersets of standard SQL. One vendor’s extensions generally are incompatible with other vendors’ products. I don’t cover proprietary SQL extensions, but I do point out when a vendor’s SQL dialect doesn’t comply with the standard SQL examples in this book; see “Using SQL with a Specific DBMS” later in this introduction.

Used to change data and database objects.SQL statements are divided into three categories:

• Data manipulation language (DML) statements retrieve, reckon, insert, edit, and delete data stored in a database. Chapters 4 through 10 cover the DML statements SELECT, INSERT, UPDATE, and DELETE. Chapter 14 covers START (or BEGIN), COMMIT, and ROLLBACK.
• Data definition language (DDL) statements create, modify, and destroy database objects such as tables, indexes, and views. Chapters 11 through 13 cover the DDL statements CREATE, ALTER, and DROP.
• Data control language (DCL) statements authorize certain users to view, change, or delete data and database objects. The GRANT statement assigns privileges to users and roles (a role is a named set of privileges). The REVOKE statement removes privileges. GRANT and REVOKE aren’t covered in this book because they’re the responsibility of database administrators. All the DBMSs (except Microsoft Access) covered in this book support GRANT and REVOKE, with variations on the SQL standard.

Not an acronym.It’s a common misconception that SQL stands for structured query language; it stands for S–Q–L and nothing else. Why? Because ISO says so. The official name is Database Language SQL (refer to Figure i.1). Furthermore, referring to it as a structured query language is a disservice to new SQL programmers. It amuses insiders to point out that “structured query language” is the worst possible description because SQL:

• Isn’t structured (because it can’t be broken down into blocks or procedures)
• Isn’t for only queries (because it has more than just the SELECT statement)
• Isn’t a language (because it’s not Turing-complete, a central concept in the theory of computation)

About This Book

Chapters 1 through 3 contain expository material about DBMSs, the relational model, and SQL syntax. Later chapters all use a learn-by-example approach to teach you how to use the SQL programming language to maintain and query database information.

You don’t need prior programming experience but you must be familiar with your operating system’s filesystem (the hierarchy of folders and files) and know how to issue commands at a command prompt or shell.

This book isn’t an exhaustive guide to SQL; I’ve limited its scope to the most-used statements and functions (itself a vast topic). For information about other SQL statements, refer to your DBMS’s documentation.

Typographic Conventions

I use the following typographic conventions:

Bold type introduces new terms.

Italic type represents replaceable variables in regular text.

Monospace type denotes SQL code and syntax in listings and in regular text. It also shows command-prompt text.

Highlighted monospace type highlights SQL code fragments and results that are explained in the accompanying text.

Italic monospace type denotes a variable in SQL code that you must replace with a value. You’d replace column with the name of an actual column, for example.

Syntax Conventions

SQL is a free-form language without restrictions on line breaks or the number of words per line, so I use a consistent style in SQL syntax diagrams and code listings to make the code easy to read and maintain:

• Each SQL statement begins on a new line.
• The indentation level is two spaces.

• Each clause of a statement begins on a new, indented line:

  SELECT au_fname, au_lname
  FROM authors
  ORDER BY au_lname;

• SQL is case insensitive, meaning that myname, MyName, and MYNAME are considered to be identical identifiers. I use UPPERCASE for SQL keywords such as SELECT, NULL, and CHARACTER (see “SQL Syntax” in Chapter 3), and lowercase or lower_case for user-defined values, such as table, column, and alias names.

  (User-defined identifiers are case sensitive when quoted and in a few other situations for some DBMSs, so it’s safest to respect identifier case in SQL programs.)

• All quote marks in SQL code are straight quotes (such as ' and "), not curly, or smart, quotes (such as ’ and “). Curly quotes prevent code from working.

• Table i.1 shows special symbols used in syntax diagrams.

  Table i.1Syntax Symbols

  Characters	Description
  |	The vertical-bar or pipe symbol separates alternative items. You can choose exactly one of the given items. (Don’t type the vertical bar.) A|B|C is read “A or B or C”. Don’t confuse the pipe symbol with the double-pipe symbol, ||, which is SQL’s string-concatenation operator.
  []	Brackets enclose one or more optional items. (Don’t type the brackets.) [A|B|C] means “type A or B or C or type nothing”. [D] means “type D or type nothing”.
  {}	Braces enclose one or more required items. (Don’t type the braces.) {A|B|C} means “type A or B or C”.
  ...	Ellipses mean that the preceding item(s) can be repeated any number of times.

Using SQL with a Specific DBMS

The DBMS icon flags a vendor-specific departure from the SQL standard. If you see this icon, then it means that a particular vendor’s SQL dialect doesn’t comply with the standard, and you must modify the listed SQL program to run on your DBMS. For example, the standard SQL operator that joins (concatenates) two strings is || (a double pipe), but Microsoft products use + (a plus sign) and MySQL uses the CONCAT() function instead, so you’ll need to change all occurrences of a || b in the example SQL listing to a + b (if you’re using Microsoft Access or Microsoft SQL Server) or to CONCAT(a, b) (if you’re using MySQL). In most cases, the SQL examples will work as is or with minor syntactic changes. Occasionally, SQL code won’t work at all because the DBMS doesn’t support a particular feature.

This book covers the following DBMSs (see Chapter 1 for details):

• Microsoft Access
• Microsoft SQL Server
• Oracle Database
• IBM Db2 Database
• MySQL
• PostgreSQL

If you’re using a different DBMS (such as SAP, Teradata, MariaDB, FileMaker, or SAS SQL), and one of the SQL examples doesn’t work, then read the documentation to see how your DBMS’s SQL implementation departs from the SQL standard.

What You’ll Need

To replicate this book’s examples on your own computer, you’ll need:

• A text editor
• The sample database
• A database management system

A text editor.Typing short or ad-hoc interactive SQL statements at a prompt is convenient, but you’ll want to store nontrivial SQL programs in text files. A text editor is a program that you use to open, create, and edit text files, which contain only printable letters, numbers, and symbols—no formatting, styles, graphics, columns, tables, or any of the clutter usually associated with a word processor. Every operating system includes a free text editor. Windows has Notepad, Unix and Linux have vi and emacs, and macOS has TextEdit, for example. By convention, SQL files have the filename extension .sql, but you can use .txt (or any extension) if you prefer. For programming, professional editors such as Sublime Text (sublimetext.com) and Vim (vim.org) are superior to the basic editors that come with Windows and macOS.

The sample database.The examples in this book use the same database, described in “The Sample Database” in Chapter 2. To build the sample database, follow the instructions in “Creating the Sample Database” in Chapter 2. If you’re working with a production-server DBMS, then you might need permission from your database administrator to run SQL programs that create, update, and delete data and database objects.

A database management system.How do you get SQL? You don’t—you get a DBMS that understands SQL and feed it an SQL program. The DBMS runs your program and displays the results, as described in the next chapter.

1. Running SQL Programs

You need a database management system to run SQL programs. You can have your own private copy of a DBMS running on your personal (local) computer, or you can use a shared DBMS over a network. In the latter case, you use your personal computer to connect to a DBMS server running on another machine. The computer where the DBMS is running is called a host.

DBMSs and SQL Tools

This chapter describes how to run SQL programs on these DBMSs:

• Microsoft Access
• Microsoft SQL Server
• Oracle Database
• IBM Db2 Database
• MySQL
• PostgreSQL

Microsoft Access’s graphical interface lets you run only one SQL statement at a time. The other systems, all DBMS servers, let you run SQL programs in interactive mode or script mode. In interactive mode, you type individual SQL statements at a command prompt and view the results of each statement separately, so input and output are interleaved. In script mode (also called batch mode), you save your entire SQL program in a text file (called a script or a batch file), and a command-line tool takes the file, executes the program, and returns the results without your intervention. I use the sample database and the SQL program shown in Listing 1.1 in all the examples in the rest of this chapter. The examples give the minimal syntax of command-line tools; the complete syntax is given in the DBMS documentation.

Microsoft Access

Microsoft Access is a personal and commercial desktop DBMS that supports small and medium-size databases. Learn about Access at microsoft.com/microsoft-365/access and download a free trial copy. To determine which version of Access you’re using, choose File tab > Account > About Access.

In Access, you must turn on ANSI-92 SQL syntax to run many of the examples in this book.

To turn on ANSI-92 SQL syntax for a Microsoft Access database:

1. In Microsoft Access, open the database.
2. Choose File tab > Options > Object Designers (in the left pane).

3. Below SQL Server Compatible Syntax (ANSI 92), select “This database”. (Figure 1.1).

   Figure 1.1Select this checkbox to turn on ANSI-92 SQL syntax mode for the open database.

   

4. Click OK.

   Access closes, compacts, and then reopens the database before the new setting takes effect. You might see a few warnings, depending on your security settings.

If you’re a casual Access user, then you’ve probably used the query design grid to create a query. When you create a query in Design View, Access builds the equivalent SQL statement behind the scenes for you. You can view, edit, and run the SQL statement in SQL View.

You can run only a single SQL statement through an Access Query object. To run multiple statements, use multiple Query objects or a host language such as Visual Basic or C#.

To run an SQL statement in Microsoft Access:

1. In Microsoft Access, open a database.

2. Choose Create tab > Queries group > Query Design (Figure 1.2).

   Figure 1.2Query Design lets you skip the hand-holding wizards.

   

3. Without adding tables or queries, click Close in the Show Table dialog box (Figure 1.3).

   Figure 1.3You don’t need to add tables graphically because the SQL statement specifies the tables.

   

4. To run the SQL statement, choose Design tab > Results group > SQL View (Figure 1.4).

   Figure 1.4SQL View hides the graphical query grid and instead shows a text editor where you can type or paste an SQL statement.

   

5. Type or paste an SQL statement (Figure 1.5).

   Figure 1.5Enter an SQL statement...

   

6. Choose Design tab > Results group > Run (Figure 1.6).

   Figure 1.6...and run it.

   

   Access displays the result of a SELECT statement (Figure 1.7) but blocks or executes other types of SQL statements, with or without warning messages, depending on your settings.

   Figure 1.7Microsoft Access displays the result of a SELECT statement.

   

Microsoft SQL Server

Microsoft SQL Server is a commercial DBMS that supports very large databases and numbers of transactions. It runs on Microsoft Windows and Linux operating systems and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about SQL Server products at microsoft.com/sql-server and download a free trial copy of SQL Server or a (permanently) free copy of SQL Server Express. SQL Server Express is a free, limited version of SQL Server. To run SQL programs in SQL Server or SQL Server Express, you can use the SQL Server Management Studio graphical tool or the sqlcmd command-line tool.

To determine which version of Microsoft SQL Server you’re using, run the SQL Server command-line command

sqlcmd -S server\instance_name -E -Q "SELECT @@VERSION;"

server\instance_name is the named instance of SQL Server to which to connect. Alternatively, run the query

SELECT SERVERPROPERTY('ProductVersion');

or

SELECT @@VERSION;

To use SQL Server Management Studio:

1. On the Windows desktop, choose Start > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio.
2. In the Connect to Server dialog box, select the server and authentication mode, and then click Connect.

3. In Object Explorer (the left pane), expand the Databases folder of the server that you’re using, and then select a database (Figure 1.8).

   If Object Explorer isn’t visible, then choose View > Object Explorer (or press F8).

   Figure 1.8SQL Server Management Studio uses the selected database to resolve references in your SQL statements.

   

4. To run SQL interactively, click New Query (on the toolbar) or right-click the database (in Object Explorer) and choose New Query in the context menu. Type or paste an SQL statement in the empty tab that appears in the right pane.

   or

   To run an SQL script, choose File > Open > File (or press Ctrl+O), navigate to and select the script file, and then click Open. The file’s contents appear in a new tab in the right pane.

5. Click Execute (on the toolbar) or choose Query > Execute (or press F5).

   SQL Server displays the results in the bottom pane (Figure 1.9).

   Figure 1.9The result of a SELECT statement in SQL Server Management Studio.

   

To use the sqlcmd command-line tool interactively:

1. At an administrator command prompt, type:

   sqlcmd -S server\instance_name -d dbname

   server\instance_name is the named instance of SQL Server to which to connect, and dbname is the name of the database to use.

2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter.

3. Type go and then press Enter to display the result. (Figure 1.10).

   Figure 1.10The result of a SELECT statement in sqlcmd interactive mode. (Click image to enlarge.)

   

To use the sqlcmd command-line tool in script mode:

1. At an administrator command prompt, type:

   sqlcmd -S server\instance_name -d dbname -i sql_script

   server\instance_name is the named instance of SQL Server to which to connect, and dbname is the name of the database to use. sql_script is a text file containing SQL statement(s) and can include an absolute or relative path.

2. Press Enter to display the results (Figure 1.11).

   Figure 1.11The result of a SELECT statement in sqlcmd script mode. (Click image to enlarge.)

   

To exit the sqlcmd command-line tool:

• Type exit or quit and then press Enter.

To show sqlcmd command-line options:

• At a command prompt, type sqlcmd -? and then press Enter.

sqlcmd tries to use a trusted connection by default. If instead you have to specify a user name and password, then add the option -U login_id. login_id is your user name. sqlcmd will prompt you for your password.

If SQL Server is running on a remote network computer, then the sqlcmd option -S server\instance_name is required to specify the SQL Server instance to connect to. Ask your database administrator for the connection parameters. The -S option also works for local connections, when SQL Server is running on your own personal computer rather than on a server elsewhere.

Oracle Database

Oracle Database is a commercial DBMS that supports very large databases and numbers of transactions. It runs on many operating systems and hardware platforms and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about Oracle products at oracle.com and download Oracle Database Express Edition (XE)—a free, limited version of Oracle Database. Documentation is at docs.oracle.com.

To determine which version of Oracle you’re using, run the query

SELECT banner FROM v$version;

The Oracle version also is displayed in the initial “Connected to” message that appears when you log on to SQL*Plus.

To run SQL programs, use the sqlplus command-line tool.

To use the sqlplus command-line tool interactively:

1. At an administrator command prompt, type:

   sqlplus user/password@dbname

   user is your Oracle user name, password is your password, and dbname is the name of the database to connect to. For security, you can omit the password and instead type:

   sqlplus user@dbname

   sqlplus will prompt you for your password.

2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.12).

   Figure 1.12The result of a SELECT statement in sqlplus interactive mode. (Click image to enlarge.)

   

To use the sqlplus command-line tool in script mode:

1. At an administrator command prompt, type:

   sqlplus user/password@dbname @sql_script

   user is your Oracle user name, password is your password, dbname is the name of the database to connect to, and sql_script is a text file containing SQL statement(s) and can include an absolute or relative path. For security, you can omit the password, and instead type:

   sqlplus user@dbname @sql_script

   sqlplus will prompt you for your password.

2. Press Enter to display the results (Figure 1.13).

   Figure 1.13The result of a SELECT statement in sqlplus script mode. (Click image to enlarge.)

   

To exit the sqlplus command-line tool:

• Type exit or quit and then press Enter.

To show sqlplus command-line options:

• At a command prompt, type sqlplus -H and then press Enter.

  This command displays pages that speed by. To view one page at a time, type

  sqlplus -H | more

  and then press Enter. Tap the spacebar to advance pages (Figure 1.14).

  Figure 1.14The sqlplus help screen. (Click image to enlarge.)

  

If you’re running Oracle locally, then you can use the user name system and the password that you specified when you created the database:

sqlplus system@dbname

If you’re connecting to a remote Oracle database, then ask your database administrator for the connection parameters.

An alternative way to start sqlplus in Windows is to choose Start > Oracle > SQL Plus.

IBM Db2 Database

IBM Db2 Database is a commercial DBMS that supports very large databases and numbers of transactions. It runs on many operating systems and hardware platforms and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about Db2 products at ibm.com/products/db2 and download a free trial copy of Db2 or a (permanently) free copy of IBM Db2 Community Edition.

To determine which version of Db2 you’re using, run the Db2 command-line command

db2level

or run the query

SELECT service_level
  FROM SYSIBMADM.ENV_INST_INFO;

To run SQL programs, use the IBM Data Studio graphical tool or the db2 command-line processor (CLP).

To use Data Studio:

1. Open Data Studio.

   This procedure varies by platform. In Microsoft Windows, for example, choose Start > IBM Data Studio > Data Studio Client.

2. On the Administration Explorer tab (on the left), expand the All Databases folder of the object tree until you find your instance of Db2, and then select a database (below the Db2 instance).

   If the desired database doesn’t appear, then right-click the All Databases folder, choose “New Connection to a Database” in the context menu, and then connect to the target database.

3. To run SQL interactively, click New SQL Script on the Administration Explorer toolbar. On the Script tab that opens, type or paste an SQL statement in the box.

   or

   To run an SQL script, choose File > Open File, navigate to and select the script file, and then click Open. On the File tab that opens, click No Connection (if it appears) to connect to the target database.

4. Choose Script > Run SQL or click .

   Data Studio displays the results in the SQL Results tab (at the bottom). Click the Result tab to see the query results or click the Status tab to see query-processing information.

   The result of a SELECT statement in IBM Data Studio. (Click image to enlarge.)

   

For technical reasons involving parent and child processes, (only) Microsoft Windows users must start the db2 command-line processor with a special preliminary step.

To start the db2 command-line processor in Windows:

• At an administrator command prompt, type db2cmd and then press Enter. Alternatively, choose Start > IBM DB2 DB2COPY1 (default) > Db2 Command Line Processor.

  A new DB2 CLP command-prompt window appears.

You must use the Db2 CLP window for all db2 commands (described next). If you try to run db2 at a normal Windows command prompt, then Db2 responds with the error “Command line environment not initialized.”

If you launch a new Db2 CLP window via the db2cmd command, then you can close the original command-prompt window.

In the Db2 CLP window, change (cd) your working directory if necessary before you run db2 commands.

To use the db2 command-line processor interactively:

1. At a command prompt, type:

   db2 -t

   and then press Enter. The -t option tells db2 that a semicolon (;) terminates statements.

   The db2 => prompt appears.

2. At the db2 prompt, type:

   connect to dbname;

   and then press Enter. dbname is the name of the database to use.

3. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.15).

   Figure 1.15The result of a SELECT statement in db2 interactive mode. (Click image to enlarge.)

   

Alternatively, you can avoid the db2 => prompt by typing commands and SQL statements right on the command line. For example:

db2 connect to books
db2 SELECT * FROM authors

If you omit the -t option, as here, then don’t terminate commands and SQL statements with a semicolon.

To use the db2 command-line processor in script mode:

1. At a command prompt, type:

   db2 connect to dbname

   dbname is the name of the database to use.

2. At a command prompt, type:

   db2 -t -f sql_script

   sql_script is a text file containing SQL statement(s) and can include an absolute or relative path. The -t option tells db2 that a semicolon (;) terminates statements. Add the -v option if you want to echo the contents of sql_script in the output.

3. Press Enter to display the results (Figure 1.16).

   Figure 1.16The result of a SELECT statement in db2 script mode. (Click image to enlarge.)

   

To exit the db2 command-line tool:

• At the db2 prompt, type quit; and then press Enter. (Omit the semicolon if you didn’t use the -t option when you started db2.)

To show db2 command-line options:

• At a command prompt, type db2 ? and then press Enter.

  This command displays pages that speed by. To view one page at a time, type

  db2 ? | more

  and then press Enter. Tap the spacebar to advance pages (Figure 1.17).

  To get help while you’re at a db2 => prompt, type ?; and then press Enter. (Omit the semicolon if you didn’t use the -t option when you started db2.)

  Figure 1.17The db2 help screen. (Click image to enlarge.)

  

MySQL

MySQL (pronounced my-es-kyoo-el) is an open-source DBMS that supports large databases and numbers of transactions. MySQL is known for its speed and ease of use. It’s free for personal use and runs on many operating systems and hardware platforms. You can download it at mysql.com.

To determine which version of MySQL you’re using, run the MySQL command-line command

mysql -V

or run the query

SELECT VERSION();

To run SQL programs, use the mysql command-line tool.

To use the mysql command-line tool interactively:

1. At an administrator command prompt, type:

   mysql -h host -u user -p dbname

   host is the host name, user is your MySQL user name, and dbname is the name of the database to use. MySQL will prompt you for your password (for a passwordless user, either omit the -p option or press Enter at the password prompt).

2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.18).

   Figure 1.18The result of a SELECT statement in mysql interactive mode. (Click image to enlarge.)

   

To use the mysql command-line tool in script mode:

1. At an administrator command prompt, type:

   mysql -h host -u user -p -t dbname < sql_script

   host is the host name, user is your MySQL user name, and dbname is the name of the database to use. MySQL will prompt you for your password (for a passwordless user, either omit the -p option or press Enter at the password prompt). The -t option formats the results as a table; omit this option if you want tab-delimited output. The redirection operator < reads from the file sql_script, which is a text file containing SQL statement(s) and can include an absolute or relative path.

2. Press Enter to display the results (Figure 1.19).

   Figure 1.19The result of a SELECT statement in mysql script mode. (Click image to enlarge.)

   

To exit the mysql command-line tool:

• Type quit or \q and then press Enter.

To show mysql command-line options:

• At a command prompt, type mysql -? and then press Enter.

  This command displays pages that speed by. To view one page at a time, type

  mysql -? | more

  and then press Enter. Tap the spacebar to advance pages (Figure 1.20).

  Figure 1.20The mysql help screen. (Click image to enlarge.)

  

If MySQL is running on a remote network computer, then ask your database administrator for the connection parameters. If you’re running MySQL locally (that is, on your own computer), then set host to localhost, set user to root, and use the password that you assigned to root when you set up or installed MySQL.

PostgreSQL

PostgreSQL (pronounced post-gres-kyoo-el) is an open-source DBMS that supports large databases and numbers of transactions. PostgreSQL is known for its rich feature set and its high conformance with standard SQL. It’s free and runs on many operating systems and hardware platforms. You can download it at postgresql.org.

To determine which version of PostgreSQL you’re using, run the PostgreSQL command-line command

psql -V

or run the query

SELECT VERSION();

To run SQL programs, use the psql command-line tool.

To use the psql command-line tool interactively:

1. At an administrator command prompt, type:

   psql -h host -U user -W dbname

   host is the host name, user is your PostgreSQL user name, and dbname is the name of the database to use. PostgreSQL will prompt you for your password (for a passwordless user, either omit the -W option or press Enter at the password prompt).

2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.21).

   Figure 1.21The result of a SELECT statement in psql interactive mode. (Click image to enlarge.)

   

To use the psql command-line tool in script mode:

1. At an administrator command prompt, type:

   psql -h host -U user -W -f sql_script dbname

   host is the host name, user is your PostgreSQL user name, and dbname is the name of the database to use. PostgreSQL will prompt you for your password (for a passwordless user, either omit the -W option or press Enter at the password prompt). The -f option specifies the name of the SQL file sql_script, which is a text file containing SQL statement(s) and can include an absolute or relative path.

2. Press Enter to display the results (Figure 1.22).

   Figure 1.22The result of a SELECT statement in psql script mode. (Click image to enlarge.)

   

To exit the psql command-line tool:

• Type \q and then press Enter.

To show psql command-line options:

• At a command prompt, type psql -? and then press Enter.

  This command displays pages that speed by. To view one page at a time, type

  psql -? | more

  and then press Enter. Tap the spacebar to advance pages (Figure 1.23).

  Figure 1.23The psql help screen. (Click image to enlarge.)

  

If PostgreSQL is running on a remote network computer, then ask your database administrator for the connection parameters. If you’re running PostgreSQL locally (that is, on your own computer), then set host to localhost, set user to postgres, and use the password that you assigned to postgres when you set up or installed PostgreSQL.

You can set the environment variables PGHOST, PGDATABASE, and PGUSER to specify the default host, database, and user names used to connect to the database. See “Environment Variables” in the PostgreSQL documentation.

As an alternative to the command prompt, you can use the pgAdmin graphical tool. If the PostgreSQL installer didn’t install pgAdmin automatically, then you can download it for free at pgadmin.org.

2. The Relational Model

To program competently in SQL, you must be familiar with the standard data model for organizing and managing data, called the relational model (Figure 2.1).

The foundation of the relational model, set theory, makes you think in terms of sets of data rather than individual items or rows of data. The model describes how to perform common algebraic operations (such as unions and intersections) on database tables in much the same way that they’re performed on mathematical sets.

The Venn diagram in Figure 2.2 expresses the results of operations on sets. The rectangle (U) represents the universe, and the circles (A and B) inside represent sets of objects. The relative position and overlap of the circles indicate relationships between sets. In the relational model, the circles are tables, and the rectangle is all the information in a database.

Tables are analogues of sets: they’re collections of distinct elements having common properties. A mathematical set would contain positive integers, for example, whereas a database table would contain information about, say, students.

Tables, Columns, and Rows

If you’re familiar with databases already, then you’ve heard alternative terms for similar concepts. Table 2.1 shows how these terms are related. E. F. Codd’s relational-model terms are in the first column; SQL-standard and DBMS-documentation terms are in the second column; and the third-column terms are holdovers from traditional (nonrelational) file processing. SQL terms are used in this book (although in formal texts the SQL and Model terms never are used interchangeably).

Tables

From a user’s point of view, a database is a collection of one or more tables (and nothing but tables). A table:

• Is the database structure that holds data.
• Contains data about a specific entity type. An entity type is a class of distinguishable real-world objects, events, or concepts with common properties—patients, movies, genes, weather conditions, invoices, projects, or appointments, for example. (Patients and appointments are different entities, so you’d store information about them in different tables.)

• Is a two-dimensional grid characterized by rows and columns (Figure 2.3 and Figure 2.4).

  Figure 2.3This grid is an abstract representation of a table—the fundamental storage unit in a database.

  

  Figure 2.4This grid represents an actual (not abstract) table, shown as it usually appears in database software and books. This table has 3 columns, 4 rows, and 3 × 4 = 12 values. The top “row” is not a row but a header that displays column names.

  au_id au_fname      au_lname
----- ------------- -------------
A01   Sarah         Buchman
A02   Wendy         Heydemark
A03   Hallie        Hull
A04   Klee          Hull

• Holds a data item called a value at each row–column intersection (refer to Figure 2.3 and Figure 2.4).
• Has at least one column and zero or more rows. A table with no rows is an empty table.
• Has a unique name within a database (or, strictly speaking, within a schema).

Columns

Columns in a given table have these characteristics:

• Each column represents a specific attribute (or property) of the table’s entity type. In a table employees, a column named hire_date might show when an employee was hired, for example.
• Each column has a domain that restricts the set of values allowed in that column. A domain is a set of constraints that includes restrictions on a value’s data type, length, format, range, uniqueness, specific values, and nullability (whether the value can be null or not). You can’t insert the string value 'john' into the column hire_date, for example, if hire_date requires a valid date value. Furthermore, you can’t insert just any date if hire_date’s range is further constrained to fall between the date that the company started and today’s date. You can define a domain by using data types (Chapter 3) and constraints (Chapter 11).
• Entries in columns are single-valued (atomic); see “Normalization” later in this chapter.

• The order of columns (left to right) is unimportant (Figure 2.5).

  Figure 2.5Rows and columns are said to be unordered, meaning that their order in a table is irrelevant for informational purposes. Interchanging columns or rows does not change the meaning of the table; this table conveys the same information as the table in Figure 2.4.

  au_lname      au_id au_fname
------------- ----- -------------
Hull          A04   Klee
Buchman       A01   Sarah
Hull          A03   Hallie
Heydemark     A02   Wendy

• Each column has a name that identifies it uniquely within a table. (You can reuse the same column name in other tables.)

Rows

Rows in a given table have these characteristics:

• Each row describes a fact about an entity, which is a unique instance of an entity type—a particular student or appointment, for example.
• Each row contains a value or null for each of the table’s columns.
• The order of rows (top to bottom) is unimportant (refer to Figure 2.5).
• No two rows in a table can be identical.
• Each row in a table is identified uniquely by its primary key; see “Primary Keys” later in this chapter.

Tips for Tables, Columns, and Rows

• Use the SELECT statement to retrieve columns and rows; see Chapters 4 through 9. Use INSERT, UPDATE, and DELETE to add, edit, and delete rows; see Chapter 10. Use CREATE TABLE, ALTER TABLE, and DROP TABLE to add, edit, and delete tables and columns; see Chapter 11.

• Tables have the attractive property of closure, which ensures that any operation performed on a table yields another table (Figure 2.6).

  Figure 2.6Closure guarantees that you’ll get another table as a result no matter how you split or merge tables. This property lets you chain any number of table operations or nest them to any depth. Unary (or monadic) table operations operate on one table to produce a result table. Binary (or dyadic) table operations operate on two tables to produce a result table.

  

• A DBMS uses two types of tables: user tables and system tables. User tables store user-defined data. System tables contain metadata—data about the database—such as structural information, physical details, performance statistics, and security settings. System tables collectively are called the system catalog; the DBMS creates and manages these tables silently and continually. This scheme conforms with the relational model’s rule that all data be stored in tables (Figure 2.7).

  Figure 2.7DBMSs store system information in special tables called system tables. Here are some of the system tables that Microsoft SQL Server creates and maintains. You access system tables in the same way that you access user-defined tables, but don’t alter them unless you know what you’re doing.

  

• In practice, the number of rows in a table changes frequently, but the number of columns changes rarely. Database complexity makes adding or dropping columns difficult; column changes can affect keys, referential integrity, privileges, and so on. Inserting or deleting rows doesn’t affect these things.
• Database designers divide values into columns based on the users’ needs. Telephone numbers, for example, might reside in the single column tel_no or be split into the columns country_code, area_code, prefix, and line_number, depending on what users want to query, analyze, and report.
• The resemblance of spreadsheets to tables is superficial. Unlike a spreadsheet, a table doesn’t depend on row and column order, doesn’t perform calculations, doesn’t allow free-form data entry, strictly checks each value’s validity, and is related easily to other tables.
• The SQL standard defines a hierarchy of relational-database structures. A catalog contains one or more schemas (sets of objects and data owned by a given user). A schema contains one or more objects, such as base tables, views, functions, and procedures.

• DBMSs sometimes use other terms for the same concepts. An instance (analogous to a catalog) contains one or more databases. A database contains one or more schemas. A schema contains tables, views, privileges, stored procedures, and so on. To refer to an object unambiguously, each item at each level in the hierarchy needs a unique name (identifier). Table 2.2 shows how to address objects. See also “Identifiers” in Chapter 3.

  Table 2.2Object References

  DBMS	Address
  Standard SQL	catalog.schema.object
  Access	database.object
  SQL Server	server.database.owner.object
  Oracle	schema.object
  Db2	schema.object
  MySQL	database.object
  PostgreSQL	database.schema.object

Primary Keys

Every value in a database must be accessible. Values are stored at row–column intersections in tables, so a value’s location must refer to a specific table, column, and row. You can identify a table or column by its unique name. Rows are unnamed, however, and need a different identification mechanism called a primary key. A primary key is:

• Required.Every table has exactly one primary key. Remember that the relational model sees a table as an unordered set of rows. Because there’s no concept of a “next” or “previous” row, you can’t identify rows by position; without a primary key, some data would be inaccessible.
• Unique.Because a primary key identifies a single row in a table, no two rows in a table can have the same primary-key value.
• Simple or composite.A primary key has one or more columns in a table; a one-column key is called a simple key, and a multiple-column key is called a composite key.
• Not null.A primary-key value can’t be empty. For composite keys, no column’s value can be empty; see “Nulls” in Chapter 3.
• Stable.Once created, a primary-key value seldom if ever changes. If an entity is deleted, then its primary-key value isn’t reused for a new entity.
• Minimal.A primary key includes only the column(s) necessary for uniqueness.

A database designer designates each table’s primary key. This process is crucial because the consequence of a poor key choice is the inability to add data (rows) to a table. I’ll review the essentials here, but read a database-design book if you want to learn more about this topic.

Suppose that you need to choose a primary key for the table in Figure 2.8. The columns au_fname and au_lname separately won’t work because each one violates the uniqueness requirement. Combining au_fname and au_lname into a composite key won’t work because two authors might share a name. Names generally make poor keys because they’re unstable (people divorce, companies merge, spellings change). The correct choice is au_id, which I created to identify authors uniquely. Database designers create unique identifiers when natural or obvious ones (such as names) won’t work.

After a primary key is defined, your DBMS will enforce the integrity of table data. You can’t insert the following row because the au_id value A02 already exists in the table:

A02   Christian    Kells

Nor can you insert this row because au_id can’t be null:

NULL  Christian    Kells

This row is legal:

A05   Christian    Kells

Tips for Primary Keys

• See also “Specifying a Primary Key with PRIMARY KEY” in Chapter 11.
• In practice, the primary key often is placed in a table’s initial (leftmost) column(s). When a column name contains id, key, code, or num, it’s a clue that the column might be a primary key or part of one (or a foreign key, described in the next section).
• Database designers often forgo common unique identifiers such as Social Security numbers for U.S. citizens. Instead, they use artificial keys that encode internal information that is meaningful inside the database users’ organization. An employee ID, for example, might embed the year that the person was hired. Other reasons, such as privacy and security concerns, also spur the use of artificial keys.
• Database designers might have a choice of several unique candidate keys in a table, one of which is designated the primary key. After designation, the remaining candidate keys become alternate keys. Candidate keys often have non-nullable, unique constraints; see “Forcing Unique Values with UNIQUE” in Chapter 11.
• You could use au_id and, say, au_lname as a composite key, but that combination violates the minimality criterion. For an example of a composite primary key, see the table title_authors in “The Sample Database” later in this chapter.
• DBMSs provide data types and attributes that provide unique identification values automatically for each row (such as an integer that autoincrements when a new row is inserted). See “Unique Identifiers” in Chapter 3.

Foreign Keys

Information about different entities is stored in different tables, so you need a way to navigate between tables. The relational model provides a mechanism called a foreign key to associate tables. A foreign key has these characteristics:

• It’s a column (or group of columns) in a table whose values relate to, or reference, values in some other table.
• It ensures that rows in one table have corresponding rows in another table.
• The table that contains the foreign key is the referencing or child table. The other table is the referenced or parent table.
• A foreign key establishes a direct relationship to the parent table’s primary key (or any candidate key), so foreign-key values are restricted to existing parent-key values. This constraint is called referential integrity. A particular row in a table appointments must have an associated row in a table patients, for example, or there would be appointments for patients who don’t exist or can’t be identified. An orphan row is a row in a child table for which no associated parent-table row exists. In a properly designed database, you can’t insert new orphan rows or make orphans out of existing child-table rows by deleting associated rows in the parent table.
• The values in the foreign key have the same domain as the parent key. Recall from “Tables, Columns, and Rows” earlier in this chapter that a domain defines the set of valid values for a column.
• Unlike primary-key values, foreign-key values can be null (empty); see “Tips for Foreign Keys”.
• A foreign key can have a different column name than its parent key.
• Foreign-key values generally aren’t unique in their own table.
• The first point above is simplified: a foreign key can actually reference the primary key of its own table (rather than only some other table). A table employees with the primary key emp_id can have a foreign key boss_id, for example, that references the column emp_id. This type of table is called self-referencing.

Figure 2.9 shows a primary- and foreign-key relationship between two tables.

After a foreign key is defined, your DBMS will enforce referential integrity. You can’t insert the following row into the child table titles because the pub_id value P05 doesn’t exist in the parent table publishers:

T07    I Blame My Mother         P05

You can insert this row only if the foreign key accepts nulls:

T07    I Blame My Mother         NULL

This row is legal:

T07    I Blame My Mother         P03

Tips for Foreign Keys

• See also “Specifying a Foreign Key with FOREIGN KEY” in Chapter 11.
• SQL lets you specify the referential-integrity action that the DBMS takes when you attempt to update or delete a parent-table key value to which foreign-key values point; see “Tips for FOREIGN KEY” in Chapter 11.
• Allowing nulls in a foreign-key column complicates enforcement of referential integrity. In practice, nulls in a foreign key often remain null temporarily, pending a real-life decision or discovery; see “Nulls” in Chapter 3.

Relationships

A relationship is an association established between common columns in two tables. A relationship can be:

• One-to-one
• One-to-many
• Many-to-many

One-to-One

In a one-to-one relationship, each row in table A can have at most one matching row in the table B, and each row in table B can have at most one matching row in table A. Even though it’s practicable to store all the information from both tables in only one table, one-to-one relationships often are used to segregate confidential information for privacy and security reasons, to speed queries by splitting single monolithic tables, and to avoid inserting nulls into tables (see “Nulls” in Chapter 3).

A one-to-one relationship is established when the primary key of one table also is a foreign key referencing the primary key of another table (Figure 2.10 and Figure 2.11).

One-to-Many

In a one-to-many relationship, each row in table A can have many (zero or more) matching rows in table B, but each row in table B has only one matching row in table A. A publisher can publish many books, but each book is published by only one publisher, for example.

One-to-many relationships are established when the primary key of the “one” table appears as a foreign key in the “many” table (Figure 2.12 and Figure 2.13).

Many-to-Many

In a many-to-many relationship, each row in table A can have many (zero or more) matching rows in table B, and each row in table B can have many matching rows in table A. Each author can write many books, and each book can have many authors, for example.

A many-to-many relationships is established only by creating a third table called a junction table, whose composite primary key is a combination of both tables’ primary keys; each column in the composite key separately is a foreign key. This technique always produces a unique value for each row in the junction table and splits the many-to-many relationship into two separate one-to-many relationships (Figure 2.14 and Figure 2.15).

Tips for Relationships

• Joins (for performing operations on multiple tables) are covered in Chapter 7.
• You can establish a many-to-many relationship without creating a third table if you add repeating groups to the tables, but that method violates first normal form; see “Normalization” later in this chapter.
• A one-to-many relationship also is called a parent–child or master–detail relationship.
• A junction table also is called an associating, linking, pivot, connection, or intersection table.

Normalization

It’s possible to consolidate all information about books (or any entity type) into a single monolithic table, but that table would be loaded with duplicate data; each title (row) would contain redundant author, publisher, and royalty details. Redundancy is the enemy of database users and administrators: it causes databases to grow wildly large, it slows queries, and it’s a maintenance and reporting nightmare. (When someone moves, you want to change his address in one place, not thousands of places.)

Redundancies lead to a variety of update anomalies—that is, difficulties with operations that insert, update, and delete rows. Normalization is the process—a series of steps—of modifying tables to reduce redundancy and inconsistency. After each step, the database is in a particular normal form. The relational model defines three normal forms, named after famous ordinal numbers:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

Each normal form is stronger than its predecessors; a database in 3NF also is in 2NF and 1NF. Higher normalization levels tend to increase the number of tables relative to lower levels. Lossless decomposition ensures that table splitting doesn’t cause information loss, and dependency-preserving decomposition ensures that relationships aren’t lost. The matching primary- and foreign-key columns that appear when tables are split are not considered to be redundant data.

Normalization is not systematic; it’s an iterative process that involves repeated table splitting and rejoining and refining until the database designer is (temporarily) happy with the result.

First Normal Form

A table in first normal form:

• Has columns that contain only atomic values

  and

• Has no repeating groups

An atomic value, also called a scalar value, is a single value that can’t be subdivided (Figure 2.16). A repeating group is a set of two or more logically related columns (Figure 2.17). To fix these problems, store the data in two related tables (Figure 2.18).

A database that violates 1NF causes problems:

• Multiple values in a row–column intersection mean that the combination of table name, column name, and key value is insufficient to address every value in the database.
• It’s difficult to retrieve, insert, update, or delete a single value (among many) because you must rely on the order of the values.
• Queries are complex and slow.
• The problems that further normalization solves become unsolvable.

Second Normal Form

A table in second normal form:

• Is in first normal form

  and

• Has no partial functional dependencies

A table contains a partial functional dependency if some (but not all) of a composite key’s values determine a nonkey column’s value. A 2NF table is fully functionally dependent, meaning that a nonkey column’s value might need to be updated if any column values in the composite key change.

The composite key in the table in Figure 2.19 is title_id and au_id. The nonkey columns are au_order (the order in which authors are listed on the cover of a book with multiple authors) and au_phone (the author’s telephone number).

For each nonkey column, ask, “Can I determine a nonkey column value if I know only part of the primary-key value?” A no answer means the nonkey column is fully functionally dependent (good); a yes answer means that it’s partially functionally dependent (bad).

For the column au_order, the questions are:

• Can I determine au_order if I know only title_id? No, because there might be more than one author for the same title.
• Can I determine au_order if I know only au_id? No, because I need to know the particular title too.

Good—au_order is fully functionally dependent and can remain in the table. This dependency is denoted by

{title_id, au_id} → {au_order}

and is read “title_id and au_id determine au_order” or “au_order depends on title_id and au_id.” The determinant is the expression to the left of the arrow.

For the column au_phone, the questions are:

• Can I determine au_phone if I know only title_id? No, because there might be more than one author for the same title.
• Can I determine au_phone if I know only au_id? Yes! The author’s telephone number doesn’t depend on the title.

Bad—au_phone is partially functionally dependent and must be moved elsewhere (probably to an authors or telephone_numbers table) to satisfy 2NF rules.

Third Normal Form

A table in third normal form:

• Is in second normal form

  and

• Has no transitive dependencies

A table contains a transitive dependency if a nonkey column’s value determines another nonkey column’s value. In 3NF tables, nonkey columns are mutually independent and dependent on only primary-key column(s). 3NF is the next logical step after 2NF.

The primary key in the table in Figure 2.20 is title_id. The nonkey columns are price (the book’s price), pub_city (the city where the book is published), and pub_id (the book’s publisher).

For each nonkey column, ask, “Can I determine a nonkey column value if I know any other nonkey column value?” A no answer means that the column is not transitively dependent (good); a yes answer means that the column whose value you can determine is transitively dependent on the other column (bad).

For the column price, the questions are:

• Can I determine pub_id if I know price? No.
• Can I determine pub_city if I know price? No.

For the column pub_city, the questions are:

• Can I determine price if I know pub_city? No.
• Can I determine pub_id if I know pub_city? No, because a city might have many publishers.

For the column pub_id, the questions are:

• Can I determine price if I know pub_id? No.
• Can I determine pub_city if I know pub_id? Yes! The city where the book is published depends on the publisher.

Bad—pub_city is transitively dependent on pub_id and must be moved elsewhere (probably to a publishers table) to satisfy 3NF rules.

As you can see, it’s not enough to ask, “Can I determine A if I know B?” to discover a transitive dependency; you also must ask, “Can I determine B if I know A?”

Other Normal Forms

Higher levels of normalization exist, but the relational model doesn’t require (or even mention) them. They’re useful in some cases to avoid redundancy. Briefly, they are:

• Boyce-Codd normal form is a more rigorous version of 3NF. BCNF deals with tables that have multiple candidate keys, composite candidate keys, or candidate keys that overlap. A table is in BCNF if every determinant is a candidate key. (A determinant column is one on which some of the columns are fully functionally dependent.)

• A table in fourth normal form is in BCNF and has no multivalued dependencies (MVDs). An MVD occurs when in a table containing at least three columns, one column has multiple rows whose values match a value of a single row of one of the other columns.

  Suppose that employees can be assigned to multiple projects and each employee can have multiple skills. If you stuff all this information into a single table, then you must use all three attributes as the key because nothing less can identify a row uniquely. The relationship between emp_id and proj_id is an MVD because for each pair of emp_id–skill_id values in the table, only emp_id (independent of skill_id) determines the associated set of proj_id values. The relationship between emp_id and skill_id also is an MVD because the set of skill values for an emp_id–proj_id pair always depends on only emp_id. To transform a table with MVDs to 4NF, move each MVD pair to a new table.

• A table in fifth normal form is in 4NF and has no join dependencies, which are generalizations of MVDs. The aim of 5NF is to have tables that can’t be decomposed further into any number of smaller tables. The redundancies and anomalies that 5NF cures are rare and unintuitive. In real databases, you’ll see 1NF, 2NF, 3NF, and occasionally 4NF. 4NF and even 3NF tables almost always are 5NF too.

The Sample Database

Pick up an SQL or database-design book, and probably you’ll find a students/courses/teachers, customers/orders/products, or authors/books/publishers database. In a bow to convention, most of the SQL examples in this book use an authors/books/publishers sample database named books. Here are some things that you should know about books:

• Recall from “Tables, Columns, and Rows” earlier in this chapter that a database appears to the user as a collection of tables (and nothing but tables). books contains five main tables that contain information about authors, titles they’ve published, their publishers, and their royalties. Figure 2.21 depicts the tables and relationships in books by using the graphical conventions introduced earlier in this chapter.
• books also contains a few additional tables that are used in specific examples. These tables are described later in the text when they’re relevant.
• The SQL statements in Chapters 10 and later modify data in books (rather than just retrieve data). Unless otherwise noted, each new section in a chapter starts with a pristine copy of books. In other words, assume that database changes made in one section don’t carry over to the next section.
• Some of the concepts mentioned in this section, such as data types and nulls, are covered in the next chapter.
• books is a teaching tool; its structure and size don’t approach the complexity of real production databases.
• To create the sample database on your own DBMS, see “Creating the Sample Database” later in this chapter.

The Table authors

The table authors describes the books’ authors. Each author has a unique identifier (au_id) that’s the primary key. Table 2.3 shows the structure of the table authors, and Figure 2.22 shows its contents.

The Table publishers

The table publishers describes the books’ publishers. Each publisher has a unique identifier (pub_id) that’s the primary key. Table 2.4 shows the structure of the table publishers, and Figure 2.23 shows its contents.

The Table titles

The table titles describes the books. Each book has a unique identifier (title_id) that’s the primary key. titles contains a foreign key, pub_id, that references the table publishers to indicate a book’s publisher. Table 2.5 shows the structure of the table titles, and Figure 2.24 shows its contents.

The Table title_authors

Authors and books have a many-to-many relationship because an author can write multiple books and a book can have multiple authors. title_authors is the junction table that associates the tables authors and titles; see “Relationships” earlier in this chapter. title_id and au_id together form a composite primary key, and each column separately is a foreign key that references titles and authors, respectively. The nonkey columns indicate the order of the author’s name on the book’s cover (always 1 for a book with a sole author) and the fraction of total royalties that each author receives (always 1.0 for a book with a sole author). Table 2.6 shows the structure of the table title_authors, and Figure 2.25 shows its contents.

The Table royalties

The table royalties specifies the royalty rate paid to all the authors (not each author) of each book, including the total up-front advance against royalties paid to all authors (again, not each author) of a book. The royalties primary key is title_id. The table royalties has a one-to-one relationship with titles, so the royalties primary key also is a foreign key that references the titles primary key. Table 2.7 shows the structure of the table royalties, and Figure 2.26 shows its contents.

Creating the Sample Database

To create (or re-create) the database books on your own DBMS, visit questingvolepress.com, click the Download link for this book, and then follow the onscreen instructions. Creating books is a two-step process:

1. Use your DBMS’s built-in tools to create a new, blank database named books.
2. Run an SQL script that creates tables within books and populates them with data.

Listing 2.1 shows a standard (ISO/ANSI) SQL script that creates the sample-database tables and inserts rows into them.

If you’re using Microsoft Access, then you don’t run a script—you simply open an Access database file.

3. SQL Basics

SQL is based on the relational model but doesn’t implement it faithfully. One departure from the model is that in SQL, primary keys are optional rather than mandatory. Consequently, tables without keys will accept duplicate rows, rendering some data inaccessible. A complete review of the many disparities is beyond the scope of this book (see “Learning Database Design” in Chapter 2). The upshot of these discrepancies is that DBMS users, and not the DBMS itself, are responsible for enforcing a relational structure. Another result is that the Model and SQL terms in Table 2.1 in Chapter 2 aren’t interchangeable.

With that warning, it’s time to learn SQL. An SQL program is a sequence of SQL statements executed in order. To write a program, you must know the rules that govern SQL syntax. This chapter explains how to write valid SQL statements and also covers data types and nulls.

SQL Syntax

Figure 3.1 shows the syntax of an example SQL statement (don’t worry about the actual meaning, or semantics, of the statement).

1. Comment.A comment is optional text that explains your program. Comments usually describe what a program does and how, or why code was changed. Comments are for humans—the DBMS ignores them. A comment is introduced by two consecutive hyphens and continues until the end of the line.
2. SQL statement.An SQL statement is a valid combination of tokens introduced by a keyword. Tokens are the basic indivisible particles of the SQL language; they can’t be reduced grammatically. Tokens include keywords, identifiers, operators, literals (constants), and punctuation symbols.
3. Clauses.An SQL statement has one or more clauses. In general, a clause is a fragment of an SQL statement that’s introduced by a keyword, is required or optional, and must be given in a particular order. SELECT, FROM, WHERE, and ORDER BY introduce the four clauses in this example.
4. Keywords.A keyword is a word that SQL reserves because it has special meaning in the language. Using a keyword outside its specific context (as an identifier, for example) causes an error. DBMSs use a mix of standard and nonstandard keywords; search your DBMS documentation for keywords or reserved words. The keywords in this example are SELECT, FROM, WHERE, ORDER, and BY.
5. Identifiers.Identifiers are words that you (or the database designer) use to name database objects such as tables, columns, aliases, indexes, and views. The identifiers in this example are au_fname, au_lname, authors, and state. For more information, see “Identifiers” later in this chapter.
6. Terminating semicolon.An SQL statement ends with a semicolon.

SQL is a free-form language whose statements can:

• Be in uppercase or lowercase (SELECT, select, and sElEcT are considered to be identical keywords, for example)
• Continue on the next line as long as you don’t split words, tokens, or quoted strings in two
• Be on the same line as other statements
• Start in any column

Despite this flexibility, you should adopt a consistent style (Figure 3.2). I use uppercase keywords and lowercase identifiers and indent each clause on its own line; see “About This Book” for my typographic and syntax conventions.

Tips for SQL Syntax

• The introductory keyword of an SQL statement is called a verb because it indicates an action to perform.
• Distinguish between a SELECT statement, which is the entire statement from SELECT to semicolon, and a SELECT clause, which is the part of the SELECT statement that lists the output columns.
• Some DBMSs support bracketed comments, which start with /*, continue over one or more lines, and end with */. You can nest a bracketed comment within another.

• An expression is any legal combination of symbols that evaluates to a single data value. You can combine mathematical or logical operators, identifiers, literals, functions, column names, aliases, and so on. Table 3.1 lists some common expressions and examples. These expressions are covered in more detail later.

  Table 3.1Types of Expressions

  Type	Example
  Case	CASE WHEN n <> 0 THEN x/n ELSE 0 END
  Cast	CAST(pubdate AS CHARACTER)
  Datetime value	start_time + '01:30'
  Interval value	INTERVAL '7' DAY * 2
  Numeric value	(sales*price)/12
  String value	'Dear '||au_fname||','

SQL Standards and Conformance

The ISO SQL technical committee has been revising the official SQL standard every few years since 1986. Each revision:

• Introduces new elements to the language
• Clarifies or updates the elements of earlier standards
• Drops existing elements because new elements have superseded them or they never caught on among DBMS vendors or SQL programmers

The standard is enormous—thousands of pages of dense specifications—and no vendor conforms (or ever will conform) to the entire thing. Instead, vendors try to conform to a subset of the standard called Core SQL. This level of conformance is the minimal category that vendors have to achieve to claim that they conform to standard SQL. The SQL-92 revision introduced levels of conformance, and later standards have them too, so when you read a DBMS’s conformance statement, note which SQL standard it’s referring to and which level. In fact, SQL-92 often is thought of as the standard because it defined many of the most vital and unchanging parts of the language. Except where noted, the SQL elements in this book are part of SQL-92 as well as later standards. The lowest level of SQL-92 conformance is called Entry (not Core).

Your programs should follow the SQL standard as closely as possible. Ideally, you should be able to write portable SQL programs without even knowing which DBMS you’re programming for. Unfortunately, the SQL committee is not made up of language theorists and relational-model purists but is top-heavy with commercial DBMS vendors, all jockeying and maneuvering. The result is that each DBMS vendor devotes resources to approach minimal Entry or Core SQL conformance requirements and then scampers off to add nonstandard features that differentiate their products in the marketplace—meaning that your SQL programs won’t be portable. These vendor-specific lock-ins often force you to modify or rewrite SQL programs to run on different DBMSs.

Of the DBMSs covered in this book, PostgreSQL is the “purest” with respect to the SQL standard. Your DBMS might offer settings that make it better conform to the SQL standard. MySQL has ANSI mode, for example, and Microsoft SQL Server has SET ANSI_DEFAULTS ON.

Identifiers

An identifier is a name that lets you refer to an object unambiguously within the hierarchy of database objects (whether a schema, database, column, key, index, view, constraint, or anything created with a CREATE statement). An identifier must be unique within its scope, which defines where and when it can be referenced. In general:

• Database names must be unique on a specified instance of a database server.
• Table and view names must be unique within a given database (or schema).
• Column, key, index, and constraint names must be unique within a given table or view.

These rules let you duplicate names for objects whose scopes don’t overlap. You can give the same name to columns in different tables, for example, or to tables in different databases.

DBMS scopes vary in the extent to which they require identifier names to be unique. Microsoft SQL Server requires an index name to be unique for only its table, for example, whereas Oracle and Db2 require an index name to be unique throughout the database. Search your DBMS documentation for identifiers or names.

Standard SQL has the following identifier rules for names:

• Can be up to 128 characters long
• Must begin with a letter
• Can contain letters, digits, and underscores (_)
• Can’t contain spaces or special characters (such as #, $, &, %, or punctuation)
• Can’t be reserved keywords (except for quoted identifiers)

Standard SQL distinguishes between reserved and non-reserved keywords. You can’t use reserved keywords as identifiers because they have special meaning in SQL. You can’t name a table “select” or a column “sum”, for example. Non-reserved keywords have a special meaning in only some contexts and can be used as identifiers in other contexts. Most non-reserved keywords actually are the names of built-in tables and functions, so it’s safest never to use them as identifiers either.

You can use a quoted identifier, also called a delimited identifier, to break some of SQL’s identifier rules. A quoted identifier is a name surrounded by double quotes. The name can contain spaces and special characters, is case sensitive, and can be a reserved keyword. Quoted identifiers can annoy other programmers and cause problems with third-party and even a vendor’s own tools, so using them usually is a bad idea.

Here’s some more advice for choosing identifier names:

• Stick to the standard rules even if your DBMS has less restrictive ones (Oracle names can contain # and $ symbols, for example).
• In some cases, your DBMS will be more restrictive than the standard (MySQL identifiers can be up to only 64 characters long, for example).
• Use lowercase letters.
• names_with_underscores are easier to read than nameswithoutthem.
• Use consistent names and abbreviations throughout the database—pick either emp or employee and stick with it.

Although you can’t use (unquoted) reserved words as identifiers, you can embed them in identifiers. group and max are illegal identifiers, for example, but groups and max_price are valid. If you’re worried that your identifier might be a reserved word in some other SQL dialect, then just add an underscore to the end of the name (element_, for example); no reserved keyword ends with an underscore.

You can surround Microsoft SQL Server quoted identifiers with double quotes or brackets ([]); brackets are preferred. In Db2, you can use reserved words as identifiers (but doing so isn’t a good idea because your program won’t be portable). MySQL ANSI_QUOTES mode allows double-quoted identifiers. DBMSs have their own nonstandard keywords; search your DBMS documentation for keywords or reserved words.

In MySQL, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names.

The SQL standard directs DBMSs to convert identifier names to uppercase internally. So in the guts of your SQL compiler, the unquoted identifier myname is equivalent to the quoted identifier "MYNAME" (not "myname"). PostgreSQL doesn’t conform to the standard and converts to lowercase. To write portable programs, always quote a particular name or never quote it (don’t mix them). DBMSs aren’t consistent when it comes to case sensitivity, so the best practice is always to respect case for user-defined identifiers.

Data Types

Recall from “Tables, Columns, and Rows” in Chapter 2 that a domain is the set of valid values allowed in a column. To define a domain, you use a column’s data type (and constraints, described in Chapter 11). A data type, or column type, has these characteristics:

• Each column in a table has a single data type.

• A data type falls into one of categories listed in Table 3.2 (each covered in the following sections).

  Table 3.2Categories of Data Types

  Category	Stores These Data
  Character string	Strings of characters
  Binary large object	Binary data
  Exact numeric	Integers and decimal numbers
  Approximate numeric	Floating-point numbers
  Boolean	Truth values: true, false, or unknown
  Datetime	Date and time values
  Interval	Date and time intervals

• The data type determines a column’s allowable values and the operations it supports. An integer data type, for example, can represent any whole number between certain DBMS-defined limits and supports the usual arithmetic operations: addition, subtraction, multiplication, and division (among others). But an integer can’t represent a nonnumeric value such as 'john' and doesn’t support character operations such as capitalization and concatenation.
• The data type affects the column’s sort order. The integers 1, 2, and 10 are sorted numerically, yielding 1, 2, 10. The character strings '1', '2', and '10' are sorted lexicographically, yielding '1', '10', '2'. Lexicographical ordering sorts strings by examining the values of their characters individually. Here, '10' comes before '2' because '1' (the first character of '10') is less than '2' lexicographically. For information about sorting, see “Sorting Rows with ORDER BY” in Chapter 4.
• Some data types, such as binary objects, can’t be indexed (see Chapter 12).

• You store literal values (constants) in character, numeric, boolean, datetime, and interval columns. Table 3.3 shows some examples; the following sections have more examples. Be sure not to confuse the string literal '2009' with the numeric literal 2009. The SQL standard defines a literal as any constant that isn’t null.

  Table 3.3Examples of Literals

  Literal	Examples
  Character string	'42', 'ennui', 'don''t', N'Jack'
  Numeric	42, 12.34, 2., .001, -123, +6.33333, 2.5E2, 5E-3
  Boolean	TRUE, FALSE, UNKNOWN
  Datetime	DATE '2005-06-22', TIME '09:45:00', TIMESTAMP '2006-10-19 10:23:54'
  Interval	INTERVAL '15-3' YEAR TO MONTH, INTERVAL '22:06:5.5' HOUR TO SECOND

Tips for Data Types

• Use the statements CREATE TABLE and ALTER TABLE to define and change a column’s data type; see Chapter 11.
• Database designers choose data types carefully. The consequences of a poor data-type choice include the inability to insert values into a column and data loss if the existing data type must be changed.
• The SQL:2003 standard dropped SQL-92’s bit-string data types (BIT and BIT VARYING) in favor of binary large objects. Bit strings held smaller binary-data items than BLOBs do.
• The SQL standard leaves many data-type implementation details up to the DBMS vendor. Consequently, SQL data types don’t map directly to specific DBMS data types, even if the data types have identical names. You’ll find equivalent or similar DBMS data types in the tips of each of the following data-type sections. Some DBMS data types have synonyms that match the SQL standard’s data-type names.

Character String Types

Use character string data types to represent text. A character string, or simply string, has these characteristics:

• It’s an ordered sequence of zero or more characters.
• Its length can be fixed or varying.
• It’s case sensitive ('A' comes before 'a' when sorted).
• In SQL statements, a string is surrounded by single quotes.
• It’s one of the types listed in Table 3.4.

Tips for Character Strings

• Two consecutive single quotes represent one single-quote character in a string. Type 'don''t' to represent don't, for example. A double-quote character (") is a separate character and doesn’t need this special treatment.
• The length of a string is an integer between 0 and length, inclusive. A string with no characters—'' (two single quotes with no intervening space)—is called an empty string or a zero-length string. An empty string is considered to be a VARCHAR of length zero.
• DBMSs often can sort and manipulate fixed-length strings faster than variable-length ones.
• Keep character columns as short as possible rather than giving them “room to grow” in the future. Shorter columns sort and group faster than longer ones.
• The SQL:1999 standard introduced CLOB and NCLOB to the SQL language (but most DBMSs already had similar data types by then).

• Table 3.5 lists character-string and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

  Oracle treats empty strings as nulls; see “Nulls” later in this chapter.

  In MySQL ANSI_QUOTES mode, string literals can be quoted only with single quotes; a string quoted with double quotes is interpreted as an identifier.

Binary Large Object Type

Use the binary large object (BLOB) data type to store binary data. A BLOB has these characteristics:

• The type name is BLOB or BINARY LARGE OBJECT.
• Unlike a CLOB, which stores a long character string, a BLOB stores a long sequence of bytes. The two data types are incompatible.
• BLOBs are used mainly to store large amounts of multimedia data (graphics, photos, audio, or video, for example), scientific data (medical images or climate maps), or technical data (engineering drawings).
• BLOBs can’t be used as keys or in indexes and support far fewer functions and operations than do other types. BLOBs can be compared for only equality (=) or inequality (<>) because it makes no sense for a BLOB to be “less than” another BLOB. BLOBs can’t be used with DISTINCT, in GROUP BY or ORDER BY clauses, or in column functions.
• In host languages, BLOBs are referenced with a unique locator (pointer) value, avoiding the overhead of transferring entire BLOBs across a client/server network.

Tips for Binary Large Objects

• DBMSs don’t attempt to interpret BLOBs; their meaning is up to the application.
• A binary string literal is given in hexadecimal, or hex (base 16), format. The hexadecimal system uses the digits 0 through 9 and the letters A through F (uppercase or lowercase). One hex character is equivalent to 4 bits. In SQL statements, hex strings have an X in front of the first quote, with no intervening space. The hex string X'4B' corresponds to the bits 01001011 or the bit string B'01001011', for example.
• The SQL:1999 standard introduced BLOB to the SQL language (but most DBMSs already had similar data types by then).
• Table 3.6 lists BLOB and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

Exact Numeric Types

Use exact numeric data types to represent exact numerical values. An exact numerical value has these characteristics:

• It can be a negative, zero, or positive number.
• It’s an integer or a decimal number. An integer is a whole number expressed without a decimal point: −42, 0, 62262. A decimal number has digits to the right of the decimal point: −22.06, 0.0, 0.0003, 12.34.
• It has a fixed precision and scale. The precision is the number of significant digits used to express the number; it’s the total number of digits both to the right and to the left of the decimal point. The scale is the number of digits to the right of the decimal point. Obviously, the scale can’t exceed the precision. To represent a whole number, set the scale equal to zero. See “Tips for Exact Numeric Types” for some examples.
• It’s one of the types listed in Table 3.7.

Tips for Exact Numeric Types

• Table 3.8 shows how the number 123.89 is stored for different precision and scale values.

  Table 3.8Precision and Scale Examples for 123.89

  Specified As	Stored As
  NUMERIC(5)	124
  NUMERIC(5,0)	124
  NUMERIC(5,1)	123.9
  NUMERIC(5,2)	123.89
  NUMERIC(4,0)	124
  NUMERIC(4,1)	123.9
  NUMERIC(4,2)	Exceeds precision
  NUMERIC(2,0)	Exceeds precision

• Don’t enclose a numeric literal in quotes.
• Store numbers as strings if the numbers are not involved in arithmetic calculations. Store telephone numbers, credit-card numbers, postal codes, and U.S. Social Security numbers as strings, for example. This technique can save space and prevent data loss: if you store the postal code '02116' as a number instead of as a string, then you’ll lose the leading zero.
• Calculations involving only integers are much faster than those involving decimal and floating-point numbers.

• Table 3.9 lists exact-numeric and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Some DBMSs accept type names that they don’t implement, converting them to suitable, supported types; Oracle converts INT to NUMBER(32), for example.

  DBMSs usually implement SMALLINT as 16-bit values (−32,768 through 32,767), INTEGER as 32-bit values (−2,147,483,648 through 2,147,483,647), and BIGINT as 64-bit values (quintillions). The SQL:2003 standard introduced BIGINT to the SQL language (but most DBMSs already had a similar data type by then).

Approximate Numeric Types

Use approximate numeric data types to represent approximate numerical values. An approximate numerical value has these characteristics:

• It can be a negative, zero, or positive number.
• It’s considered to be an approximation of a floating-point (real) number.
• It typically is used to represent the very small or very large quantities common in technical, scientific, statistical, and financial calculations.
• It’s expressed in scientific notation. A number in scientific notation is written as a decimal number multiplied by an (integer) power of 10. An uppercase E is the exponentiation symbol: 2.5E2 = 2.5 × 10² = 250, for example. The mantissa is the portion that expresses the significant digits (2.5 here), and the exponent is the power of 10 (2 here). The mantissa and exponent each can have a sign: −2.5E−2 = −2.5 × 10⁻² = −0.025.
• It has a fixed precision but no explicit scale. (The sign and magnitude of the exponent determine the scale intrinsically.) The precision is the number of (binary) bits used to store the mantissa. To convert from binary to decimal precision, multiply the precision by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. For example, 24 bits yields 7 digits of precision, and 53 bits yields 15 digits of precision.
• It’s one of the types listed in Table 3.10.

Tips for Approximate Numeric Types

• Don’t enclose a numeric literal in quotes.
• Table 3.11 lists approximate numeric and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Some DBMSs accept type names that they don’t implement, converting them to suitable, supported types; PostgreSQL converts float to double precision, for example.

Boolean Type

Use the boolean data type to store truth values. A boolean value has these characteristics:

• The type name is BOOLEAN.
• The truth values are True, False, and Unknown, represented by the boolean literals TRUE, FALSE, and UNKNOWN. Truth values are described in “Combining and Negating Conditions with AND, OR, and NOT” in Chapter 4.
• A null is equivalent to the Unknown truth value and, in practice, usually is used instead of Unknown (most DBMS boolean types don’t accept the literal UNKNOWN). See “Nulls” later in this chapter.

Tips for Boolean Values

• Don’t enclose a boolean literal in quotes.
• The SQL:1999 standard introduced BOOLEAN to the SQL language.
• Table 3.12 lists boolean and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Where no boolean type is available, use the data type for storing single bits or small integers. SQL programmers often use these numeric values to represent truth values, where zero means false, 1 (or any nonzero number) means true, and null means unknown.

Datetime Types

Use datetime data types to represent the date and time of day. A datetime value has these characteristics:

• It’s specified with respect to UTC, or Universal Coordinated Time (formerly called Greenwich Mean Time or GMT). The SQL standard requires that every SQL session have a default offset from UTC that is used for the duration of the session; −8 hours is the time-zone offset of San Francisco, California, for example.
• The rules of the Gregorian calendar determine how date values are formed. DBMSs reject values that they can’t recognize as dates.
• Time values are based on a 24-hour clock, also called military time (use 13:00, not 1:00 p.m.).
• Hyphens (-) separate the parts of a date, and colons (:) separate the parts of a time. A space separates a date and time when both are combined.
• It’s one of the types listed in Table 3.13.

Tips for Datetime Values

• To get your system time, see “Getting the Current Date and Time” in Chapter 5.
• You can compare two datetime values if they have the same fields; see “Filtering Rows with WHERE” in Chapter 4. See also “Performing Datetime and Interval Arithmetic” in Chapter 5.
• The SECOND field in Table 3.14 can accept values up to 61.999... (instead of 59) to allow for the (rare) insertion of leap seconds into a particular minute to keep Earth’s clocks synchronized with sidereal time.
• A datetime literal is a datetime type name, followed by a space, followed by a datetime value surrounded by single quotes—DATE 'yyyy-mm-dd', TIME 'hh:mm:ss', and TIMESTAMP 'yyyy-mm-dd hh:mm:ss', for example.
• Standard SQL can’t handle B.C.E./B.C. (Before the Common Era/Before Christ) dates, but your DBMS might be able to do so.
• Timestamps often are used to mark events associated with the row in which they appear. In MySQL, for example, a timestamp column is useful for recording the date and time of an UPDATE operation.
• The data type TIME WITH TIME ZONE makes no sense because real-world time zones have no meaning unless they’re associated with a date (because the time-zone offset varies throughout the year). Favor TIMESTAMP WITH TIME ZONE.

• Table 3.15 lists datetime and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

  DBMSs let you enter date values in month-day-year, day-month-year, and other formats and time values based on a 12-hour (a.m./p.m.) clock. The format in which dates and times are displayed can differ from the format in which they’re entered.

  In Microsoft Access, surround datetime literals with # characters instead of quotes and omit the data type name prefix. The standard SQL date DATE '2006-03-17' is equivalent to the Access date #2006-03-17#, for example.

  In Microsoft SQL Server, omit the data type name prefix from datetime literals. The standard SQL date DATE '2006-03-17' is equivalent to the SQL Server date '2006-03-17', for example.

  In Db2, omit the data type name prefix from datetime literals. The standard SQL date DATE '2006-03-17' is equivalent to the Db2 date '2006-03-17', for example.

Interval Types

DBMS conformance to standard SQL interval types is spotty or nonexistent, so you might not find this section to be useful in practice. DBMSs have their own extended data types and functions that calculate intervals and perform date and time arithmetic.

Use interval data types to represent sets of time values or spans of time. An interval value has these characteristics:

• It stores the quantity of time between two datetime values. Between 09:00 and 13:30 is an interval of 04:30 (4 hours and 30 minutes), for example. If you subtract two datetime values, then you get an interval.
• It can be used to increment or decrement a datetime value; see “Performing Datetime and Interval Arithmetic” in Chapter 5.
• It has the same fields as datetime values (YEAR, HOUR, SECOND, and so on), but the number can have a + (forward) or - (backward) sign to indicate a direction in time. The field separators are the same as for datetime values.
• It comes in two categories: year-month intervals and day-time intervals. A year-month interval expresses an interval as years and a whole number of months. A day-time interval expresses an interval as days, hours, minutes, and seconds.

• It has a single-field or multiple-field qualifier. A single-field qualifier is specified as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. A multiple-field qualifier is specified as:

  start_field TO end_field

  start_field is YEAR, DAY, HOUR, or MINUTE, and end_field is YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. end_field must be a smaller time period than start_field.

  A single-field column defined as INTERVAL HOUR could store intervals such as “4 hours” or “25 hours”, for example. A multiple-field column defined as INTERVAL DAY TO MINUTE could store intervals such as “2 days, 5 hours, 10 minutes”, for example.

• A single-field column can have a precision that specifies the length (number of positions) of the field; INTERVAL HOUR(2), for example. The precision defaults to 2 if omitted. A SECOND field can have an additional fractional precision that specifies the number of digits to the right of the decimal point—INTERVAL SECOND(5,2), for example. The fractional precision defaults to 6 if omitted.

  A multiple-field column can have a precision for start_field but not end_field (unless end_field is SECOND, in which case it can have a fractional precision)—INTERVAL DAY(3) TO MINUTE and INTERVAL MINUTE(2) TO SECOND(4), for example.

• It’s one of the types listed in Table 3.16.

Tips for Interval Types

• An interval literal is the word INTERVAL followed by a space, followed by an interval value surrounded by single quotes, followed by the interval qualifier—INTERVAL '15-3' YEAR TO MONTH (15 years and 3 months) and INTERVAL '22:06:5.5' HOUR TO SECOND (22 hours, 6 minutes, and 5.5 seconds), for example.
• See also “Working with Dates” in Chapter 15.
• Table 3.17 lists interval and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

Unique Identifiers

Unique identifiers are used to generate primary-key values to identify rows (see “Primary Keys” in Chapter 2). An identifier can be unique universally (large random numbers unique in any context) or only within a specific table (simple serial numbers 1, 2, 3,...). Table 3.18 lists unique-identifier types and attributes for the DBMSs. See the DBMS documentation for size limits and usage restrictions. The SQL standard calls columns with auto-incrementing values identity columns. See also “Generating Sequences” in Chapter 15.

Other Data Types

The SQL standard defines other data types than the ones covered in the preceding sections, but some of them rarely are implemented or used in practice (ARRAY, MULTISET, REF, and ROW, for example). More useful are the extended (nonstandard) data types that are available in various DBMSs. Depending on your DBMS, you can find data types for:

• Time intervals
• Network and internet addresses
• Links to files stored outside the database
• Monetary (currency) amounts
• Geographic and geometric (spatial) coordinates
• Row version-stamping
• Arrays and other collections
• XML
• JSON
• Full text searching
• Enumerated (enum) values from a specified list
• Position in a hierarchy

Nulls

When your data are incomplete, you can use a null to represent a missing or unknown value. A null has these characteristics:

• In SQL statements, the keyword NULL represents a null.
• A null is used for a value that might never be known, might be determined later, or is inapplicable. (Think of a null as a marker or flag for a missing value, rather than as a value itself.)
• A null differs from zero, a string that contains only spaces, or an empty string (''). A null in the column price doesn’t mean that an item has no price or that its price is zero; it means that the price is unknown or has not been set. (Oracle is a special case with respect to empty strings; see the DBMS tip in this section.)
• Nulls can appear in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY constraints (Chapter 11). You should not allow nulls in alternate keys.
• To detect nulls, see “Testing for Nulls with IS NULL” in Chapter 4.
• Nulls aren’t equal (or unequal) to each other. You can’t determine whether a null matches any other value, including another null, so the expressions NULL = any_value, NULL <> any_value, NULL = NULL, and NULL <> NULL are neither true nor false but unknown; see “Combining and Negating Conditions with AND, OR, and NOT” in Chapter 4.
• Although nulls are never equal to each other, DISTINCT treats all the nulls in a particular column as duplicates; see “Eliminating Duplicate Rows with DISTINCT” in Chapter 4.
• When you sort a column that contains nulls, the nulls will be either greater than or less than all the non-null values, depending on the DBMS; see “Sorting Rows with ORDER BY” in Chapter 4.
• Nulls propagate through computations. The result of any arithmetic expression or operation that involves a null is null: (12*NULL)/4 is null; see Chapter 5.
• Most aggregate functions, such as SUM(), AVG(), and MAX(), ignore nulls. COUNT(*) is an exception. See Chapter 6.
• If the grouping column in a GROUP BY clause contains nulls, then all the nulls are put in a single group; see “Grouping Rows with GROUP BY” in Chapter 6.
• Nulls affect the results of joins; see “Using Joins” in Chapter 7.
• Nulls can cause problems in subqueries; see “Nulls in Subqueries” in Chapter 8.

Tips for Nulls

• Use caution when interpreting results in which nulls are involved. Nulls cause so many problems and complications—some important ones are listed here—that some database experts urge users to minimize their use or not use them at all (and instead use default values or some other missing-data scheme). Nevertheless, you won’t become a competent SQL programmer without understanding nulls completely.
• See also “Checking for Nulls with COALESCE” and “Comparing Expressions with NULLIF” in Chapter 5.
• Nullable means that a column is allowed to contain nulls. Use the CREATE TABLE or ALTER TABLE statement (Chapter 11) to set a column’s nullability.
• The term null value is inaccurate—a null indicates the lack of a value.
• Don’t place the keyword NULL in quotes; your DBMS will interpret it as the character string 'NULL' rather than as a null.

• You can get a null from a column that doesn’t allow nulls. The column au_id in the table authors doesn’t allow nulls, but the SELECT statement in Figure 3.3 returns a null for the maximum au_id.

  Figure 3.3Getting a null from a column that isn’t nullable.

  SELECT MAX(au_id)
  FROM authors
  WHERE au_lname = 'XXX';

MAX(au_id)
----------
NULL

• If nulls appear in a column because actual values are not meaningful (rather than unknown), then you can split the column off into its own table with a one-to-one relationship with the other table. In Figure 3.4, the original table employees has the column commission, which specifies an employee’s sales commission. commission contains mostly nulls because most employees aren’t salespeople. To avoid the proliferation of nulls, move commission to its own table.

  Figure 3.4Nulls are eliminated by splitting the original table (top) into a one-to-one relationship (bottom).

  

• The display of nulls in results varies by DBMS. A null might appear as NULL, (NULL), <NULL>, -, or empty space, for example.

  Oracle treats an empty string ('') as a null. This treatment might not continue to be true in future releases, however, and Oracle recommends that you do not treat empty strings the same as nulls in your SQL code. This behavior can cause conversion problems among DBMSs. In the sample database, for example, the column au_fname in the table authors is defined as NOT NULL. In Oracle, the first name of the author Kellsey (author A06) is a space (' '); in the other DBMSs, the first name is an empty string (''). For information about the sample database, see “The Sample Database” in Chapter 2.

4. Retrieving Data from a Table

This chapter introduces SQL’s workhorse—the SELECT statement. Most SQL work involves retrieving and manipulating data by using this one (albeit complex) statement. SELECT retrieves rows, columns, and derived values from one or more tables in a database; its syntax is:

SELECT columns
  FROM tables
  [JOIN joins]
  [WHERE search_condition]
  [GROUP BY grouping_columns]
  [HAVING search_condition]
  [ORDER BY sort_columns];


SELECT, FROM, ORDER BY, and WHERE are covered in this chapter, GROUP BY and HAVING in Chapter 6, and JOIN in Chapter 7. By convention, I call only a SELECT statement a query because it returns a result set. DBMS documentation and other books might refer to any SQL statement as a query. Although SELECT is powerful, it’s not dangerous: you can’t use it to add, change, or delete data or database objects. (The dangerous stuff starts in Chapter 10.)

Retrieving Columns with SELECT and FROM

In its simplest form, a SELECT statement retrieves columns from a table; you can retrieve one column, multiple columns, or all columns. The SELECT clause lists the columns to display, and the FROM clause specifies the table from which to draw the columns.

To retrieve a column from a table:

• Type:

  SELECT column
  FROM table;

  column is a column name, and table is the name of the table that contains column (Listing 4.1 and Figure 4.1).

To retrieve multiple columns from a table:

• Type:

  SELECT columns
  FROM table;

  columns is two or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.2 and Figure 4.2).

  Columns are displayed in the same order in which they’re listed in columns, not the order in which they’re defined in table.

To retrieve all columns from a table:

• Type:

  SELECT *
  FROM table;

  table is the name of the table (Listing 4.3 and Figure 4.3).

  Columns are displayed in the order in which they’re defined in table.

Tips for Retrieving Columns

• The SELECT and FROM clauses always are required to retrieve columns from tables; all other clauses are optional.
• Closure guarantees that the result of every SELECT statement is a table; see “Tips for Tables, Columns, and Rows” in Chapter 2.
• The result in Figure 4.1 contains duplicate rows because two authors live in San Francisco. To remove duplicates, see “Eliminating Duplicate Rows with DISTINCT” later in this chapter.
• The rows in your results might be ordered differently from the rows in mine; see “Sorting Rows with ORDER BY” later in this chapter.

• I use NULL to indicate a null in a table or result; see “Nulls” in Chapter 3 (Listing 4.4 and Figure 4.4).

  Listing 4.4List each publisher’s city, state, and country. See Figure 4.4 for the result.

  SELECT city, state, country
  FROM publishers;

  Figure 4.4Result of Listing 4.4. The column state doesn’t apply to Germany. NULL specifies a null, which is distinct from an “invisible” value such as an empty string or a string of spaces.

  city          state country
------------- ----- -------
New York      NY    USA
San Francisco CA    USA
Hamburg       NULL  Germany
Berkeley      CA    USA

• Chapters 7, 8, and 9 describe how to retrieve columns from multiple tables.
• SELECT * often is risky because the number or order of a table’s columns can change and cause your program to fail. Likewise, SELECT * won’t be understood by people unfamiliar with the table’s columns. In contrast to queries that name specific columns in the SELECT clause, SELECT * is a resource hog that drags unneeded data across networks. (To see how a table is defined, rather than list its rows, see “Displaying Table Definitions” in Chapter 10.)
• An operation that selects certain columns from a table is called a projection.

Creating Column Aliases with AS

In the query results so far, I’ve allowed the DBMS to use default values for column headings. (A column’s default heading in a result is the source column’s name in the table definition.) You can use the AS clause to create a column alias. A column alias is an alternative name (identifier) that you specify to control how column headings are displayed in a result. Use column aliases if column names are cryptic, hard to type, too long, or too short.

A column alias immediately follows a column name in the SELECT clause of a SELECT statement. Enclose the alias in single or double quotes if it’s a reserved keyword or if it contains spaces, punctuation, or special characters. You can omit the quotes if the alias is a single non-reserved word that contains only letters, digits, or underscores. If you want a particular column to retain its default heading, then omit its AS clause.

To create column aliases:

• Type:

  SELECT
    column1 [AS] alias1,
    column2 [AS] alias2,
    ...
    columnN [AS] aliasN
  FROM table;

  column1, column2,..., columnN are column names; alias1, alias2,..., aliasN are their corresponding column aliases; and table is the name of the table that contains column1, column2,....

Listing 4.5 shows the syntactic variations of the AS clause. Figure 4.5 shows the result of Listing 4.5.

In standard SQL and most DBMSs, the keyword AS is optional, but you should include it and surround aliases with double quotes to make your SQL code more portable and readable. With these syntactic conventions, Listing 4.5 is equivalent to:

SELECT
    au_fname AS "First name",
    au_lname AS "Last name",
    city     AS "City",
    state,
    zip      AS "Postal code"
  FROM authors;

Tips for Column Aliases

• A column alias doesn’t change the name of a column in a table.
• To determine a column’s name in a table definition, see “Displaying Table Definitions” in Chapter 10.
• You can use a reserved keyword if you quote the alias. The query SELECT SUM(sales) AS "Sum" FROM titles; uses the reserved word SUM as a column alias, for example. For information about keywords, see “SQL Syntax” and “Identifiers” in Chapter 3.
• AS also is used to name derived columns (whose values are determined by expressions other than simple column names); see “Creating Derived Columns” in Chapter 5.
• You also can create table aliases with AS; see “Creating Table Aliases with AS” in Chapter 7.

• Microsoft Access and PostgreSQL require the AS keyword for column references.

  Oracle and Db2 display unquoted column names and aliases in uppercase.

  sqlplus (Oracle’s command-line processor) truncates column aliases to the number of characters specified in the table’s column definitions. The column alias "Postal code" displays as Posta in a CHAR(5) column, for example.

  DBMSs have restrictions on embedded spaces, punctuation, and special characters in aliases; search your DBMS documentation for SELECT or AS.

Eliminating Duplicate Rows with DISTINCT

Columns often contain duplicate values, and it’s common to want a result that lists each duplicate only once. If I type Listing 4.6 to list the states where the authors live, then the result, Figure 4.6, contains unneeded duplicates.

The DISTINCT keyword eliminates duplicate rows from a result. Note that the columns of a DISTINCT result form a candidate key (unless they contain nulls).

To eliminate duplicate rows:

• Type:

  SELECT DISTINCT columns
  FROM table;

  columns is one or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.7 and Figure 4.7).

If the SELECT DISTINCT clause contains more than one column, then the values of all the specified columns combined determine the uniqueness of rows. The result of Listing 4.8 is Figure 4.8, which contains a duplicate row that has two columns. The result of Listing 4.9 is Figure 4.9, which eliminates the two-column duplicate.

Tips for DISTINCT

• Although nulls never equal each other because their values are unknown, DISTINCT considers all nulls to be duplicates of each other. SELECT DISTINCT returns only one null in a result, regardless of how many nulls it encounters; see “Nulls” in Chapter 3.

• The SELECT statement syntax includes the optional ALL keyword. You rarely see ALL in practice because it denotes the default behavior: display all rows, including duplicates.

  SELECT columns FROM table;

  is equivalent to:

  SELECT ALL columns FROM table;

  The syntax diagram is:

  SELECT [ALL | DISTINCT] columns
  FROM table;

• If a table has a properly defined primary key, then SELECT DISTINCT * FROM table; and SELECT * FROM table; return identical results because all rows are unique.
• See also “Aggregating Distinct Values with DISTINCT” in Chapter 6.
• For DISTINCT operations, the DBMS performs an internal sort to identify and remove duplicate rows. Sorting is computationally expensive—don’t use DISTINCT if you don’t have to.

Sorting Rows with ORDER BY

Rows in a query result are unordered, so you should view the order in which rows appear as being arbitrary. This situation arises because the relational model posits that row order is irrelevant for table operations. You can use the ORDER BY clause to sort rows by a specified column or columns in ascending (lowest to highest) or descending (highest to lowest) order; for details, see “Sort Order” in this section. The ORDER BY clause always is the last clause in a SELECT statement.

To sort by a column:

• Type:

  SELECT columns
  FROM table
  ORDER BY sort_column [ASC | DESC];

  columns is one or more comma-separated column names, sort_column is the name of the column on which to sort the result, and table is the name of the table that contains columns and sort_column. (sort_column doesn’t have to be in listed in columns.) Specify ASC for an ascending sort or DESC for a descending sort. If no sort direction is specified, then ASC is assumed (Listings 4.10 and 4.11, Figures 4.10 and 4.11).

To sort by multiple columns:

• Type:

  SELECT columns
  FROM table
  ORDER BY
    sort_column1 [ASC | DESC],
    sort_column2 [ASC | DESC],
    ...
    sort_columnN [ASC | DESC];

  columns is one or more comma-separated column names; sort_column1, sort_column2,..., sort_columnN are the names of the columns on which to sort the result; and table is the name of the table that contains columns and the sort columns. (The sort columns don’t have to be in listed in columns.) Rows are sorted first by sort_column1; then rows that have equal values in sort_column1 are sorted by the values in sort_column2, and so on. For each sort column, specify ASC for an ascending sort or DESC for a descending sort. If no sort direction is specified, then ASC is assumed (Listing 4.12 and Figure 4.12).

You can specify relative column-position numbers instead of column names in ORDER BY. The position numbers refer to the columns in the result, not the original table. Using column positions saves typing, but the resulting code is unclear and invites mistakes if you reorder the columns in the SELECT clause.

To sort by relative column positions:

• Type:

  SELECT columns
  FROM table
  ORDER BY
    sort_num1 [ASC | DESC],
    sort_num2 [ASC | DESC],
    ...
    sort_numN [ASC | DESC];

  columns is one or more comma-separated column names; and sort_num1, sort_num2,..., sort_numN are integers between 1 and the number of columns in columns, inclusive. Each integer specifies the relative position of a column in columns. table is the name of the table that contains columns. (The sort numbers can’t refer to a column that’s not listed in columns.) The sort order is the same order described in “To sort by multiple columns” earlier in this section (Listing 4.13 and Figure 4.13).

Tips for ORDER BY

• You can sort by columns that aren’t listed in the SELECT clause (Listing 4.15 and Figure 4.15). This technique won’t work for relative column positions.

  Listing 4.15zip doesn’t appear in the list of columns to retrieve. See Figure 4.15 for the result.

  SELECT city, state
  FROM authors
  ORDER BY zip ASC;

  Figure 4.15Result of Listing 4.15. This result is sorted by ascending zip code (postal code). Rows might appear to be in random order if you sort by an undisplayed column, confusing your end user.

  city          state
------------- -----
New York      NY
Bronx         NY
Sarasota      FL
Boulder       CO
San Francisco CA
San Francisco CA
Palo Alto     CA


• You can specify column aliases instead of column names in ORDER BY (Listing 4.16 and Figure 4.16). See “Creating Column Aliases with AS” earlier in this chapter.

  Listing 4.16This query uses column aliases in the ORDER BY clause. See Figure 4.16 for the result.

  SELECT
    au_fname AS "First Name",
    au_lname AS "Last Name",
    state
  FROM authors
  ORDER BY
    state        ASC,
    "Last Name"  ASC,
    "First Name" ASC;

  Figure 4.16Result of Listing 4.16.

  First Name Last Name   state
---------- ----------- -----
Hallie     Hull        CA
Klee       Hull        CA
           Kellsey     CA
Wendy      Heydemark   CO
Paddy      O'Furniture FL
Sarah      Buchman     NY
Christian  Kells       NY

• You can specify the same column multiple times in ORDER BY (but that’s silly).
• If the ORDER BY columns don’t identify each row uniquely in the result, then rows with duplicate values will be listed in arbitrary order. Although that’s the case in some of my examples (refer to Figures 4.10, 4.12, and 4.13), you should include enough ORDER BY columns to identify rows uniquely, particularly if the result is to be displayed to an end user.
• According to the SQL standard, the ORDER BY clause is part of a CURSOR declaration and not the SELECT statement. Cursors, which allow result sets to be processed one row at a time, are beyond the scope of this book. All SQL implementations let you to use ORDER BY in a SELECT statement (because the DBMS builds a cursor invisibly). Standard SQL also lets ORDER BY appear in window functions; search your DBMS documentation for window functions.

• To sort based on conditional logic, add a CASE expression to the ORDER BY clause (see “Evaluating Conditional Values with CASE” in Chapter 5). For example, this query sorts by price if type is “history”; otherwise, it sorts by sales:

  SELECT title_id, type, price, sales
  FROM titles
  ORDER BY CASE WHEN type = 'history'
    THEN price ELSE sales END;

• You can sort by the results of expressions; Chapter 5 describes how to create expressions by using functions and operators (Listing 4.17 and Figure 4.17).

  Listing 4.17This query sorts by an expression. See Figure 4.17 for the result. I’ve created a column alias for the expression because it would be cumbersome to repeat the expression in the ORDER BY clause and because it creates a more meaningful column label in the result.

  SELECT
    title_id,
    price,
    sales,
    price * sales AS "Revenue"
  FROM titles
  ORDER BY "Revenue" DESC;

  Figure 4.17Result of Listing 4.17. This result lists titles by descending revenue (the product of price and sales).

  title_id price sales   Revenue
-------- ----- ------- -----------
T07      23.95 1500200 35929790.00
T05       6.95  201440  1400008.00
T12      12.99  100001  1299012.99
T03      39.95   25667  1025396.65
T11       7.99   94123   752042.77
T13      29.99   10467   313905.33
T06      19.95   11320   225834.00
T02      19.95    9566   190841.70
T04      12.99   13001   168882.99
T09      13.95    5000    69750.00
T08      10.00    4095    40950.00
T01      21.99     566    12446.34
T10       NULL    NULL        NULL

• You can intermingle column names, relative column positions, and expressions in ORDER BY.
• You should create indexes for columns that you sort frequently (see Chapter 12).
• The sequence in which unordered rows appear actually is based on the physical order of rows in the DBMS table. You shouldn’t rely on physical order because it changes often, such as when rows are inserted, updated, or deleted or an index is created.
• Sorting by relative column position is useful in UNION queries; see “Combining Rows with UNION” in Chapter 9.

• DBMSs restrict the columns that can appear in an ORDER BY clause, depending on data type. For example, in Microsoft SQL Server, you can’t sort by nvarchar(max), varchar(max), and varbinary(max) columns; and in Oracle, you can’t sort by blob, clob, nclob, and bfile columns. Search your DBMS documentation for SELECT or ORDER BY.

  In Microsoft Access you can’t use an expression’s column alias in ORDER BY. To run Listing 4.17, either retype the expression in the ORDER BY clause:

  ORDER BY price * sales DESC

  or use the relative column position:

  ORDER BY 4 DESC

Filtering Rows with WHERE

The result of each SELECT statement so far has included every row in the table (for the specified columns). You can use the WHERE clause to filter unwanted rows from the result. This filtering capability gives the SELECT statement its real power. In a WHERE clause, you specify a search condition that has one or more conditions that need to be satisfied by the rows of a table. A condition, or predicate, is a logical expression that evaluates to true, false, or unknown. Rows for which the condition is true are included in the result; rows for which the condition is false or unknown are excluded. (An unknown result, which arises from nulls, is described in the next section.) SQL provides operators that express different types of conditions (Table 4.1). Operators are symbols or keywords that specify actions to perform on values or other elements.

SQL’s comparison operators compare two values and evaluate to true, false, or unknown (Table 4.2).

The data type determines how values are compared:

• Character strings are compared lexicographically. < means precedes, and > means follows. See “Data Types” in Chapter 3 and “Sorting Rows with ORDER BY” earlier in this chapter.
• Numbers are compared arithmetically. < means smaller, and > means larger.
• Datetimes are compared chronologically. < means earlier, and > means later. Datetimes must have the same fields (year, month, day, hour, and so on) to be compared meaningfully.

Compare only identical or similar data types. If you try to compare values that have different data types, then your DBMS might:

• Return an error

  or

• Compare the values unequally and return a result with no rows

  or

• Attempt to convert the values to a common type and compare them if successful or return an error if unsuccessful

To filter rows by making a comparison:

• Type:

  SELECT columns
  FROM table
  WHERE test_column op value;

  columns is one or more comma-separated column names, and table is the name of the table that contains columns.

  In the search condition, test_column is the name of a column in table. (test_column doesn’t have to be listed in columns.) op is one of the comparison operators listed in Table 4.2, and value is a value that’s compared with the value in test_column (Listings 4.18, 4.19, and 4.20, Figures 4.18, 4.19, and 4.20).

The right and left sides of the comparison can also be complex expressions. The general form of a comparison is:

expr1 op expr2

expr1 and expr2 are expressions. An expression is any valid combination of column names, literals, functions, and operators that resolves to a single value (per row). Chapter 5 covers expressions in more detail (Listing 4.21 and Figure 4.21).

Tips for WHERE

• Place the WHERE clause before the ORDER BY clause in a SELECT statement in which both appear.
• A null represents the unknown and won’t match anything, not even another null. Rows in which nulls are involved in comparisons won’t be in the result. To compare nulls, use WHERE test_column IS NULL (WHERE test_column = NULL is incorrect); see “Testing for Nulls with IS NULL” later in this chapter. See also “Nulls” in Chapter 3.

• For speed, fold your constants into a minimal number of expressions. For example, change

  WHERE col1 + 2 <= 10

  to

  WHERE col1 <= 8

  The best practice is to put only simple column references to the left of the = and more-complex expressions to the right.

• In general, the fastest comparison is for equality (=), following by the inequalities (<, <=, >, >=). The slowest is not-equal (<>). If possible, express your conditions by using faster comparisons.
• You can’t use an aggregate function such as SUM() or COUNT() in a WHERE clause; see Chapter 6.
• An operation that selects certain rows from a table is called a restriction.

• Your DBMS’s collation determines whether string comparisons are case insensitive (‘A’ = ‘a’) or case sensitive (‘A’ ≠ ‘a’). Microsoft Access, Microsoft SQL Server, Db2, and MySQL perform case-insensitive comparisons by default. Oracle and PostgreSQL perform case-sensitive comparisons by default. In general, case-sensitive comparisons are slightly faster than case-insensitive ones. See also “Changing String Case with UPPER() and LOWER()” in Chapter 5.

  Case sensitivity can vary by context. MySQL comparisons are case insensitive in WHERE comparisons but are case sensitive in string-related functions, for example.

  In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.20, change the date in the WHERE clause to #2001-01-01#.

  In Microsoft SQL Server and Db2 date literals, omit the DATE keyword. To run Listing 4.20, change the date in the WHERE clause to '2001-01-01'.

  In older PostgreSQL versions, to compare a value in a NUMERIC or DECIMAL column with a real (floating-point) number, convert the real number to NUMERIC or DECIMAL explicitly. See “Converting Data Types with CAST()” in Chapter 5.

  Some DBMSs support the comparison operator != as a synonym for <> (not equal). You should use <> to keep your code portable.

Combining and Negating Conditions with AND, OR, and NOT

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators. Logical operators, or boolean operators, are operators designed to work with truth values: true, false, and unknown.

If you’ve programmed in other languages (or studied propositional logic), then you’re familiar with the two-value logic (2VL) system. In two-value logic, the result of a logical expression is either true or false. 2VL assumes perfect knowledge, in which all propositions are known to be true or false. Databases model real data, however, and our knowledge of the world is imperfect—that’s why we use nulls to represent unknown values (see “Nulls” in Chapter 3).

2VL is insufficient to represent knowledge gaps, so SQL uses three-value logic (3VL). In three-value logic, the result of a logical expression is true, false, or unknown. If the result of a compound condition is false or unknown, then the row is excluded from the result. (To retrieve rows with nulls, see “Testing for Nulls with IS NULL” later in this chapter.)

The AND Operator

The AND operator’s important characteristics are:

• AND connects two conditions and returns true only if both conditions are true.

• Table 4.3 shows the possible outcomes when you combine two conditions with AND. The table’s left column shows the truth values of the first condition, the top row shows the truth values of the second condition, and each intersection shows the AND outcome. This type of table is called a truth table.

  Table 4.3AND Truth Table

  AND	True	False	Unknown
  True	True	False	Unknown
  False	False	False	False
  Unknown	Unknown	False	Unknown

• Any number of conditions can be connected with ANDs. All the conditions must be true for the row to be included in the result.

• AND is commutative (independent of order):

  WHERE condition1 AND condition2

  is equivalent to

  WHERE condition2 AND condition1

• You can enclose one or both of the conditions in parentheses. Some compound conditions need parentheses to force the order in which conditions are evaluated.

See Listings 4.22 and 4.23, and Figures 4.22 and 4.23, for some AND examples.

The OR Operator

The OR operator’s important characteristics are:

• OR connects two conditions and returns true if either condition is true or if both conditions are true.

• Table 4.4 shows the OR truth table.

  Table 4.4OR Truth Table

  OR	True	False	Unknown
  True	True	True	True
  False	True	False	Unknown
  Unknown	True	Unknown	Unknown

• Any number of conditions can be connected with ORs. OR will retrieve rows that match any condition or all the conditions.
• Like AND, OR is commutative; the order in which you list the conditions doesn’t matter.
• You can enclose one or both of the conditions in parentheses.

See Listings 4.24 and 4.25, and Figures 4.24 and 4.25, for some OR examples.

Listing 4.25 shows the effect of nulls in conditions. You might expect the result, Figure 4.25, to display all the rows in the table publishers. But the row for publisher P03 (located in Germany) is missing because it contains a null in the column state. The null causes the result of both of the OR conditions to be unknown, so the row is excluded from the result. To test for nulls, see “Testing for Nulls with IS NULL” later in this chapter.

The NOT Operator

The NOT operator’s important characteristics are:

• Unlike AND and OR, NOT doesn’t connect two conditions. Instead, it negates (reverses) a single condition.

• Table 4.5 shows the NOT truth table.

  Table 4.5NOT Truth Table

  Condition	NOT Condition
  True	False
  False	True
  Unknown	Unknown

• In comparisons, place NOT before the column name or expression

  WHERE NOT state = 'CA'     --Correct

  and not before the operator (even though it sounds better when read):

  WHERE state NOT = 'CA'     --Illegal

• NOT acts on one condition. To negate two or more conditions, repeat the NOT for each condition. To list titles that are not biographies and are not priced less than $20, for example, type

  SELECT title_id, type, price
  FROM titles
  WHERE NOT type = 'biography'
    AND NOT price < 20;    --Correct

  and not

  SELECT title_id, type, price
  FROM titles
  WHERE NOT type = 'biography'
    AND price < 20;         --Wrong

  The latter clause is legal but returns the wrong result. See “Tips for AND, OR, and NOT” in this section to learn ways to express equivalent NOT conditions.

• In comparisons, using NOT often is a matter of style. The following two clauses are equivalent:

  WHERE NOT state = 'CA'

  and

  WHERE state <> 'CA'

• You can enclose the condition in parentheses.

See Listings 4.26 and 4.27, and Figures 4.26 and 4.27, for some NOT examples.

Using AND, OR, and NOT Together

You can combine the three logical operators in a compound condition. Your DBMS uses SQL’s precedence rules to determine which operators to evaluate first. Precedence is covered in “Determining the Order of Evaluation” in Chapter 5, but for now you need know only that when you use multiple logical operators in a compound condition, NOT is evaluated first, then AND, and finally OR. You can override this order with parentheses: everything in parentheses is evaluated first. When parenthesized conditions are nested, the innermost condition is evaluated first. Under the default precedence rules, the condition

p AND NOT q OR r

is equivalent to

(p AND (NOT q)) OR r

It’s wise to use parentheses, rather than rely on the default evaluation order, to make the evaluation order clear.

If I want to list history and biography titles priced less than $20, for example, then Listing 4.28 won’t work. AND is evaluated before OR, so the query is evaluated as follows:

1. Find all the biography titles less than $20.
2. Find all the history titles (regardless of price).
3. List both sets of titles in the result (Figure 4.28).

To fix this query, add parentheses to force evaluation of OR first. Listing 4.29 is evaluated as follows:

1. Find all the biography and history titles.
2. Of the titles found in step 1, keep the ones priced less than $20.
3. List the subset of titles in the result (Figure 4.29).

Tips for AND, OR, and NOT

• The examples in this section show the AND, OR, and NOT operators used with comparison conditions, but these operators can be used with any type of condition.

• If your search condition contains only AND operators, then your query will run faster if you put the conditions least likely to be true first. If col1 = 'A' is less likely than col2 = 'B', then

  WHERE col1 = 'A' AND col2 = 'B'

  is faster than

  WHERE col2 = 'B' AND col1 = 'A'

  because the DBMS won’t bother to evaluate the second expression if the first is false. For search conditions that contain only OR operators, do the reverse: put the most likely conditions first. If the conditions are equally likely, then put the least complex expression first.

  This logic depends on your DBMS’s optimizer reading WHERE clauses from left to right, which most do. Oracle’s cost-based optimizer (as opposed to its rule-based optimizer), however, reads right to left.

• It’s a common error to type

  WHERE state = 'NY' OR 'CA' --Illegal

  instead of

  WHERE state = 'NY' OR state = 'CA'

• It’s easy to translate a correctly phrased spoken-language statement into an incorrect SQL statement. If you say, “List the books priced less than $10 and more than $30,” then the and suggests the use of the AND operator:

  SELECT title_name, price
  FROM titles
  WHERE price < 10 AND price > 30; --Wrong

  This query returns no rows, however, because it’s impossible for a book to be priced less than $10 and more than $30 simultaneously, as AND logic commands. The logical meaning of OR finds books that meet any of the criteria, not all the criteria at the same time:

  WHERE price < 10 OR price > 30 --Correct

• Table 4.6 shows alternative ways of expressing the same condition. The first equivalency is double negation, the second two are De Morgan’s Laws, and the final two are the distributive laws.

  Table 4.6Equivalent Conditions

  This Condition	Is Equivalent To
  NOT (NOT p)	p
  NOT (p AND q)	(NOT p) OR (NOT q)
  NOT (p OR q)	(NOT p) AND (NOT q)
  p AND (q OR r)	(p AND q) OR (p AND r)
  p OR (q AND r)	(p OR q) AND (p OR r)

• Some DBMSs support the exclusive-or (or xor) logical operator, which yields true only if exactly one of its operands is true:

  p XOR q

  is equivalent to

  (p AND (NOT q)) OR ((NOT p) AND q)

• In MySQL 4.0.4 and earlier, false AND unknown evaluates to unknown, not false.

Matching Patterns with LIKE

The preceding examples retrieved rows based on the exact value of a column or columns. You can use LIKE to retrieve rows based on partial information. LIKE is useful if you don’t know an exact value (“The author’s last name is Kel-something”) or you want to retrieve rows with similar values (“Which authors live in the San Francisco Bay Area?”). The LIKE condition’s important characteristics are:

• LIKE works with only character strings, not numbers or datetimes.

• LIKE uses a pattern that values are matched against. A pattern is a quoted string that contains the literal characters to match and any combination of wildcards. Wildcards are special characters used to match parts of a value. Table 4.7 lists the wildcard operators, and Table 4.8 lists some example patterns.

  Table 4.7Wildcard Operators

  Operator	Matches
  %	A percent sign matches any string of zero or more characters.
  _	An underscore matches any one character.

  Table 4.8Examples of % and _ Patterns

  Pattern	Matches
  'A%'	Matches a string of length ≥ 1 that begins with A, including the single letter A. Matches ‘A’, ‘Anonymous’, and ‘AC/DC’.
  '%s'	Matches a string of length ≥ 1 that ends with s, including the single letter s. A string with trailing spaces (after the s) won’t match. Matches ‘s’, ‘Victoria Falls’, and ‘DBMSs’.
  '%in%'	Matches a string of length ≥ 2 that contains in anywhere. Matches ‘in’, ‘inch’, ‘Pine’, ‘linchpin’, and ‘lynchpin’.
  '____'	Matches any four-character string. Matches ‘ABCD’, ‘I am’, and ‘Jack’.
  'Qua__'	Matches any five-character string that begins with Qua. Matches ‘Quack’, ‘Quaff’, and ‘Quake’.
  '_re_'	Matches any four-character string that has re as its second and third characters. Matches ‘Tree’, ‘area’, and ‘fret’.
  '_re%'	Matches a string of length ≥ 3 that begins with any character and has re as its second and third characters. Matches ‘Tree’, ‘area’, ‘fret’, ‘are’, and ‘fretful’.
  '%re_'	Matches a string of length ≥ 3 that has re as the second and third characters from its end and ends with any character. Matches ‘Tree’, ‘area’, ‘fret’, ‘red’, and ‘Blood red’.

• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” earlier in this chapter.
• You can negate a LIKE condition with NOT LIKE.
• You can combine LIKE conditions and other conditions with AND and OR.

To filter rows by matching a pattern:

• Type:

  SELECT columns
  FROM table
  WHERE test_column [NOT] LIKE 'pattern';

  columns is one or more comma-separated column names, and table is the name of the table that contains columns.

  In the search condition, test_column is the name of a column in table (test_column doesn’t have to be listed in columns), and pattern is the pattern that’s compared with the value in test_column. pattern is a string like one of the examples listed in Table 4.8. To retrieve rows with values that don’t match pattern, specify NOT LIKE (Listings 4.30 through 4.33, Figures 4.30 through 4.33).

You can search for values that contain the special wildcard characters. Use the ESCAPE keyword to specify an escape character that you can use to search for a percent sign or underscore as a literal character. Immediately precede a wildcard character with an escape character to strip the wildcard of its special meaning. If the escape character is !, for example, then !% in a pattern searches values for a literal %. (Unescaped wildcards still have their special meaning.) The escape character can’t be part of the value that you’re trying to retrieve; if you’re searching for '50% OFF!', then choose an escape character other than !. Table 4.9 shows some examples of escaped and unescaped patterns; the designated escape character is !.

To match a wildcard character:

• Type:

  SELECT columns
  FROM table
  WHERE test_column [NOT] LIKE 'pattern'
    ESCAPE 'escape_char';

  The syntax is the same as the SELECT statement in “To filter rows by matching a pattern,” earlier in this section, except for the ESCAPE clause. escape_char is a single character. Any character in pattern that follows escape_char is interpreted literally; escape_char itself is not considered to be part of the search pattern (Listing 4.34 and Figure 4.34).

Tips for LIKE

• test_column can be an expression.

• The NOT that can precede LIKE is independent of the NOT that can precede test_column (see “The NOT Operator” earlier in this chapter). The clause

  WHERE phone NOT LIKE '212-%'

  is equivalent to

  WHERE NOT phone LIKE '212-%'

  You even can write this silly double negation, which retrieves everyone with a 212 area code:

  WHERE NOT phone NOT LIKE '212-%'

• Wildcard searches are time-consuming—particularly if you use % at the start of a pattern. Don’t use wildcards if another type of search will do.

• In the simplest case in which a pattern contains no wildcards, LIKE works like an = comparison (and NOT LIKE works like <>). In many cases

  WHERE city LIKE 'New York'

  is equivalent to

  WHERE city = 'New York'

  But these comparisons will differ if your DBMS takes trailing spaces into account for LIKE but not for =. If that’s not important, then the = form is usually faster than LIKE.

• Microsoft Access doesn’t support the ESCAPE clause. Instead, surround a wildcard character with brackets to render it a literal character. To run Listing 4.34, replace the WHERE clause with:

  WHERE title_name LIKE '%[%]%'

  Some DBMSs let you use regular expressions to match patterns. Microsoft SQL Server, for example, supports a limited variant of POSIX-style regular expressions. The [] wildcard matches any single character within a range or set, and the [^] wildcard matches any single character not within a range or set. Table 4.10 lists some examples. The SQL standard uses the SIMILAR operator for regex matching. Regex support varies by DBMS; search your DBMS documentation for LIKE, regular expressions, or pattern matching.

  Some DBMSs let you use LIKE to search numeric and datetime columns.

  Table 4.10Examples of [] and [^] Patterns

  Pattern	Matches
  '[a-c]at'	Matches ‘bat’ and ‘cat’ but not ‘fat’.
  '[bcf]at'	Matches ‘bat’, ‘cat’, and ‘fat’ but not ‘eat’.
  '[^c]at'	Matches ‘bat’ and ‘fat’ but not ‘cat’.
  'se[^n]%'	Matches strings of length ≥ 2 that begin with se and whose third character isn‘t n.

Range Filtering with BETWEEN

Use BETWEEN to determine whether a given value falls within a specified range. The BETWEEN condition’s important characteristics are:

• BETWEEN works with character strings, numbers, and datetimes.
• The BETWEEN range contains a low value and a high value, separated by AND. The low value must be less than or equal to the high value.

• BETWEEN is a convenient, shorthand clause that you can replicate by using AND.

  WHERE test_column BETWEEN
      low_value AND high_value

  is equivalent to:

  WHERE (test_column >= low_value)
  AND (test_column <= high_value)

• BETWEEN specifies an inclusive range, in which the high value and low value are included in the search. To specify an exclusive range, which excludes endpoints, use > and < comparisons instead of BETWEEN:

  WHERE (test_column > low_value)
  AND (test_column < high_value)

• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” earlier in this chapter.
• You can negate a BETWEEN condition with NOT BETWEEN.
• You can combine BETWEEN conditions and other conditions with AND and OR.

To filter rows by using a range:

• Type:

  SELECT columns
  FROM table
    WHERE test_column [NOT] BETWEEN
          low_value AND high_value;

  columns is one or more comma-separated column names, and table is the name of the table that contains columns.

  In the search condition, test_column is the name of a column in table (test_column doesn’t have to be listed in columns), and low_value and high_value specify the endpoints of the range that is compared with the value in test_column. low_value must be less than or equal to high_value, and both values must be the same as or comparable to the data type of test_column. To match values that lie outside the range, specify NOT BETWEEN (Listings 4.35, 4.36, and 4.37, Figures 4.35, 4.36, and 4.37).

Tips for BETWEEN

• test_column can be an expression.
• The NOT that can precede BETWEEN is independent of the NOT that can precede test_column; see “Tips for LIKE” earlier in this chapter.

• Listing 4.38 shows how to rewrite Listing 4.36 with an exclusive range, which doesn’t include the $10 and $19.95 endpoints. See Figure 4.38 for the result.

  Listing 4.38List the titles priced between $10 and $19.95, exclusive. See Figure 4.38 for the result.

  SELECT title_id, price
  FROM titles
  WHERE (price > 10)
    AND (price < 19.95);

  Figure 4.38Result of Listing 4.38.

  title_id price
-------- -----
T04      12.99
T09      13.95
T12      12.99

• Specifying a character range often needs some thought. Suppose you want to search for last names that begin with the letter F. The following clause won’t work because it will retrieve someone whose last name is the letter G (is the letter G, not starts with the letter G):

  WHERE last_name BETWEEN 'F' AND 'G'

  This next clause shows the correct way to specify the ending point (in most cases):

  WHERE last_name BETWEEN 'F' AND 'Fz'

• In older PostgreSQL versions, convert the floating-point numbers in Listings 4.36 and 4.38 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listings 4.36 and 4.38, change the floating-point literals to:

  CAST(19.95 AS DECIMAL)

  In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.37, change the dates in the WHERE clause to #2000-01-01# and #2000-12-31#.

  In Microsoft SQL Server and Db2 date literals, omit the DATE keyword. To run Listing 4.37, change the dates in the WHERE clause to '2000-01-01' and '2000-12-31'.

  In some DBMSs, low_value can exceed high_value; search your DBMS documentation for WHERE or BETWEEN.

List Filtering with IN

Use IN to determine whether a given value matches any value in a specified list. The IN condition’s important characteristics are:

• IN works with character strings, numbers, and datetimes.
• The IN list is a parenthesized listing of one or more comma-separated values. The list items needn’t be in any particular order.

• IN is a convenient, shorthand clause that you can replicate by using OR.

  WHERE test_column IN
      (value1, value2, value3)

  is equivalent to:

  WHERE (test_column = value1)
   OR (test_column = value2)
   OR (test_column = value3)

• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” earlier in this chapter.
• You can negate an IN condition with NOT IN.
• You can combine IN conditions and other conditions with AND and OR.

To filter rows by using a list:

• Type:

  SELECT columns
  FROM table
  WHERE test_column [NOT] IN
      (value1, value2,...);

  columns is one or more comma-separated column names, and table is the name of the table that contains columns.

  In the search condition, test_column is the name of a column in table (test_column doesn’t have to be listed in columns), and value1, value2,... are one or more comma-separated values that are compared with the value in test_column. The list values can appear in any order and must be the same as or comparable to the data type of test_column. To match values that aren’t in the list, specify NOT IN (Listings 4.39, 4.40, and 4.41, Figures 4.39, 4.40, and 4.41).

Tips for IN

• test_column can be an expression.
• The NOT that can precede IN is independent of the NOT that can precede test_column; see “Tips for LIKE” earlier in this chapter.
• If your list contains a large number of values, then your code will be easier to read if you use one IN condition instead of many OR conditions. (Also, one IN usually runs faster than multiple ORs.)

• For speed, list the most likely values first. If you’re testing U.S. addresses, for example, then list the most populous states first:

  WHERE state IN ('CA', 'TX', 'NY', 'FL',...,'VT', 'DC', 'WY')

• The search condition

  WHERE col1 BETWEEN 1 AND 5
  AND col1 <> 3

  usually is faster than

  WHERE col1 IN (1, 2, 4, 5)

• A compound condition’s order of evaluation is easier to read and manage if you use IN instead of multiple ORs; see “Combining and Negating Conditions with AND, OR, and NOT” earlier in this chapter.
• You also can use IN to determine whether a given value matches any value in a subquery; see Chapter 8.

• NOT IN is equivalent to combining tests for inequality with AND. This statement is equivalent to Listing 4.39:

  SELECT au_fname, au_lname, state
  FROM authors
  WHERE state <> 'NY'
    AND state <> 'NJ'
    AND state <> 'CA';

• In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.41, change the WHERE clause to:

  WHERE pubdate IN
      (#1/1/2000#,
       #1/1/2001#,
       #1/1/2002#)

  In Microsoft SQL Server and Db2 date literals, omit the DATE keyword. To run Listing 4.41, change the WHERE clause to:

  WHERE pubdate IN
      ('2000-01-01',
       '2001-01-01',
       '2002-01-01')

  In older PostgreSQL versions, convert the floating-point number in Listing 4.40 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listing 4.40, change the WHERE clause to:

  WHERE advance IN
      (CAST(   0.00 AS DECIMAL),
       CAST(1000.00 AS DECIMAL),
       CAST(5000.00 AS DECIMAL))

Testing for Nulls with IS NULL

Recall from “Nulls” in Chapter 3 that nulls represent missing or unknown values. This situation causes a problem: LIKE, BETWEEN, IN, and other WHERE-clause conditions can’t find nulls because unknown values don’t satisfy specific conditions. A null matches no value—not even other nulls. You can’t use = or <> to test whether a value is null.

In the table publishers, for example, note that publisher P03 has a null in the column state because that column doesn’t apply to Germany (Listing 4.42 and Figure 4.42). I can’t use complementary comparisons to select the null because null is neither California nor not-California; it’s undefined (Listings 4.43 and 4.44, Figures 4.43 and 4.44).

To avert disaster, SQL provides IS NULL to determine whether a given value is null. The IS NULL condition’s important characteristics are:

• IS NULL works for columns of any data type.
• You can negate an IS NULL condition with IS NOT NULL.
• You can combine IS NULL conditions and other conditions with AND and OR.

To retrieve rows with nulls or non-null values:

• Type:

  SELECT columns
  FROM table
  WHERE test_column IS [NOT] NULL;

  columns is one or more comma-separated column names, and table is the name of the table that contains columns.

  In the search condition, test_column is the name of a column in table. (test_column doesn’t have to be listed in columns.) To match non-null values, specify NOT NULL (Listings 4.45 and 4.46, Figures 4.45 and 4.46).

Tips for IS NULL

• test_column can be an expression.
• The NOT that can precede NULL is independent of the NOT that can precede test_column; see “Tips for LIKE” earlier in this chapter.

• Nulls cause rows to be excluded from results only if a column containing nulls is a test column in a WHERE condition. The following query, for example, retrieves all the rows in the table publishers (refer to Figure 4.42) because the null in the column state isn’t compared with anything:

  SELECT pub_id, city, state, country
  FROM publishers
  WHERE country <> 'Canada';

  To forbid nulls in a column, see “Forbidding Nulls with NOT NULL” in Chapter 11.

• It bears repeating that a null isn’t the same as an empty string (''). In the table authors, for example, the column au_fname contains an empty string for author A06 (last name of Kellsey). The WHERE condition to find the first name is

  WHERE au_fname = ''

  and not

  WHERE au_fname IS NULL

• Oracle treats an empty string ('') as a null; see the DBMS tip in “Tips for Nulls” in Chapter 3.

5. Operators and Functions

Operators and functions let you calculate results derived from column values, system-determined values, constants, and other data. You can perform:

• Arithmetic operations—Cut everyone’s salary by 10 percent.
• String operations—Concatenate personal information into a mailing address.
• Datetime operations—Compute the time interval between two dates.
• System operations—Find out what time your DBMS thinks it is.

An operator is a symbol or keyword indicating an operation that acts on one or more elements. The elements, called operands, are SQL expressions. Recall from “Tips for SQL Syntax” in Chapter 3 that an expression is any legal combination of symbols and tokens that evaluates to a single value (or null). In the expression price * 2, for example, * is the operator, and price and 2 are its operands.

A function is a built-in, named routine that performs a specialized task. Most functions take parenthesized arguments, which are values you pass to the function that the function then uses to perform its task. Arguments can be column names, literals, nested functions, or more-complex expressions. In UPPER(au_lname), for example, UPPER is the function name, and au_lname is the argument.

Creating Derived Columns

You can use operators and functions to create derived columns. A derived column is the result of a calculation and is created with a SELECT-clause expression that is something other than a simple reference to a column. Derived columns don’t become permanent columns in a table; they’re for display and reporting purposes.

The values in a derived column are often computed from values in existing columns, but you can also create a derived column by using a constant expression (such as a string, number, or date) or system value (such as the system time). Listing 5.1 shows a SELECT statement that yields a trivial arithmetic calculation; it needs no FROM clause because it doesn’t retrieve data from a table. Figure 5.1 shows the result.

Recall from “Tables, Columns, and Rows” in Chapter 2 that closure guarantees that every result is a table, so even this simple result is a table: a 1 × 1 table that contains the value 5. If I retrieve a column along with a constant, then the constant appears in every row of the result (Listing 5.2 and Figure 5.2).

Your DBMS will assign the derived column a default name, typically the expression itself as a quoted identifier. You should name derived columns explicitly with an AS clause because system-assigned names can be long, unwieldy, and inconvenient for database applications to refer to; see “Creating Column Aliases with AS” in Chapter 4 (Listing 5.3 and Figure 5.3).

Tips for Derived Columns

• Oracle requires a FROM clause in a SELECT statement and so creates the dummy table DUAL automatically to be used for SELECTing a constant expression; search Oracle documentation for DUAL table. To run Listing 5.1, add a FROM clause that selects the constant value from DUAL:

  SELECT 2 + 3
  FROM DUAL;

  Db2 requires a FROM clause in a SELECT statement and so creates the dummy table SYSIBM.SYSDUMMY1 automatically to be used for SELECTing a constant expression; search Db2 documentation for SYSIBM.SYSDUMMY1. To run Listing 5.1, add a FROM clause that selects the constant value from SYSIBM.SYSDUMMY1:

  SELECT 2 + 3
  FROM SYSIBM.SYSDUMMY1;

  In older PostgreSQL versions, convert the floating-point number in Listing 5.3 to DECIMAL; see “Converting Data Types with CAST()” later in this chapter. To run Listing 5.3, change the New price calculation in the SELECT clause to:

  price * CAST((1 - 0.10) AS DECIMAL)

Performing Arithmetic Operations

A monadic (or unary) arithmetic operator performs a mathematical operation on a single numeric operand to produce a result. The – (negation) operator changes the sign of its operand, and the not-very-useful + (identity) operator leaves its operand unchanged. A dyadic (or binary) arithmetic operator performs a mathematical operation on two numeric operands to produce a result. These operators include the usual ones: + (addition), – (subtraction), * (multiplication), and / (division). Table 5.1 lists SQL’s arithmetic operators (expr is a numeric expression).

To change the sign of a number:

• Type -expr

  expr is a numeric expression (Listing 5.4 and Figure 5.4).

To add, subtract, multiply, or divide:

• Type expr1 + expr2 to add, expr1 - expr2 to subtract, expr1 * expr2 to multiply, or expr1 / expr2 to divide.

  expr1 and expr2 are numeric expressions (Listing 5.5 and Figure 5.5).

Tips for Arithmetic Operations

• The result of any arithmetic operation that involves a null is null.
• If you use multiple operators in a single expression, then you might need to use parentheses to control the calculation order; see “Determining the Order of Evaluation” later in this chapter.
• If you mix numeric data types in an arithmetic expression, then your DBMS converts, or coerces, all the numbers to the data type of the expression’s most complex operand and returns the result in this type. This conversion process is called promotion. If you add an INTEGER and a FLOAT, for example, then the DBMS converts the integer to a float, adds the numbers, and returns the sum as a float. In some cases, you must convert a data type to another data type explicitly; see “Converting Data Types with CAST()” later in this chapter.
• If you’re writing a database application or UPDATEing rows, then note that data types aren’t closed for some arithmetic operations. If you multiply or add two SMALLINTs, for example, then the result might be greater than a SMALLINT column can hold. Similarly, dividing two INTEGERs doesn’t necessarily yield an INTEGER.

• Sometimes DBMSs force mathematical closure, so be careful when dividing integers by integers. If an integer dividend is divided by an integer divisor, then the result might be an integer that has any fractional part of the result truncated. You might expect the two derived columns in Listing 5.6 to contain the same values because the column pages (an INTEGER) is divided by two equal constants: 10 (an integer) and 10.0 (a float). Microsoft Access, Oracle, and MySQL return the result you’d expect (Figure 5.6a), but Microsoft SQL Server, Db2, and PostgreSQL truncate the result of an integer division (Figure 5.6b).

  Listing 5.6This query’s first derived column divides pages by the integer constant 10, and the second derived column divides pages by the floating-point constant 10.0. In the result, you’d expect identical values to be in both derived columns. See Figures 5.6a and 5.6b for the results.

  SELECT
    title_id,
    pages,
    pages/10   AS "pages/10",
    pages/10.0 AS "pages/10.0"
  FROM titles;


  Figure 5.6aResult of Listing 5.6 for Microsoft Access, Oracle, and MySQL. Dividing two integers yields a floating-point number (as you’d expect).

  title_id pages pages/10 pages/10.0
-------- ----- -------- ----------
T01        107     10.7       10.7
T02         14      1.4        1.4
T03       1226    122.6      122.6
T04        510     51.0       51.0
T05        201     20.1       20.1
T06        473     47.3       47.3
T07        333     33.3       33.3
T08         86      8.6        8.6
T09         22      2.2        2.2
T10       NULL     NULL       NULL
T11        826     82.6       82.6
T12        507     50.7       50.7
T13        802     80.2       80.2

  Figure 5.6bResult of Listing 5.6 for Microsoft SQL Server, Db2, and PostgreSQL. Dividing two integers yields an integer; the fractional part of the result is discarded (not as you’d expect).

  title_id pages pages/10 pages/10.0
-------- ----- -------- ----------
T01        107       10       10.7
T02         14        1        1.4
T03       1226      122      122.6
T04        510       51       51.0
T05        201       20       20.1
T06        473       47       47.3
T07        333       33       33.3
T08         86        8        8.6
T09         22        2        2.2
T10       NULL     NULL       NULL
T11        826       82       82.6
T12        507       50       50.7
T13        802       80       80.2


Determining the Order of Evaluation

Precedence determines the priority of various operators when more than one operator is used in an expression. Operators with higher precedence are evaluated first. Arithmetic operators (+, –, *, and so on) have higher precedence than comparison operators (<, =, >, and so on), which have higher precedence than logical operators (NOT, AND, OR), so the expression

a or b * c >= d

is equivalent to

a or ((b * c) >= d)

Operators with lower precedence are less binding than those with higher precedence. Table 5.2 lists operator precedences from most to least binding. Operators in the same row have equal precedence.

Associativity determines the order of evaluation in an expression when adjacent operators have equal precedence. SQL uses left-to-right associativity.

You don’t need to memorize all this information. You can use parentheses to override precedence and associativity rules (Listing 5.7 and Figure 5.7).

Tips for Order of Evaluation

• It’s good programming style to add parentheses (even when they’re unnecessary) to complex expressions to ensure your intended evaluation order and make code more portable and easier to read.

• Table 5.2 is incomplete; it omits some standard (such as IN and EXISTS) and nonstandard (DBMS-specific) operators. To determine the complete order of evaluation that your DBMS uses, search your DBMS documentation for precedence.

  To run Listing 5.7 in Oracle, add the clause FROM DUAL. To run it in Db2, add the clause FROM SYSIBM.SYSDUMMY1. See the DBMS tip in “Tips for Derived Columns” earlier in this chapter.

Concatenating Strings with ||

Use the operator || to combine, or concatenate, strings. The operator’s important characteristics are:

• The operator || is two consecutive vertical-bar, or pipe, characters.
• Concatenation doesn’t add a space between strings.
• ||, a dyadic (binary) operator, combines two strings into a single string: 'formal' || 'dehyde' is 'formaldehyde'.
• You can chain concatenations to combine multiple strings into a single string: 'a' || 'b' || 'c' || 'd' is 'abcd'.
• Concatenation with an empty string ('') leaves a string unchanged: 'a' || '' || 'b' is 'ab'.
• The result of any concatenation operation that involves a null is null: 'a' || NULL || 'b' is NULL. (But see the Oracle exception in the DBMS tip in “Tips for Concatenating Strings”.)
• To concatenate a string and a nonstring (such as a numeric or datetime value), you must convert the nonstring to a string if your DBMS doesn’t convert it implicitly; see “Converting Data Types with CAST()” later in this chapter.

To concatenate strings:

• Type:

  string1 || string2

  string1 and string2 are the strings to be combined. Each operand is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string (Listings 5.8 through 5.11, Figures 5.8 through 5.11).

Tips for Concatenating Strings

• You can use || in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• You can concatenate hex and bit strings: B'0100' || B'1011' is B'01001011'.

• Listing 5.11 shows how to use || in a WHERE clause, but it’s actually bad SQL. The efficient way to express the clause is:

  WHERE au_fname = 'Klee'
  AND au_lname = 'Hull'

• You can use the TRIM() function to remove unwanted spaces from concatenated strings. Recall from “Character String Types” in Chapter 3 that CHAR values are padded with trailing spaces, sometimes creating long, ugly stretches of spaces in concatenated strings. TRIM() will remove the extra space in front of the name Kellsey in Figure 5.8, for example; see “Trimming Characters with TRIM()” later in this chapter.

• In Microsoft Access, the concatenation operator is +, and the conversion function is Format(string). To run Listings 5.8 through 5.11, change the concatenation and conversion expressions to (Listing 5.8):

  au_fname + ' ' + au_lname

  and (Listing 5.9):

  Format(sales) + ' copies sold of title ' + title_id

  and (Listing 5.10):

  'Title ' + title_id + ' published on ' + Format(pubdate)

  and (Listing 5.11):

  au_fname + ' ' + au_lname = 'Klee Hull';

  In Microsoft SQL Server, the concatenation operator is +. To run Listings 5.8 through 5.11, change the concatenation expressions to (Listing 5.8):

  au_fname + ' ' + au_lname

  and (Listing 5.9):

  CAST(sales AS CHAR(7)) + ' copies sold of title ' + title_id

  and (Listing 5.10):

  'Title ' + title_id + ' published on ' + CAST(pubdate AS CHAR(10))

  and (Listing 5.11):

  au_fname + ' ' + au_lname = 'Klee Hull';

  In MySQL, the concatenation function is CONCAT(). The || operator is legal, but it means logical OR in MySQL by default. (Use PIPES_AS_CONCAT mode to treat || as a string-concatenation operator rather than as a synonym for OR.) CONCAT() takes any number of arguments and converts nonstrings to strings as necessary (so CAST() isn’t needed). To run Listings 5.8 through 5.11, change the concatenation expressions to (Listing 5.8):

  CONCAT(au_fname, ' ', au_lname)

  and (Listing 5.9):

  CONCAT(sales, ' copies sold of title ', title_id)

  and (Listing 5.10):

  CONCAT('Title ', title_id, ' published on ', pubdate)

  and (Listing 5.11):

  CONCAT(au_fname, ' ', au_lname) = 'Klee Hull';

  For string operations, Oracle treats a null as an empty string: 'a' || NULL || 'b' returns 'ab' (not NULL). See the DBMS tip in “Tips for Nulls” in Chapter 3.

  Oracle, MySQL, and PostgreSQL convert nonstrings to strings implicitly in concatenations; Listings 5.9 and 5.10 still will run on these DBMSs if you omit CAST(). Search your DBMS documentation for concatenation or conversion.

  Microsoft SQL Server, Oracle, Db2, and PostgreSQL also support the CONCAT() function.

Extracting a Substring with SUBSTRING()

Use the function SUBSTRING() to extract part of a string. The function’s important characteristics are:

• A substring is any sequence of contiguous characters from the source string, including an empty string or the entire source string itself.
• SUBSTRING() extracts part of a string starting at a specified position and continuing for a specified number of characters.
• A substring of an empty string is an empty string.
• If any argument is null, then SUBSTRING() returns null. (But see the Oracle exception in the DBMS tip in “Tips for Substrings”.)

To extract a substring:

• Type:

  SUBSTRING(string FROM start [FOR length])

  string is the source string from which to extract the substring. string is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string. start is an integer that specifies where the substring begins, and length is an integer that specifies the length of the substring (the number of characters to return). start starts counting at 1. If FOR length is omitted, then SUBSTRING() returns all the characters from start to the end of string (Listings 5.12, 5.13, and 5.14, Figures 5.12, 5.13, and 5.14).

Tips for Substrings

• You can use SUBSTRING() in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• You can extract substrings from hex and bit strings: SUBSTRING(B'01001011' FROM 5 FOR 4) returns B'1011'.

• In Microsoft Access, the substring function is Mid(string, start [,length]). Use + to concatenate strings. To run Listings 5.12 through 5.14, change the substring expressions to (Listing 5.12):

  Mid(pub_id, 1, 1)
Mid(pub_id, 2)

  and (Listing 5.13):

  Mid(au_fname, 1, 1) + '. ' + au_lname

  and (Listing 5.14):

  Mid(phone, 1, 3) = '415'

  In Microsoft SQL Server, the substring function is SUBSTRING(string, start, length). Use + to concatenate strings. To run Listings 5.12 through 5.14, change the substring expressions to (Listing 5.12):

  SUBSTRING(pub_id, 1, 1)
SUBSTRING(pub_id, 2, LEN(pub_id)-1)

  and (Listing 5.13):

  SUBSTRING(au_fname, 1, 1) + '. ' + au_lname

  and (Listing 5.14):

  SUBSTRING(phone, 1, 3) = '415'

  In Oracle and Db2, the substring function is SUBSTR(string, start [,length]). To run Listings 5.12 through 5.14, change the substring expressions to (Listing 5.12):

  SUBSTR(pub_id, 1, 1)
SUBSTR(pub_id, 2)

  and (Listing 5.13):

  SUBSTR(au_fname, 1, 1) || '. ' || au_lname

  and (Listing 5.14):

  SUBSTR(phone, 1, 3) = '415'

  In MySQL, use CONCAT() to run Listing 5.13 (see “Concatenating Strings with ||” earlier in this chapter). Change the concatenation expression to:

  CONCAT(SUBSTRING(au_fname FROM 1 FOR 1), '. ', au_lname)

  For string operations, Oracle treats a null as an empty string: SUBSTR(NULL, 1, 2) returns '' (not NULL). See the DBMS tip in “Tips for Nulls” in Chapter 3.

  Your DBMS implicitly might constrain start and length arguments that are too small or too large to sensible values. The substring function silently might replace a negative start with 1 or a too-long length with the length of string, for example. Search your DBMS documentation for substring or substr.

  MySQL and PostgreSQL also support the SUBSTR(string, start, length) form of the substring function.

Changing String Case with UPPER() and LOWER()

Use the function UPPER() to return a string with lowercase letters converted to uppercase, and use the function LOWER() to return a string with uppercase letters converted to lowercase. The functions’ important characteristics are:

• A cased character is a letter that can be lowercase (a) or uppercase (A).
• Case changes affect only letters. Digits, punctuation, and whitespace are left unchanged.
• Case changes have no effect on empty strings ('').
• If its argument is null, then UPPER() and LOWER() return null. (But see the Oracle exception in the DBMS tip in “Tips for String Case”.)

To convert a string to uppercase or lowercase:

• To convert a string to uppercase, type:

  UPPER(string)

  or

  To convert a string to lowercase, type:

  LOWER(string)

  string is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string (Listings 5.15 and 5.16, Figures 5.15 and 5.16).

Tips for String Case

• You can use UPPER() and LOWER() in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• UPPER() and LOWER() don’t affect character sets with no concept of case (such as Hebrew and Chinese).

• In Microsoft Access, the upper- and lowercase functions are UCase(string) and LCase(string). To run Listings 5.15 and 5.16, change the case expressions to (Listing 5.15):

  LCase(au_fname)
UCase(au_lname)

  and (Listing 5.16):

  UCase(title_name) LIKE '%MO%'

  For string operations, Oracle treats a null as an empty string: UPPER(NULL) and LOWER(NULL) return '' (not NULL). See the DBMS tip in “Tips for Nulls” in Chapter 3.

  Your DBMS might provide other string-casing functions to, say, invert case or convert strings to sentence or title case. Search your DBMS documentation for character functions or string functions.

Trimming Characters with TRIM()

Use the function TRIM() to remove unwanted characters from the ends of a string. The function’s important characteristics are:

• You can trim leading characters, trailing characters, or both. (You can’t use TRIM() to remove characters from within a string.)
• By default, TRIM() trims spaces, but you can strip off any unwanted characters, such as leading and trailing zeros or asterisks.
• TRIM() typically is used to format results and make comparisons in a WHERE clause.
• TRIM() is useful for trimming trailing spaces from CHAR values. Recall from “Character String Types” in Chapter 3 that DBMSs add spaces automatically to the end of CHAR values to create strings of exactly a specified length.
• Trimming has no effect on empty strings ('').
• If any argument is null, then TRIM() returns null. (But see the Oracle exception in the DBMS tip in “Tips for Trimming Characters”.)

To trim spaces from a string:

• Type:

  TRIM([[LEADING | TRAILING | BOTH] FROM] string)

  string is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string. Specify LEADING to remove leading spaces, TRAILING to remove trailing spaces, or BOTH to remove leading and trailing spaces. If this specifier is omitted, then BOTH is assumed (Listing 5.17 and Figure 5.17).

To trim characters from a string:

• Type:

  TRIM([LEADING | TRAILING | BOTH] 'trim_chars' FROM string)

  string is the string to trim, and trim_chars is one or more characters to remove from string. Each argument is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string. Specify LEADING to remove leading characters, TRAILING to remove trailing characters, or BOTH to remove leading and trailing characters. If this specifier is omitted, then then BOTH is assumed (Listings 5.18 and 5.19, Figures 5.18 and 5.19).

Tips for Trimming Characters

• You can use TRIM() in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.

• In Listing 5.8 earlier in this chapter, I concatenated authors’ first and last names into a single column. The result, Figure 5.8, contains a single extra space before the author named Kellsey. This space—which separates the first and last names in the other rows—appears because Kellsey has no first name. You can use TRIM() to remove this leading space. Change the concatenation expression in Listing 5.8 to:

  TRIM(au_fname || ' ' || au_lname)

• In Microsoft Access, the trimming functions are LTrim(string) to trim leading spaces, RTrim(string) to trailing spaces, and Trim(string) to trim both leading and trailing spaces. Use the function Replace(string, find, replacement [, start[, count[, compare]]]) to trim nonspace characters (actually, to replace nonspaces with empty strings). Use + to concatenate strings. To run Listings 5.17 and 5.18, change the trim expressions to (Listing 5.17):

  '<' + '  AAA  ' + '>'
'<' + LTRIM('  AAA  ') + '>'
'<' + RTRIM('  AAA  ') + '>'
'<' + TRIM('  AAA  ') + '>'

  and (Listing 5.18):

  Replace(au_lname, 'H', '', 1, 1)

  In Microsoft SQL Server, the trimming functions are LTRIM(string) to trim leading spaces and RTRIM(string) to trim trailing spaces. Use + to concatenate strings. To run Listing 5.17, change the trim expressions to:

  '<' + '  AAA  ' + '>'
'<' + LTRIM('  AAA  ') + '>'
'<' + RTRIM('  AAA  ') + '>'
'<' + LTRIM(RTRIM('  AAA  ')) + '>'

  SQL Server’s LTRIM() and RTRIM() functions remove spaces but not arbitrary trim_chars characters. You can nest and chain SQL Server’s CHARINDEX(), LEN(), PATINDEX(), REPLACE(), STUFF(), SUBSTRING(), and other character functions to replicate arbitrary-character trimming. To run Listing 5.18, change the trim expression to:

  REPLACE(SUBSTRING(au_lname, 1, 1),'H','') + SUBSTRING(au_lname, 2, LEN(au_lname))

  To run Listing 5.19, change the trim expression to:

  LTRIM(RTRIM(title_id)) LIKE 'T1_'

  In Oracle, add the clause FROM DUAL to run Listing 5.17; see the DBMS tip in “Tips for Derived Columns” earlier in this chapter. Oracle forbids multiple characters in trim_chars.

  In Db2, the trimming functions are LTRIM(string) to trim leading spaces and RTRIM(string) to trim trailing spaces. To run Listing 5.17, change the trim expressions:

  '<' || '  AAA  ' || '>'
'<' || LTRIM('  AAA  ') || '>'
'<' || RTRIM('  AAA  ') || '>'
'<' || LTRIM(RTRIM('  AAA  ')) || '>'

  You also must add the clause FROM SYSIBM.SYSDUMMY1 to Listing 5.17; see the DBMS tip in “Tips for Derived Columns” earlier in this chapter.

  You can nest and chain Db2’s LENGTH(), LOCATE(), POSSTR(), REPLACE(), SUBSTR(), and other character functions to replicate arbitrary-character trimming. To run Listing 5.18, change the trim expression to:

  REPLACE(SUBSTR(au_lname, 1, 1),'H','') || SUBSTR(au_lname, 2, LENGTH(au_lname))

  To run Listing 5.19, change the trim expression to:

  LTRIM(RTRIM(title_id)) LIKE 'T1_'

  In MySQL, use CONCAT() to run Listing 5.17 (see “Concatenating Strings with ||” earlier in this chapter). Change the concatenation expressions to:

  CONCAT('<','  AAA  ','>')
CONCAT('<', TRIM(LEADING FROM '  AAA  '), '>')
CONCAT('<', TRIM(TRAILING FROM '  AAA  '), '>')
CONCAT('<',TRIM('  AAA  '),'>')

  For string operations, Oracle treats a null as an empty string: TRIM(NULL) returns '' (not NULL). See the DBMS tip in “Tips for Nulls” in Chapter 3.

  Your DBMS might provide padding functions to add spaces or other characters to strings. The Oracle and PostgreSQL padding functions are LPAD() and RPAD(), for example. Search your DBMS documentation for character functions or string functions.

Finding the Length of a String with CHARACTER_LENGTH()

Use the function CHARACTER_LENGTH() to return the number of characters in a string. The function’s important characteristics are:

• CHARACTER_LENGTH() returns an integer greater than or equal to zero.
• CHARACTER_LENGTH() counts characters, not bytes. A multibyte or Unicode character represents one character. (To count bytes, see the tips in this section.)
• The length of an empty string ('') is zero.
• If its argument is null, then CHARACTER_LENGTH() returns null. (But see the Oracle exception in the DBMS tip in “Tips for String Lengths”.)

To find the length of a string:

• Type:

  CHARACTER_LENGTH(string)

  string is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string (Listings 5.20 and 5.21, Figures 5.20 and 5.21).

Tips for String Lengths

• You can use CHARACTER_LENGTH() in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• CHARACTER_LENGTH and CHAR_LENGTH are synonyms.
• SQL also defines the BIT_LENGTH() and OCTET_LENGTH() functions. BIT_LENGTH(expr) returns the number of bits in an expression; BIT_LENGTH(B'01001011') returns 8. OCTET_LENGTH(expr) returns the number of bytes in an expression; OCTET_LENGTH(B'01001011') returns 1, and OCTET_LENGTH('ABC') returns 3. Octet length equals bit-length ÷ 8 (rounded up to the nearest integer, if necessary). See the DBMS tip in this section for information about DBMS bit- and byte-length functions.

• In Microsoft Access and Microsoft SQL Server, the string-length function is LEN(string). To run Listings 5.20 and 5.21, change the length expressions to (Listing 5.20):

  LEN(au_fname)

  and (Listing 5.21):

  LEN(title_name)

  In Oracle and Db2, the string-length function is LENGTH(string). To run Listings 5.20 and 5.21, change the length expressions to (Listing 5.20):

  LENGTH(au_fname)

  and (Listing 5.21):

  LENGTH(title_name)

  Bit- and byte-count functions vary by DBMS. Microsoft Access has Len(). Microsoft SQL Server has DATALENGTH(). Oracle has LENGTHB(). Db2 has LENGTH(). MySQL has BIT_COUNT() and OCTET_LENGTH(). PostgreSQL has BIT_LENGTH() and OCTET_LENGTH().

  For string operations, Oracle treats a null as an empty string: LENGTH('') returns NULL (not 0). Listing 5.20 will show 1 (not 0) in the next-to-last row because the author’s first name is ' ' (a space) in the Oracle database. For more information, see the DBMS tip in “Tips for Nulls” in Chapter 3.

Finding Substrings with POSITION()

Use the function POSITION() to locate a particular substring within a given string. The function’s important characteristics are:

• POSITION() returns an integer (≥0) that indicates the starting position of a substring’s first occurrence within a string.
• If the string doesn’t contain the substring, then POSITION() returns zero.
• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.
• The position of any substring within an empty string ('') is zero. (But see the Oracle exception in the DBMS tip in “Tips for Finding Substrings”.)
• If any argument is null, then POSITION() returns null.

To find a substring:

• Type:

  POSITION(substring IN string)

  substring is the string to search for, and string is the string to search. Each argument is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string. POSITION() returns the lowest (integer) position in string in which substring occurs, or zero if substring isn’t found (Listings 5.22 and 5.23, Figures 5.22 and 5.23).

Tips for Finding Substrings

• You can use POSITION() in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.

• The SQL standard also defines the function OVERLAY() to replace substrings. The syntax is:

  OVERLAY(string PLACING substring FROM start_position [FOR length])

  For example, OVERLAY('Txxxxas' PLACING 'hom' FROM 2 FOR 4) is 'Thomas'. The equivalent functions in the DBMSs are REPLACE() (Microsoft Access, Microsoft SQL Server, Db2, and MySQL), REGEXP_REPLACE() (Oracle), and OVERLAY() (PostgreSQL).

• In Microsoft Access, the position function is InStr(start_position, string, substring). To run Listings 5.22 and 5.23, change the position expressions to (Listing 5.22):

  InStr(1, au_fname, 'e')
InStr(1, au_lname, 'ma')

  and (Listing 5.23):

  InStr(1, title_name, 'u')

  In Microsoft SQL Server, the position function is CHARINDEX(substring, string). To run Listings 5.22 and 5.23, change the position expressions to (Listing 5.22):

  CHARINDEX('e', au_fname)
CHARINDEX('ma', au_lname)

  and (Listing 5.23):

  CHARINDEX('u', title_name)

  In Oracle, the position function is INSTR(string, substring). To run Listings 5.22 and 5.23, change the position expressions to (Listing 5.22):

  INSTR(au_fname, 'e')
INSTR(au_lname, 'ma')

  and (Listing 5.23):

  INSTR(title_name, 'u')

  In Db2, the position function is POSSTR(string, substring). To run Listings 5.22 and 5.23, change the position expressions to (Listing 5.22):

  POSSTR(au_fname, 'e')
POSSTR(au_lname, 'ma')

  and (Listing 5.23):

  POSSTR(title_name, 'u')

  For string operations, Oracle treats a null as an empty string: INSTR('', substring) returns NULL (not 0). See the DBMS tip in “Tips for Nulls” in Chapter 3.

  You can nest and chain substring and position functions to find substring occurrences beyond the first occurrence, but DBMSs provide enhanced position functions to do that. Microsoft Access has InStr(). Microsoft SQL Server has CHARINDEX(). Oracle has INSTR(). Db2 has LOCATE(). MySQL has LOCATE().

Performing Datetime and Interval Arithmetic

DBMS compliance with standard SQL datetime and interval operators and functions is spotty because DBMSs usually provide their own extended (nonstandard) operators and functions that perform date and time arithmetic. For information about datetime and interval data types, see “Datetime Types” and “Interval Types” in Chapter 3.

Use the same operators introduced in “Performing Arithmetic Operations” earlier in this chapter to perform datetime and interval arithmetic. The common temporal operations are:

• Subtracting two dates to calculate the interval between them
• Adding or subtracting an interval and a date to calculate a future or past date
• Adding or subtracting two intervals to get a new interval
• Multiplying or dividing an interval by a number to get a new interval

Some operations are undefined; adding two dates makes no sense, for example. Table 5.3 lists the valid SQL operators involving datetimes and intervals. The “Operator Overloading” section explains why you can use the same operator to perform different operations.

The function EXTRACT() isolates a single field of a datetime or interval and returns it as a number. EXTRACT() typically is used in comparison expressions or for formatting results.

To extract part of a datetime or interval:

• Type:

  EXTRACT(field FROM datetime_or_interval)

  field is the part of datetime_or_interval to return. field is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, or TIMEZONE_MINUTE (refer to Table 3.14 in Chapter 3). datetime_or_interval is a datetime or interval expression such as a column that contains datetime or interval values, a datetime or interval literal, or the result of an operation or function that returns a datetime or interval. If field is SECOND, then EXTRACT() returns a NUMERIC value; otherwise, it returns an INTEGER (Listing 5.24 and Figure 5.24).

Tips for Datetime and Interval Arithmetic

• You can use temporal operators and functions in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• If any operand or argument is null, then an expression returns null.
• See also “Working with Dates” in Chapter 15.

• In Microsoft Access and Microsoft SQL Server, the extraction function is DATEPART(datepart, date). To run Listing 5.24, change the extraction expressions to:

  DATEPART("yyyy", pubdate)
DATEPART("m", pubdate)

  Oracle, MySQL, and PostgreSQL accept different or additional values for the field argument of EXTRACT().

  Instead of EXTRACT(), Db2 extracts parts by using individual functions such as DAY(), HOUR(), and SECOND(). To run Listing 5.24, change the extraction expressions to:

  YEAR(pubdate)
MONTH(pubdate)

  In addition to (or instead of) the standard arithmetic operators, DBMSs provide functions that add intervals to dates. Some examples: DATEDIFF() in Microsoft Access and Microsoft SQL Server, ADD_MONTHS() in Oracle, and DATE_ADD() and DATE_SUB() in MySQL.

  Complex date and time arithmetic is so common in SQL programming that all DBMSs provide lots of temporal extensions. Search your DBMS documentation for date and time functions or datetime functions.

Getting the Current Date and Time

Use the functions CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP to get the current date and time from the system clock of the particular computer where the DBMS is running.

To get the current date and time:

• To get the current date, type:

  CURRENT_DATE

  or

  To get the current time, type:

  CURRENT_TIME

  or

  To get the current timestamp, type:

  CURRENT_TIMESTAMP

  CURRENT_DATE returns a DATE, CURRENT_TIME returns a TIME, and CURRENT_TIMESTAMP returns a TIMESTAMP; see “Datetime Types” in Chapter 3 (Listings 5.25 and 5.26, Figures 5.25 and 5.26).

Tips for the Current Date and Time

• You can use datetime functions in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• CURRENT_TIME and CURRENT_TIMESTAMP each take a precision argument that specifies the decimal fractions of a second to be included in the time. CURRENT_TIME(6), for example, returns the current time with six digits of precision in the SECOND field. For information about precision, see “Datetime Types” in Chapter 3.
• See also “Working with Dates” in Chapter 15.

• In Microsoft Access, the datetime system functions are Date(), Time(), and Now(). To run Listing 5.25, change the datetime expressions to:

  Date() AS "Date"
Time() AS "Time"
Now() AS "Timestamp"

  To run Listing 5.26, change the BETWEEN clause to:

  BETWEEN NOW() - 90
    AND NOW() + 90

  In Microsoft SQL Server, the datetime system function is CURRENT_TIMESTAMP (or its synonym, GETDATE()). CURRENT_DATE and CURRENT_TIME aren’t supported. To run Listing 5.25, omit the CURRENT_DATE and CURRENT_TIME expressions. To run Listing 5.26, change the BETWEEN clause to:

  BETWEEN CURRENT_TIMESTAMP - 90
    AND CURRENT_TIMESTAMP + 90

  In Oracle, the datetime system function is SYSDATE. Oracle 9i and later versions support CURRENT_DATE and CURRENT_TIMESTAMP (but not CURRENT_TIME). Listing 5.25 also requires the clause FROM DUAL; see the DBMS tip in “Tips for Derived Columns” earlier in this chapter. To run Listing 5.25, change the statement to:

  SELECT SYSDATE AS "Date"
  FROM DUAL;

  SYSDATE returns the system date and time but doesn’t display the time unless formatted to do so with the function TO_CHAR():

  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM DUAL;

  To run Listing 5.26, change the BETWEEN clause to:

  BETWEEN SYSDATE - 90
    AND SYSDATE + 90

  To run Listing 5.25 in Db2, add the clause FROM SYSIBM.SYSDUMMY1; see the DBMS tip in “Tips for Derived Columns” earlier in this chapter. To run Listing 5.26, change the WHERE clause to:

  BETWEEN CURRENT_DATE - 90 DAYS
    AND CURRENT_DATE + 90 DAYS

  To run Listing 5.26 in PostgreSQL, change the WHERE clause to:

  BETWEEN CURRENT_TIMESTAMP - 90
    AND CURRENT_TIMESTAMP + 90

  For information about datetime system functions, search your DBMS documentation for date and time functions or system functions.

Getting User Information

Use the function CURRENT_USER to identify the active user within the database server.

To get the current user:

• Type:

  CURRENT_USER

  (Listing 5.27 and Figure 5.27).

Tips for User Information

• You can use user functions in SELECT, WHERE, and ORDER BY clauses or anywhere an expression is allowed.
• SQL also defines the SESSION_USER and SYSTEM_USER functions. The current user indicates the authorization identifier under whose privileges SQL statements currently are run. (The current user might have permission to run, say, only SELECT statements.) The session user indicates the authorization ID associated with the current session. The system user is the user as identified by the host operating system. The DBMS determines user values, and these three values may or may not be identical. For information about users, sessions, and privileges, search your DBMS documentation for authorization, session, user, or role.
• See also “Retrieving Metadata” in Chapter 15.

• To run Listing 5.27 in Microsoft Access, change the statement to:

  SELECT CurrentUser AS "User";

  To run Listing 5.27 in Oracle, change the statement to:

  SELECT USER AS "User"
  FROM DUAL;

  To run Listing 5.27 in Db2, change the statement to:

  SELECT CURRENT_USER AS "User"
  FROM SYSIBM.SYSDUMMY1;

  To run Listing 5.27 in MySQL, change the statement to:

  SELECT USER() AS "User";

  Microsoft SQL Server supports SESSION_USER and SYSTEM_USER. MySQL supports SESSION_USER() and SYSTEM_USER(). Oracle’s SYS_CONTEXT() returns a session’s user attributes. Db2 supports SESSION_USER and SYSTEM_USER. PostgreSQL supports SESSION_USER.

  For information about user system functions, search your DBMS documentation for user or system functions.