Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

  

 

ISBN 10: 
0-9776715-8-5
ISBN 13: 
978-0-9776715-8-8
Library of Congress Number:
2008937201
300 pages
Perfect bind - 9x7
PD 1208
Shelving:  Databases/Oracle Oracle In-Focus Series #28

  Advanced Oracle SQL Programming
The Expert Guide to Writing Complex Queries

Laurent Schneider,
Oracle ACE, Oracle Certified Master  
 
         
Retail Price $49.95
/  £24.95

Order now at 30% off and get access to the code depot! Only $34.95
(30% off)
       
Key Features   About the Author Reader Comments
Table of Contents   Index Errata
       

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.


 

 
 

          
          
            Expert tips for
         SQL Programming

 

Key Features

• 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
 

About the Author:

 
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.Oracle Certified Master  
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.

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

About Laurent Schneider      

About Chen Shapira  

About Tom Routen    

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     

 

 

Reviews:

From the series Editor:

Errata:

p227: conclusion: the main keywords in building a hierarchy are STAND BY, PRIOR and START WITH,

the main keywords in building a hierarchy are CONNECT BY, PRIOR and START WITH,

 

 The diagrams in pages 237 and 238 require correction. ANALY-ST is cut, same as 1-9-8-1, 1-9-8-0 and 198-1.

Hit Counter

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks