Vous voulez voir cette page en franšais ? Cliquez ici.


or
Sign in to turn on 1-Click ordering.
or
Amazon Prime Free Trial required. Sign up when you check out. Learn More
More Buying Choices
Have one to sell? Sell yours here
Tell the Publisher!
I'd like to read this book on Kindle

Don't have a Kindle? Get your Kindle here, or download a FREE Kindle Reading App.

Beginning Oracle SQL [Paperback]

Lex deHaan , Karen Morton , Tim Gorman , rgensen, Inger J├ , Daniel Fink

List Price: CDN$ 44.94
Price: CDN$ 35.38 & FREE Shipping. Details
You Save: CDN$ 9.56 (21%)
o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o
Only 2 left in stock (more on the way).
Ships from and sold by Amazon.ca. Gift-wrap available.
Want it delivered Tuesday, April 22? Choose One-Day Shipping at checkout.

Book Description

Dec 21 2009 1430271973 978-1430271970 1st Edition.

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 the very latest 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

  • Create database tables and define their relationships
  • Add data to your tables; change and delete that data
  • Write database queries that generate accurate results
  • Avoid common traps and pitfalls in writing SQL queries
  • 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
  • Get a handle on Oracle Database’s support for object types in the database

Who is this book 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.


Frequently Bought Together

Customers buy this book with Pro Oracle SQL CDN$ 32.92

Beginning Oracle SQL + Pro Oracle SQL
Price For Both: CDN$ 68.30

One of these items ships sooner than the other. Show details

  • This item: Beginning Oracle SQL

    In Stock.
    Ships from and sold by Amazon.ca.
    FREE Shipping. Details

  • Pro Oracle SQL

    Usually ships within 4 to 7 weeks.
    Ships from and sold by Amazon.ca.
    FREE Shipping. Details


Customers Who Bought This Item Also Bought


Product Details


Product Description

About the Author

Karen Morton is a consultant and educator specializing in application optimization in both shoulder-to-shoulder consulting engagements and classroom settings. She is a Senior DBA Performance and Tuning Specialist for Fidelity Information Services.


For over 20 years, Karen has worked in information technology. Starting as a mainframe programmer and developer, she has been a DBA, a data architect and now is a researcher, educator and consultant. Having used Oracle since the early 90’s, 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 IT 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.


Inside This Book (Learn More)
Browse Sample Pages
Front Cover | Copyright | Table of Contents | Excerpt | Index
Search inside this book:

Sell a Digital Version of This Book in the Kindle Store

If you are a publisher or author and hold the digital rights to a book, you can sell a digital version of it in our Kindle Store. Learn more

Customer Reviews

There are no customer reviews yet on Amazon.ca
5 star
4 star
3 star
2 star
1 star
Most Helpful Customer Reviews on Amazon.com (beta)
Amazon.com: 3.3 out of 5 stars  3 reviews
2 of 2 people found the following review helpful
5.0 out of 5 stars The Ideal Guide for Beginning to Intermediate Oracle SQL: Providing the Logical Reasoning Behind the Approach to Understanding Aug. 23 2011
By Charles Hooper - Published on Amazon.com
Format: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 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 the 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.
4.0 out of 5 stars Beginning Oracle SQL June 1 2012
By cyn - Published on Amazon.com
Format:Paperback|Verified Purchase
I bought this after I started my SQL class. At first I wanted to gloss over what
I already learned. However although that saved time it turned out to be a felony.
I turned out to be a person who understood how to write queries but tended to do
poorly on tests. That is because I was not trained in relational algebra.Once I
get trained in relational algebra and look through the problems I really
understood multiple approaches to looking at SQL problems.
1 of 7 people found the following review helpful
1.0 out of 5 stars Good luck beginning with "Beginning ....." July 13 2012
By T. McKenna - Published on Amazon.com
Format:Paperback
What a disappointment. As a "beginners" guide you would expect the initial setup of the sample database to be quite easy but it is impossible.

1) You have to download the scripts from the publisher site but only certain browsers and certain compression utilities will work so I was forced into upgrading IE8 to IE9 and then had to install WinZip just to get the scripts.

2) The "Readme.txt" included with the scripts does not provide instructions for setting up the sample database; it only lists which scripts are included with the download. In the folder that appears to have the setup scripts there are about 9 scripts that are not intuitively named and the readme does not tell you which order to run the scripts.

3) There is an Appendix that provides the table structure with a diagram identifying foreign key, primary key information. One of the mappings is wrong, just what a new SQL guy needs from a book right off the back 'eh. The diagram indicates that a foreign key should be created to a table that has a composite key but the target table does not have a composite key as per the table definition.

This book should be titled "Beginning Oracle SQL (but BYODB)" (Bring Your Own Database)

Hours wasted on this.
ARRAY(0xb3b9842c)

Look for similar items by category


Feedback