In this class you will quickly learn how to build applications and / or support an Oracle database. This course is designed for developers, analysts and persons working towards being an Oracle DBA who have experience with a relational database (e.g. Sybase, DB2 or SQL Server) but are new to Oracle. This course leverages your existing knowledge of relational concepts and SQL to quickly get you started working with the Oracle database. Your instructor will not waste your time covering basics such as simple SELECT, UPDATE, DELETE and INSERT statements.
You will receive a comprehensive foundation of knowledge in Oracle SQL extensions (subquery factoring, regular expressions, flashback query, etc), Oracle datatypes, Oracle functions, Oracle schema objects, Oracle utilities and the common developer / administration tools, SQL*Plus and SQL Developer. Developers will be prepared to begin work in an Oracle environment. Administrators will be prepared for out Oracle 11g Database Administration course.
As with all of our courses, this class is highly customizable to your specific training requirements (when delivered at you company facility). Hands-on workshops constitute approximately 50% of the class.
This class will fully prepare you for Oracle Exam 1Z0-047 Oracle Database SQL Expert.
Compare to Oracle University course Oracle Database 11g: Introduction to SQL SkillBuilders course is:
- More complete in breadth and depth of subjects covered.
- Less expensive.
- Shorter: only 3 days (28 hours online) versus 5 days for the Oracle University course.
- Taught by an experienced instructor you can talk to before enrolling in class.
Course Features
- Lecture 0
- Quiz 0
- Duration 3 Days
- Skill level All levels
- Language English
- Students 17
- Assessments Yes
Introduction to the Oracle Database
- Principal Features and Tools Overview Enterprise Edition
- Express, Standard, Enterprise & Personal Editions
- 9i Release Highlights
- 10g Release Highlights
- 11g Release Highlights
- Related Products
- Summary
- Workshop
Introduction to SQL*Plus
- Introduction (And Why SQL*Plus is Still Important)
- Introducing the Development Environment
- Connect to SQL*Plus
- SQL*Plus Describe Command
- SQL*Plus Connect Command
- Customizing Your Environment (LOGIN.SQL and Predefined Variables)
- Replacing gLogin with Executable Defaults (11g Feature)
- SQL*Plus Host Command
- Executing Queries in SQL*Plus
- Spooling the Output
- Editing in SQL*Plus
- Editor Commands
- SQL*Plus Edit Command
- Related SQL*Plus Commands
- Editing: A Better Way
- Running SQL*Plus Scripts
- Exit from SQL*Plus
- What’s in my Recyclebin?
- SQL*Plus Error Logging (11g Feature)
- New BLOB Support (11g Feature)
- Workshop
Introduction to SQL Developer
- Downloading and Installing SQL Developer
- Getting to Know the Interface
- Making Database Connections
- Using the SQL Worksheet
- Creating a Table
- Creating Some Data
- Creating Reports
- Exporting Data
- Workshop
Querying the Database, Part I (Things that are different from your database.)
- Available Comparison Operators (=, BETWEEN, IN, LIKE, etc.)
- Pattern Matching with REGEXP_LIKE
- Oracle’s Treatment of NULL (NULLIF, and COALESCE functions)
- Quoting Text Strings (10g)
- Column Aliases
- Sorting by Column Alias
- Sorting by NULL
- Case (and Accent) Insensitive Sort and Search (10g)
- Accessing Remote Tables
- Dual: The Oracle Work Table
- Using CASE in SQL Statements
- Introduction to Regular Expressions
Pattern Matching with REGEXP_LIKE
- Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
- 11g Enhancements in Regular Expressions
- Workshop
Oracle Datatypes
- Oracle Date and Datetime Datatypes
- Workshop
- XML Data Type
- Miscellaneous Data Types
- Datatype Conversion
- Workshop
Oracle Functions
- Functions Overview
- String Manipulation Functions
- Regular Expressions
- Case Conversion Functions
- Numeric Functions
- Conversion Functions
- Time Zone Functions
- Date Arithmetic & Functions
- Adding & Subtracting Days
- Pseudo Columns
- Workshop
Querying the Database, Part II – Oracle Joins
- Oracle Outer Joins (+)
- ANSI Compliant Joins
- ANSI Natural Join
- ANSI Inner Join
- ANSI Outer Join
- ANSI Full Outer Join
- ANSI Cross Join
- Quick Mention: Partition Outer Join (10g) (attend our Advanced SQL Queries course for in-depth coverage of this feature functions)
- Workshop
- Querying the Database, Part III – Set Operators
- UNION, INTERSECT and MINUS Operators
- UNION Example
- INTERSECT Example
- MINUS Example
- Workshop
Querying the Database, Part IV – Subqueries
- Overview of Oracle Subquery Support
- Extended Subquery Support Examples
- Subquery Factoring (WITH)
- Workshop
- Querying the Database, Part V – Grouping Data
- Oracle Group (Aggregate) Functions
- Group Functions with Nulls
- ROLLUP
- CUBE
- GROUPING Function
- DECODE and GROUPING
- Quick Mention: Analytic Functions (attend our Advanced SQL Queries course for in-depth coverage of analytic functions)
- Workshop
Flashback Query
- Session and Statement-Level Flashback
- Flashback Version Query
- Flashback_Transaction_Query
- Flashback Table
- Undo Application Changes
- Recover from DROP TABLE
- Flashback Table: Recover from DROP
- Repeat Un-Drop
- Purging Recycle Bin
- Workshop
Data Manipulation
- List of Oracle DML Statements
- Returning Values from DML
- Returning Aggregates (10g)
- Inserting Multiple Rows
- Multi-Table INSERT
- INSERT IGNORE_ROW_ON_DUPKEY_INDEX (11g Feature)
- TRUNCATE Command
- MERGE statement
- Using Sequences in DML
- Creating Sequences
- Referencing Sequences
- Sequences & The Data Dictionary
- Workshop
Concurrency Control
- Introduction to Oracle Database Locks
- Oracle Row Lock Architecture
- Basic Locking Rules
- DML Locks
- DDL Locks
- Locking Issues: Lost Update
- Locking Issues: Blocking
- Locking Issues: Deadlocks
- Deadlocks: Cause and Fix
- Workshop
Transaction Control
- Transaction Review
- Supported Statements
- Statement Level Read Consistency
- Transaction Level Read Consistency
- Oracle Isolation Levels
- Phantom and Non-Repeatable Reads
- Read Committed
- Serializable
- Read Only
- Savepoints
- Implicit Commits
- Workshop
Introduction to Tables
- Types of Tables Supported (Heap, Clustered, IOT, etc)
- Naming Rules
- Listing Oracle Objects using the Data Dictionary
- Creating Tables
- Creating Tables from Other Tables (CTAS)
- Introducing Constraints
- Disabling Constraints
- Enabling Constraints
- Listing Constraints using the Data Dictionary
- ALTER TABLE Structure
- ALTER TABLE Adding Columns
- ALTER TABLE Adding Constraints
- DROP COLUMN
- Dropping Tables
- Looking at the Recyclebin
- Workshop
Introduction to Indexes
- Overview of Indexes Support (B-Tree, Bitmap, Reverse Key, etc)
- B-Tree Indexes
- Index Diagram
- ROWID Structure
- Index Creation
- Cost Based Optimizer Chooses Index
- Workshop
Oracle View Support
- Updateable Views
- Read Only Views
- WITH CHECK OPTION
- Views & The Data Dictionary
Oracle Synonyms
- Introduction to Synonyms
- Private Synonyms
- Public Synonyms
- CREATE SYNONYM Examples
- Synonym Search Sequence
- Synonyms & The Data Dictionary
- Workshop
Introduction to Oracle Security
- User Passwords
- Tablespace Quota Assigned to a User
- Security Basics
- Privilege Types
- System Privileges
- Object Privileges
- Role Concepts
- Determining Existing Privileges
- Workshop
Loading Data With SQL*Loader
- Introduction
- Executing SQL*Loader
- The Control File
- Variable Format Data
- Variable Format Data
- Fixed Format Data
- LOG File
- Conditional Loads
- SQL*Loader Workshop
- Introduction to External Tables
- Concepts
- Implementation and Use
- Use for Querying External Files
- Use for Loading Data
- External Table Tips
- Limitations
- Summary
- Workshop
- Introduction to Data Pump
- Data Pump Concepts
- Data Pump Modes
- Data Pump Access Methods
- Parallel Processing
- Export Example
- Import Example
- Workshop
- Introduction to SQL Tuning
- SQL Tuning Basics
- Tuning – The Process
- The Plan Table
- SQL*Plus AUTOTRACE
- Workshop
