| |
| |
| Preface | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Introduction to SQL1 | |
| |
| |
| |
| Database, database management system and database language | |
| |
| |
| |
| The relational model | |
| |
| |
| |
| What is SQL? | |
| |
| |
| |
| Several categories of SQL applications | |
| |
| |
| |
| The history of SQL | |
| |
| |
| |
| From monolithic architecture to client/server | |
| |
| |
| |
| Transaction databases and data warehouses | |
| |
| |
| |
| Standardization of SQL | |
| |
| |
| |
| The market for SQL products | |
| |
| |
| |
| Which SQL dialect? | |
| |
| |
| |
| The structure of the book | |
| |
| |
| |
| The tennis club sample database | |
| |
| |
| |
| Description of the tennis club | |
| |
| |
| |
| The contents of the tables | |
| |
| |
| |
| Constraints | |
| |
| |
| |
| Working with SOLID | |
| |
| |
| |
| The SOLID product | |
| |
| |
| |
| The version of SOLID supplied with the book | |
| |
| |
| |
| Installing SOLID | |
| |
| |
| |
| Stopping SOLID | |
| |
| |
| |
| Starting SOLID | |
| |
| |
| |
| Entering statements | |
| |
| |
| |
| Editing statements | |
| |
| |
| |
| Storing statements | |
| |
| |
| |
| Additional documentation | |
| |
| |
| |
| SQL in a nutshell | |
| |
| |
| |
| Creating tables | |
| |
| |
| |
| The Null value | |
| |
| |
| |
| Populating tables with data | |
| |
| |
| |
| Querying tables | |
| |
| |
| |
| Updating and deleting rows | |
| |
| |
| |
| Optimizing query processing | |
| |
| |
| |
| Views | |
| |
| |
| |
| Users and data security | |
| |
| |
| |
| Deleting database objects | |
| |
| |
| |
| Grouping of SQL statements | |
| |
| |
| |
| The catalog tables | |
| |
| |
| |
| Definitions of SQL statements | |
| |
| |
| |
| Querying and updating data | |
| |
| |
| |
| Select statement: common elements | |
| |
| |
| |
| Data types and literals | |
| |
| |
| |
| Expressions | |
| |
| |
| |
| System variables | |
| |
| |
| |
| The case expression | |
| |
| |
| |
| Numerie expressions | |
| |
| |
| |
| Alphanumerie expressions | |
| |
| |
| |
| Sealar functions | |
| |
| |
| |
| Date expressions | |
| |
| |
| |
| Time expressions | |
| |
| |
| |
| Timestamp expressions | |
| |
| |
| |
| Set functions and subqueries | |
| |
| |
| |
| Casting of values and expressions | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Clauses of the Select statement | |
| |
| |
| |
| Processing a Select statement: example 1 | |
| |
| |
| |
| Processing a Select statement: example 2 | |
| |
| |
| |
| The table expression | |
| |
| |
| |
| Answers | |
| |
| |
| |
| The Select statement: the From clause | |
| |
| |
| |
| Table specifications in the From clause | |
| |
| |
| |
| The column specification | |
| |
| |
| |
| Multiple table specifications | |
| |
| |
| |
| The pseudonym | |
| |
| |
| |
| Various examples | |
| |
| |
| |
| Mandatory use of pseudonyms | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Select statement: the Where clause | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Conditions using comparison operators | |
| |
| |
| |
| Conditions coupled with And, Or and Not | |
| |
| |
| |
| The Between operator | |
| |
| |
| |
| The In operator | |
| |
| |
| |
| The Like operator | |
| |
| |
| |
| The Is Null operator | |
| |
| |
| |
| The In operator with subquery | |
| |
| |
| |
| The subquery as expression | |
| |
| |
| |
| The Exists operator | |
| |
| |
| |
| The All and Any operators | |
| |
| |
| |
| New conditions | |
| |
| |
| |
| Conditions with negation | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Select statement: the Select clause and functions | |
| |
| |
| |
| Selecting all columns (*) | |
| |
| |
| |
| Expressions in the Select clause | |
| |
| |
| |
| Removing duplicate rows with Distinct | |
| |
| |
| |
| When are two rows equal? | |
| |
| |
| |
| An introduction to set functions | |
| |
| |
| |
| The Count function | |
| |
| |
| |
| Max and Min functions | |
| |
| |
| |
| The Sum function | |
| |
| |
| |
| AVG, Variance and STDEV functions | |
| |
| |
| |
| General rule for using set functions | |
| |
| |
| |
| Specifying column headings | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Select statement: Group By and Having | |
| |
| |
| |
| Grouping on one column | |
| |
| |
| |
| Grouping on two or more columns | |
| |
| |
| |
| Grouping on expressions | |
| |
| |
| |
| Grouping on Null values | |
| |
| |
| |
| Group By and Distinct | |
| |
| |
| |
| General rule for using set functions | |
| |
| |
| |
| Introduction to the Having clause | |
| |
| |
| |
| Examples of the Having clause | |
| |
| |
| |
| General rule for the Having clause | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Select statement: the Order By clause | |
| |
| |
| |
| Sorting on one column | |
| |
| |
| |
| Sorting with sequence numbers and column headings | |
| |
| |
| |
| Sorting in ascending and descending order | |
| |
| |
| |
| Sorting on more than one column | |
| |
| |
| |
| Sorting on expressions | |
| |
| |
| |
| Sorting on Null values | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Combining Select statements | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Combining with Union | |
| |
| |
| |
| Rules for using Union | |
| |
| |
| |
| Combining with Intersect | |
| |
| |
| |
| Combining with Except | |
| |
| |
| |
| Keeping duplicate rows | |
| |
| |
| |
| Set operators and the Null value | |
| |
| |
| |
| Combining multiple set operators | |
| |
| |
| |
| Set operators and the theory | |
| |
| |
| |
| Answers | |
| |
| |
| |
| The subquery | |
| |
| |
| |
| Rules for subqueries | |
| |
| |
| |
| Range of columns | |
| |
| |
| |
| Examples with correlated subqueries | |
| |
| |
| |
| Subqueries in the Select clause | |
| |
| |
| |
| Working with composite keys | |
| |
| |
| |
| Answers | |
| |
| |
| |
| The From clause extended | |
| |
| |
| |
| Joins in the From clause | |
| |
| |
| |
| Outer joins | |
| |
| |
| |
| Examples with outer joins | |
| |
| |
| |
| Cross join and union join | |
| |
| |
| |
| Equi joins and theta joins | |
| |
| |
| |
| Subqueries in the From clause | |
| |
| |
| |
| Creating a new table in the From clause | |
| |
| |
| |
| Joins without join conditions | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Updating tables | |
| |
| |
| |
| Inserting new rows | |
| |
| |
| |
| Populating a table with rows from another table | |
| |
| |
| |
| Updating values in rows | |
| |
| |
| |
| Deleting rows from a table | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Creating database objects | |
| |
| |
| |
| Creating tables | |
| |
| |
| |
| Creating new tables | |
| |
| |
| |
| Copying tables | |
| |
| |
| |
| Naming tables and columns | |
| |
| |
| |
| Dropping tables | |
| |
| |
| |
| Changing the table structure | |
| |
| |
| |
| Computed columns | |
| |
| |
| |
| Default values for columns | |
| |
| |
| |
| The owner of a table | |
| |
| |
| |
| Storing comments in the catalog | |
| |
| |
| |
| Tables and the catalog | |
| |
| |
| |
| Synonyms for table names | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Specifying constraints | |
| |
| |
| |
| Primary keys | |
| |
| |
| |
| Alternate keys | |
| |
| |
| |
| Foreign keys | |
| |
| |
| |
| The referencing action | |
| |
| |
| |
| Check constraints | |
| |
| |
| |
| Deleting constraints | |
| |
| |
| |
| Naming constraints | |
| |
| |
| |
| Constraints and the catalog | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Designing tables | |
| |
| |
| |
| Which tables and columns? | |
| |
| |
| |
| Adding redundant data | |
| |
| |
| |
| Choosing a data type for a column | |
| |
| |
| |
| When should you use Not Null? | |
| |
| |
| |
| Closing remark | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Using indexes | |
| |
| |
| |
| Rows, tables and files | |
| |
| |
| |
| How does an index work? | |
| |
| |
| |
| Processing a Select statement: the steps | |
| |
| |
| |
| Creating and dropping indexes | |
| |
| |
| |
| Indexes and primary keys | |
| |
| |
| |
| Choosing columns for indexes | |
| |
| |
| |
| Special types of index | |
| |
| |
| |
| Indexes and the catalog | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Views | |
| |
| |
| |
| Creating views | |
| |
| |
| |
| The column names of views | |
| |
| |
| |
| Updating views: With Check Option | |
| |
| |
| |
| Views and the catalog | |
| |
| |
| |
| Restrictions on querying views | |
| |
| |
| |
| Restrictions on updating views | |
| |
| |
| |
| Processing view statements | |
| |
| |
| |
| Application areas for views | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Users and data security | |
| |
| |
| |
| Adding and removing users | |
| |
| |
| |
| Granting table privileges | |
| |
| |
| |
| Passing on privileges: With Grant Option | |
| |
| |
| |
| Working with roles | |
| |
| |
| |
| Recording privileges in the catalog | |
| |
| |
| |
| Revoking privileges | |
| |
| |
| |
| Denying privileges | |
| |
| |
| |
| Security of and through views | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Catalog tables | |
| |
| |
| |
| Querying the catalog tables | |
| |
| |
| |
| Protecting the catalog tables | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Programming with SQL | |
| |
| |
| |
| Introduction to embedded SQL | |
| |
| |
| |
| The pseudo programming language | |
| |
| |
| |
| Simple examples | |
| |
| |
| |
| Processing programs | |
| |
| |
| |
| Using host variables in SQL statements | |
| |
| |
| |
| The Sqlcode host variable | |
| |
| |
| |
| Executable versus non-executable SQL statements | |
| |
| |
| |
| The Whenever statement | |
| |
| |
| |
| Logging on to SQL | |
| |
| |
| |
| Select statements returning one row | |
| |
| |
| |
| The Null indicator | |
| |
| |
| |
| Select statements returning multiple rows | |
| |
| |
| |
| Processing cursors | |
| |
| |
| |
| The For clause | |
| |
| |
| |
| Deleting rows via cursors | |
| |
| |
| |
| Dynamic SQL | |
| |
| |
| |
| Examples of C programs | |
| |
| |
| |
| Transactions and multi-user usage | |
| |
| |
| |
| What is a transaction? | |
| |
| |
| |
| Embedded SQL and transactions | |
| |
| |
| |
| Savepoints | |
| |
| |
| |
| Problems with multi-user usage | |
| |
| |
| |
| Locking | |
| |
| |
| |
| Deadlocks | |
| |
| |
| |
| The granularity of locked data | |
| |
| |
| |
| The Lock Table statement | |
| |
| |
| |
| The isolation level | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Introduction to ODBC | |
| |
| |
| |
| The history of ODBC | |
| |
| |
| |
| How does ODBC work? | |
| |
| |
| |
| A simple example of ODBC | |
| |
| |
| |
| Returncodes | |
| |
| |
| |
| Retrieving data about SQL | |
| |
| |
| |
| DDL statements and ODBC | |
| |
| |
| |
| DML statements and OBDC | |
| |
| |
| |
| Using host variables in SQL statements | |
| |
| |
| |
| Settings for a statement handle | |
| |
| |
| |
| Select statements | |
| |
| |
| |
| Asynchronous processing of SQL statements | |
| |
| |
| |
| The For Update clause | |
| |
| |
| |
| Accessing catalog tables with ODBC | |
| |
| |
| |
| Levels and support | |
| |
| |
| |
| The competitors to ODBC | |
| |
| |
| |
| Optimization of statements | |
| |
| |
| |
| Avoid the OR operator | |
| |
| |
| |
| Avoid unnecessary use of the Union operator | |
| |
| |
| |
| Avoid the Not operator | |
| |
| |
| |
| Isolate columns in conditions | |
| |
| |
| |
| Use the Between operator | |
| |
| |
| |
| Avoid particular forms of the Like operator | |
| |
| |
| |
| Add redundant conditions to joins | |
| |
| |
| |
| Avoid the Having clause | |
| |
| |
| |
| Make the Select clause as small as possible | |
| |
| |
| |
| Avoid Distinct | |
| |
| |
| |
| Use of the All option | |
| |
| |
| |
| Prefer outer joins to Union operators | |
| |
| |
| |
| Avoid data type conversions | |
| |
| |
| |
| The largest table last | |
| |
| |
| |
| Avoid the Any and All operators | |
| |
| |
| |
| The future of the optimizer | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Procedural database objects | |
| |
| |
| |
| Stored procedures | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| An example of a stored proceedure | |
| |
| |
| |
| Definition of a stored procedure | |
| |
| |
| |
| Processing stored procedures | |
| |
| |
| |
| More complex examples | |
| |
| |
| |
| Removing stored procedures | |
| |
| |
| |
| Transactions, savepoints and subtransactions | |
| |
| |
| |
| Compiling and recompiling | |
| |
| |
| |
| Security with stored procedures | |
| |
| |
| |
| Advantages of stored procedures | |
| |
| |
| |
| Stored procedures and error messages | |
| |
| |
| |
| Extensions of other products | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Triggers | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| An example of a trigger | |
| |
| |
| |
| More complex examples | |
| |
| |
| |
| Triggers as constraints | |
| |
| |
| |
| Removing triggers | |
| |
| |
| |
| Differences between products | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Object relational concepts | |
| |
| |
| |
| User-defined data types, functions and operators | |
| |
| |
| |
| Creating user-defined data types | |
| |
| |
| |
| Access to data types | |
| |
| |
| |
| Casting of values | |
| |
| |
| |
| Creating user-defined functions | |
| |
| |
| |
| Creating user-defined operators | |
| |
| |
| |
| Opaque data type | |
| |
| |
| |
| Named row data type | |
| |
| |
| |
| The typed table | |
| |
| |
| |
| Constraints on data types | |
| |
| |
| |
| Keys and indexes | |
| |
| |
| |
| Answers | |
| |
| |
| |
| Inheritance, references and collections | |
| |
| |
| |
| Inheritance of data types | |
| |
| |
| |
| Linking tables via row identifications | |
| |
| |
| |
| Collections | |
| |
| |
| |
| Inheritance of tables | |
| |
| |
| |
| The future of SQL | |
| |
| |
| |
| A Syntax of SQL | |
| |
| |
| |
| The BNF notation | |
| |
| |
| |
| Reserved words in SQL3 | |
| |
| |
| |
| Syntax definitions of SQL statements | |
| |
| |
| |
| Scalar functions | |
| |
| |
| |
| Bibliography | |
| |
| |
| Index | |