Oracle 9i

Oracle 9i: SQL, PL/SQL and SQL *Plus This course covers data server technology and explores the concepts of relational databases, the SQL*Plus interface tool, and the powerful SQL and PL/SQL programming languages. This course is designed to give individuals a firm foundation in database tasks including how to design, create, and maintain an Oracle database. Students will gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. Students will also learn how to create an operational database and properly manage the various structures in an effective and efficient manner. The lesson topics are reinforced with structured hands-on practice exercises. Lesson 1: Introduction to Oracle9i: SQL, PL/SQL, and SQL*Plus Introduction to SQL*Plus and iSQL*Plus Introduction to SQL Introduction to PL/SQL Lesson 2: SQL*Plus Commands Using SQL*Plus for Editing Using SQL*Plus to Work with Files Using SQL*Plus for Formatting Output Using SQL*Plus Variables Lesson 3: Filtering and Sorting Data WHERE Clause ORDER BY Clause Lesson 4: SQL Functions Expressions in a SELECT Statement Single Row Functions Group Functions Lesson 5: Working with Multiple Tables Joins Subqueries Set Operators Lesson 6: Tables and Constraints Creating and Modifying Tables Integrity Constraints Managing Data in Tables Transaction Control Language Statements Lesson 7: Other Database Schema Objects Sequences, Synonyms, and Indexes Views Database Object Security Lesson 8: Building PL/SQL Blocks Creating PL/SQL Blocks Manipulating Data in PL/SQL Lesson 9: PL/SQL Control Structures Conditional Control Structures Iterative Control Structures Lesson 10: Cursors Introduction to Cursors Implicit Cursors Explicit Cursors Cursor Loops Lesson 11: Exception Handling Introduction to Exception Handling Types of Exceptions Lesson 12: Procedures and Functions Developing Procedures and Functions Passing Parameters Lesson 13: Oracle Packages Developing Packages Oracle-supplied Packages Lesson 14: Oracle Triggers Building a Trigger Trigger Restrictions Lesson 15: PL/SQL Collections Defining and Using Collections Collection Methods Oracle 9i The course encompasses the techniques of logical database design, the goals of normalization, the architecture of Oracle9i, writing SQL statements to create, retrieve, and manipulate data as well as creating PL/SQL programs. Also, the development life cycle, and the activities that take place during each phase is covered. In addition, the course instructs on basic data models, and how to implement models as a fully functioning Oracle database and database storage and the overall architecture of the Oracle Relational Database Management System (RDBMS). Lesson 1: Introduction to Database Concepts Users of Oracle The Relational Database Model The Development Life Cycle Lesson 2: Data Modeling and Database Design Overview of Normalization Data Modeling Entities and Attributes Entities and Their Relationships Denormalization Lesson 3: Introduction to Structured Query Language Introduction to SQL*Plus The SELECT Statement Lesson 4: Other Structured Query Language Statements Data Manipulation Language (DML) Transaction Control Language (TCL) Data Definition Language (DDL) Data Control Language (DCL) Lesson 5: Procedural Language/Structured Query Language Overview of PL/SQL Stored Packages, Procedures, and Functions Triggers Lesson 6: Database Objects Data Blocks Database Storage Oracle and Object-oriented Methodology Lesson 7: Oracle Architecture The Oracle Database The Oracle Instance Executing SQL Statements Other Oracle Products and Tools