Beginning Oracle SQL (Expert's Voice in Oracle) [Paperback]

Lex deHaan (Author), Karen Morton (Author), Tim Gorman (Author), Inger Jørgensen (Author), Daniel Fink (Author)

Book Description

Publication Date: December 21, 2009 | ISBN-10: 1430271973 | ISBN-13: 978-1430271970 | Edition: 1

Beginning Oracle SQL is your introduction to the interactive query tools and specific dialect of SQL used with Oracle Database. The book is a revision of the classic Mastering Oracle SQL and SQL*Plus by Lex de Haan, and has been updated to cover developments in Oracle’s version of the SQL query language. Written in an easygoing and example-based style, Beginning Oracle SQL is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle database.

• Takes an example-based approach, with clear and authoritative explanations
• Introduces both SQL and the query tools used to execute SQL statements
• Shows how to create tables, populate them with data, and then query that data to generate business results

What you’ll learn

• How to create database tables and define their relationships
• How to add data to your tables, then change and delete that data
• How to write database queries that generate accurate results
• How to avoid common traps and pitfalls in writing SQL queries
• How to take advantage of advanced Oracle SQL features such as support for hierarchical queries, analytic and window functions, flashback to a given point in time, and more
• How to get a handle on Oracle database’s support for object types in the database

Who this book is for

This book is aimed at developers and database administrators who must write SQL statements to execute against an Oracle database. No prior knowledge of SQL is assumed.

Table of Contents

1. Relational Database Systems and Oracle
2. Introduction to SQL, AQL*Plus, and SQL Developer
3. Data Definition, Part I
4. Retrieval: The Basics
5. Retrieval: Functions
6. Data Manipulation
7. Data Definition, Part II
8. Retrieval: Multiple Tables and Aggregation
9. Retrieval: Some Advanced Features
10. Views
11. Writing and Automating SQL*Plus Scripts
12. Object-Relational Features

Editorial Reviews

About the Author

Lex de Haan studied applied mathematics at the University of Technology in Delft, the Netherlands. His experience with Oracle goes back to the mid-1980s, version 4. He worked for Oracle Corporation from 1990 to 2004 in various education-related roles, ending up in Server Technologies (product development) as senior curriculum manager for the advanced database administration curriculum. In that role, he was involved in the development of Oracle9i and Oracle Database 10g. In March 2004, he decided to go independent and founded Natural Join B.V. (http://www.naturaljoin.nl). From 1999 until his passing in 2006, he was involved in the ISO SQL language standardization process, as a member of the Dutch national body. He was also one of the founding members of the OakTable network (http://www.oaktable.net).

Karen Morton is a consultant and educator specializing in application optimization in both shoulder-to-shoulder consulting engagements and classroom settings. She is senior database administration performance and tuning specialist for Fidelity Information Services.

For more than 20 years, Karen has worked in information technology. Starting as a mainframe programmer and developer, she has been a database administrator, a data architect and now is a researcher, educator and consultant. Having used Oracle since the early 90s, she began teaching others how to use Oracle over a decade ago.

Karen is a frequent speaker at conferences and user groups, an Oracle ACE, and a member of the OakTable network (an informal association of "Oracle scientists" that are well known throughout the Oracle community). She blogs at karenmorton.blogspot.com.

Tim Gorman began his information technology career in 1984 as a C programmer on UNIX and VMS systems, working on medical and financial systems as an application developer, systems programmer, and systems administrator. He joined Oracle Corporation in 1990 as a consultant, then became an independent consultant in 1998, and has worked for SageLogix since 2000. Gorman is the coauthor of Essential Oracle8i Data Warehousing and Oracle8 Data Warehousing. He specializes in performance tuning applications, databases, and systems, as well as data warehouse design and implementation, backup and recovery, architecture and infrastructure, and database administration. Gorman still considers himself a pretty good coder, although the market for C programs has dried up somewhat lately.

 

A bio is not available for this author.

Andrew Morton is a developer for Cypress Systems, Inc. and creator of the PHP-driven Phlickr application programming interface.

Product Details

• Paperback: 459 pages
• Publisher: Apress; 1 edition (December 21, 2009)
• Language: English
• ISBN-10: 1430271973
• ISBN-13: 978-1430271970
• Product Dimensions: 9.3 x 7.4 x 0.9 inches
• Shipping Weight: 1.4 pounds (View shipping rates and policies)
• Average Customer Review: 5.0 out of 5 stars  See all reviews (1 customer review)
• Amazon Best Sellers Rank: #175,557 in Books (See Top 100 in Books)
  • #93 in Books > Computers & Technology > Databases > Database Management Systems

Customer Reviews

Most Helpful Customer Reviews

5.0 out of 5 stars The Ideal Guide for Beginning to Intermediate Oracle SQL: Providing the Logical Reasoning Behind the Approach to Understanding, August 23, 2011

By 

Charles Hooper (Michigan, USA) - See all my reviews
(REAL NAME)   

This review is from: Beginning Oracle SQL (Expert's Voice in Oracle) (Paperback)

The Ideal Guide for Beginning to Intermediate Oracle SQL: Providing the Logical Reasoning Behind the Approach to Understanding and Solving Data Access Challenges

The "Beginning Oracle SQL" book is an updated version of the book "Mastering Oracle SQL and SQL*Plus" which covered Oracle release versions through 10.1.0.2. The original version, while extremely well written, suffered from a handful of typesetting flaws that resulted in most pages appearing without page numbers, initial words of some paragraphs discarded, left-most columns of some tables mangled, and a small number of typos. The "Beginning Oracle SQL" book corrects the typesetting flaws, eliminates sections of the original book that are obsolete due to Oracle product changes, eliminates SQL*Plus related sections that can be easily found in the Oracle documentation and those sections that are deemed too advanced, and adds a couple of new sections. I purchased two copies of the "Beginning Oracle SQL" book, a Google Books version through the Android market on a Motorola Xoom (unfortunately, the Google Books app crashes when displaying book pages 33 and 34 side-by-side, pages 56 and 57 as displayed in Google Books), and a PDF copy from Apress.

There are several Oracle SQL books on the market, and I have had the opportunity to read a couple of those books. This book, much like the original, is exceptional in comparison with other SQL language books, and seems to be suitable for use in classroom environments due to the inclusion of the exploratory excises at the end of each chapter (I did not spend much time reviewing these exercises or the Appendix contents). The book offers a carefully constructed progression of topics that build on prior material presented in the book.

Concepts Covered by the Book, Book Specific Features, and Quick Observations:
* Why do we implement the various types of database constraints.
* Forms an early distinction between the database and the software database management system, while other books muddy the term "database".
* Demonstrates how to get started with SQL*Plus, edit lines of the SQL statement that is currently in the buffer, and save the SQL statement that is currently in the buffer.
* Builds a foundation of why things are as they are, and how to find where things are located before diving too deeply into SQL syntax.
* Downloaded source code examples seems to work.
* Plenty of forward and backward references within the book, and external references to other resources.
* Components of the SQL statement, column aliases, regular expressions (page 125).
* Several of the chapters (4, 5, 6, 9, 12) include SQL statements without showing the typical SQL*Plus SQL> prompt and line numbers - the formatting without SQL> is helpful when copying the SQL statements from the PDF file for execution in SQL*Plus (holding the Alt key while selecting the text is a work-around for the other chapters).
* Changing the default object schema used by a session: alter session set current_schema=scott; (page 189).
* Recovering a dropped table: flashback table history to before drop; (page 190).
* Correctly states the facts, while other books state otherwise: "If you drop a table, you implicitly drop certain dependent database objects, such as indexes, triggers, and table privileges granted to other database users. You also invalidate certain other database objects, such as views and packages," (page 190).
* Cartesian products (page 198), equijoins (page 198), non-equijoins (page 199), self-joins (page 201), natural joins (page 203), outer joins (page 205 - other Oracle specific and ANSI))
* Offers advice to pick a SQL formatting convention and adhere to that convention (page 199).
* Book repeatedly reminds the reader of potential problems caused by NULL values, and includes several examples which show where NULL values may cause unexpected problems for developers.
* Nugget of advice: Test performance of a query not only with the typically small datasets found in development environments, but also with larger datasets that are more typical of production systems (page 237).
* Correlated subqueries (page 237).
* Scalar subqueries - subqueries in the SELECT clause (page 243).
* Inline views - subqueries in the FROM clause (page 244).
* Demonstrates throughout the book proper techniques for table alias names, rather than just using generic aliases such as A, B, C, etc.
* USER_UPDATABLE _COLUMNS view shows which view and table columns are possible to update through inserts, updates, and deletes (page 277).
* Brief mention of Instead of Triggers to permit the data normally returned by a view to be updated (page 278).
* Materialized views and automatic query rewrite (page 283).
* Substitution variables in SQL*Plus (page 288).
* User-defined variables in SQL*Plus (page 290).
* Implicit user-defined variables in SQL*Plus (page 291).
* PROMPT, PAUSE, and ACCEPT SQL*Plus commands (page 292).
* Common SQL*Plus system variables (page 294).
* Bind variables (page 298).
* login.sql and glogin.sql scripts for customizing the SQL*Plus environment (page 305).
* COLUMN, TTITLE, BTITLE, REPHEADER, REPFOOTER, BREAK, COMPUTE, SPOOL SQL*Plus features (page 307).
* SQL*Plus MARKUP setting to produce HTML formatted output for SQL statements (page 318).
* Scripting with SQL*Plus using input parameters for scripts (page 322).
* Passing values between scripted SQL statements with the NEW_VALUE clause in SQL*Plus (page 323).
* Passing values between scripted SQL statements with the help of bind variables (page 324).
* SQL*Plus WHENEVER command to react to certain errors during script execution (page 325).
* VARARRAYs in custom data types (page 332).
* Inserting VARARRAY values into table columns (page 333).
* Nested tables (page 336).
* Multiset operators with nested tables (page 341).
* Using CAST and COLLECT to convert an array to a nested table (page 346).

Much like the "Expert Oracle Database Architecture" books, this book uses Oracle error messages to teach SQL troubleshooting techniques. Unfortunately, most of these Oracle error messages are not highlighted in the index at the back of the book, so it is a difficult to locate a specific error message and its solution. Oracle Error Messages as Teachable Moments:
* Chapter 2: ORA-00904: "column": invalid identifier (page 44, 48); ORA-00933: SQL command not properly ended (page 63)
* Chapter 3: ORA-04043: object "owner"."name" does not exist (page 77)
* Chapter 4: ORA-00920: invalid relational operator (page 97); ORA-00913: too many values (page 106); ORA-01427: single-row subquery returns more than one row (page 108)
* Chapter 5: ORA-01722: invalid number (page 118)
* Chapter 6: ORA-00001: unique constraint (owner.name) violated (page 148); ORA-02292: integrity constraint (owner.name) violated - child record found (page 156); ORA-01555: Snapshot too old (page 161)
* Chapter 7: ORA-02292: integrity constraint (owner.name) violated - child record found (page 172); ORA-02449: unique/primary keys in table referenced by foreign keys (page 191); ORA-08002: sequence sequencename.CURRVAL is not yet defined in this session (page 193)
* Chapter 8: ORA-00918: column ambiguously defined (page 197); ORA-00937: not a single-group group function (page 216, 221); ORA-00934: group function is not allowed here (page 218, 219); ORA-00979: not a GROUP BY expression (page 221)
* Chapter 9: ORA-01436: CONNECT BY loop in user data (page 249); ORA-00907: missing right parenthesis (page 254); ORA-30483: window functions are not allowed here (page 258); ORA-01466: unable to read data - table definition has changed (page 261)
* Chapter 10: ORA-00957: duplicate column name (page 269); ORA-01400: cannot insert NULL into (???) (page 275); ORA-01732: data manipulation operation not legal on this view (page 276); ORA-01752: cannot delete from view without exactly one key-preserved table (page 276); ORA-01779: cannot modify a column which maps to a non key-preserved table (page 276); ORA-01402: view WITH CHECK OPTION where-clause violation (page 280)
* Chapter 11: ORA-00936: missing expression (page 302); ORA-01403: no rows found (page 325)
* Chapter 12: ORA-00904: "owner"."table": invalid identifier (page 338)
* Appendix B: ORA-01839: date not valid for month specified (page 373); ORA-00001: unique constraint (owner.name) violated (page 376); ORA-02297: cannot disable constraint (owner.constraint) - dependencies exist (page 400)

The Oracle specific SQL syntax is quite broad, and while this book makes a great effort to cover much of the syntax, it does not starve the "Pro Oracle SQL" book (the intermediate to advanced SQL book) of too few advanced topics. The "Beginning Oracle SQL" book covers the following SQL keywords:
Operators:
* OR (page 94)
* AND (page 95)
* NOT (page 96)
* BETWEEN (page 98)
* IN (page 99)
* LIKE (page 100)
* CASE (page 101)
* GROUP BY (page 208)
* HAVING (page 217)
* GROUP BY ROLLUP (page 222)
* GROUP BY CUBE (page 223)
* PARTITION BY in the FROM clause (page 227)
* UNION, UNION ALL, MINUS, INTERSECT (page 229)
* ANY and ALL operators with subqueries (page 234)
* EXISTS operator (page 238)
* WITH clause - subquery factoring (page 245)
* STARTS WITH, CONNECT BY, NOCYCLE, PRIOR (page 248)
* LEVEL, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF (page 249)
* CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH (page 250)
* SIBLINGS keyword in the ORDER BY clause (page 251)
* AS OF - for use with Flashback Query (page 260)
* VERSIONS BETWEEN... Read more ›