SQL Clearly Explained

by Harrington

• ISBN13: 9781558608764
• ISBN10: 1558608761
• Edition: 2nd
• Format: Paperback
• Copyright: 2003-04-11
• Publisher: Elsevier Science

Summary

This is the second edition of the popular practitioner's guide to SQL, the industry standard database query language. Revised to enhance practical usage. Also includes two new chapters on unimplemented SQL2 features and SQL3, to educate readers as to what is coming.

Author Biography

Jan L. Harrington has been writing about databases since 1984, and is the author of over 30 books, She is chair of the Department of Computer Science and Information Systems at Marist College, where she teaches database design and management, object-oriented programming, data communications, and computer architecture

Table of Contents

Preface to the Second Edition	v
The Organization of This Book	vi
The CD and the Sample Database	vii
Acknowledgments	vii
Part 1: Getting Started
Installing and Running MySQL	3	(6)
Linux	4	(2)
Installing Under Linux	5	(1)
Launching the Server Under Linux	5	(1)
Windows	6	(1)
Installing a Windows Distribution	6	(1)
Starting the Server Under Windows	7	(1)
Mac OS X	7	(2)
Creating and Loading Databases	9	(8)
Creating a New Database	10	(4)
Starting the MySQL Daemon	10	(2)
Running the Client	12	(1)
Creating New Users	13	(1)
Creating and Using the Database	13	(1)
Creating the Tables	14	(1)
Populating the Tables	15	(2)
Introduction to SQL	17	(18)
A Bit of SQL History	18	(2)
Conformance Levels	20	(1)
Relational Database Terminology and the Sample Database	21	(5)
Basic Relational Database Concepts	22	(1)
The Online Bookstore Database	23	(3)
Manipulating Relations	26	(1)
SQL Environments	27	(8)
Interactive SQL Command Processors	28	(3)
The Embedded SQL Dilemma	31	(4)
Part 2: Performing Interactive Data Manipulation
Simple SQL Retrieval	35	(30)
Choosing Columns	36	(5)
Retrieving All Columns	37	(1)
Retrieving Selected Columns	38	(1)
Removing Duplicates	38	(2)
The Relational Algebra Project Operation	40	(1)
Ordering the Result Table	41	(1)
Choosing Rows	42	(20)
Predicates	44	(11)
Performing Row Selection Queries	55	(6)
The Relational Algebra Restrict Operation	61	(1)
Nulls and Retrieval: Three-Valued Logic	62	(3)
Retrieving Data from More Than One Table: Joins	65	(36)
The Relational Algebra Join Operation	66	(5)
A Non-Database Example	66	(1)
Equi-Joins	67	(2)
What's Really Going On: Product and Restrict	69	(2)
SQL Syntax for Inner Joins	71	(10)
Traditional SQL Joins	71	(3)
SQL-92 Join Syntax	74	(2)
Joining Using Concatenated Keys	76	(2)
Joining More Than Two Tables	78	(3)
Finding Multiple Rows in One Table: Joining a Table to Itself	81	(4)
Correlation Names	83	(1)
Performing the Same-Table Join	84	(1)
Outer Joins	85	(5)
The Relational Algebra Outer Join	86	(3)
SQL Syntax for Outer Joins	89	(1)
Valid versus Invalid Joins	90	(4)
Avoiding Joins with Uncorrelated Subqueries	94	(7)
Using the IN Operator	95	(1)
Using the ANY Operator	96	(1)
Nesting Subqueries	97	(1)
Replacing a Same-Table Join with Subqueries	98	(3)
Advanced Retrieval Operations	101	(18)
Union	101	(7)
The Relational Algebra Union Operation and Union Compatibility	102	(1)
Performing a Query That Includes a Union	103	(4)
Alternative SQL-92 Union Syntax	107	(1)
Negative Queries	108	(7)
The Relational Algebra Difference Operation	108	(1)
Performing Queries That Require a Difference	109	(3)
Performing Negative Queries Using MySQL	112	(3)
The Exists Operator	115	(2)
CASE Expressions	117	(2)
Calculations and Grouping Queries	119	(28)
Performing Arithmetic	120	(3)
Arithmetic Operators	120	(1)
Operator Precedence	121	(2)
String Manipulations	123	(4)
Concatenation	123	(1)
UPPER and LOWER	124	(1)
TRIM	125	(1)
SUBSTRING	126	(1)
Date and Time Manipulation	127	(3)
Date and Time System Values	128	(1)
Date and Time Arithmetic	128	(2)
Set Functions	130	(6)
COUNT	131	(2)
SUM	133	(1)
AVG	134	(1)
MIN and MAX	134	(1)
Set Functions in Predicates	135	(1)
Changing Data Types: Using CAST	136	(3)
Grouping Queries	139	(8)
Forming Groups	139	(3)
Restricting Groups	142	(5)
Data Modification	147	(10)
Inserting Rows	148	(3)
Inserting One Row	148	(1)
Copying Multiple Rows	149	(1)
Placement of New Rows	150	(1)
Updating Data	151	(1)
Deleting Rows	152	(5)
Deletes and Referential Integrity	153	(4)
Part 3: Managing Database Structure
Schemas and Tables	157	(22)
Database Object Hierarchy	158	(2)
Naming and Identifying Objects	159	(1)
Schemas	160	(3)
Creating a Schema	161	(1)
Identifying the Schema You Want to Use	162	(1)
Domains	163	(1)
Tables	164	(15)
Column Data Types	166	(3)
Default Values	169	(1)
NOT NULL Constraints	169	(2)
Primary Keys	171	(1)
Foreign Keys	171	(5)
Additional Column Constraints	176	(3)
Views, Temporary Tables, and Indexes	179	(24)
Views	179	(5)
Why We Use Views	180	(1)
Creating Views	180	(2)
Querying Views	182	(1)
View Updatability Issues	183	(1)
Temporary Tables	184	(3)
Creating Temporary Tables	185	(1)
Loading Temporary Tables with Data	186	(1)
Disposition of Temporary Table Rows	186	(1)
Indexes	187	(3)
Deciding Which Indexes to Create	189	(1)
Creating Indexes	189	(1)
Modifying Database Elements	190	(5)
Adding New Columns	191	(1)
Adding Table Constraints	191	(1)
Modifying Columns	192	(1)
Deleting Elements	193	(1)
Renaming Elements	194	(1)
Deleting Database Elements	195	(1)
Granting and Revoking Access Rights	196	(7)
Types of Access Rights	196	(1)
Storing Access Rights	197	(1)
Granting Rights	198	(1)
Revoking Rights	199	(4)
Part 4: Program-Based Data Manipulation
Users, Sessions, and Transaction Control	203	(14)
The Concurrent Use Data Environment	204	(4)
Muddying the Waters: Isolation Levels	207	(1)
Database User IDs	208	(2)
Database Sessions and Connections	210	(2)
SQL for Connecting and Disconnecting	210	(1)
Session Length Considerations	211	(1)
Transaction Control	212	(5)
Transaction Read/Write Permissions	212	(1)
Transaction Termination	213	(1)
Starting Transactions	213	(1)
Ending Transactions	214	(1)
Transaction Length Considerations	215	(2)
Embedded SQL	217	(18)
The Embedded SQL Environment	217	(3)
Using Host Language Variables	219	(1)
DBMS Return Codes	220	(1)
Retrieving a Single Row	221	(3)
Indicator Variables	223	(1)
Retrieving Multiple Rows: Cursors	224	(7)
Declaring a Cursor	225	(3)
Opening a Cursor	228	(1)
Fetching Rows	228	(2)
Closing a Cursor	230	(1)
Embedded SQL Data Modification	231	(4)
Direct Modification	231	(1)
Indicator Variables and Data Modification	232	(1)
Modification Using Cursors	232	(2)
Deletion Using Cursors	234	(1)
Dynamic SQL	235	(16)
Immediate Execution	236	(2)
Dynamic SQL with Dynamic Parameters	238	(13)
Creating the Statement String	239	(2)
Setting up Parameter Descriptor Areas	241	(1)
Preparing the Statement	242	(1)
Describing, Setting, and Accessing Parameters	243	(4)
Executing the Statement	247	(1)
Removing Dynamic SQL Elements from Main Memory	248	(3)
Part 5: Additional Language Features
Unimplemented SQL-92 Features	251	(14)
Additional Relational Algebra Operations	251	(5)
The Union Join	252	(1)
Except	253	(1)
Intersect	254	(2)
Additional Predicate Operators	256	(3)
UNIQUE	256	(1)
OVERLAPS	256	(1)
MATCH	257	(2)
Table Constructors in Queries	259	(1)
Additional Foreign Key Options	260	(1)
Assertions	261	(1)
Determining When Constraints Are Checked	261	(4)
Changing the Constraint Mode	262	(3)
Object-Oriented Features of the SQL:1999 Standard	265	(68)
The Basics of Object Orientation	266	(20)
Writing Instructions	267	(1)
Objects	268	(5)
Classes	273	(5)
Class Relationships	278	(7)
Benefits of Object Orientation	285	(1)
SQL:1999 versus Pure Object-Orientation	286	(2)
Object-Relational Support	288	(1)
Typed Tables	289	(1)
Creating Classes	290	(5)
Creating and Using Classes for Domains	291	(1)
Creating Typed Tables	292	(2)
SQL:1999 Inheritance	294	(1)
Querying with Classes	295	(2)
Modifying Data for Classes as Domains	297	(4)
Part 6: Appendices
Appendix A: The Online Bookstore Database	301	(8)
Appendix B: SQLSTATE Return Codes	309	(6)
Appendix C: SQL Syntax Summary	315	(8)
Appendix D: SQL:1999 Core Features	323	(10)
Glossary	333	(10)
Index	343