SUMMARY:   This course will introduce students to the concept 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 our Oracle SQL Part Two course, which is an essential pre-requesite before embarking on training for PL/SQL Programming and Developer components such as Forms or Reports. This course is suitable for Oracle10g. AUDIENCE:   Programmers, support staff and powerusers who need to gain a comprehensive understanding of how the Oracle Structured Query Language is used. PREREQUISITES:   Students should be computer literate with a good knowledge of the standard editor available on their target hardware platform. DURATION:   3 Days. * Note:This course can also be combined with our Oracle10g PL/SQL Part One which is an essential prerequisite before embarking on training for PL/SQL programming, Developer Forms or Developer Reports. APPROACH:   This course is highly interactive and consists of theory coupled with plenty of hands-on practical sessions. OBJECTIVES:   Upon Completion of this course, students will be able to utilize the SQL language to retrieve data from an Oracle Database. Students will also be able to edit, store and recall queries in SQL*Plus format results, and create standard queries with substitution variables. COURSE CONTENT:   DATABASE OVERVIEW INTRODUCTION DATABASE (R)DBMS (RELATIONAL) DATABASE MANAGEMENT SYSTEM RELATIONAL DATABASE THEORY ENTITY/RELATION/TABLE ATTRIBUTE/COLUMN/FIELD TUPLE/ROW/RECORD RELATIONSHIPS 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 iSQL*Plus, SQL*PLUS, SQLPlus Worksheet & SQL INTRODUCTION SQL*PLUS SQL*Plus Editor Commands SQL*Plus Commands (Continued) SQL*PLUS Worksheet iSQL*PLUS 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< JOINS AND FUNCTIONS INTRODUCTION ORACLE SQL99 JOINS OUTER JOINS ORACLE SQL99 OUTER JOINS 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 SUB-QUERIES INTRODUCTION NESTED SUB-QUERIES MULTI-COLUMN SUB-QUERIES CORRELATED SUB-QUERIES EXISTS INLINE VIEWS CASE STATEMENTS INTRODUCTION CASE STATEMENT EXAMPLES DECODE VS. CASE SET OPERATORS INTRODUCTION UNION INTERSECT MINUS DATA MANIPULATION INSERTING VALUES INTO A TABLE SELECT CLAUSE WITH INSERT CHANGING EXISTING VALUES DELETING RECORDS CHANGES VIA VIEWS TRANSACTIONS LOCKING SQL*PLUS FORMATTING & REPORTING INTRODUCTION SQL*PLUS DEFINE COMMAND SQL*PLUS AUTOTRACE SQL*PLUS TIMING SET OPTIONS COLUMN HEADINGS TITLES BREAK POINTS CALCULATIONS SPOOL STORE EXIT COMMAND FILE PARAMETERS INTRODUCTION KEYWORD PARAMETERS POSITIONAL PARAMETERS ADDITIONAL VARIABLE SUBSTITUTION FEATURES APPPENDICES APPENDIX A: ORACLE DATA DICTIONARY INTRODUCTION VIEWS