The ideal tutorial and workbook for mastering SQL for Oracle systems.
° Class-tested material and a proven teaching method supported by hundreds of examples and exercises
° Helps readers build their skillset by mastering the industry-standard SQL
° From SQL basics to security to optimization -- the book covers all aspects of SQL for Oracle administrators
The book ... has enough depth for even a seasoned professional to pick up enough tips to pay back the price of the book many times over.—Dr. Paul Dorsey, President Dulcian, Inc., and President, New York Oracle Users Group.
Students find Oracle SQL by Example to be extremely helpful not only to their coursework but also in preparing them for future careers as Oracle DBAs and developers. The layout of the text is conducive to the learning process. It introduces a concept and then reinforces that concept with an exercise.—Betsy Jenaway, Coordinator, Malcomb Operations, and Senior Lecturer College of Management, Lawrence Technological University, Southfield, MI.
The world's #1 hands-on Oracle SQL workbook ... fully updated for Oracle 10g
Crafted for hands-on learning and tested in classrooms worldwide this book illuminates in-depth every Oracle SQL technique you'll need. From the simplest query fundamentals through the newest regular expression database enhancements, you will focus on the tasks that matter most. Hundreds of guided lab exercises will systematically strengthen your expertise in writing effective, high-performance SQL. Along the way, you will acquire an arsenal of useful Oracle SQL knowledge and techniques: an extraordinary library of solutions for your real-world challenges with Oracle SQL.
Here you will find instructions for downloading the sample database used in the exercises and labs in Oracle SQL Interactive Workbook, 3rd Edition, as well as instructions for optionally installing additional example tables.
This self-extracting zip file contains the sample database files you will need to complete the exercises and labs. Take the following steps to download and extract the file:
If you want to optionally install the additional tables used for some of the examples in the book you can run an additional script.
If you downloaded the sample database you will automatically have two files named sql_book_add_tables.sql and drop_extra_tables.sql in your c:\guest\schemasetup directory. You may install these additional tables after you have created the STUDENT account. Instructions on how to create the additional files are found in the readme.pdf file under the header "IV. Additional Example Tables Installation Instructions."
Please see also more information on installing example tables.
To aid you in your work with the exercises and labs in the book, we offer resources on the following topics:
Download the answers to the Test Your Thinking plus Additional Practice Q&A .
Download the Sample Chapter related to this title.
(NOTE: Each chapter concludes with Test Your Thinking.)
About the Author.
1. SQL and Data.
Data, Databases, and the Definition of sql. Table Relationships. The Student Schema Diagram.
The SQL*Plus Environment. The Anatomy of a SELECT Statement. Editing a SQL Statement. The WHERE Clause: Comparison and Logical Operators. The ORDER BY Clause.
Character Functions. Number Functions. Miscellaneous Single-Row Functions.
Applying Oracle's Date Format Models. Performing Date and Time Math. Understanding Timestamp and Time Zone Datatypes. Performing Calculations with the Interval Datatypes. Converting from One Datatype to Another.
Aggregate Functions. The GROUP BY and HAVING Clauses.
The Two-Table Join. Joining Three or More Tables.
Simple Subqueries. Correlated Subqueries. Inline Views and Scalar Subquery Expressions . ANY, SOME, and ALL Operators in Subqueries.
The Power of UNION and UNION ALL. The MINUS and INTERSECT Set Operators.
Outer Joins. Self-Joins.
Creating Data and Transaction Control. Updating and Deleting Data.
Creating and Dropping Tables. Altering Tables and Manipulating Constraints.
Creating and Modifying Views. Indexes. Sequences.
The Oracle Data Dictionary Views. Advanced SQL*Plus Commands.
Users, Privileges, Roles, and Synonyms.
Regular Expressions. Hierarchical Queries.
-Advanced SQL Concepts, Analytical Functions, and the with Clause. ROLLUP and CUBE Operators.
-The Oracle Optimizer and Writing Effective SQL Statements.
Page 22, 1st sentence under heading Identifying and Nonidentifying Relationships. It should read "In an identifying relationship, the PRIMARY key of the entity is propagated to the child entity as part of the primary key."
Page 150, question 7. Answer choices a and b seem identical because answer b is missing spaces. Answer choice b should read "Mary Jones". Note the extra spaces between the two words.
Page 240, Table 4.12, The INTERVAL DAY TO SECOND example reads "INTERVAL '200' DAY (3) (300 days." It should read "INTERVAL '200' DAY (3) (200 days)."
Page 269, last SQL statement on the page. The SELECT statement's first line should read, '… IS NULL…' instead of 'IS NOT NULL'. The result will be 1256.13793 instead of 1301.81034.
Page 312, line 14. The foreign key references the GRADE_TYPE_WEIGHT table not of the GRADE_TYPE table.
Page 362, line 9 (answer to question c). "…course number exists… " should read "…course number does not exist…"
Page 433, line 1. Replace the SECTION table with the ENROLLMENT table instead.
Page 522, line 7. The word "you" is missing between "if" and "do".
Page 563, line 2. "study_months" should be "study_duration".
Page 637, Question 13.2.1 a , last line on page and Page 642 Answers to 13.2.1. a. Replace 'ORDER BY POSITION' with 'ORDER BY 1, 2'.
Page 676, Heading User-Defined Roles, last sentence in first paragraph. Remove the word "to" in the sentence "…group of programmers need to privileges…".
Page 685, line 15, 16. This should be VARCHAR2 instead of VARCHAR in both places.
Page 727, last line in SQL statement on bottom of page. Single quotes around the literal '132' should be removed. (Statement will still execute with quotes, and Oracle will implicitly convert to NUMBER datatype.)
Page 744, grey chapter tab. This should read Lab 16.1 instead of 15.1.
Page 847, the last execution plan on the page. It shows UNION-ALL instead of UNION.