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
Oracle SQL by Example, Third Edition is a convenient, quick way to master Oracle SQL. This unique integrated book-and-Web learning solution teaches all the SQL skills you will need to succeed in an Oracle environment. Through hands-on, real-world labs, exercises, projects, and a superb Web-based training site, the book teaches administrators and programemrs alike the core skills required for their jobs. Coverage includes every key Oracle SQL concept, including: entity-relationship diagrams, the SQL*Plus environment, SELECT and other statements, Boolean operations, joins, aggregate and not-aggregate functions. Each chapter contains review exercises that help the reader master the important skills before they move on.
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.