The Expert Guide to Writing Complex Queries
Oracle ACE, Oracle Certified Master
Retail Price $49.95 / £24.95
|Order now at 30% off!||Only $34.95
|Library of Congress Number:
| 300 pages
Perfect bind - 9x7
|Shelving: Databases/Oracle||Oracle In-Focus Series #28|
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.
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 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
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
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
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