Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 22, 2010

Book Review: Oracle SQL by Example

By Steve Callan

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. This book covers the fundamentals as well as more advanced constructs in SQL.

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM