Course Overview
This 3 day course provides a comprehensive introduction to the Oracle 9i SQL language and SQL*Plus. It has been designed for programmers, database administrators, consultants, managers, and anyone involved with Oracle database applications.
Extensive exercises start on day one and continue throughout the course, enabling delegates to practice and consolidate the material learned in each module.
Prerequisites
Any experience of programming and working with databases would be advantageous, but not essential.
Course Outline
Creating Views: What is a View? Simple and Complex views, creating a view, modifying a view, performing data manipulation through a view, the CHECK OPTION clause, in-line views.
Additional Database Objects: Creating Sequences, creating Indexes, creating Synonyms
Database Privileges: Obtaining system privileges, Database Roles, changing your password, obtaining and providing object privileges.
Data Manipulation: The INSERT command, inserting rows from another table, the UPDATE command, the DELETE command, using sub-queries for data manipulation, the MERGE command, COMMIT, ROLLBACK & SAVEPOINT commands, transactions and read-consistancy.
Creating Tables: The CREATE TABLE statement, describing the table structure, standard data types and the new Oracle 9i data types, creating a table using a sub-query, the ALTER TABLE statement, dropping, renaming and truncating a table.
Adding Constraints to Tables: What are Constraints ?, Constraint types, adding, dropping and disabling constraints.
Subqueries: What is a sub-query ?, single row sub-queries, multiple row subqueries, multiple column subqueries.
Set Operators: Using the UNION, INTERSECT and MINUS operators
Formatting Output using SQL*Plus: Substitution variables, changing settings in the SQL*Plus evironment using the SET command, the COLUMN command, the BREAK command, applying headers & footers, using script files.
Oracle Single Row Functions: Character manipulation functions, number functions, working with dates in Oracle, SYSDATE, date functions, datatype conversion functions, conditional functions, nested functions
Querying Data from Multiple Tables: Cartesian Products, Joins using the ORACLE syntax: Equi-Joins, Non-equi joins, Outer joins, Self Joins, overview of ANSI standard joins, using Table Aliases.
Using Group Functions: Types of group functions. The GROUP BY clause, the HAVING clause, ROLLUP and CUBE operators, nesting group functions.
Introduction: Relational Database concepts, Data Models, Entity Relationship models, introduction to Structured Query Language, SQL command categories, tables used in the course.
The Select Statement: The basic select statement, arithmetic expressions, operator precedence, Null values, using literals, column aliases, duplicate rows, interaction with SQL*Plus, creating script files
Applying Conditions and Sorting Data: The WHERE clause, comparison operators, multiple conditions using AND and OR, rules of precedence, the ORDER BY clause.
