Structure of This Book
This book is divided into 18 chapters and 1 appendix:
Chapter 1, introduces the SQL language and describes its brief history. This chapter is primarily for those readers who have little or no prior SQL experience. You'll find simple examples of the core SQL statements (SELECT, INSERT, UPDATE, and DELETE) and of SQL's basic features.
Chapter 2, describes ways to filter data in your SQL statements. You'll learn to restrict the results of a query to the rows you wish to see, and restrict the results of a data manipulation statement to the rows you wish to modify.
Chapter 3, describes constructs used to access data from multiple, related tables. The important concepts of inner join and outer join are discussed in this chapter.
Chapter 4, shows you how to generate summary information, such as totals and subtotals, from your data. Learn how to define groups of rows, and how to apply various aggregate functions to summarize data in those groups.
Chapter 5, shows you how to use correlated and noncorrelated subqueries and inline views to solve complex problems that would otherwise require procedural code together with more than one query.
Chapter 6, talks about handling date and time information in an Oracle database. Learn the tricks and traps of querying time-based data.
Chapter 7, shows you how to use UNION, INTERSECT, and MINUS to combine results from two or more independent component queries into one.
Chapter 8, shows you how to store and extract hierarchical information (such as in an organizational chart) from a relational table. Oracle provides many features to facilitate working with hierarchical data, including several new features introduced in Oracle Database 10g.
Chapter 9, talks about two very powerful yet simple features of Oracle SQL that enable you to simulate conditional logic in what is otherwise a declarative language.
Chapter 10, discusses the issues involved with creating and accessing partitioned tables using SQL. Learn to write SQL statements that operate on specific partitions and subpartitions.
Chapter 11, explores the integration of SQL and PL/SQL. This chapter describes how to call PL/SQL stored procedures and functions from SQL statements, and how to write efficient SQL statements within PL/SQL programs.
Chapter 12, explores the object-oriented aspects of the Oracle database server, including object types and collections.
Chapter 13, deals with complex grouping operations used mostly in decision support systems. We show you how to use Oracle features such as ROLLUP, CUBE, and GROUPING SETS to efficiently generate various levels of summary information required by decision-support applications. We also discuss the grouping features that enable composite and concatenated groupings, including the GROUP_ID and GROUPING_ID functions.
Chapter 14, deals with analytical queries and analytic functions. Learn how to use ranking, windowing, and reporting functions to generate decision-support information.
Chapter 15, talks about best practices that you should follow to write efficient and maintainable queries. Learn which SQL constructs are the most efficient for a given situation. For example, we describe when it's better to use WHERE instead of HAVING to restrict query results. We also discuss the performance implications of using bind variables vis-à-vis literal SQL.
Chapter 16, explores how the Oracle server can store XML documents, features used to navigate, search, and extract content from XML documents, and functions used to generate XML documents from ordinary tables.
Chapter 17, shows how to write and interpret regular expressions for performing advanced text searches and substitutions.
Chapter 18, introduces the new, MODEL clause, which lets you manipulate relational data as if it were a big, multidimensional, spreadsheet (Oracle prefers the term model). Model queries enable you to solve problems using a single SQL statement that previously would have required you to download data to a third-party, spreadsheet program such as Microsoft Excel.
The Appendix, describes the SQL89 join syntax, and Oracle's proprietary, outer-join syntax. Only this syntax was available for joins until the release of Oracle9i Database, which introduced support for the newer, and better, SQL92 join syntax.
