Tables

Temporary tables in SQL99

SQL99 mandates two types of temporary tables: LOCAL and GLOBAL. The difference is in their data visibility. Even though the data in a temporary table is visible only within the session (or transaction) that populated it, GLOBAL tables can be accessed by any program or module within the session; thus, a stored procedure sp_1 can create the global temporary table TEMP_TABLE1; another stored procedure sp_2 can populate it with data, and then other stored procedures sp_3, sp_4, and sp_5 can use the data in their processing as long as all five stored procedures are started from the same session.

Unlike temporary table data, the temporary table's definition is permanent; so, if user A creates a temporary table B, populates it with data, and logs off, when s/he logs back the next day (or next year), the table is still there, but it is empty.

Temporary tables in Oracle 9i

You can create GLOBAL TEMPORARY tables in Oracle; LOCAL TEMPORARY tables are not yet implemented. The idea of a GLOBAL TEMPORARY table is slightly different from that described in the SQL99 concept. The table definition is visible to all sessions, but the data in a temporary table is only visible to the session that populated the data:

CREATE GLOBAL TEMPORARY
				TABLE tmp_customer_order_totals ( customer_name VARCHAR2(30), customer_total
				NUMBER ) ON COMMIT DELETE ROWS;

In other words, user John might have created the TMP_CUSTOMER_ORDER_TOTALS table a year ago; users Mary, Susan, and Kyle are using the table concurrently (assuming they have appropriate privileges), but from their point of view it's like each of them was using his or her own temporary table; see Figures 4-1, 4-2, and 4-3.

Figure 4-1: Mary's rows in the TMP_CUSTOMER_ORDER_TOTALS temporary table

Figure 4-2: Susan's rows in the TMP_CUSTOMER_ORDER_TOTALS temporary table

Figure 4-3: Kyle's rows in the TMP_CUSTOMER_ORDER_TOTALS temporary table

Temporary tables in DB2 UDB 8.1

Temporary tables cannot be created in DB2 UDB 8.1 with CREATE TABLE; you can instead declare a temporary table for current session with the DECLARE GLOBAL TEMPORARY TABLE statement.

The declared temporary table cannot be shared with other sessions; when a session is terminated, rows and the table definition are both dropped.

The syntax of the DECLARE GLOBAL TEMPORARY TABLE statement is similar to DB2's CREATE TABLE statement; here is an example:

DECLARE GLOBAL TEMPORARY
				TABLE tmp_customer_order_totals ( customer_name VARCHAR(30), customer_total
				DECIMAL(12,2) ) ON COMMIT PRESERVE ROWS NOT LOGGED

Temporary tables in MS SQL Server 2000

The MS SQL Server syntax used to create a temporary table is not consistent with SQL99 standards. To create a local temporary table, you prefix it with the pound sign (#); the double pound sign (##) indicates a global temporary table.

Local temporary tables are visible only to the current session; both the table data and table definition are deleted when the user logs off (comparable to DB2 temporary tables created with the DECLARE GLOBAL TEMPORARY TABLE statement):

CREATE TABLE
				#tmp_customer_order_totals ( customer_name VARCHAR(30), customer_total MONEY
				)

Global temporary tables are visible to all users; they are destroyed after every user who was referencing the table disconnects from the SQL Server:

CREATE TABLE
				##tmp_customer_order_totals ( customer_name VARCHAR(30),
				customer_total MONEY )

SQL99

According to SQL99, a domain can be used for a column instead of a data type. (Domains are covered later in this chapter.)

customer_name
				  VARCHAR2(30) customer_total NUMBER

customer_name VARCHAR(30)
				  customer_total MONEY

Referential integrity constraints optional clauses

SQL99 assumes an optional clause on the creation of FOREIGN KEY and REFERENCES constraints that specifies what happens to a row if that row has a referential relationship and the referenced row is deleted from the parent table or changes are made to the referenced table. The syntax is as follows:

[ON DELETE {NO ACTION |
				CASCADE | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET NULL | SET
				DEFAULT}]

NO ACTION is the default behavior and means an error will be generated if one tries to delete the row (or update the primary key value) referenced by other table(s).

CASCADE assumes that the same changes will automatically be done to the foreign key as were made to the parent; thus, if the parent row is deleted, all child rows that referenced it will also be deleted; if the parent's primary key is updated, all child rows' foreign keys that reference it will also be updated with the same value.

SET NULL means if the parent row was deleted or its primary key was changed, all child tables' referencing foreign key values will be set to NULL.

SET DEFAULT is very similar to SET NULL, but the child tables' columns are set to their default values rather than to NULLs. This assumes that column default values exist.

All our three vendors implemented the above SQL99 standard to certain degree.

Oracle 9i does not have the ON UPDATE clause; the ON DELETE clause syntax is:

[ON DELETE { NO ACTION |
				CASCADE | SET NULL }]

DB2 UDB 8.1 has an additional optional RESTRICT that in most cases behaves like NO ACTION:

[ON DELETE {NO ACTION |
				RESTRICT | CASCADE | SET NULL}] [ON UPDATE {NO ACTION |
				RESTRICT}]

MS SQL Server 2000 has two options — NO ACTION and CASCADE for both ON DELETE and ON UPDATE clauses:

[ON DELETE {NO ACTION |
				CASCADE}] [ON UPDATE {NO ACTION | CASCADE}]

The default is NO ACTION for all three implementations.

The following examples illustrate the difference between NO ACTION and CASCADE using Oracle 9i syntax.

Listing 4-1 uses previously created SALESMAN and ADDRESS tables with default (NO ACTION) option on the referential integrity constraint FK_ADDR_SALESMAN.

Listing 4-1: Default (NO ACTION) option

SQL> INSERT INTO
				  salesman 2 ( 3 salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6
				  salesman_status_s 7 ) 8 VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS
				  ASSOCIATION', 13 'Y' 14 ); 1 row created. SQL> INSERT INTO address 2 ( 3
				  addr_id_n, 4 addr_custid_fn, 5 addr_salesmanid_fn, 6 addr_address_s, 7
				  addr_type_s, 8 addr_city_s, 9 addr_state_s, 10 addr_zip_s, 11 addr_country_s 12
				  ) 13 VALUES 14 ( 15 49, 16 NULL, 17 23, 18 '223 E FLAGLER ST.', 19 NULL, 20
				  'MIAMI', 21 'FL', 22 '33131', 23 'USA' 24
				  ); 1 row created. SQL> commit; Commit complete. SQL> DELETE FROM
				  salesman; DELETE FROM salesman * ERROR at line 1: ORA-02292: integrity
				  constraint (TEMP.FK_ADDR_SALESMAN) violated - child record
				  found

Listing 4-2 — with CASCADE option on the referential integrity constraint FK_ADDR_SALESMAN:

Listing 4-2: CASCADE option

SQL> drop table
				  ADDRESS; Table dropped. SQL> CREATE TABLE address 2 ( 3 addr_id_n INT
				  CONSTRAINT pk_addrprimary PRIMARY KEY, 4 addr_custid_fn INT, 5
				  addr_salesmanid_fn INT CONSTRAINT fk_addr_salesman 6 REFERENCES salesman
				  (salesman_id_n) 7 ON DELETE CASCADE, 8 addr_address_s VARCHAR(60), 9
				  addr_type_s VARCHAR(8) CONSTRAINT chk_addr_type CHECK 10 (addr_type_s IN
				  ('BILLING', 'SHIPPING')), 11 addr_city_s VARCHAR(18) CONSTRAINT nn_addr_city
				  NOT NULL, 12 addr_state_s CHAR(2), 13 addr_zip_s VARCHAR(10) NOT NULL, 14
				  addr_country_s CHAR(3) 15 ); Table created. SQL> INSERT INTO address 2 ( 3
				  addr_id_n, 4 addr_custid_fn, 5 addr_salesmanid_fn, 6 addr_address_s, 7
				  addr_type_s, 8 addr_city_s, 9 addr_state_s, 10 addr_zip_s, 11 addr_country_s 12
				  ) 13 VALUES 14 ( 15 49, 16 NULL, 17 23, 18 '223 E FLAGLER ST.', 19 NULL, 20
				  'MIAMI', 21 'FL', 22 '33131', 23 'USA' 24 ); 1 row created. SQL> COMMIT;
				  Commit complete. SQL> SELECT * FROM ADDRESS; ADDR_ID_N ADDR_CUSTID_FN
				  ADDR_SALESMANID_FN ADDR_ADDRESS_S ---------- -------------- ------------------
				  ----------------- 49 23 223 E FLAGLER ST. SQL> DELETE FROM salesman; 1 row
				  deleted. SQL> SELECT * FROM address; no rows selected

Deferrable constraints

SQL99 standards say that constraints can be either DEFERRABLE or NOT DEFERRABLE (default). A NOT DEFERRABLE constraint is checked after each DDL statement; DEFERRABLE constraints can either be checked immediately after every INSERT, DELETE, or UPDATE (INITIALLY IMMEDIATE) or at the end of the transaction (INITIALLY DEFERRED).

That feature can be especially helpful when data loads are performed with no particular order; that allows you to load data into child table(s) first, then into parent table(s). Another use would be loading data that does not comply with a CHECK constraint and then updating it appropriately.

The only vendor out of our "big three" who provides deferrable constraints is Oracle 9i. The syntax is

[[NOT] DEFERRABLE
				[INITIALLY {IMMEDIATE | DEFERRED}]]

or

[[INITIALLY {IMMEDIATE |
				DEFERRED}] [NOT] DEFERRABLE]

Listing 4-3 illustrates the use of deferrable constraints.

Listing 4-3: Using deferrable constraints

-- Create SALESMAN table
				  with NOT DEFERRABLE CHECK constraint -- chk_salesstatus on salesman_status_s
				  column SQL> DROP TABLE salesman; Table dropped. SQL> CREATE TABLE
				  salesman 2 ( 3 salesman_id_n NUMBER NOT NULL, 4 salesman_code_s VARCHAR2(2) NOT
				  NULL, 5 salesman_name_s VARCHAR2(50) NOT NULL, 6 salesman_status_s CHAR(1)
				  DEFAULT 'Y', 7 CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N',
				  'Y')), 8 CONSTRAINT pk_salesmanprim PRIMARY KEY (salesman_id_n) 9 ); Table
				  created. -- Now, try to insert a row with salesman_status_s = 'A' SQL>
				  INSERT INTO salesman 2 ( 3
				  salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8
				  VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION',  13
				  'A' 14 ); /* The result is a constraint violation error (constraint
				  is checked immediately). */ INSERT INTO SALESMAN * ERROR at line 1: ORA-02290:
				  check constraint (TEST.CHK_SALESSTATUS) violated -- Drop SALESMAN table and
				  re-create it with DEFERRABLE CHECK constraint SQL> DROP
				  TABLE salesman; Table dropped. SQL> CREATE TABLE salesman 2 ( 3
				  salesman_id_n NUMBER NOT NULL, 4 salesman_code_s VARCHAR2(2) NOT NULL, 5
				  salesman_name_s VARCHAR2(50) NOT NULL, 6 salesman_status_s CHAR(1) DEFAULT 'Y',
				  7 CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y')) 
				  8 DEFERRABLE INITIALLY DEFERRED, 9 CONSTRAINT pk_salesmanprim
				  PRIMARY KEY (salesman_id_n) 10 ); Table created. -- Try to insert the same row
				  again – works this time SQL> INSERT INTO salesman 2 ( 3 salesman_id_n, 4
				  salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8 VALUES 9
				  (
				  10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION', 13 'A' 14 ); 1 row created. --
				  Try to commit changes SQL> COMMIT; -- Error occurs – the constraint is
				  checked at the end of the transaction. COMMIT * ERROR at line 1: ORA-02091:
				  transaction rolled back ORA-02290: check constraint (TEST.CHK_SALESSTATUS)
				  violated -- Trying to insert again SQL> INSERT INTO salesman 2 ( 3
				  salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8
				  VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION', 13 'A' 14 ); 1 row
				  created. -- Now update it with an appropriate value ('Y') SQL> UPDATE
				  salesman 2 SET salesman_status_s = 'Y' 3 WHERE salesman_status_s = 'A'; 1 row
				  updated. -- COMMIT works this time. SQL> COMMIT;
				  
				  Commit complete.

Using INITIALLY DEFERRED Constraints

The INITIALLY DEFERRED constraint status is usually used for special cases (data loads, conversions, etc.); when used in everyday database operations, the deferred constraints can be confusing and even harmful.

For example, imagine someone issued a couple dozen DML statements and then tries to commit their changes, but the commit fails because the very first INSERT statement violates an INITIALLY DEFERRED constraint. The transaction will be rolled back, so the user would have to retype all the statements. If the constraint were checked after each statement, the user would have known to correct data before going any further.

The solution is simple: only use INITIALLY DEFERRED constraint status for special needs and change it to INITIALLY IMMEDIATE whenever the deferred functionality is no longer desirable. (You can always change it back and forth if your constraint was created as DEFERRABLE.) We'll learn more about that in Chapter 5.

Oracle 9i

Oracle uses tablespaces (logical database structure explained later in this chapter). You can specify separate tablespaces for table data, table indexes, and table LOBs:

CREATE
				TABLE phone ( phone_id_n NUMBER CONSTRAINT pk_phonerimary PRIMARY KEY
				USING INDEX  TABLESPACE INDEX01,
				phone_custid_fn NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s
				VARCHAR2(20), phone_type_s VARCHAR2(20), CONSTRAINT chk_phone_type CHECK
				(phone_type_s IN ('PHONE', 'FAX')) ) TABLESPACE DATA01

This example assumes the existence of tablespaces DATA01, where the table data will reside, and INDEX01, to store the index for the primary key.

DB2 UDB 8.1

DB2 also uses tablespaces much as Oracle does. You can specify separate tablespaces for table data, indexes, and large objects:

CREATE TABLE phone (
				phone_id_n INTEGER NOT NULL, phone_custid_fn INTEGER, phone_salesmanid_fn
				INTEGER, phone_phonenum_s VARCHAR(20), phone_type_s VARCHAR(20), CONSTRAINT
				chk_phone_type CHECK (phone_type_s IN ('PHONE', 'FAX')), CONSTRAINT
				pk_phonerimary PRIMARY KEY (phone_id_n) ) IN
				USERDATA01

The system-managed tablespace USERDATA01 presumably exists. We'll show how to create it at the end of the chapter in the section about tablespaces.

Index Organized Tables and Other Physical Properties

The ability to specify where data is physically stored on the server is just one of many options you can specify when creating a database table. For example, you can specify how much space will be left for updates when a row is inserted, regulate the physical table growth, slice the table based on certain conditions (either horizontally or vertically), place the different partitions on separate physical devices, and so on.

Oracle also allows you to create index-organized tables. An index-organized table is a special type of table in which the table rows are maintained in an index built on the primary key. In other words, unlike regular tables in which rows are stored in no particular order, the rows of an index-organized table are always sorted by the primary key field. The access to such tables based on primary key is therefore much faster, but the DML statements can be considerably slower. For example, to insert a row into an index-organized table, an RDBMS has to re-sort and rebuild the whole physical table structure.

This syntax creates an index-organized table named ORDER_LINE:

CREATE TABLE ORDER_LINE (
				  ORDLINE_ID_N NUMBER NOT NULL, ORDLINE_ORDHDRID_FN NUMBER NOT NULL,
				  ORDLINE_PRODID_FN NUMBER, ORDLINE_ORDQTY_N NUMBER, ORDLINE_SHIPQTY_N NUMBER,
				  ORDLINE_CREATEDATE_D DATE, ORDLINE_CREATEDBY_S VARCHAR2(10), CONSTRAINT
				  PK_ORDLINEPRIM PRIMARY KEY (ORDLINE_ID_N) ) ORGANIZATION INDEX
				  ;

MS SQL Server implements a similar concept in clustered indexes, which are explained later in this chapter.

MS SQL Server 2000

Instead of tablespaces, MS SQL Server employs filegroups. Again, the idea is very similar; the difference is mostly in the syntax:

CREATE TABLE phone (
				phone_id_n INTEGER NOT NULL, phone_custid_fn INTEGER, phone_salesmanid_fn
				INTEGER, phone_phonenum_s VARCHAR(20), phone_type_s VARCHAR(20), CONSTRAINT
				chk_phone_type CHECK (phone_type_s IN ('PHONE', 'FAX')), CONSTRAINT
				pk_phonerimary PRIMARY KEY (phone_id_n) ON INDEX01 )
				ON DATA01

In this example, we have presumed the existence of previously created filegroups DATA01 and INDEX01. MS SQL filegroups are covered in more detail later in this chapter.

Oracle 9i

Oracle does not have identity columns; it uses special database objects called sequences instead. You can simply create a table with a numeric field to be populated; at the moment of table creation the RDBMS does not need to know that, for example, you intend to populate the PAYTERMS_ID_N field of the PAYMENT_TERMS table using a sequence:

CREATE TABLE payment_terms
				( payterms_id_n NUMBER NOT NULL, payterms_code_s VARCHAR(6), payterms_desc_s
				VARCHAR(60), payterms_discpct_n NUMBER, payterms_daystopay_N NUMBER, CONSTRAINT
				pk_payterms PRIMARY KEY (payterms_id_n) )

More about sequences can be found in the CREATE SEQUENCE section that follows in this chapter as well as in the ALTER SEQUENCE, and DROP SEQUENCE sections of Chapter 5.

DB2 UDB 8.1

Identity properties for a column can be specified instead of the default clause. You can specify the starting value, the increment, the minimum and maximum values, whether the sequence should cycle around when it reaches the maximum value or whether it should stop, and the number of values cached in memory:

CREATE
				TABLE payment_terms ( payterms_id_n INTEGER NOT NULL GENERATED ALWAYS
				AS IDENTITY  (START WITH 1, INCREMENT BY 1, CACHE
				5), payterms_code_s VARCHAR(6), payterms_desc_s VARCHAR(60),
				payterms_discpct_n DECIMAL(5,2), payterms_daystopay_N INTEGER, CONSTRAINT
				pk_payterms PRIMARY KEY (payterms_id_n) )

The default number of values to cache is 20; you can specify NO CACHE if you don't want to cache values.

MS SQL Server 2000

An identity column is created in a very similar way with slightly different syntax:

CREATE
				TABLE payment_terms ( -- The first 1 means "start with"; the second stands for
				"increment by" payterms_id_n INT NOT NULL IDENTITY (1,1),
				payterms_code_s VARCHAR(6), payterms_desc_s VARCHAR(60), payterms_discpct_n
				DECIMAL(5,2), payterms_daystopay_n INT, CONSTRAINT pk_payterms PRIMARY KEY
				(payterms_id_n) )

The caching option is not available in MS SQL Server.

Oracle 9i

The statement below creates a temporary table that is a copy of the PAYMENT_TERMS table (including all rows):

CREATE GLOBAL TEMPORARY
				TABLE payment_terms2 AS (SELECT * FROM payment_terms);

The proper use of the SELECT statement enables you to create a table that only contains certain columns and/or rows from the target table:

CREATE
				TABLE customer ( cust_id_n NUMBER NOT NULL, cust_paytermsid_fn NUMBER,
				cust_salesmanid_fn NUMBER, cust_status_s VARCHAR2(1) DEFAULT 'Y' NOT NULL,
				cust_name_s VARCHAR2(50) NOT NULL, cust_alias_s VARCHAR2(15), cust_credhold_s
				VARCHAR2(1) DEFAULT 'Y' NOT NULL, CONSTRAINT chk_cust_status CHECK
				(cust_status_s IN ('N', 'Y')), CONSTRAINT chk_cust_credhold CHECK
				(cust_credhold_s IN ('N', 'Y')), CONSTRAINT pk_custprimary PRIMARY KEY
				(cust_id_n) ); CREATE TABLE phone ( phone_id_n NUMBER NOT NULL, phone_custid_fn
				NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s VARCHAR2(20), phone_type_s
				VARCHAR2(20), CONSTRAINT chk_phone_type CHECK (phone_type_s IN ('PHONE',
				'FAX')), CONSTRAINT pk_phonerimary PRIMARY KEY (phone_id_n) ); CREATE TABLE
				customer_phone AS ( SELECT cust_name_s, phone_phonenum_s FROM customer, phone
				WHERE cust_id_n = phone_custid_fn );

To create an empty table in Oracle, deliberately use a FALSE condition in the WHERE clause:

/* We
				know that all primary keys in ACME database are positive integers so we know
				that PAYTERMS_ID_N < 0 condition always evaluates to "FALSE" */ CREATE
				GLOBAL TEMPORARY TABLE payment_terms3 AS (SELECT * FROM payment_terms WHERE
				PAYTERMS_ID_N < 0);

DB2 UDB 8.1

Either one of two statements below will create a copy of the PAYMENT_TERMS table in DB2 UDB:

CREATE TABLE payment_terms2
				AS (SELECT * FROM payment_terms) DEFINITION ONLY 

CREATE TABLE payment_terms2
				LIKE payment_terms

The first syntax (DB2 considers it a special case of a summary table — see the sidebar "Creating Summary Tables") is more flexible because it allows you to create tables based on a subset of the original table columns or even on a multitable query:

CREATE
				TABLE customer ( cust_id_n NUMBER NOT NULL, cust_paytermsid_fn NUMBER,
				cust_salesmanid_fn NUMBER, cust_status_s VARCHAR2(1) DEFAULT 'Y' NOT NULL,
				cust_name_s VARCHAR2(50) NOT NULL, cust_alias_s VARCHAR2(15), cust_credhold_s
				VARCHAR2(1) DEFAULT 'Y' NOT NULL, CONSTRAINT chk_cust_status CHECK
				(cust_status_s IN ('N', 'Y')), CONSTRAINT chk_cust_credhold CHECK
				(cust_credhold_s IN ('N', 'Y')), CONSTRAINT pk_custprimary PRIMARY KEY
				(cust_id_n) ) CREATE TABLE phone ( phone_id_n NUMBER NOT NULL, phone_custid_fn
				NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s VARCHAR2(20), phone_type_s
				VARCHAR2(20), CONSTRAINT chk_phone_type CHECK (phone_type_s IN ('PHONE',
				'FAX')), CONSTRAINT pk_phonerimary PRIMARY KEY (phone_id_n) ) CREATE TABLE
				customer_phone AS ( SELECT cust_name_s, phone_phonenum_s FROM customer, phone
				WHERE cust_id_n = phone_custid_fn ) DEFINITION ONLY -- required
				clause

The foregoing statement creates an empty CUSTOMER_PHONE table with two columns, CUST_NAME_S and PHONE_PHONENUM_S, with the same data types as the corresponding columns in the underlying tables; you could have achieved the same result using this syntax:

CREATE TABLE customer_phone
				AS ( cust_name_s VARCHAR(50) NOT NULL, phone_phonenum_s VARCHAR(20)
				)

The advantage of CREATE TABLE ... LIKE syntax is that it can optionally create a copy of table with all column defaults and/or identity columns.

MS SQL Server 2000

In MS SQL Server, you can create a copy of the PAYMENT_TERMS table using this syntax:

SELECT * INTO
				#PAYMENT_TERMS2 FROM PAYMENT_TERMS

Creating Summary Tables

Some RDBMS tables can be very large, so querying them can take a long time. Even when all appropriate indexes are in place and the database is properly tuned, some queries still have to perform full table scans, in which each and every record is searched for specific values, to return the desired results. That is especially true when you want a query to retrieve records summarized with a GROUP BY clause using aggregate functions.

The concept of summary tables introduced in DB2 allows you to maintain a summary of table (or multiple tables) data in another table, refreshing it every time when the underlying table(s) data changes. The most interesting fact about DB2 summary tables is that not only users can query them to obtain results faster, but the RDBMS

BackCover SQL Bible Preface How This Book Is Organized Conventions Used in This Book Acknowledgments Part I: SQL Basic Concepts and Principles Chapter 1: SQL and Relational Database Management Systems (RDBMS) Selecting Your Database Software Everything in Details: DBMS Implementations Real-Life Database Examples Database Legacy Relational Databases Object Database and Object-Relational Database Models Brief History of SQL and SQL Standards Summary Chapter 2: Fundamental SQL Concepts and Principles SQL: The First Look Any Platform, Any Time Summary Chapter 3: SQL Data Types No Strings Attached In Numbers Strength Once Upon a Time: Date and Time Data Types Object and User-Defined Data Types Other Data Types NULL Summary Part II: Creating and Modifying Database Objects Chapter 4: Creating RDBMS Objects Tables Views Aliases and Synonyms Schemas Other SQL99 and Implementation-Specific Objects CREATE Statement Cross-Reference Summary Chapter 5: Altering and Destroying RDBMS Objects Tables Views Aliases and Synonyms Schemas Other Implementation-Specific Objects ALTER and DROP Statements Cross-Reference Summary Part III: Data Manipulation and Transaction Control Chapter 6: Data Manipulation Language (DML) INSERT: Populating Tables with Data UPDATE: Modifying Table Data DELETE: Removing Data from Table Other SQL Statements to Manipulate Data Summary Chapter 7: Sessions, Transactions, and Locks Sessions Transactions Understanding Locks Summary Part IV: Retrieving and Transforming Data Chapter 8: Understanding SELECT Statement Single Table SELECT Statement Syntax SELECT Clause: What Do We Select? FROM Clause: Select from What? WHERE Clause: Setting Horizontal Limits GROUP BY and HAVING Clauses: Summarizing Results ORDER BY Clause: Sorting Query Output Combining the Results of Multiple Queries Summary Chapter 9: Multitable Queries Inner Joins Outer Joins: Joining Tables on Columns Containing NULL Values Joins Involving Inline Views Multitable Joins with Correlated Queries Improving Efficiency of Multitable Queries Summary Chapter 10: SQL Functions Summary Chapter 11: SQL Operators Arithmetic Operators Logical Operators Operator Precedence Assignment Operator Comparison Operators Bitwise Operators User-defined Operators Summary Part V: Implementing Security Using System Catalogs Chapter 12: SQL and RDBMS Security Defining a Database User Managing Security with Privileges Managing Security with Roles Using Views for Security Using Constraints for Security Using Stored Procedures and Triggers for Security Database Auditing Security Standards Summary Chapter 13: The System Catalog and INFORMATION_SCHEMA Oracle 9i Data Dictionary IBM DB2 UDB 8.1 System Catalogs Microsoft SQL Server 2000 System Catalog Summary Part VI: Beyond SQL--Procedural Programming and Database Access Mechanisms Chapter 14: Stored Procedures, Triggers, and User-Defined Functions Procedural Extension Uses and Benefits Key Elements of a SQL Procedural Language Stored procedures User-Defined Functions Triggers Summary Chapter 15: Dynamic and Embedded SQL Overview SQL Statement Processing Steps Embedded (Static) SQL Dynamic SQL Techniques The Future of Embedded SQL Summary Chapter 16: SQL API Microsoft Open Database Connectivity (ODBC) Java Database Connectivity (JDBC) IBM DB2 UDB Call-Level Interface (CLI) Oracle Call Interface (OCI) Oracle Objects for OLE (OO4O) Microsoft Data Access Interfaces Summary Chapter 17: New Developments--XML, OLAP, and Objects XML OLAP and Business Intelligence Objects Summary Part VII: Appendix Appendix A: What's on the CD-ROM Using the CD with Windows What's on the CD Troubleshooting Appendix B: The ACME Sample Database General Information and Business Rules Naming Conventions Relationships Between Tables Column Constraints and Defaults SQL Scripts to Create ACME Database Objects Appendix C: Basics of Relational Database Design Identifying Entities and Attributes Normalization Specifying Constraints Pitfalls of Relational Database Design Appendix D: Installing RDBMS Software Installing IBM DB2 UDB 8.1 Personal Edition Installing Microsoft SQL Server 2000 Appendix E: Accessing RDBMS Using IBM DB2 UDB 8.1 Command-Line Processor (CLP) Using Microsoft SQL Server Utilities to Access Database Appendix F: Installing the ACME Database Installing the ACME Database on Oracle 9i (Unix/Linux) Using SQL*Plus Installing the ACME Database on DB2 UDB 8.1 (Windows) Using CLP Installing ACME Database on Microsoft SQL Server 2000 Using OSQL Utility Appendix G: SQL Functions Appendix H: SQL Syntax Reference DDL Statements DCL Statements DML Statements DQL Statements Transactional Control Statements Predicates Appendix I: SQL-Reserved Keywords Future Keywords ODBC Reserved Keywords Appendix J: SQL99 Major Features Compliance Across Different RDBMS Appendix K: The Other RDBMS Appendix L: A Brief Introduction to the Number Systems, Boolean Algebra, and Set Theory The Number Systems Logic Elements of Boolean Algebra Set Theory List of Figures List of Tables List of Code Examples List of Sidebars CD Content