Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Introduction

This book was born from a translation of a book originally written by Lex de Haan in Dutch. That book was first published in 1993, and went through several revisions in its native Dutch before Lex decided to produce an English version. Apress published that English version in 2005 under the title “Mastering Oracle SQL and SQL*Plus”. The book has since earned respect as an excellent, accurate, and concise tutorial on Oracle’s implementation of SQL.

While SQL is a fairly stable language, there have been changes to Oracle’s implementation of it over the years. The book you are holding now is a revision of Lex’s original, English-language work. The book has been revised to cover new developments in Oracle SQL since 2005, especially those in Oracle Database 11g Release 1 and Release 2, and Oracle Database 12c Release 1. The book has also been given the title “Beginning Oracle SQL”. The new title better positions the book in Apress’s line, better reflects the content, fits better with branding and marketing efforts, and marks the book as a foundational title that Apress intends to continue revising and publishing in the long term.

About this Book

This is not a book about advanced SQL. It is not a book about the Oracle optimizer and diagnostic tools. And it is not a book about relational calculus, predicate logic, or set theory. This book is a SQL primer. It is meant to help you learn Oracle SQL by yourself. It is ideal for self-study, but it can also be used as a guide for SQL workshops and instructor-led classroom training.

This is a practical book; therefore, you need access to an Oracle environment for hands-on exercises. All the software that you need to install Oracle Database on either Windows or Linux for learning purposes is available free of charge from the Oracle Technology Network (OTN). Begin your journey with a visit to the OTN website at:

http://www.oracle.com/technology/index.html

From the OTN home page, you can navigate to product information, to documentation and manual sets, and to free downloads that you can install on your own PC for learning purposes.

This edition of the book is current with Oracle Database 12c Release 1. However, Oracle SQL has been reasonably stable over the years. All the examples should also run under 11g Release 2. And most will still run under Oracle Database 10g, under Oracle Database 9i, and even under Oracle Database 8i, if you’re running software that old. Of course, as you go further back in release-time, you will find more syntax that is not supported in each successively older release. Oracle Corporation does tend to add a few new SQL features with each new release of their database product.

Oracle Corporation has shown great respect for SQL standards over the past decade. We agree with supporting standards, and we follow the ANSI/ISO standard SQL syntax as much as possible in this book. Only in cases of useful, Oracle-specific SQL extensions do we deviate from the international standard. Therefore, most SQL examples given in this book are probably also valid for other database management system (DBMS) implementations supporting the SQL language.

SQL statements discussed in this book are explained with concrete examples. We focus on the main points, avoiding peripheral and arcane side-issues as much as possible. The examples are presented clearly in a listing format, as in the example shown here in Listing I-1.

Listing I-1. A SQL SELECT Statement

SELECT 'Hello world!'
FROM dual;

One difference between this edition and its predecessor is that we omit the “SQL>” prompt from many of our examples. That prompt comes from SQL*Plus, the command-line interface that old-guard database administrators and developers have used for years. We now omit SQL*Plus prompts from all examples that are not specific to SQL*Plus. We do that out of respect for the growing use of graphical interfaces such as Oracle SQL Developer.

This book does not intend (nor pretend) to be complete; the SQL language is too voluminous and the Oracle environment is much too complex. Oracle’s SQL reference manual, named the Oracle Database SQL Language Reference, comes in at just over 1800 pages for the Oracle Database 12c Release 1 edition. Moreover, the current ISO SQL standard documentation has grown to a size that is simply not feasible anymore to print on paper.

The main objective of this book is the combination of usability and affordability. The official Oracle documentation offers detailed information in case you need it. Therefore, it is a good idea to have the Oracle manuals available while working through the examples and exercises in this book. The Oracle documentation is available online from the OTN website mentioned earlier in this introduction. You can access that documentation in HTML form, or you can download PDF copies of selected manuals.

The focus of this book is using SQL for data retrieval. Data definition and data manipulation are covered in less detail. Security, authorization, and database administration are mentioned only for the sake of completeness in the “Overview of SQL” section of Chapter 2.

Throughout the book, we use a case consisting of seven tables. These seven tables contain information about employees, departments, and courses. As Chris Date, a well-known guru in the professional database world, said during one of his seminars, “There are only three databases: employees and departments, orders and line items, and suppliers and shipments.”

The amount of data (i.e., the cardinality) in the case tables is deliberately kept low. This enables you to check the results of your SQL commands manually, which is nice while you’re learning to master the SQL language. In general, checking your results manually is impossible in real information systems due to the volume of data in such systems.

It is not the data volume or query response time that matters in this book. What’s important is the database structure complexity and SQL statement correctness. After all, it does no good for a statement to be fast, or to perform well, if all it does in the end is produce incorrect results. Accuracy first! That’s true in many aspects of life, including in SQL.

About the Chapters of this Book

Chapter 1 provides a concise introduction to the theoretical background of information systems and some popular database terminology, and then continues with a global overview of the Oracle software and an introduction to the seven case tables. It is an important, foundational chapter that will help you get the most from the rest of the book.

Chapter 2 starts with a high-level overview of the SQL language. SQL Developer is then introduced. It is a tool for testing and executing SQL. It is a nice, fairly intuitive graphical user interface, and it is a tool that has gained much ground and momentum with developers. Free download and documentation can be found here:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Data definition is covered in two nonconsecutive chapters: Chapter 3 and Chapter 7. This is done to allow you to start with SQL retrieval as soon as possible. Therefore, Chapter 3 covers only the most basic data-definition concepts (tables, datatypes, and the data dictionary).

Retrieval is also spread over multiple chapters—four chapters, to be precise. Chapter 4 focuses on the SELECT, WHERE, and ORDER BY clauses of the SELECT statement. The most important SQL functions are covered in Chapter 5, which also covers null values and subqueries. InChapter 8, we start accessing multiple tables at the same time (joining tables) and aggregating query results; in other words, the FROM, the GROUP BY, and the HAVING clauses get our attention in that chapter. To finish the coverage of data retrieval with SQL, Chapter 9 revisits subqueries to show some more advanced subquery constructs. That chapter also introduces windows and analytic functions, the row limiting clause, hierarchical queries, and flashback features.

Chapter 6 discusses data manipulation with SQL. The commands INSERT, UPDATE, DELETE, and MERGE are introduced. This chapter also pays attention to some topics related to data manipulation: transaction processing, read consistency, and locking.

In Chapter 7, we revisit data definition, to drill down into constraints, indexes, sequences, and performance. Synonyms are explained in the same chapter. Chapters 8 and 9 continue coverage of data retrieval with SQL.

Chapter 10 introduces views. What are views, when should you use them, and what are their restrictions? This chapter explores the possibilities of data manipulation via views, discusses views and performance, and introduces materialized views.

Chapter 11 is about automation and introduces the reader to the SQL*Plus tool. SQL statements can be long, and sometimes you want to execute several in succession. Chapter 11 shows you how to develop automated scripts that you can run via SQL*Plus. SQL*Plus is a command-line tool that you can use to send a SQL statement to the database and get results back. Many database administrators use SQL*Plus routinely, and you can rely upon it to be present in any Oracle Database installation. Many, many Oracle databases are kept alive and healthy by automated SQL*Plus scripts written by savvy database administrators.

Oracle is an object-relational database management system. Since Oracle Database 8, many object-oriented features have been added to the SQL language. As an introduction to these features, Chapter 12 provides a high-level overview of user-defined datatypes, arrays, nested tables, and multiset operators.

Finally, the book ends with two appendixes. Appendix A at the end of this book provides a detailed look into the example tables used in this book’s examples. Appendix B gives the exercise solutions.

About the Case Tables

Chapter 1 describes the case tables used in the book’s examples. Appendix A goes into even more detail, should you want it. The book’s catalog page on the Apress.com website contains a link to a SQL*Plus script that you can use to create and populate the example tables. The direct link to that page is: http://www.apress.com/9781430265566. When you get there, scroll down the page about halfway and click on the Source Code/Downloads tab, which will reveal the link from which you can download the aforementioned script.