Course Length:

5 Days

Course Description:

This course is designed to give users an understanding of Oracle SQL and Oracle PL/SQL languages using Oracle’s SQL*Plus and iSQL*Plus tools. The course covers SQL commands for DML, DDL, Query, and Transaction Control operations. Students are also introduced to procedural programming using PL/SQL. The course topics are applicable to all versions of Oracle through Oracle 10g.

Who Should Attend:

Application designers and developers, database administrators and operators, and end users should attend this course.

Benefits of Attendance:

Upon completion of this course, students will be able to:

• Explain the difference between SQL, PL/SQL and SQL*PLUS.
• Write basic SQL queries to retrieve desired data.
• Use DML statements (insert, update, and delete).
• Use DDL statements (create, alter, and drop).
• Use transaction control statements to commit, rollback, and create savepoints.
• Write advanced queries.
• Write scripts to do queries and display SQL*PLUS reports.
• Control access to your database objects.
• Write a PL/SQL block using all elements of block structure.
• Write PL/SQL statements using lexical units, declared variables, and assignment statements.
• Use control structures to loop, branch, and jump.
• Use PL/SQL named exceptions and user-defined exceptions.
• Use explicit cursors.

Prerequisites:

There are no prerequisites for this course.

Course Outline:

• Chapter 1: Database Design
  1. Database Models
  2. Beginnings
  3. Some Introductory Terminology
  4. Codd's 12 Rules
  5. Normalization
  6. First Normal Form
  7. Higher Order Normal Forms
• Chapter 2: Oracle Standard Interfaces
  1. SQL
  2. SQL*Plus
  3. Oracle Architecture
  4. Interfaces to Oracle
  5. Command Line Interface
  6. Viewing a Sample Table
  7. The Graphical User Interface
  8. The SQL Buffer
  9. The Web-Based Interface
  10. Describe
• Chapter 3: The Sample Database
  1. The Entity Relationship Model
  2. Entity Relationship Diagrams
  3. The Sample Database
  4. ER Diagram for Sample Database
  5. Creating the Sample Data
  6. Viewing Sample Data
  7. Data Types of the Sample Data
• Chapter 4: Data Definition Language
  1. Categories of SQL Statements
  2. Oracle Datatypes
  3. The CREATE Statement
  4. The DROP Command
  5. The ALTER Command
  6. Integrity Constraints
  7. Entity Integrity Constraints
  8. Referential Integrity Constraints
  9. Modifying Table to Use Constraints
  10. Checking Constraints
  11. The Data Dictionary
• Chapter 5: Data Manipulation Language
  1. DML Statements
  2. The SELECT Statement
  3. The INSERT Statement
  4. The DELETE Statement
  5. The UPDATE Statement
  6. More SQL*Plus Commands
• Chapter 6: Transaction Control
  1. Transactions
  2. Command Classification
  3. Savepoints
  4. The SET TRANSACTION Command
• Chapter 7: SQL Operators
  1. Simple Selects
  2. Comparison Operators
  3. IN and NOT IN Operators
  4. BETWEEN Operator
  5. The LIKE Operator
  6. Logical Operators
  7. IS NULL and IS NOT NULL
  8. ANY
  9. ALL
• Chapter 8: SQL FUNCTIONS
  1. Introduction
  2. The DISTINCT Keyword
  3. Aliases
  4. Miscellaneous Functions
  5. Mathematical Functions
  6. String Functions
  7. Date Functions
  8. Conversion Functions
  9. Pseudo Columns
• Chapter 9: Joining Tables
  1. Joins
  2. Cartesian Product
  3. Inner Joins
  4. Equi-Join
  5. Table Aliases
  6. Non-Equi Join
  7. Non-Key Join
  8. Reflexive Join
  9. Natural Join
  10. Outer Joins
  11. Right Outer Join
  12. Left Outer Join
  13. Full Outer Join
  14. Oracle-Specific Syntax for Outer Joins
• Chapter 10: Set Operators
  1. Introduction
  2. Selection Criteria
  3. Union
  4. Union All
  5. Intersect
  6. Minus
• Chapter 11: SQL Subqueries
  1. Introduction
  2. Using a Subquery with a DML Statement
  3. Typical Subqueries
  4. Subquery Operators
  5. Standard vs. Correlated Subqueries
  6. Correlated Subquery Example
  7. Predicate Operators
• Chapter 12: Groups
  1. SQL Statements
  2. GROUP BY Clause
  3. HAVING Clause
  4. Order of a SELECT Statement
• Chapter 13: More Database Objects
  1. More Database Objects
  2. Relational Views
  3. Updating a View
  4. Create or Replace
  5. Forcing a View
  6. The Data Dictionary Revisited
  7. Indexes
  8. Synonyms
• Chapter 14: Reports
  1. Report Features
  2. Session Control
  3. The SET Command
  4. The COLUMN Command
  5. The BREAK Command
  6. The COMPUTE Command
• Chapter 15: Introduction
  1. SQL vs. PL/SQL
  2. A Few Simple Examples
  3. Saving Procedures
  4. A More Complete Picture
  5. Comments
  6. Variable Substitution
  7. Simple Exception Handling
  8. Advantages of PL/SQL
  9. Assignments
• Chapter 16: Declarations and Data Types
  1. Declarations
  2. Standard Data Types
  3. Initialization
  4. Variable Names
  5. Specialized Data Types - %TYPE
  6. Specialized Data Types - %ROWTYPE
  7. Building Your Own Data Types - Records
  8. A Quick look at Loops
  9. Arrays
  10. Tables
  11. Nested Blocks
• Chapter 17: Language Components
  1. Introduction
  2. Assignments
  3. Decision Making Statements
  4. Simple Loops
  5. Loops - for
  6. Loops - indefinite
  7. Loops - while
  8. Simple Loops
  9. Nested Loops
  10. Boolean Variables
  11. PL/SQL Relational Operators
  12. PL/SQL Logical Operators
  13. The CASE Construct
• Chapter 18: Cursors
  1. Introduction
  2. Cursor Manipulation
  3. Using the Cursor
  4. For Loops Cursors
  5. Cursor Attributes
  6. Cursor Parameters
  7. Nested Cursors
  8. Cursor Exceptions
• Chapter 19: Exceptions
  1. Errors in Programs
  2. Run Time Exceptions
  3. Oracle Built In Exceptions
  4. Unnamed Exceptions
  5. Built in Exception Functions
  6. Creating Your Own Exceptions
  7. Building Non Terminating Exceptions
• Chapter 20: Functions and Procedures
  1. Introduction
  2. Creating a Procedure
  3. Example Procedure
  4. Using Parameters
  5. Functions
  6. Procedures and Exceptions
• Appendix A: An SQL and SQL*Plus Reference
  1. DDL Statements
  2. DML Statements
  3. Transaction Control Statements
  4. Operators
  5. Common SQL*Plus Commands