The fourth edition of Oracle SQL by Example is an excellent resource for not only learning SQL but also for serving as a reference book for experienced users. If you ever get tired of seeing the HR sample schema from Oracle, this book provides a set of files for creating and populating a STUDENT schema, with everything based on Oracle 11g. Granted, the book schema isn't as comprehensive as the full set of Oracle's sample schemas, but on the other hand, most of Oracle's SQL courses and examples are based on just the HR schema, so I would consider the HR versus STUDENT schema comparison to be a non-issue in terms of a lack of completeness or thoroughness.

How do users learn SQL, and by "users," which users? High-end users (aspiring developers and DBAs) are going to concentrate on the command line interface (i.e., SQL*Plus in a command shell in 11g) or a GUI interface more specific to Oracle than not (Windows SQL*Plus client or SQL Developer). Aside from these users, other tools may be the main interface to learning SQL, and one such tool that satisfies this need is Toad for Data Analysts (TDA). One of the features in TDA is its Query Builder, and the reason I'm introducing a tool on top of the book is that quite a few tools, provided by Oracle or otherwise, are more and more query builder-like, where queries are constructed via a "point and click" interface. Oracle Warehouse Builder, Oracle Reports, and Oracle Business Intelligence Suite, to name a few tools, are exactly like this.

Running the script used to create tables and insert data relies on SQL*Plus in terms of not erroring out because of "REM" and "PROMPT" SQL*Plus-specific commands (i.e., these are not SQL keywords). Once the script runs within the STUDENT schema, we can take advantage of the ER Diagrammer tool in TDA.

Right away, we can see that to get a course number a specific student was enrolled in, four tables will be involved in the join. In the Understand-Query-Report-Automate workflow model of TDA, having a simple entity relationship diagrammer tool makes transitioning to Query a lot easier. Using Query Builder, a simple report of student ID, name, course information and grade can be constructed in a few seconds. Teaching the underlying SQL takes a bit longer, but the visual representation and its translation in a valid SQL statement speeds up the process quite a bit.

The underlying SQL is shown below.

SELECT STUDENT.STUDENT_ID,
       STUDENT.FIRST_NAME,
       STUDENT.LAST_NAME,
       ENROLLMENT.ENROLL_DATE,
       ENROLLMENT.FINAL_GRADE,
       COURSE.DESCRIPTION,
       COURSE.COST
  FROM STUDENT.STUDENT STUDENT,
       STUDENT.ENROLLMENT ENROLLMENT,
       STUDENT."SECTION" "SECTION",
       STUDENT.COURSE COURSE
 WHERE     (ENROLLMENT.STUDENT_ID = STUDENT.STUDENT_ID)
       AND (ENROLLMENT.SECTION_ID = "SECTION".SECTION_ID)
       AND ("SECTION".COURSE_NO = COURSE.COURSE_NO)
ORDER BY STUDENT.STUDENT_ID ASC

If you prefer ANSI SQL syntax as opposed to "old school" Oracle syntax, toggle the "Use ANSI joins in query" button to get the corresponding (and equivalent) ANSI syntax.

SELECT STUDENT.STUDENT_ID,
       STUDENT.FIRST_NAME,
       STUDENT.LAST_NAME,
       ENROLLMENT.ENROLL_DATE,
       ENROLLMENT.FINAL_GRADE,
       COURSE.DESCRIPTION,
       COURSE.COST
  FROM    (   (   STUDENT."SECTION" "SECTION"
               INNER JOIN
                  STUDENT.COURSE COURSE
               ON ("SECTION".COURSE_NO = COURSE.COURSE_NO))
           INNER JOIN
              STUDENT.ENROLLMENT ENROLLMENT
           ON (ENROLLMENT.SECTION_ID = "SECTION".SECTION_ID))
       INNER JOIN
          STUDENT.STUDENT STUDENT
       ON (ENROLLMENT.STUDENT_ID = STUDENT.STUDENT_ID)
ORDER BY STUDENT.STUDENT_ID ASC

One of the more difficult areas of SQL to learn is that of subqueries. The author presents this material in a very straightforward manner with many examples. Once students get the idea of a query within a query, the concept can be reinforced visually via Query Builder.

The overall query for the above diagram is shown below.

SELECT COURSE.COURSE_NO, COURSE.DESCRIPTION
  FROM STUDENT.COURSE COURSE
 WHERE (COURSE.COURSE_NO IN (SELECT "SECTION".COURSE_NO
                               FROM STUDENT."SECTION" "SECTION"
                              WHERE ("SECTION".LOCATION = 'L211')))

What TDA does to reinforce the idea of a query within a query is to offset the subquery into its own query, so to speak. Clicking on the "W" link on the left side brings up the select statement for the subquery (or you can edit the "Where Clause Subquery" balloon to get the same result).

As far as basic SQL is concerned, this book in combination with a fairly robust tool such as TDA can make learning SQL quite a bit easier than having someone brand new to SQL starting off with SQL*Plus. The command line interface is great for many things, but one thing it isn't necessarily good for is learning SQL in the first place. Generations of DBAs and developers have learned SQL this way, but times are changing and so are learning methods. The tool-based approach to learning (and using it afterwards) can give people a good running start in crafting statements.

Going back to the book by itself, its coverage of more advanced constructs in SQL is impressive. Hierarchical queries, regular expressions, and data warehousing analytical functions (including rollup and cube) are presented with easy to understand examples. Many other books on SQL either do not mention advanced functions, or if they do, they barely mention them and provide a few sparse examples.

Oracle by Example also has a good bit of coverage on administration and performance. Administration in this case refers to data definition language (create, alter and drop tables); manipulating constraints; using indexes, sequences and views; using the data dictionary; security via roles and privileges; and synonyms. For performance (or SQL optimization), the book imparts a working knowledge of the optimizer and its relationship to statistics.

Out of the 900-plus pages of this book, one bit of advice really sums up the bottom line on what writing SQL is about: getting the right results. The right results may come at a cost to performance, but the important thing here is that the results are correct. Performance can always be worked on or improved later (ignoring system-killing queries for the sake of the point here). Not catching or knowing the impact of a null value being returned by a NOT IN subquery is a subtle error that may go unnoticed for quite some time (if ever caught or noticed). The book does a good job of explaining this point and reinforces the concept of getting the correct data as opposed to the fastest data.

As a bonus, the schema used in this book is the same one used in Oracle PL/SQL by Example, so if you like the author's writing and presentation style here, you'll also like the style in the PL/SQL book, and there won't be any overhead in terms of having to become familiar with a new sample schema. As far as "what are the books I want near me at work" goes, Oracle by Example is definitely on my short list.

Back to DBAsupport.com