Oracle SQL 10g: Level 1: Introduction for Beginners (3 day)

Course Overview

This course introduces delegates to the concepts of relational databases and provides an in-depth look at SQL in the SQL*Plus environment in order to retrieve and manipulate data from an Oracle database. The course may be combined with Oracle SQL Part 2, which is a useful pre-requisite before embarking on training for PL/SQL and Developer components such as Forms or Reports. This course is suitable for Oracle 9i and 10G.

Audience

Programmers, support staff and power users who wish to gain a comprehensive understanding of how the Oracle Structured Query Language is used.

Skills Gained

On completing the course, delegates will be able to:

• Utilise the SQL language to retrieve data from an Oracle Database.
• Edit, store and recall queries in SQL* Plus, format results, and create standard queries with substitution variables.

Examinations

N/A

Prerequisites

Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform.

Course Outline

• Introduction to RDBMS and Oracle
• Relational database theory
• Entity/relation/table
• Attribute/column/field
• Tuple/row/record
• Relationship
• Primary key
• Foreign key
• Normalisation
• Relational operations Relational database properties
• Table properties
• An overview of oracle
• iso (incorporating ansi and bsi) standards
• Oracle architecture
• Oracle background processes
• Quiz

• iSQL*Plus, SQL*Plus, SQLPlus Worksheet & SQL
• SQL*Plus
• SQL*Plus editor commands
• SQL*Plus worksheet
• iSQL*Plus
• A practical session on SQL*Plus
• Select all columns
• Select certain columns
• Distinct/unique
• Select certain rows
• Between
• Like
• In
• Nulls
• Compound where
• Sort rows
• Perform a calculation
• Column alias
• Non-column text
• Common sql errors
• Quiz and practical session on Structured Query Language

• Joins and Functions
• Oracle9i/10G joins
• Outer joins
• Oracle9i/10G outer joins
• A practical session on joins
• Dates
• Functions
• Conversion functions
• Date functions
• Character functions
• Numeric functions
• Group functions
• Group by
• Having
• Cube, rollup & grouping
• Hierarchical quieries
• System functions
• Quiz and practical session on functions

• Sub-Queries
• Nested sub-queries
• Multi-column sub-queries
• Correlated sub-queries
• Exists
• Inline views
• Quiz and practical session on sub-queries

• Case Statements
• Case statement examples
• Decode vs. Case
• Quiz and practical session on case statements

• Set Operators
• Union
• Intersect
• Minus
• Quiz and practical session on set operators

• Data Manipulation
• Inserting values into a table
• Select clause with insert
• Changing existing values
• Deleting records
• Changes via views
• Transactions
• Locking
• Quiz and practical session on data manipulation

• SQL*Plus Formatting & Reporting
• SQL*Plus define command
• SQL*Plus autotrace
• SQL*Plus timing
• Set options
• Column headings
• Titles
• Break points
• Calculations
• Spool
• Store
• Exit
• Quiz and practical session on SQL*Plus formatting & reporting

• Command File Parameters
• Keyword parameters
• Positional parameters
• Additional variable substitution features
• Quiz and Practical session on command file parameters

Follow On Courses

Oracle SQL 10G: Level 2

Oracle SQL 10g: Level 1: Introduction for Beginners (3 day) / ORSQL1