Oracle Database Training
Oracle Introduction for Experienced SQL Users
Overview
The Oracle Introduction for Experienced SQL Users course is designed to give experienced SQL users practical experience in accessing an Oracle Database. This course is suitable for users of versions 10g, 11g and 12c.
Prerequisites
Practical experience of using SQL with another relational database is required.
Objectives
The Oracle Introduction for Experienced SQL Users course provides the skills necessary to query and update an Oracle Database using SQL*Plus and SQL Developer.
Outline
Course Contents - DAY 1
Course Introduction
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
Session 1: INTRODUCTION
- Oracle - a brief overview
- Users and roles
- Object naming rules
- Oracle datatypes
- The data dictionary
- Using SQL*Plus
- Entering and executing SQL statements
- Creating, editing and running SQL scripts
- Using SQL Developer
- Starting SQL Developer
- Configuring a Connection
- Navigation Tabs
- SQL Worksheet
Session 2: QUERYING THE DATABASE
- Overview of the basics
- Traditional Oracle v. ANSI-standard table joins
- Set operators
- Using aggregate functions
- Subqueries and in-line views
- Using the WITH clause
- Hierarchical queries
Session 3: NUMERIC AND CHARACTER FUNCTIONS
- Using the table dual
- Numeric functions
- Formatting numeric output
- Character functions and string concatenation
Session 4: DATE AND TIME FUNCTIONS
- Default date and timestamp formats
- Accessing the current date and time
- Date and time arithmetic
- Date functions
- Formatting date and time output
- Conversion functions
Session 5: MISCELLANEOUS FUNCTIONS
- NVL and NVL2
- DECODE
- The simple CASE expression
- The searched CASE expression
Course Contents - DAY 2
Session 6: ENHANCED GROUPING FEATURES
- The ROLLUP extension
- The CUBE extension
- The GROUPING function
- The GROUPING_ID function
- The GROUPING SETS extension
Session 7: TABLES, INDEXES AND VIEWS
- Creating tables
- Defining datatypes
- Specifying constraints
- Table, column and constraint operations
- Recovering dropped tables
- Temporary tables
- External tables
- Index-organized tables
- Handling B-tree indexes
- Creating and using views
- Creating and using sequences
Session 8: MANAGING DATA
- Inserting rows
- Updating rows
- Deleting rows
- Verifying updates
- Multi-table inserts
- Merging rows
- Basic transaction control
Session 9: ACCESS CONTROL
- System privileges
- User accounts and roles
- Granting and revoking object privileges
- Using synonyms
Session 10: THE DATA DICTIONARY
- Data dictionary tables
- Querying the data dictionary
- Useful SQL statements