Advanced Oracle SQL Programming: Expert Guide to Writing Complex Queries

0.0

Oracle SQL is one of the world's most powerful dialects, packed with advanced functions and features that give Oracle SQL  procedural language capabilities.  Using these powerful features it is no longer necessary to embed Oracle SQL inside a procedural language, and native Oracle SQL can now be used to solve complex query problems. This is an indispensable book for all Oracle DBA's and developers who need to understand how to apply Oracle SQL features for complex queries. Written by a certified Oracle Master and Oracle ACE, this advanced book shows working examples of how to leverage Oracle SQL's powerful built-in functions for fast extraction of complex data and how to use native SQL for procedural processing. Every developer needs to know how to use Oracle SQL to summarize and aggregate data from large tables and how to use powerful features such as the "model" clause to make SQL act as a procedural language. This book shows important techniques on how to leverage Oracle's powerful XML interface to quickly format Oracle data with XML for instant publication. This is not a book for beginners or dilettantes and it is designed for the professional Oracle developer, covering many advanced Oracle SQL techniques including XML queries, Oracle SQL analytics, SQL for aggregation, and traversing Hierarchies. Best of all, the author reveals secrets for retrieving Oracle data faster and more elegantly than the traditional approaches to writing SQL. About The Author Laurent Schneider is an Oracle Certified Master with more than 10 years of experience with Oracle and UNIX. He has written many articles about Oracle technologies and has provided thousands of answers on online technical forums. He is currently a management system engineer at LC Systems Engineering. Donald Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience. He has written 30 books, including Oracle Data Mining and Oracle Shell Scripting, and serves as editor-in-chief of Rampant TechPress. Table of Contents Using the Online Code Depot           Conventions Used in this Book         Acknowledgements  Chapter 1: Oracle SQL Query Overview  Introduction   Syntax Review           Subquery Inline view Nested subquery        Joins   Sets    SQL expressions        Conclusion     Exercises        Solutions        Chapter 2: Oracle SQL Function Oracle SQL functions Mathematic functions Binary functions        Signs functions          Rounding and truncating functions   Modulo functions      Functions to search and modify strings         Regular expression functions Conversion functions NLS functions           National character set Logical functions with true or false values    Null functions Conclusion     Exercises        Solutions        Chapter 3: SQL Aggregate Functions Aggregate functions  Standard Aggregate Functions          Distinct          Keep   Nested Aggregates    Subtotals        PIVOT and UNPIVOT         Conclusion     Exercises        Solutions        Chapter 4: Oracle SQL Analytics Analytics        OVER PARTITION  Ranking functions      Window         Aggregation   FIRST_VALUE and LAST_VALUE          Conclusion     Exercises        Solutions        Chapter 5: XML XML  XML Instance XMLTYPE    XMLELEMENT       XMLCONCAT and XMLFOREST XPATH          XMLSEQUENCE     XQuery          Aggregation   XMLSERIALIZE     XMLCAST    Conclusion     Exercises        Solutions        Chapter 6: Oracle Hierarchies Hierarchies     Hierarchical Queries   CONNECT BY, PRIOR and START WITH           LEVEL          ORDER SIBLINGS BY       PRIOR           SYS_CONNECT_BY_PATH           CONNECT_BY_ROOT       CONNECT BY LOOP         CONNECT BY NOCYCLE and CONNECT_BY_ISCYCLE      CONNECT BY without PRIOR       CONNECT_BY_IS_LEAF  WHERE and JOIN    Aggregation   Conclusion     Exercises        Solutions Chapter 7: SQL For Modeling SQL for Modeling     Partitions, dimensions and measures Update           Upsert CV     Conditions     FOR loops      Iterations        Reference Model       Aggregation   Analytics        Ordered Rows           Conclusion     Exercises        Solutions        Appendix A:  SQL*Plus Starting SQL*Plus     SQL*Plus Statements Formatting     Substitution Variables HTML Index  About Laurent Schneider About Chen Shapira About Tom Routen