Introduction to Oracle8i: SQL & SQL*Plus

This 3-day course is a common starting point in the Oracle database curriculum. The objective of this course is to provide an introduction to the SQL database language within the context of the Oracle database. On course completion, students will have an understanding of :

• A summation of relational database principles.
• An introduction to the Oracle database server.
• Creating and maintaining database tables.
• Creating and maintaining other database objects.
• Writing simple SQL queries.
• Writing moderately complex SQL queries using various join techniques.
• Advanced query techniques such as correlated subqueries.
• Summarizing, grouping and sorting data.
• Developing simple SQL*Plus reports
• Build basic report modules
• Produce all report styles including master-detail, form letter, labels
• Customize report output with frames, PL/SQL triggers and report objects
• Output report contents as HTML pages

Target audience

The target audience for this course is all Oracle professionals, both business and systems professionals. Students are recommended to have a basic understanding of computer programming concepts. Students are recommended to have attended the following course or have the equivalent knowledge and experience:

• Advanced Technology Tour
• Oracle Designer Data Modeling

Course content

Relational databases and SQL About SQL • About SQL*PLUS • Syntax rules for SQL & SQL*PLUS

SQL data definition language Create table statement • NOT NULL • DEFAULT • ALTER TABLE S_BLELT • DROP TABLE STATEMENT • DESCRIBE • RENAME • Additional database objects

Using the SQL*PLUS tool Session control commands • Buffer control commands • SQL buffer editing commands

SQL data manipulation language About the SELECT statement • About the INSERT statement • About the DELETE statement • About the UPDATE statement • About transactions • Complex table references • Building a select clause • Selecting columns • Using alias names

Building a WHERE clause Equality operator • Boolean operators • NULL & BETWEEN operators • Finding text strings • IN operator

Ordering the result table Multiple column sorts • Specifying the sort sequence • About null values within sorts • Using column ALIA_N

Pseudo columns and functions Using ROWID • Using ROWNUM • Using the functions • SYSDATE • USER & UID • Using the dual table

Joining tables About joins • INNER JOIN • REFLEXIVE JOIN • NON-KEY JOIN • OUTER JOIN

Using set operators Set operator examples • Performing a full outer join

Summary functions

Using subqueries Standard subqueries • Correlated subqueries • Creating and selecting groups • Finding groups within the base tables • Selecting data from the base tables • Selecting groups from the result table

Creating and using relational views

Creating and using indexes and synonyms

Understanding the data dictionary

Build simple SQL*PLUS reports Session control with SET • Format column output with COLUMN

Produce subtotals with COMPUTE

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today