AUDIENCE:   Programmers, support staff and power users who wish to gain a comprehensive understanding of how the Oracle Structured Query Language is used. PREREQUISITES:   Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform. DURATION:   3 days. Hands on. OBJECTIVES:   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, 10G and 11G. 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. COURSE CONTENT:   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 PI07/01