About the course

The course is intended for those who need to become familiar with SQL in general, or ORACLE SQL in particular. This is an excellent foundation for anyone intending to become a developer, a database administrator or a 'power' end user, writing their own queries and reports. The course is suitable for those with little or no knowledge of SQL or ORACLE.

The workshops used on the course are open-ended, in that any number of outcomes may well be acceptable. As you will all have different solutions, be prepared for a variety of problems to crop up in the training laboratory. The workshops are an opportunity for you to order a large amount of unfamiliar material in your minds and put into practice the skills you have learned on the course.

A real-world view of the training is adopted, to take the delegate past the training lab and into the world of business computing. Your contribution to the labs is highly valued in this respect. Feel free to discuss issues relating to your own businesses, especially where the class as a whole would benefit from seeing what problems, and options to solve them, exist in the real world, outside the training lab.

Should you need examples to help you get started with individual a resource for delegates has been provided, mounted under the root directory as:

c:\resources\SQLegs … on a windows platform
/rootmgr/oracle/SQLegs on a Unix system
Please feel free to take this away with you on floppy disk. Routines are present to facilitate the complete reconstruction of the classroom environment on a machine at work that you may wish to practice on.

The course introduces some of Oracle's powerful data manipulation utilities, eg SQL*LOADER which though not strictly speaking SQL, form a powerful component of the users Oracle tools set.

Prerequisites

Familiarity with Oracle is not necessary as we teach you all you need to know to get started on the course. You should be confident with the use of office applications generally. You will be learning what is in effect a programming environment, albeit one that is readily accessible to the user, and should feel comfortable with this type of work.

Participants

The course is intended for those who need to become familiar with SQL in general, or Oracle's version of SQL in particular. This is an excellent foundation for anyone intending to become a developer, a database administrator or a 'power' end user, writing their own queries and reports. BusinessObjects managers will also find the training essential.

Duration 4 days

Please note the course contents can be tailored for in-house customers who may require only certain sections to be delivered in 2 or 3 days, to suit requirements.

1. Introducing SQL

• Where SQL is used
• Why SQL?
• Oracle Architecture

7. Scalar functions

• Introduction
• Number functions
• Character functions returning character values
• Character functions returning number values
• Date functions
• Conversion functions
• Other functions
• Group functions
• User functions
• Format models

12. Transaction control

• COMMIT and ROLLBACK
• Locking

2. The SQL*PLUS monitor

• Connecting to ORACLE
• SQL code - the development life cycle
• Additional commands available in SQL*PLUS
• Configuring the SQL*PLUS monitor environment

8. Table joins and complex queries

• Cartesian products
• Simple joins
• Inner and outer joins
• Self joins
• Complex queries
• Set operators

13. SQL in programming environments

• Using substitution variables
• Bind variables
• PL/SQL procedures and functions

4. Database design principles

• Database principles
• Relational terminology
• Designing a simple suite of application tables
• Oracle software products

9. Creating database objects

• Introducing object types
• Object life-cycle management (create, alter, drop)
• Tables
• Partitions
• Indexes
• Sequences
• Synonyms
• Views
• Introduction to the data dictionary

14. SQL*PLUS reports

• Control breaks
• Column output
• Headers and Footers
• Report formatting
• Column layouts and alternative headings
• Page formatting
• Column breaks, subtotals and hidden columns

5. Introduction to SQL syntax

• Structure of an SELECT statement
• The WHERE clause
• Column and table aliasing
• Handling duplicates
• Order by clause
• IN lists
• Predicates
• Performing calculations

10. Data manipulation language (DML)

• INSERT
• UPDATE
• DELETE

6. Aggregate functions

• MAX, MIN, AVG, SUM, COUNT
• The group by clause
• The having clause

11. Working with batch files

• ORACLE's "sqlplus" command
• The SQL loader, "sqlldr"

16. Security

• Creating users and managing resources
• Database roles
• Permission and privilege