SQL Bible

Acknowledgements xxiii Introduction xxv Part I SQL Basic Concepts and Principles Chapter 1: SQL and Relational Database Management Systems 3 Desirable Database Characteristics 3 Sufficient capacity 4 Adequate security and auditing 4 Multiuser environment 4 Effectiveness and searchability 4 Scalability 5 User friendliness 5 Selecting Your Database Software 6 Market share 6 Total cost of ownership 6 Support and persistence 7 Major DBMS Implementations 7 Real-Life Database Examples 9 Order management system database 9 Health care provider database 10 Video sharing and editing database 10 Scientific database 11 Nonprofit organization database 11 Database Legacy 11 Flat file databases 11 Hierarchical databases 12 Network databases 14 Relational Databases 15 Tables 16 Relationships 17 Primary key 17 Foreign key 18 Invasion of RDBMS 18 Other DBMS Models 19 Brief History of SQL and SQL Standards 20 Humble beginnings: RDBMS and SQL evolution 20 A brief history of SQL standards 23 Summary 26 Chapter 2: Fundamental SQL Concepts and Principles 27 Promises and Deliverables 27 SQL: The First Look 32 Database example 32 Getting the data in and out 33 Slice and dice: Same data, different angle 35 Aggregation 37 Data security 38 Accessing data from a client application 39 New developments 40 Any Platform, Any Time 40 Summary 43 Chapter 3: SQL Data Types 45 No Strings Attached 46 Character strings 46 Binary strings 54 In Numbers Strength 56 Exact numbers 56 Approximate numbers 60 Once Upon a Time: Date and Time Data Types 61 Introduction to complex data types 62 Date and time implementations 63 XML Data Type 69 XML data type implementations 69 Constructed and User-Defined Data Types 70 SQL:2003 71 Oracle 11g 73 DB2 9.5 75 Microsoft SQL Server 2008 76 Other Data Types 77 BOOLEAN 77 ROWID 77 UROWID 78 BFILE 78 DATALINK 78 BIT 78 TIMESTAMP 78 SQL_VARIANT 78 Null 79 Summary 79 Part II Creating and Modifying Database Objects Chapter 4: Creating RDBMS Objects 83 Tables 83 CREATE TABLE Statement 84 Indexes 113 CREATE INDEX statement 116 Views 120 CREATE VIEW statement 121 Creating complex views 127 Aliases and Synonyms 130 SQL:2003 131 Oracle 11 g CREATE SYNONYM statement 131 DB2 9.5 CREATE ALIAS statement 133 Microsoft SQL Server 2008 CREATE SYNONYM statement 133 Schemas 134 CREATE SCHEMA statement 134 Sequences 138 External sequence generators in SQL:2003 139 Sequences in Oracle 11 g 139 DB2 9.5 142 Other SQL:2003 and Implementation-Specific Objects 143 Domains (SQL:2003) 144 Character sets (SQL:2003) 144 Collations (SQL:2003) 144 Tablespaces and filegroups 144 Materialized views (Oracle 11 g) 147 Database links (Oracle 11 g) 149 Directories (Oracle 11 g) 150 CREATE Statement Cross-Reference 151 Summary 154 Chapter 5: Altering and Destroying RDBMS Objects 155 Tables 155 ALTER TABLE statement 156 DROP TABLE statement 166 Indexes 168 ALTER INDEX statement 168 DROP INDEX statement 170 Views 171 ALTER VIEW statement 171 DROP VIEW statement 173 Aliases and Synonyms 173 Oracle 11 g 174 DB2 9.5 174 Microsoft SQL Server 2008 174 Schemas 174 SQL:2003 174 DB2 9.5 175 Microsoft SQL Server 2008 175 Sequences 176 ALTER SEQUENCE statement 176 DROP SEQUENCE statement 177 Other Implementation-Specific Objects 178 Domains (SQL:2003) 178 Character sets (SQL:2003) 178 Collations (SQL:2003) 178 Tablespaces 178 Materialized views (Oracle 11 g) 180 Database Links (Oracle 11 g) 180 Directories (Oracle 11 g) 181 ALTER and DROP Statements Cross-Reference 181 Summary 186 Part III Data Manipulation and Transaction Control Chapter 6: Data Manipulation Language (DML) 191 INSERT: Populating Tables with Data 191 Common INSERT statement clauses 192 INSERT statement vendor-related specifics 200 UPDATE: Modifying Table Data 208 Common UPDATE statement clauses 209 Vendor-specific UPDATE statement details 213 DELETE: Removing Data from Tables 216 Common DELETE statement clauses 216 Vendor-specific DELETE statement clauses 218 MERGE: Combining INSERT, UPDATE, and DELETE in One Statement 219 Common MERGE statement clauses 219 Vendor-specific MERGE statement clauses 220 TRUNCATE Statement 223 Differences between Oracle and Microsoft SQL Server TRUNCATE statements 224 Summary 225 Chapter 7: Sessions, Transactions, and Locks 227 Sessions 227 Transactions 237 What is a transaction? 237 Transactions COMMIT and ROLLBACK 241 Transaction isolation levels 248 Understanding Locks 251 Locking modes 252 Dealing with deadlocks 256 Summary 257 Part IV Retrieving and Transferring Data Chapter 8: Understanding the SELECT Statement 261 Single Table SELECT Statement Syntax 261 SELECT Clause: What Do You Select? 262 Single-column select 262 Multicolumn SELECT 263 Using literals, functions, and calculated columns 267 Using subqueries in a SELECT clause 271 FROM Clause: Select from What? 272 Selecting from tables and views 272 Using aliases in a FROM clause 273 Using subqueries in a FROM clause (inline views) 273 WHERE Clause: Setting Horizontal Limits 274 Using comparison operators 275 Compound operators: Using AND and OR 276 Using the BETWEEN operator 277 Using the IN operator: Set membership test 278 The NOT operator 279 Using the IS NULL operator: Special test for NULLS 280 Using subqueries in a WHERE clause 281 GROUP BY and HAVING Clauses: Summarizing Results 285 ORDER BY Clause: Sorting Query Output 288 Combining the Results of Multiple Queries 291 Union 292 Intersect 295 Except (minus) 296 SQL Analytic Functions and Top N Queries 299 Analytic functions and the SQL:2003 standard 299 Designing top N queries 301 Limit N queries 303 Summary 304 Chapter 9: Multitable Queries 307 Inner Joins 307 Two syntaxes for inner joins 308 Equijoin 309 Nonequijoin 312 Self-join 314 Cross join (Cartesian product) 317 Joining more than two tables 318 Outer Joins: Joining Tables on Columns Containing NULL Values 321 Two syntaxes for outer joins 321 Left outer join 323 Right outer join 326 Full outer join 327 Union join 328 Joins Involving Inline Views 329 Multitable Joins with Correlated Queries 331 Improving Efficiency of Multitable Queries 333 Summary 334 Chapter 10: SQL Functions 335 Numeric functions 338 String functions 345 Date and time functions 357 Aggregate functions 368 Conversion functions 376 System Functions 393 Miscellaneous functions 397 User-defined functions 404 Summary 405 Chapter 11: SQL Operators 407 Arithmetic and String Concatenation Operators 407 Logical Operators 411 ALL 412 ANY | SOME 412 BETWEEN AND 413 IN 414 EXISTS 416 LIKE 417 AND 421 NOT 421 OR 422 Operator Precedence 422 Assignment Operator 425 Comparison Operators 425 Bitwise Operators 430 Summary 432 Part V Implementing Security Using the System Catalog Chapter 12: SQL and RDBMS Security 435 Basic Security Mechanisms 435 Identification and authentication 436 Authorization and access control 436 Encryption 436 Integrity and consistency 436 Auditing 436 Defining a Database User 437 Managing Security with Privileges 443 GRANT statement 444 REVOKE privileges 460 Managing Security with Roles 465 Using Views for Security 472 Using Constraints for Security 475 Using Stored Procedures and Triggers for Security 477 Data encryption 480 Database Auditing 485 Security Standards 489 International security standards 490 Regulatory compliance 491 Summary 493 Chapter 13: The System Catalog and INFORMATION_SCHEMA 495 SQL System Catalogs 495 Oracle 11 g Data Dictionary 500 Oracle data dictionary structure 501 Oracle data dictionary and SQL:2007 standards 504 One level deeper: Data about metadata 505 IBM DB2 9.5 System Catalogs 507 The INFORMATION_SCHEMA objects in DB2 507 Obtaining information about INFORMATION_SCHEMA objects 510 Microsoft SQL Server 2008 System Catalog 511 MS SQL Server 2008 INFORMATION_SCHEMA Views 511 Microsoft SQL Server system stored procedures 515 Microsoft SQL Server 2008 system functions 520 Summary 523 Part VI Beyond SQL: Procedural Programming and Database Access Mechanisms Chapter 14: Stored Procedures, Triggers, and User-Defined Functions 527 Procedural Extension Uses and Benefits 529 Performance and network traffic 529 Database security 529 Code reusability 530 Key Elements of a SQL Procedural Language 530 Variables and assignment 530 Modularity, subprograms, and block structure 532 Passing parameters 533 Oracle 11 g 536 DB2 9.5 536 Microsoft SQL Server 2008 537 Conditional execution 537 Repeated execution 539 Cursors 540 Error handling 544 Stored Procedures 547 CREATE PROCEDURE syntax 547 Creating a simple stored procedure 548 Removing a stored procedure 554 User-Defined Functions 554 CREATE FUNCTION syntax 554 Creating a simple function 555 Removing a user-defined function 559 Triggers 559 CREATE TRIGGER syntax 559 Removing a trigger 563 .NET Stored Procedures and Functions 563 Summary 565 Chapter 15: SQL and XML 567 Introduction 567 The Structure of XML 567 XML as a data source 573 Encoding XML 574 Presenting XML documents 575 XML and RDBMS 577 Oracle 11 g XML DB 579 IBM DB 9.5 pureXML 586 Microsoft SQL Server 2008 590 Summary 596 Chapter 16: SQL and Procedural Programming 599 SQL Statement Processing Steps 600 Embedded and Dynamic SQL 601 Embedded SQL 601 Dynamic SQL techniques 606 The future of Embedded and Dynamic SQL 614 SQL/CLI Standard 614 Open Database Connectivity and Object Linking and Embedding, Database 619 ODBC 619 OLEDB 621 SQL and Java 621 Java Database Connectivity (JDBC) 622 SQLJ 627 Oracle API options 629 Oracle Call Interface 629 Oracle Objects for OLE 631 Oracle Data Provider for .NET 632 IBM DB2 Call-Level Interface 632 Microsoft Data Access Interfaces 633 ActiveX Data Objects 634 Ado.net 634 Summary 640 Chapter 17: The Future of SQL 643 OLAP and Business Intelligence 644 Oracle 11 g 647 IBM DB2 9.5 647 Microsoft SQL Server 2008 648 LINQ to SQL 649 Objects 650 OOP Paradigm 650 Object Language Bindings (SQL/OLB) in SQL:2003 Standard 652 Oracle 11 g support 652 IBM DB2 9.5 support 653 Microsoft SQL Server 2008 654 Abstract data types 656 Object-oriented databases 656 Summary 658 Appendix A: Accompanying Website 661 Appendix B: The ACME Sample Database 663 General Information and Business Rules 663 Naming Conventions 664 Relationships between Tables 665 Column Constraints and Defaults 665 Indexes 667 Views 667 SQL Scripts to Create ACME Database Objects 667 Appendix C: Basics of Relational Database Design 679 Identifying Entities and Attributes 680 Normalization 681 First normal form 682 Second normal form 683 Third normal form 683 Specifying Constraints 686 OLTP versus OLAP Designs 687 Data warehouses and data marts 687 Star and snowflake schemas 688 Pitfalls of Relational Database Design 688 Appendix D: Installing RDBMS Software 691 Installing Oracle 11g 691 Installing Oracle 11g release 1 (11.1.0.6.0) Enterprise Edition on Linux 692 Installing Oracle 11g release 1 (11.1.0.6.0) Personal Edition on Windows 708 Installing IBM DB2 9.5 710 Installing DB2 9.5 software on Windows 710 Installing Microsoft SQL Server 2008 720 Prerequisites for the Microsoft SQL Server 2008 (CTP, November 2007 release) 720 Installing on Windows Vista Enterprise Edition 721 Appendix E: Accessing RDBMS 731 Using ORACLE 11g Utilities to Access RDBMS 731 SQL∗Plus 731 SQL Developer 736 Oracle Enterprise Manager (SQL Worksheet) 740 Using IBM DB2 9.5 Utilities to Access the RDBMS 741 Command-Line Processor (CLP) 741 Command Editor 744 Using Microsoft SQL Server 2008 Utilities to Access the RDBMS 745 Using SQLCMD 745 Using SQL Server Management Studio 747 Appendix F: Installing the ACME Database 749 Installing the ACME Database on Oracle 11g Using SQL∗Plus 749 Linux / Unix 749 Microsoft Windows 750 Installing the ACME Database on DB2 9.5 (Windows) Using CLP 751 Installing the ACME Database on Microsoft SQL Server 2008 Using the SQLCMD Utility 753 Appendix G: SQL Functions 755 Appendix H: SQL Syntax Reference 795 DDL Statements 796 Tables 796 Indexes 796 Views 796 Schemas 797 Stored procedures 797 User-defined functions 797 Triggers 798 dcl Statements 798 DML Statements 798 DQL Statements 799 Single-table SELECT 799 Multitable SELECT 799 Transactional Control Statements 800 Predicates 800 Appendix I: SQL-Reserved Keywords 803 Future Keywords 813 ODBC Reserved Keywords 815 Appendix J: The Other RDBMSs 819 Appendix K: A Brief Introduction to Number Systems, Boolean Algebra, and Set Theory 825 The Number Systems 825 The RDBMS connection 826 Converting numbers 826 Logic Elements of Boolean Algebra 828 NOT (complement or inverter) 828 AND (Boolean product) 829 OR (Boolean sum) 829 XOR (exclusive OR) 829 NAND (inversed AND) 830 NOR (inversed OR) 830 Rules of precedence 831 Set Theory 832 The listing of sets 832 Subsets 833 Set equality 834 Operations on sets 834 Identities of set algebra 838 Index 839