Book Review: Beginning Oracle SQL

August 23, 2011

The Ideal Guide for Beginning to Intermediate Oracle SQL: Providing the Logical Reasoning Behind the Approach to Understanding and Solving Data Access Challenges
http://www.amazon.com/Beginning-Oracle-SQL-Experts-Voice/dp/1430271973/

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 operator – for use with Flashback Query (page 262)
• WITH READ ONLY (page 267)
• WITH CHECK OPTION (page 278)

Functions:

• ROUND, TRUNC, CEIL, FLOOR, ABS, SIGN, SQRT, EXP, LN, LOG, POWER, MOD, SIN, COS, TAN, ASIN. ACOS. ATAN. SINH, COSH, TANH (page 119)
• LENGTH, ASCII, CHR, UPPER, LOWER, INITCAP, LTRIM, RTRIM, TRIM, LPAD, RPAD, SUBSTR, INSTR, TRANSLATE, REPLACE, CONCAT (page 122)
• REGEXP_LIKE (page 128)
• REGEXP_INSTR (page 129)
• REGEXP_SUBSTR (page 130)
• REGEXP_REPLACE (page 130)
• NEW_TIME (page 132)
• EXTRACT (page 132)
• ROUND (page 133)
• TRUNC (page 133)
• MONTHS_BETWEEN (page 133)
• ADD_MONTHS (page 133)
• NEXT_DAY (page 134)
• LAST_DAY (page 134)
• NULLIF, COALESCE, NVL2 (page 135)
• GREATEST (page 135)
• LEAST (page 135)
• NVL (page 136)
• DECODE (page 136)
• TO_NUMBER, TO_BINARY_FLOAT, TO_BINARY_DOUBLE, TO_YMINTERVAL, TO_DSINTERVAL, TO_TIMESTAMP (Page 137)
• TO_NUMBER (page 138)
• TO_CHAR (page 138)
• TO_DATE (page 138)
• CAST (page 141)
• COUNT, SUM, MIN, MAX, AVG, MEDIAN, STATS_MODE, STDDEV, VARIANCE (page 211)
• GROUPING function (page 224)
• GROUPING_ID function (page 225)
• RANK analytic function (page 253)
• LAG analytic function (page 256)
• DENSE_RANK analytic function (page 258)

SQL Statement Types:

• SELECT
• INSERT (page 146)
• UPDATE (page 151)
• DELETE (page 154)
• MERGE (page 157)

Data Definition:

• CREATE TABLE (pages 72, 163)

• DROP TABLE (page 154, 189)
• ALTER TABLE (page 168)
• RENAME (just a brief mention on page 169)
• Constraints (page 170)
• CREATE SCHEMA (page 176)
• CREATE INDEX (page 179)
• ALTER INDEX (page 181)
• CREATE SEQUENCE (page 185)
• CREATE SYNONYM (page 186)
• TRUNCATE TABLE (page 191)
• COMMENT (page 191)
• CREATE VIEW (page 266)
• CREATE TYPE (page 336, 339)

If you own the “Mastering Oracle SQL” book, you would see the following changes when reading this book (I might have missed a few changes):

• New section “Other Query Languages, Really” – includes a brief overview of some of the query languages used by various database management systems and the different SQL dialects used by Oracle SQL, Microsoft SQL Server, and IBM DB2 (page 9).
• New Figure 1-1 that shows the relationship between rows and columns for the DEPARTMENTS table (page 11).
• New Figure 1-2 that shows NULL values in the EMPLOYEES table (page 13).
• New section that introduces SQL Developer – provides just enough information to help someone who might find SQL*Plus to be too difficult to use (page 58).

• New tip that indicates that access to the DUAL table starting in 10g is less resource intensive than using a custom table to perform the same function (page 89).
• An explanation was added to explain why a NOT IN clause with NULL as one of the items failed to return any rows (page 114).
• The multitable INSERT command syntax was removed from this edition of the book (it is now described in the “Pro Oracle SQL” book) and is replaced with an extended description that should help those who need to perform an INSERT using a SELECT statement for the row data source.
• Provides enhanced descriptions for examples, such as the missing explanation of the UPDATE examples, which now appears on pages 153, 154-156.
• Slightly smaller font size than the original book – more information per page.
• Removed the discussion of the SQL*Plus AUTOCOMMIT option, frequency of COMMITs, and SAVEPOINTs just prior to section 6.6.
• Removed the SET TRANSACTION discussion from section 6.6.
• Section 7.6 shows using SQL Developer to display autotrace statistics rather than using SQL*Plus as in the previous edition (all mention of iSQL*Plus have been removed in the book).
• New description added to section 9.1 that is dedicated to demonstrating how NULLs can be problematic in EXISTS, IN, NOT EXISTS, and NOT IN clauses (pages 240-243).
• Removed the ROWNUM and Top-N SQL section (pages 245).
• Extended example that shows why subquery factoring might be helpful.  Also included is an example that shows the steps one might take to build a factored subquery, and then using that factored subquery in a complex SQL statement (page 246-247).
• The subtopic of windowing (ex: ROWS BETWEEN) was removed from the discussion of analytic functions.
• Added RELEASE and TAB to the table of common SQL*Plus system variables (page 294)
• Removed the “HTML in iSQL*Plus” section and added the section, “11.6 Building SQL*Plus Scripts for Automation” (page 321).

It seems that even fantastic books invite problems that creep into book pages.  The editing required to bring this book up to date seems to have introduced a couple of problems, while correcting a couple of problems that made the original book difficult to use for reference purposes.  Suggestions for Improvement, Problems, and/or Errors:

• It appears that the wrong figure was copied (figure 1-3 is a duplicate of figure 1-5) – shows an entity relationship model diagram rather than the three set operators (UNION, INTERSECT, MINUS) – the correct diagram is shown in the original book (page 15).
• The URL pointing to the script to recreate the example schema used by the book appeared to be incorrect – this issue might be caused by a recent redesign to the Apress website.  The actual location is that of the Apress website followed by /9781430271970 (page 19, 72, 116).
• Book separates SELECT SQL statements into a “Retrieval” grouping, while the Oracle SQL documentation considers SELECT SQL statements as part of the “DML” grouping.  This difference might cause a little confusion.
• Mistakenly left screen captures of SQL Plus for Windows (SQLPLUSW) in the book (page 50, 52).
• Need to change ‘S02’ to ‘SQL’ so that the SQL statement produces the expected output (page 103).
• The description of the EXP function has an NLS problem – the comma should be changed to a decimal point in the number (page 119).
• The Summary paragraph for chapter 6 still mentions the SAVEPOINT and SET TRANSACTION commands which were removed from the chapter.
• Book names the resultset rows of a query as a “result table”, which might be a little confusing.  For example, page 265 states, “The result of a query is always a table, or more precisely, a derived table.” (This might be 100% correct, it just seemed a bit odd to me.)
• The descriptions of some of the SQL keywords, such as the description for CONNECT_BY_ROOT, could have benefitted from an example SQL statement.  In a couple of rare cases in the book the output of a SQL statement could have been explained a little more completely, rather than relying on the reader to completely understand what was displayed.
• The word “unacceptable” should be replaced with “acceptable” (page 252).
• The book states that chapter 6 demonstrated changing a session’s isolation level to READ ONLY so that query results were always as of a specific transaction start time.  It appears this section was removed from chapter 6 (page 259).
• The book probably should clarify that an Enterprise Edition license is required in order to use Flashback Query – although the book does state that the DBA will be required to perform some configuration in advance (so maybe this is implied).
• The permissions described on page 182 as required to use DBMS_XPLAN might be a little excessive, “You need the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges.” (reference reference2)
• The book states, “see Appendix A of this book or SQL*Plus User’s Guide and Reference for details” – Appendix A is no longer the “Quick Reference to SQL and SQL*Plus”, but instead is “The Seven Case Tables” (page 297).  A similar problem is found on pages 39, 56, 68, 81, and 89.
• The book states: “SQL*Plus treats &1 and $2 just…” – the $2 should appear as &2 (page 303).
• The book states: “SQL Developer has more features than SQL*Plus in this area, because it runs in a browser environment itself” – this is an unfortunate copy and replace error when removing all references to iSQL*Plus from the book (page 318).

The issues identified above are minor when compared to the value offered by the book’s contents.  I would recommend this book without reservation, whether the person’s first experience with SQL was less than ten minutes ago, or for someone who has worked with Oracle SQL for a decade or longer.  Clear, concise, and helpful, with minimal confusing details – an accurate description of not only the SQL language, but also of this book.