Description
This intensive course addresses the use of SQL*Plus, the Oracle tool that provides an interactive implementation of SQL. The course teaches attendees how to connect to an Oracle database using SQL*Plus and to perform data manipulation, reporting, and administrative tasks. Attendees will also learn SQL, the standard language used for accessing relational databases.

Audience
Analysts, designers and programmers who are actively involved in or about to embark on the development of systems using an Oracle database. This course is also appropriate for users who need to gain an understanding of Oracle SQL in order to produce reports based on data held in an Oracle database.

Prerequisites
Participants are not required to have had experience using SQL or any other database management systems.

Hardware and Software
Oracle8 or 8i properly installed on any platform, with sufficient logins for the number of students and instructor.

Format

• Presentation
  
• Hands-on
  

Objectives
After completing this course, participants should be able to:

• Apply relational database concepts
• Describe data grouping, aggregate functions, and SELECT statements
• Create new tables and indexes
• Insert, update, and delete data
• Utilize SQL functions and data handling
• Produce formatted reports
• Use SQL command files
• Explain Oracle8's object relational features

Introduction

• Introduction to SQL
  • The relational model and RDBMS
  • Oracle DBMS architecture
  • SQL*Plus
• The Cars database

Single Table Queries

• Projecting data with SELECT
  • The SELECT statement
  • Developing and executing SQL statements
  • Modifying results with expressions
  • Incorporating functions in queries
  • Working with NULL values
  • Grouping related rows
• Restricting data with SELECT
  • Comparing expressions
  • Inclusion with IN
  • Restricting result sets
  • Sorting results

Multiple Table Queries

• Joining data
• Advanced joins and multi-table operations

Modifying Data

• INSERT / UPDATE / DELETE
  
  

Physical Design and Views

• Reconstructing business objects
  • What is a view?
  • Recreating subtypes
  • Recreating supertypes
  • Row-level security
  • View restrictions

Indexes

• Index design and maintenance
  • Creating an index
  • Index design

SQL*Plus Report Writing

• Creating and formatting reports
  • Reporting with SQL*Plus
  • BREAK processing

Introduction to Oracle8 Objects

• Introduction to objects

Tuning Oracle

• Optimizing queries and indexes
  • The Oracle Optimizer
  • SQL*Plus AUTOTRACE
  • Using TKPROF with TRACE
• Index performance