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.

• Understand advanced Oracle SQL analytics
  • Learn how to traverse complex hierarchical relationships
  • Expand your understanding of Oracle SQL to solve complex business queries
  • Download working examples of Oracle analytic functions
• See how to quickly format Oracle data with XML for seamless publication
• Learn expert tips and tricks for writing high-quality SQL  

  Laurent Schneider

Laurent Schneider is one of the most respected authors in Oracle technology, with many years of experience as a Systems Engineer and Database Engineer.

Laurent achieved the highest level of DBA certification in 2004, being the first Oracle Certified Master in Switzerland and recipient of the prestigious Oracle Technology Network ACE trophy.   Laurent has over a decade of expertise in development - specializing in data warehousing and database modeling as well as database administration. He currently works for a successful Swiss bank as application architect/developer.

In his spare time, Laurent enjoys studying Chinese Chess strategy and has won the Swiss Championship. He lives on the sunny side of the Uetliberg with his wife Bertille, his daughter Dora and his son Loïc.

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 Functions   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_ISLEAF   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  

[ [[:alpha:]]        

A ABS    ALL    AND   antijoin            ANY   APPENDCHILDXML          ASC    ASCIISTR      AUTOTRACE            AVG  

B BEGINTIME  BETWEEN     BFILE BFILENAME BIN_TO_NUM          BINARY_DOUBLE BINARY_FLOAT     BITAND         BLOB

C CASE  CAST  CC       CEIL   CHAR CHR    CLOB COALESCE   CONCAT       CONNECT BY        CONNECT_BY_ISLEAF   CONNECT_BY_ISCYCLE  CONNECT_BY_ROOT        correlated subquery     COUNT          COUNT DISTINCT   COUNT(*)      CROSS JOIN CUBE CURRENT ROW       CV     

D D                    DATE DAY   dbms_rowid    DD      DECODE       DELETEXML            DENSE_RANK         DESC  DIMENSION DISTINCT      DUAL

E ENAME          ENDTIME      equijoin           EVALNAME EXCLUDE NULLS   execution plan EXISTS          EXISTSNODE           EXTRACT      EXTRACTVALUE   

F FAST DUAL  FF                    FIRST_VALUE         FLOOR           FM      FOLLOWING            FOR loops       FROM full outer join  function-based indexes          

G GREATEST    greedy expression       GROUP BY   GROUP_ID    GROUPING   GROUPING SETS   

H hash join          HASH JOIN FULL OUTER HAVING        HEXTORAW HH      HH24 HOUR           

I IGNORE NULLS      IN                    INCLUDE NULLS    INITCAP        inline view       INSERTCHILDXML            INSERTXMLBEFORE         INSTR            INSTR2          INSTR4          INSTRB          INTERSECT  IS ANY          IS PRESENT  ITERATE       ITERATION_NUMBER       IW        

J J                       join      JOIN  

K KEEP 

L LAG    LAST_VALUE          LEAD LEAST            LENGTH        LENGTH2      LENGTH4      LENGTHB     LENGTHC     LEVEL           LNNVL          LOWER          LPAD LTRIM           

M materialized views      MAX   MEASURES  MI       MINUS           MINUTE        MM     MOD   MODEL          modulo            MONTH        

N NATURAL JOIN       NCHR nested subquery         NLS_CHARSET_ID NLS_DATE_TERRITORY   NLS_INITCAP          NLS_LOWER            NLS_SORT    NLS_TERRITORY    NLS_UPPER  NOCYCLE     non-greedy expression            NOT EXISTS NOT IN          NOT NULL    NULL NULLIF         NUMBER       NUMTODSINTERVAL        NUMTOYMINTERVAL      NVL    NVL2 

O OBJECT_VALUE     OCCURRENCE        ON      ora:view          Oracle Calendar          Oracle Locale Builder ORDER BY   ORDER SIBLINGS BY        outer join         OVER

P PARTITION   PARTITION BY        partition key    partition views            partitioned outer join  PERIODBEGIN        PIVOT            PIVOT XML  POSIX            PRECEDING PRIOR            pseudo column           

Q Q                    

R RANGE          RANGE BETWEEN  RANGE CURRENT ROW   RANK            RATIO_TO_REPORT           RAW   RAWTOHEX REF CURSOR           REGEXP_COUNT    REGEXP_INSTR      REGEXP_LIKE         REGEXP_REPLACE        REGEXP_SUBSTR   REMAINDER            REPLACE      RM      ROLLUP        ROUND         ROW_NUMBER       ROWNUM     ROWS            ROWS BETWEEN    ROWS CURRENT ROW      RPAD RR       RTRIM           RULES UPDATES   

S scalar subquery            SECOND        SELECT         semijoin           session-independent views     SIBLINGS     SIGN  SINGLE REFERENCE         single-row subquery    SOME SORT GROUP BY    SORT UNIQUE         SP                    SQL Spreadsheet        SSSSS START WITH            STATS_MODE          STOPKEY      subquery factoring      SUBSTR         SUBSTR2       SUBSTR4       SUBSTRB      SUBSTRC      SUM   SYS_CONNECT_BY_PATH           SYS_GUID   

T TERMOUT OFF        TERMOUT ON          TH       TIMESTAMP TO_BINARY_DOUBLE      TO_BINARY_FLOAT          TO_BLOB      TO_CHAR     TO_CLOB      TO_DATE      TO_DSINTERVAL   TO_NCHAR  TO_NCLOB   TO_NUMBER           TO_TIMESTAMP      TO_TIMESTAMP_TZ           TO_YMINTERVAL  TRANSLATE TRIM  TRUNC         

U UNBOUNDED FOLLOWING        UNBOUNDED PRECEDING          UNION           UNION ALL  UNION-ALL PARTITION   UNIQUE        UNIQUE DIMENSION        UNIQUE SINGLE REFERENCE    UNISTR         UNPIVOT      UNTIL            UPDATE        UPDATEXML           UPPER           UPSERT         UPSERT ALL            USING          

V V                     VARCHAR2  53, VSIZE           

W W                    WHERE          WITH  WW    

X XMLAGG      XMLCAST     XMLCONCAT          XMLELEMENT        XMLEXISTS XMLFOREST            XMLQUERY XMLSEQUENCE      XMLSERIALIZE      XMLTYPE     XPATH           XQuery           XQUERY      

Y Y                     YEAR YY