Oracle SQL and PL/SQL Handbook

John Adolph Palinski is a Supervisor of Computer Applications for the Omaha Public Power District where he has developed and implemented numerous Oracle systems. He is also an instructor at the University of Nebraska at Omaha. A Certified Oracle Professional Developer, he has taught SQL, PL/SQL, and Developer 2000 courses and is the author of Oracle Database Construction Kit (Que, 1997). He is a frequent contributor to Oracle periodicals such as Oracle Internals and Oracle Professional. He also provides on-site training through his consulting company, Realistic Software Training. 0201752948AB07152002

Introduction.


What is Oracle?



Who Should Use This Book?



How is this Book Organized?



Conventions.



Other Sources of Information.



Installing the Practice Database and Tools.



What Software to Get?



How to Get the Software?



Setting Up Personal Oracle.



Installing the Practice Tables.



What's Next.



1. Understanding Relational Databases—The Entity Relationship Diagram, Database Schema Diagram, and Normalization.


Entities and Attributes.



Entity Relationship Diagram Concepts.



Ordinality and Cardinality.



Keys and Joins.



Creating an Entity Relationship Drawing.



Identifying the Major Entities.



Defining the Identifiers.



Defining the Relationships, Cardinality, and Ordinality.



Defining Descriptive Attributes.



Creating the Entity Relationship Diagram.



Converting the Entity Relationship Diagram to a Relational Model.



Conversion to the Relational Model.



Normalization.



The Data Schema Diagram.



Table Relationship Drawing.



Employee Database Table Relationship Drawing.



What's Next.



Practice.



2. Building the Database with the Data Definition Language.


Logging On to SQL*PLUS.



Entering A Command.



Using The SQL*PLUS Editor.



Menu Commands.



Database Objects That Store Data.



The Desk Analogy.



Tablespaces.



Creating and Modifying Tables.



Indexes.



Maintaining the Integrity of the Database.



Synonyms and Other Nice Things.



Oracle Object-Oriented Features.



User-defined Data Types (ADTs).



Collection Types.



Toad.



What's Next.



Practice.



3. The Data Control and Data Manipulation Languages and the Data Dictionary.


Creating User Accounts.



Data Control Language.



Roles.



Revoking Privileges.



Privileges.



Granting Access to Your Tables and Database Objects.



The Data Manipulation Language (DML).



Using the INSERT Command.



Inserting Records from Another Table.



Using the UPDATE Command.



Deleting Records.



The COMMIT and ROLLBACK Commands.



The TRUNCATE Command.



Oracle's Data Dictionary.



The User_objects and All_objects Views.



Reviewing PL/SQL Code Blocks with User_source.



What's Next.



Practice.



4. Retrieving Database Records Using SQL—The Select, Where, and Order By Clauses.


Identifying Table and Column Names.



Making a Simple Database Query.



Components of a Simple Select.



Examples of Simple Select Statements.



End listing.



Computing numeric values in the Select clause.



Limiting the Database Records Retrieved.



Evaluation Operators Used In The WHERE Clause.



Using the Equal (=) and Is Operators.



Using the Greater Than (>) and Less Than (<) Operators.



Using the In Operator.



Any, Some, or All Keywords.



Using the Between Operator.



Using the Like Operator.



Using And/ Or To Document Multiple Conditions.



Mixing AND/OR in the Where Clause.



Ordering Records.



Ascending/Descending Order.



Using Expression Numbers as the Names of Sort Columns.



Printing the Results of Your Query.



What's Next.



Practice.



5. Retrieving Records Using SQL—The From, Group By, and Having Clauses.


The From Clause.



Retrieving Records From Multiple Tables.



How to Combine or Join Tables.



What to do with Join Columns with the Same Name.



Join Types.



Joining Tables When the Value Only Exists in One Table-The Outer Join.



Non-equijoins.



Self Joins.



Cartesian Joins.



Computing Group Values.



Group Functions.



Counting the Group's Record Instances.



Summing the Values in a Group.



Averaging the Values in a Group.



Determining the Minimum Value.



Determining the Maximum Value.



Using the Group Functions on Smaller Groups.



Limiting Selected Records Using a Group Function.



What's Next.



Practice.



6. Creating New Values with Character Functions.


Character Functions.



The Initcap Function.



The Instr Function.



The Length Function.



The Lower Function.



The LPAD Function.



The LTRIM Function.



The REPLACE Function.



The RPAD Function.



The RTRIM Function.



The SOUNDEX Function.



The SUBSTR Function.



The TO_CHAR Function.



The TRANSLATE Function.



Trim.



The UPPER Function.



The DECODE Function.



Case Expressions.



Using Functions in the Where Clause.



What's Next.



Practice.



7. Creating New Values with Numeric and Date Functions.


Dates and Date Functions.



Date Functions.



What's Next?



Practice.



8. Using Set Operators, Subqueries, and Variables.


Set Operator Rules.



The Union and Union All operators.



The MINUS operator.



The Intersect operator.



Subqueries.



Multiple-row subqueries.



Multiple column queries.



Correlated subqueries.



Variables.



Using the Single ampersand substitution variable.



Using the Double ampersand variable.



Defining user variables.



What's next.



Practice.



9. Analytical Processing with SQL.


Rollup.



Cube.



The Grouping function.



Ranking Functions.



The Rank Function.



The Dense_rank Function.



Top-N and Bottom-N queries.



The Percent_rank Function.



The Cume_dist Function.



The Ntile Function.



The Row Number Function.



Windowing.



The Cumulative Aggregate Function.



Moving Averages.



The Ratio_to_report Function.



The Lag and Lead Functions.



Statistic Functions.



What's Next.



Practice.



10. Using Database and Materialized Views.


The With Check Option.



The Read Only Option.



Materialized Views.



What's Next.



Practice.



11. Using SQL*PLUS as a Report Writing Tool.


Formatting Columns.



Breaks.



Subtotals.



Sending the Output to the Printer.



Set Commands.



The Environment Dialog Box.



What's Next.



12. What Can Your Do if Your SQL Does Not Perform.


Indexes.



Which Columns Should be Indexed.



Multi-column Indexes.



Index Types.



Bitmap Indexes.



Reverse Key Indexes.



Index Organized Tables.



Function Based Index.



Optimization.



The Rule Based Optimizer.



The Cost Based Optimizer.



Setting the Optimizer.



Another Word or Two on Access Paths.



Join Operations.



Nested Loop Join.



Sort Merge Join.



Hash Join.



Explain Plans.



Plan_table.



Populating Plan_table.



Reading the Explain Plan.



Dynamic Performance Views.



Trace.



Running and Viewing the Trace Statistics.



Hints.



Odds and Ends.



What's Next.



13. Using Business Objects.


Data Warehouses.



Business Objects.



Drilling Down and Exceptions.



Oracle Discoverer 4.0-The OLAP tool of Choice.



What's Next.



14. The Basics of PL/SQL.


15. Handling Exceptions, Using Named Procedures.


16. Advanced PL/SQL Topics.


Appendix A. Glossary.


Appendix B. Answers. 0201752948T03282002