Cargando…
Mastering Oracle SQL /
If you write programs to run against an Oracle database, you spend a lot of time and mental energy writing queries to return the data your programs need. Knowledge of SQL, and particularly of Oracle's implementation of SQL, is the key to writing good queries in a timely manner. In this book, th...
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Otros Autores: | |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Sebastopol, CA :
O'Reilly,
©2002.
|
| Edición: | 1st ed. |
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
MARC
| LEADER | 00000cam a2200000Ia 4500 | ||
|---|---|---|---|
| 001 | OR_ocm52355560 | ||
| 003 | OCoLC | ||
| 005 | 20231017213018.0 | ||
| 006 | m o d | ||
| 007 | cr unu|||||||| | ||
| 008 | 030603s2002 caua o 001 0 eng d | ||
| 040 | |a HUA |b eng |e pn |c HUA |d OCLCQ |d UMC |d OCLCQ |d OCLCF |d OCLCQ |d CUSER |d OCLCQ |d CEF |d CNCEN |d LDP |d HS0 |d ERF |d UKBTH |d UHL |d CZL |d OCLCQ |d OCLCO |d OCLCQ |d OCLCO | ||
| 019 | |a 326954082 |a 1156930047 |a 1180312783 |a 1204036540 |a 1204524130 |a 1233066991 |a 1244030301 |a 1258256329 |a 1295601837 |a 1300646457 |a 1303315403 | ||
| 020 | |a 0596001290 | ||
| 020 | |a 9780596001292 | ||
| 035 | |a (OCoLC)52355560 |z (OCoLC)326954082 |z (OCoLC)1156930047 |z (OCoLC)1180312783 |z (OCoLC)1204036540 |z (OCoLC)1204524130 |z (OCoLC)1233066991 |z (OCoLC)1244030301 |z (OCoLC)1258256329 |z (OCoLC)1295601837 |z (OCoLC)1300646457 |z (OCoLC)1303315403 | ||
| 050 | 4 | |a QA76.73.S67 |b M57 2002 | |
| 060 | 4 | |a QA 76.73 S673 |b M57 2002 | |
| 082 | 0 | 4 | |a 005.7565 |2 21 |
| 049 | |a UAMI | ||
| 100 | 1 | |a Mishra, Sanjay. | |
| 245 | 1 | 0 | |a Mastering Oracle SQL / |c Sanjay Mishra and Alan Beaulieu. |
| 250 | |a 1st ed. | ||
| 260 | |a Sebastopol, CA : |b O'Reilly, |c ©2002. | ||
| 300 | |a 1 online resource (xiv, 321 pages) : |b illustrations | ||
| 336 | |a text |b txt |2 rdacontent | ||
| 337 | |a computer |b c |2 rdamedia | ||
| 338 | |a online resource |b cr |2 rdacarrier | ||
| 347 | |a text file | ||
| 500 | |a "Covers Oracle 9i"--Cover. | ||
| 500 | |a "Putting Oracle SQL to work"--Cover. | ||
| 500 | |a Includes index. | ||
| 588 | 0 | |a Print version record. | |
| 520 | |a If you write programs to run against an Oracle database, you spend a lot of time and mental energy writing queries to return the data your programs need. Knowledge of SQL, and particularly of Oracle's implementation of SQL, is the key to writing good queries in a timely manner. In this book, the authors share their knowledge of Oracle SQL, and show you many creative techniques that you can use to advantage in your own applications. This book shows you how to: Leverage Oracle's vast library of built-in SQL functions Query time-based data, and write joins involving date and time ranges Use Oracle SQL's hierarchical query features to deal with data best represented in a tree format Use DECODE and CASE to implement conditional logic in your queries Use Oracle's new, analytic SQL features to write ranking queries, lag and lead queries, windowing queries, and more Join data from two or more tables using the newly supported SQL92 join syntax In addition, you'll see how SQL can best be integrated with PL/SQL. You'll also learn various best practices to help you write SQL queries that perform efficiently. Precious few books on the market today go beyond discussing syntax and the barest rudiments of using Oracle SQL. This book changes that, showing you how to creatively leverage the full power of SQL to write queries in an Oracle environment. | ||
| 546 | |a English. | ||
| 505 | 0 | |a Intro -- Table of Contents -- Preface -- Why We Wrote This Book -- What's New in Oracle SQL? -- Objectives of This Book -- Audience for This Book -- Platform and Version -- Structure of This Book -- Conventions Used in This Book -- Using Code Examples -- Comments and Questions -- Acknowledgments -- From Sanjay -- From Alan -- Introduction to SQL -- What Is SQL? -- A Brief History of SQL -- Oracle's SQL Implementation -- Theoretical Versus Practical Terminology -- A Simple Database -- DML Statements -- The SELECT Statement -- SELECT clause elements -- Ordering your results -- Removing duplicates -- The INSERT Statement -- Single-table inserts -- Multitable inserts -- The DELETE Statement -- The UPDATE Statement -- The MERGE Statement -- So Why Are There 17 More Chapters? -- The WHERE Clause -- Life Without WHERE -- WHERE to the Rescue -- WHERE Clause Evaluation -- Conditions and Expressions -- Equality/Inequality Conditions -- Membership Conditions -- Range Conditions -- Matching Conditions -- Regular Expressions -- Handling NULL -- Placement of Join Conditions -- WHERE to Go from Here -- Joins -- What Is a Join Query? -- Join Conditions -- The USING Clause -- Conditions Involving Multiple Columns -- The Natural Join Clause -- Types of Joins -- Cross Joins/Cartesian Products -- Inner Joins -- Outer Joins -- Left outer joins -- Right outer joins -- Full outer joins -- Equi-Joins Versus Non-Equi-Joins -- Self Joins -- Self outer joins -- Self non-equi-joins -- Partition Outer Joins -- Joins and Subqueries -- DML Statements on a Join View -- Key-Preserved Tables -- INSERT Statements on a Join View -- DELETE Statements on a Join View -- UPDATE Statements on a Join View -- Data Dictionary Views to Find Updatable Columns -- Impact of WITH CHECK OPTION -- Group Operations -- Aggregate Functions -- NULLs and Aggregate Functions -- Use of DISTINCT and ALL. | |
| 505 | 8 | |a The GROUP BY Clause -- Correspondence Between SELECT and GROUP BY -- Aggregate expressions generally require a GROUP BY clause -- GROUP BY clause must include all nonaggregate expressions -- Aggregate functions not allowed in GROUP BY clause -- Constants can be omitted from the GROUP BY clause -- Scalar functions may be grouped by their underlying column -- Concatenated columns may be grouped in either of two ways -- You can sometimes exclude a nonaggregate expression from the GROUP BY clause -- You are not required to show your GROUP BY columns -- GROUP BY Clause and NULL Values -- GROUP BY Clause with WHERE Clause -- The HAVING Clause -- Nested Group Operations -- Subqueries -- What Is a Subquery? -- Noncorrelated Subqueries -- Single-Row, Single-Column Subqueries -- Multiple-Row, Single-Column Subqueries -- Multiple-Column Subqueries -- The WITH Clause -- Correlated Subqueries -- Inline Views -- Inline View Basics -- Query Execution -- Data Set Fabrication -- Overcoming SQL Restrictions -- Hierarchical queries -- Aggregate queries -- Inline Views in DML Statements -- Restricting Access Using WITH CHECK OPTION -- Global Inline Views -- Subquery Case Study: The Top N Performers -- A Look at the Data -- Your Assignment -- Second Attempt -- Final Answer -- Handling Temporal Data -- Time Zones -- Database Time Zone -- Session Time Zone -- Temporal Data Types in Oracle -- The DATE Data Type -- The TIMESTAMP Data Types -- TIMESTAMP -- TIMESTAMP WITH TIME ZONE -- TIMESTAMP WITH LOCAL TIME ZONE -- The INTERVAL Data Types -- INTERVAL YEAR TO MONTH -- INTERVAL DAY TO SECOND -- Literals of Temporal Types -- DATE Literals -- TIMESTAMP Literals -- INTERVAL Literals -- YEAR TO MONTH interval literals -- DAY TO SECOND interval literals -- Getting Temporal Data In and Out of a Database -- TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ -- Using the default formats. | |
| 505 | 8 | |a Specifying a format -- Converting to TIMESTAMP WITH LOCAL TIME ZONE -- TO_YMINTERVAL and TO_DSINTERVAL -- NUMTOYMINTERVAL and NUMTODSINTERVAL -- TO_CHAR -- Date and Time Formats -- AD/BC Indicators -- AM/PM Indicators -- Case-Sensitivity of Formats -- Two-Digit Years -- ISO Standard Issues -- ISO standard weeks -- ISO standard year -- Database Parameters -- Manipulating Temporal Data -- Using the Built-in Temporal Functions -- Addition -- Adding numbers to a DATE -- Adding months to a DATE -- Adding true INTERVAL values rather than numbers -- Subtraction -- Subtracting one DATE from another -- Subtracting one TIMESTAMP from another -- Subtracting a number from a DATE -- Subtracting months from a DATE -- Number of months between two DATEs -- Time interval between two DATEs -- Subtracting an INTERVAL from a DATE or TIMESTAMP -- Determining the First Day of the Month -- Rounding and Truncating Dates -- SELECTing Data Based on Date Ranges -- Creating a Date Pivot Table -- Summarizing by a Date/Time Element -- Set Operations -- Set Operators -- UNION ALL -- UNION -- INTERSECT -- MINUS -- Precedence of Set Operators -- Comparing Two Tables -- Using NULLs in Compound Queries -- Rules and Restrictions on Set Operations -- Hierarchical Queries -- Representing Hierarchical Information -- Simple Hierarchy Operations -- Finding Root Nodes -- Finding a Node's Immediate Parent -- Finding Leaf Nodes -- Oracle SQL Extensions -- Tree Traversal Using ANSI SQL -- START WITH . . . CONNECT BY and PRIOR -- The LEVEL Pseudocolumn -- Complex Hierarchy Operations -- Finding the Number of Levels -- Listing Records in Hierarchical Order -- Checking for Ascendancy -- Deleting a Subtree -- Listing Multiple Root Nodes -- Listing the Top Few Levels of a Hierarchy -- Aggregating a Hierarchy -- Ordering Hierarchical Data -- Finding the Path to a Node. | |
| 505 | 8 | |a Restrictions on Hierarchical Queries -- Enhancements in Oracle Database 10g -- Getting Data from the Root Row -- Ignoring Cycles -- Identifying Cycles -- Identifying Leaf Nodes -- DECODE and CASE -- DECODE, NULLIF, NVL, and NVL2 -- DECODE -- NULLIF -- NVL and NVL2 -- The Case for CASE -- Searched CASE Expressions -- Simple CASE Expressions -- DECODE and CASE Examples -- Result Set Transformations -- Selective Function Execution -- Conditional Update -- Optional Update -- Selective Aggregation -- Checking for Existence -- Division by Zero Errors -- State Transitions -- Partitioning -- Partitioning Concepts -- Partitioning Tables -- Partitioning Indexes -- Partitioning Methods -- Range Partitioning -- Hash Partitioning -- Composite Range-Hash Partitioning -- List Partitioning -- Composite Range-List Partitioning -- Specifying Partitions -- Partition Pruning -- PL/SQL -- What Is PL/SQL? -- Procedures, Functions, and Packages -- Calling Stored Functions from Queries -- Stored Functions and Views -- Avoiding Table Joins -- Deterministic Functions -- Restrictions on Calling PL/SQL from SQL -- Purity Level -- Trust Me... -- Other Restrictions -- Stored Functions in DML Statements -- The SQL Inside Your PL/SQL -- Objects and Collections -- Object Types -- Subtypes -- Object Attributes -- Object Tables -- Object Parameters -- Collection Types -- Variable Arrays -- Nested Tables -- Collection Instantiation -- Querying Collections -- Collection Unnesting -- Collection Functions -- Comparing Collections -- Manipulating Collections -- Multilevel Collections -- Querying Multilevel Collections -- DML Operations on Multilevel Collections -- Advanced Group Operations -- Multiple Summary Levels -- UNION -- ROLLUP -- Partial ROLLUPs -- CUBE -- Partial CUBE -- The GROUPING Function -- GROUPING SETS -- Pushing the GROUPING Envelope. | |
| 505 | 8 | |a Repeated Column Names in the GROUP BY Clause -- Grouping on Composite Columns -- Concatenated Groupings -- Concatenated groupings with GROUPING SETS -- ROLLUP and CUBE as arguments to GROUPING SETS -- The GROUPING_ID and GROUP_ID Functions -- GROUPING_ID -- GROUPING and GROUPING_ID in ORDER BY -- GROUP_ID -- Advanced Analytic SQL -- Analytic SQL Overview -- Ranking Functions -- RANK, DENSE_RANK, and ROW_NUMBER -- Handling NULLs -- Top/bottom N queries -- FIRST/LAST -- NTILE -- WIDTH_BUCKET -- CUME_DIST and PERCENT_RANK -- Hypothetical Functions -- Windowing Functions -- Working with Ranges -- FIRST_VALUE and LAST_VALUE -- LAG/LEAD Functions -- Reporting Functions -- Report Partitions -- RATIO_TO_REPORT -- Summary -- SQL Best Practices -- Know When to Use Specific Constructs -- EXISTS Is Preferable to DISTINCT -- WHERE Versus HAVING -- UNION Versus UNION ALL -- LEFT Versus RIGHT OUTER JOIN -- Avoid Unnecessary Parsing -- Using Bind Variables -- Using Table Aliases -- Consider Literal SQL for Decision-Support Systems -- XML -- What Is XML? -- XML Resources -- Oracle and XML -- Storing XML Data -- Storing XML as a CLOB -- Inspecting the XML Document -- XPath -- The extract( ) member function -- The extractValue( ) member function -- The existsNode( ) member function -- Moving data to relational tables -- Storing XML as a Set of Objects -- Registering your schema -- Assigning the schema to a column -- Inserting data -- XMLType validity functions -- Updating document content -- Generating XML Documents -- XMLElement( ) -- XMLAgg( ) -- XMLForest( ) -- Putting It All Together -- Summary -- Regular Expressions -- Elementary Regular Expression Syntax -- Matching a Single Character -- Matching Any of a Set of Characters -- Matching Repeating Sequences -- Defining Alternate Possibilities -- Subexpressions -- Anchoring an Expression. | |
| 590 | |a O'Reilly |b O'Reilly Online Learning: Academic/Public Library Edition | ||
| 630 | 0 | 0 | |a Oracle (Computer file) |
| 630 | 0 | 7 | |a Oracle (Computer file) |2 fast |
| 650 | 0 | |a SQL (Computer program language) | |
| 650 | 6 | |a SQL (Langage de programmation) | |
| 650 | 7 | |a SQL (Computer program language) |2 fast | |
| 700 | 1 | |a Beaulieu, Alan. | |
| 776 | 0 | 8 | |i Print version: |a Mishra, Sanjay. |t Mastering Oracle SQL. |b 1st ed. |d Sebastopol, CA : O'Reilly, ©2002 |z 0596001290 |w (OCoLC)49630825 |
| 856 | 4 | 0 | |u https://learning.oreilly.com/library/view/~/0596001290/?ar |z Texto completo (Requiere registro previo con correo institucional) |
| 994 | |a 92 |b IZTAP | ||