Introduction to SQL: Mastering the Relational Database Language / Edition 3
by Rick Van Der LansISBN-10: 0201596180
ISBN-13: 9780201596182
Pub. Date: 11/12/1999
Publisher: Addison-Wesley
-Ian Cargill, Database Consultant, Soliton Software Ltd
SQL was, is and always will be the database language for relational database systems such as Oracle, DB2, Sybase, Informix and Microsoft SQL Server. Introduction to SQL describes in depth the full capacity of SQL as it is implemented by the commercial databases, without neglecting
Overview
-Ian Cargill, Database Consultant, Soliton Software Ltd
SQL was, is and always will be the database language for relational database systems such as Oracle, DB2, Sybase, Informix and Microsoft SQL Server. Introduction to SQL describes in depth the full capacity of SQL as it is implemented by the commercial databases, without neglecting the most recent changes to the standard, bringing the book up to date and fully compliant with SQL3. Unique in the extent of its coverage, this book takes you from the beginning to the end of SQL, the concepts to the practice, the apprentice to the master.
Learn how to:
i Query data
i Update data
i Create tables and views
i Specify primary and foreign keys
i Use indexes
i Secure your data
i Develop stored procedures and triggers
i Develop applications with embedded SQL and ODBC
i Carry out transactions
i Optimize statements
i Deal with object-relational concepts such as subtables, references, sets and user-defined data types.
NEW coverage of object-oriented concepts
NEW information on application development with pre-programmed SQL
NEW chapter on using SQL in combination with ODBC
The best way to learn a language is to work with it and the accompanying CD-Rom contains an SQL product called SOLID, chosen because it employs every aspect of SQL covered in the text. Experiment using the exercises in the book.
Product Details
- ISBN-13:
- 9780201596182
- Publisher:
- Addison-Wesley
- Publication date:
- 11/12/1999
- Edition description:
- Older Edition
- Pages:
- 720
- Product dimensions:
- 6.86(w) x 9.24(h) x 1.41(d)
Table of Contents
(Each chapter concludes with Answers.)
Preface.
I. INTRODUCTION.
1. Introduction to SQL. 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 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.
2. The Tennis Club Sample Database.
Description of the Tennis Club.
The Contents of the Tables.
Constraints.
The Contents of the Tables.
Constraints.
3. 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.
The Version of SOLID Supplied with the Book.
Installing SOLID.
Stopping SOLID.
Starting SOLID.
Entering Statements.
Editing Statements.
Storing Statements.
Additional Documentation.
4. 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.
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.
II. QUERYING AND UPDATING DATA.
5. SELECT Statement:Common Elements
Data Types and Literals.
Expressions.
System Variables.
The Case Expression.
Numeric Expressions.
Alphanumeric Expressions.
Scalar Functions.
Date Expressions.
Time Expressions.
Timestamp Expressions.
Set Functions and Subqueries.
Casting of Values and Expressions.
Answers.
Expressions.
System Variables.
The Case Expression.
Numeric Expressions.
Alphanumeric Expressions.
Scalar Functions.
Date Expressions.
Time Expressions.
Timestamp Expressions.
Set Functions and Subqueries.
Casting of Values and Expressions.
Answers.
6. Clauses of the SELECT Statement.
Processing a SELECT Statement: Example 1.
Processing a SELECT Statement: Example 2.
The Tables Expression.
Processing a SELECT Statement: Example 2.
The Tables Expression.
7. The SELECT Statement: The FROM Clause.
Table Specifications in the FROM Clause.
The Column Specification.
Multiple Tables Specifications.
The Pseudonym.
Various examples.
Mandatory use of Pseudonyms.
The Column Specification.
Multiple Tables Specifications.
The Pseudonym.
Various examples.
Mandatory use of Pseudonyms.
8. 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.
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.
9. 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.
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.
10. 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.
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.
11. 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.
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.
12. 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.
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.
13. The Subquery.
Rules for Subqueries.
Range of Columns.
Examples with Correlated Subqueries.
Subqueries in the SELECT Clause.
Working with Composite Keys.
Range of Columns.
Examples with Correlated Subqueries.
Subqueries in the SELECT Clause.
Working with Composite Keys.
14. 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 Tables in the FROM Clause.
Joins without Join Conditions.
Outer Joins.
Examples with Outer Joins.
Cross Join and Union Join.
Equi Joins and Theta Joins.
Subqueries in the FROM Clause.
Creating a New Tables in the FROM Clause.
Joins without Join Conditions.
15. Updating Tables.
Inserting New Rows.
Populating a Table with Rows from Another Table.
Updating Values in Rows.
Deleting Rows from a Table.
Populating a Table with Rows from Another Table.
Updating Values in Rows.
Deleting Rows from a Table.
III. CREATING DATABASE OBJECTS.
16. 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.
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.
17. Specifying Constraints.
Primary Keys.
Alternate Keys.
Foreign Keys.
The Referencing Action.
Check Constraints.
Deleting Constraints.
Naming Constraints.
Constraints and the Catalog.
Alternate Keys.
Foreign Keys.
The Referencing Action.
Check Constraints.
Deleting Constraints.
Naming Constraints.
Constraints and the Catalog.
18. Designing Tables.
Which Tables and Columns?
Adding Redundant Data.
Choosing a Data Type for a Column.
When Should You Use NOT NULL?
Closing Remark.
Adding Redundant Data.
Choosing a Data Type for a Column.
When Should You Use NOT NULL?
Closing Remark.
19. Using Indexes.
Rows, Tables and Files.
How does an Indexes Work?
Processing a SELECT Statement: The Steps.
Creating and Dropping Indexes.
Indexes and Primary Keys.
Choosing Columns for Indexes.
Special Types of Indexes.
Indexes and the Catalog.
How does an Indexes Work?
Processing a SELECT Statement: The Steps.
Creating and Dropping Indexes.
Indexes and Primary Keys.
Choosing Columns for Indexes.
Special Types of Indexes.
Indexes and the Catalog.
20. 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.
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.
21. 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.
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.
22. Catalog Tables.
Querying the Catalog Tables.
Protecting the Catalog Tables.
Protecting the Catalog Tables.
IV. PROGRAMMING WITH SQL.
23. 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.
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.
24. 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.
Embedded SQL and Transactions.
Savepoints.
Problems with Multi-user Usage.
Locking.
Deadlocks.
The Granularity of Locked Data.
The LOCK TABLE Statement.
The Isolation Level.
25. 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.
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.
26. 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.
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.
V. PROCEDURAL DATABASE OBJECTS.
27. Stored Procedures.
Introduction.
An Example of a Stored Procedure.
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.
An Example of a Stored Procedure.
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.
28. Triggers.
Introduction.
An Example of a Trigger.
More Complex Examples.
Triggers as Constraints.
Removing Triggers.
Differences Between Products.
An Example of a Trigger.
More Complex Examples.
Triggers as Constraints.
Removing Triggers.
Differences Between Products.
VI. OBJECT RELATIONAL CONCEPTS.
29. 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.
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.
30. Inheritance, References and Collections.
Inheritance of Data Types.
Linking Tables Via Row Identifications.
Collections.
Inheritance of Tables.
Linking Tables Via Row Identifications.
Collections.
Inheritance of Tables.
31. The Future of SQL.
Appendix A. Syntax of SQL.
The BNF Notation.
Reserved Words in SQL3.
Syntax Definitions of SQL Statements.
Reserved Words in SQL3.
Syntax Definitions of SQL Statements.
Appendix B. Scalar Functions.
Appendix C. Bibliography.
Index.
Customer Reviews
Average Review: