Develop the skills to effectively interact with an Oracle8i Database.

Print Outline in PDF format

Course Description

Oracle8i Foundations: SQL Basics and SQL*Plus is a hands-on course, which gives you a basic knowledge of relational databases and how to access them through SQL and SQL*Plus. This training covers topics that are necessary to the development and administration of an Oracle 8i database.

You can learn to …

• Converse in relational database terminology

• Understand Structured Query Language (SQL) basics:

  • WHERE Clause

  • Data Types, Functions, Data Manipulation

  • Ordering & Grouping, Indexing, Joining Tables

  • Transactions (Commit & Rollback)

  • Subqueries, Importing and Exporting

• Format reports using SQL*Plus commands

• Extract and organize information from the database

• Insert, update & delete information in database tables

• Create and drop tables, views, synonyms and indexes

• Load tables using SQL*Loader

• Use the Export and Import utilities

Who Needs to Attend:

Application Designers, Developers, Database Administrators and End Users.

Prerequisites:

An understanding of relational database design concepts.

Course Labs:

1. Entity Identification

2. Modeling

3. Relationship

4. Resolving M:M

5. Subtype and Exclusive Arc

6. Normalization

7. RDBMS

8. SQL*PLUS Basics

9. WHERE and ORDER By Clause

10. SQL Language Elements

11. Functions

12. Group Functions

13. Join Condition

14. Subquery

15. DML

16. Basic Data Definition Language

17. SQL*Plus Reporting

Course Content:

DATABASE MANAGEMENT SYSTEM OVERVIEW

• Database Management System (DBMS)

• Hierarchical , Network, and Relational DBMSs (RDBMSs)

INTRODUCTION TO RELATIONAL DATABASE (RDB) DESIGN

• What is a data model? Why develop & utilize an entity relationship model?

• Components of an entity relationship model

• Entities, Examples of entities, Determining entities, Entity classification

• Relationships

• Physical layout properties

• Types of relationships, Determining relationships, Reading the entity relationship (ER) model

• Attributes, Unique identifiers, Syntax

• Guidelines for a model review

• Resolving many-to-many relationships

• Supertypes and subtypes, Subtype inheritance

• Exclusive arcs, Relationship sentence syntax

• Mandatory rules, Domains, Derived attributes, Cascading, Non-transferable relationship, Qualified degree

NORMALIZATION

• The final normalized entity relationship model

FUNDAMENTALS OF RELATIONAL DATABASE MANAGEMENT SYSTEMS (RDBMSs)

• Relational database principles

SQL BASICS & SQL*PLUS

• Understanding the difference between SQL, SQL*Plus and PL/SQL

THE WHERE AND ORDER BY CLAUSE

• The Where and Order By clauses

STRUCTURED QUERY LANGUAGE (SQL) ELEMENTS

• Structured query language (SQL)

• Database schema objects, Pseudo columns, Object Reference,

• Data Dictionary, Datatypes, The dual table

SINGLE ROW FUNCTIONS

• Single row functions

GROUP FUNCTIONS

• The Group By and The Having clauses

THE JOIN CONDITION

• Outer join, Table aliases, Set operators

SUBQUERY

DATA MANIPULATION LANGUAGE (DML) COMMANDS

• Transaction control commands, Inserting data into large objects

DATA DEFINITION LANGUAGE  (DDL) COMMANDS

• Object naming, The Create Table command

• Integrity constraints, Indexes, Comment command

• Dropping, Disabling, and Enabling constraints, Displaying constraint Information

SQL* Plus REPORTING

• SQL* Plus report writing commands

• Column command, Titles, System variables

• A master-detail report with title and column commands

• Control breaks, Computing aggregate amounts

• Using set to customize the SQL*Plus environment

• Using variables in SQL*Plus, Types of variables

ADVANCED TOPICS

• Objectives, Correlated subquery, Connect By, Tree-structured query

• Oracle 8i specific terminology