SQL Bible

Alex Kriegel, PMP, MCSD/MCTS, works as a Systems Architect for the State of Oregon; prior to this he worked for Pope and Talbot, Inc., Psion Teklogix International, Inc., and for Belorussian Academy of Sciences. He received his MSc. in Physics of Metals from Belarus State Polytechnic Institute in 1988, discovered PC programming and relational databases in 1992, and has never looked back since. Alex is certified as a Project Management Professional (PMP) by the Project Management Institute, and also holds various Microsoft certifications. He is also the author of Microsoft SQL Server 2000 Weekend Crash Course (Wiley, 2001), SQL Functions (Wrox, 2005), and co-author of Introduction to Database Management (Wiley, 2007), and the first edition of this book. Boris M. Trukhnov, OCP, has been working as Senior Technical Analyst/Oracle DBA for Pope & Talbot, Inc., in Portland, Oregon, since 1998. His previous job titles include Senior Programmer Analyst, Senior Software Developer, and Senior Operations Analyst. He has been working with SQL and relational databases since 1994. Boris holds a B.S. in Computer Science from the University of Minnesota. He is a co-author of Introduction to Database Management (Wiley, 2007).

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