Oracle Training Overview
This course provides a complete, hands-on introduction to SQL including the use of both SQL Developer
and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g. A full
presentation of the basics of relational databases and their use are also covered.
Oracle Training Audience
This course is appropriate for anyone needing to interface with an Oracle database or those needing a
general understanding of Oracle database functionality. That would include end users, business analysts,
application developers and database administrators.
Oracle Training Prerequisites
Basic computer skills are needed. A basic knowledge of databases is desired but not required.
Course duration
5 Days
Oracle Training Course outline
CHAPTER 1 BASIC RDBMS PRINCIPLES
- RELATIONAL DESIGN PRINCIPLES
- ACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGE
- ENTITY RELATIONSHIP DIAGRAMS
- DATA DOMAINS
- NULL VALUES
- INDEXES
- VIEWS
- DENORMALIZATION
- DATA MODEL REVIEW
- LAB 1: BASIC RDBMS PRINCIPLES
- LAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLES
CHAPTER 2 THE SQL LANGUAGE AND TOOLS
- USING SQL*PLUS
- Why Use SQL*Plus When Other Tools Are Available?
- Starting SQL*Plus
- EZConnect
- SQL Commands
- PL/SQL Commands
- SQL*Plus Commands
- The COLUMN Command
- The HEADING Clause
- The FORMAT Clause
- The NOPRINT Clause
- The NULL Clause
- The CLEAR Clause
- Predefined DEFINE Variables
- Login.sql
- Command History
- Copy And Paste In SQL*Plus
- Entering SQL Commands
- Entering PL/SQL Commands
- Entering SQL*Plus Commands
- Default Output From SQL*Plus
- Entering Queries
- WHAT ABOUT PL/SQL?
- LAB 2: SQL LANGUAGE AND TOOLS
- LAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLS
CHAPTER 3 USING SQL DEVELOPER
- CHOOSING A SQL DEVELOPER VERSION
- CONFIGURING CONNECTIONS
- Creating A Basic Connection
- Creating A TNS Connection
- Connecting
- CONFIGURING PREFERENCES
- USING SQL DEVELOPER
- The Columns Tab
- The Data Tab
- The Constraints Tab
- The Grants Tab
- The Statistics Tab
- Other Tabs
- Queries In SQL Developer
- Query Builder
- Accessing Objects Owned By Other Users
- The Actions Pulldown Menu
- DIFFERENCES BETWEEN SQL DEVELOPER AND SQL*PLUS
- Reporting Commands Missing In SQL Developer
- General Commands Missing In SQL Developer
- DATA DICTIONARY REPORTS
- USER DEFINED REPORTS
- USING SCRIPTS IN SQL DEVELOPER
- LAB 3: USING SQL DEVELOPER
- LAB 3 SOLUTIONS: USING SQL DEVELOPER
CHAPTER 4 SQL QUERY BASICS
- UNDERSTANDING THE DATA DICTIONARY
- Exporting Key Data Dictionary Information
- THE DICTIONARY VIEW
- COMPONENTS OF A SELECT STATEMENT
- The SELECT Clause
- The FROM Clause
- The WHERE Clause
- The GROUP BY Clause
- The HAVING Clause
- The ORDER BY Clause
- The START WITH And CONNECT BY Clauses
- The FOR UPDATE Clause
- Set Operators
- COLUMN ALIASES
- FULLY QUALIFYING TABLES AND COLUMNS
- TABLE ALIASES
- USING DISTINCT AND ALL IN SELECT STATEMENTS
- LAB 4: SQL QUERY BASICS
- LAB 4 SOLUTIONS: SQL QUERY BASICS
CHAPTER 5 DATA MANIPULATION
- THE DATA MANIPULATION LANGUAGE
- The INSERT Command
- The UPDATE Command
- The Delete Command
- Using The DEFAULT Keyword With Updates And Inserts
- USING SQL DEVELOPER FOR DML
- THE TRANSACTION CONTROL LANGUAGE (TCL)
- IMPLICIT TCL
- LAB 5: DATA MANIPULATION
- LAB 5 SOLUTIONS: DATA MANIPULATION
CHAPTER 6 -- WHERE AND ORDER BY
- WHERE CLAUSE BASICS
- COMPARISON OPERATORS
- LITERALS AND CONSTANTS IN SQL
- SIMPLE PATTERN MATCHING
- LOGICAL OPERATORS
- THE DUAL TABLE
- ARITHMETIC OPERATORS
- EXPRESSIONS IN SQL
- CHARACTER OPERATORS
- PSEUDO COLUMNS
- ORDER BY CLAUSE BASICS
- ORDERING NULLS
- ACCENT AND CASE INSENSITIVE SORTS
- SAMPLING DATA
- WHERE AND ORDER BY IN SQL DEVELOPER
- ALL, ANY, SOME
- LAB 6: WHERE AND ORDER BY
- LAB 6 SOLUTIONS: WHERE AND ORDER BY
CHAPTER 7 FUNCTIONS
- THE BASICS OF ORACLE FUNCTIONS
- NUMBER FUNCTIONS
- CHARACTER FUNCTIONS
- DATE FUNCTIONS
- CONVERSION FUNCTIONS
- OTHER FUNCTIONS
- LARGE OBJECT FUNCTIONS
- ERROR FUNCTIONS
- THE RR FORMAT MODEL
- LEVERAGING YOUR KNOWLEDGE
- LAB 7: FUNCTIONS
- LAB 7 SOLUTIONS: FUNCTIONS
CHAPTER 8 ANSI 92 JOINS
- BASICS OF ANSI 92 JOINS
- USING QUERY BUILDER WITH MULTIPLE TABLES
- TABLE ALIASES
- OUTER JOINS
- Outer Joins In Query Builder
- SET OPERATORS
- SELF-REFERENTIAL JOINS
- NON-EQUIJOINS
- LAB 8: ANSI 92 JOINS
- LAB 8 SOLUTIONS: ANSI 92 JOINS
CHAPTER 9 ANSI 99 JOINS
- CHANGES WITH ANSI99
- CROSS JOIN
- NATURAL JOIN
- JOIN USING
- JOIN ON
- LEFT / RIGHT OUTER JOIN
- FULL OUTER JOIN
- LAB 9: ANSI 99 JOINS
- LAB 9 SOLUTIONS: ANSI 99 JOINS
CHAPTER 10 GROUP BY AND HAVING
- INTRODUCTION TO GROUP FUNCTIONS
- Limiting Rows
- Including NULLs
- Using DISTINCT With Group Functions
- Group Function Requirements
- The HAVING Clause
- Other Group Function Rules
- Using Query Builder With Group Clauses
- ROLLUP And CUBE
- The GROUPING Function
- GROUPING SETS
- LAB 10: GROUP BY AND HAVING
- LAB 10 SOLUTIONS: GROUP BY AND HAVING
CHAPTER 11 SUBQUERIES
- WHY USE SUBQUERIES?
- WHERE Clause Subqueries
- From Clause Subqueries
- Having Clause Subqueries
- Correlated Subqueries
- Scalar Subqueries
- DML And Subqueries
- EXISTS Subqueries
- Hierarchical Queries
- Top N And Bottom N Queries
- Creating Subqueries Using Query Builder
- LAB 11: SUBQUERIES
- LAB 11 SOLUTIONS: SUBQUERIES
CHAPTER 12 BASIC REPORTING
- BASIC REPORTING
- The COLUMN Command
- Setting Column Width
- PRINT | NOPRINT
- TTITLE | BTITLE
- REPHEADER / REPFOOTER
- NEW_VALUE / OLD_VALUE
- Using Substitution Variables
- The COMPUTE Command
- Comments In Script Files
- SUBSTITUTION VARIABLES
- Named Substitution Variables
- Numbered Substitution Variables
- Dealing With Multiple References
- Using The DEFINE Command
- The ACCEPT And PROMPT Commands
- Running Scripts Unattended
- LAB 12: BASIC REPORTING
- LAB 12 SOLUTIONS: BASIC REPORTING
CHAPTER 13 DATA IMPORT AND EXPORT
- USING SQL*LOADER WITH FIELD DELIMITED DATA
- USING SQL*LOADER WITH COMMA DELIMITED DATA
- DATA LOADING USING SQL DEVELOPER
- EXPORTING ORACLE DATA INTO EXCEL
- DOING AN ODBC QUERY
- A WORD ABOUT DATA PUMP
- LAB 13: DATA IMPORT AND EXPORT
- LAB 13 SOLUTIONS: DATA IMPORT AND EXPORT
CHAPTER 14 SECURITY
- BASIC SECURITY
- SYSTEM Privileges
- Object Privileges
- THE DATA DICTIONARY AND SECURITY
- USING ROLES FOR PRIVILEGE MANAGEMENT
- USING PROFILES
- Kernel Limits
- Password Limits
- Creating And Using Profiles
- LAB 14: SECURITY
- LAB 14 SOLUTIONS: SECURITY
CHAPTER 15 ADVANCED DATA MANIPULATION
- THE MERGE COMMAND
- MULTIPLE COLUMN SUBQUERY UPDATES AND DELETES
- DML AGAINST VIEWS
- TRANSACTIONS AND READ CONSISTENCY
- DML LOCKS
- FLASHBACK TECHNOLOGIES
- INSERTING LARGE OBJECTS
- CHANGED DATA TRACKING
- Flashback Versions Query
- Log Miner
- Change Data Capture
- Flashback Data Archive
- LAB 15: ADVANCED DATA MANIPULATION
- LAB 15 SOLUTIONS: ADVANCED DATA MANIPULATION
CHAPTER 16 INTRODUCTION TO DATA DEFINITION
- INTRODUCTION TO DDL COMMANDS
- KEY OBJECTS
- OBJECT NAMING RULES
- THE DATA DICTIONARY
- AVAILABLE DATATYPES
- THE CREATE TABLE STATEMENT
- NAMING CONSTRAINTS
- INTEGRITY CONSTRAINTS
- Primary Keys
- Foreign Keys
- NOT NULL Constraints
- UNIQUE Constraints
- CHECK Constraints
- DEFAULT Values
- IDENTITY Columns
- Constraints And CREATE TABLE
AS SELECT
- Constraint Limitations
- CREATING TABLES IN SQL DEVELOPER
- OTHER DDL ACTIONS IN SQL DEVELOPER
- THE ALTER TABLE COMMAND
- DROPPING OBJECTS
- RENAMING OBJECTS
- THE TRUNCATE COMMAND
- THE COMMENT COMMAND
- CREATING SIMPLE VIEWS
- LAB 16: INTRODUCTION TO DATA DEFINITION
- LAB 16 SOLUTIONS: INTRODUCTION TO DATA DEFINITION
CHAPTER 17 ADVANCED DATA DEFINITION
- DDL AND THE DATA DICTIONARY
- DISABLING CONSTRAINTS
- ENABLING CONSTRAINTS
- HANDLING CONSTRAINT EXCEPTIONS
- USING DEFERRABLE CONSTRAINTS
- SEQUENCES
- EXTERNAL TABLES FOR DATA STORAGE
- Why Are External Tables Useful
- Privileges Needed
- Syntax For Creating External Tables
- EXTERNAL TABLES AND THE ORACLE_DATAPUMP DRIVER
- INDEXES
- Guidelines
- Index Creation Syntax
- Rebuilding Indexes
- Function Based Indexes
< - COMMENTS
- SYNONYMS
- COMPLEX VIEWS
- VIRTUAL COLUMNS
- COMPRESSED TABLES
- INVISIBLE INDEXES
- ONLINE DDL ENHANCEMENTS
- INVISIBLE COLUMNS
- CREATING MULTIPLE INDEXES ON COLUMNS
- LAB 17: ADVANCED DATA DEFINITION
- LAB 17 SOLUTIONS: ADVANCED DATA DEFINITION
CHAPTER 18 REGULAR EXPRESSIONS
- AVAILABLE REGULAR EXPRESSION FUNCTIONS
- REGULAR EXPRESSION OPERATORS
- CHARACTER CLASSES
- PATTERN MATCHING OPTIONS
- REGEX_LIKE
- REGEXP_SUBSTR
- REGEXP INSTR
- REGEXP_REPLACE
- REGEXP_COUNT
- LAB 18: REGULAR EXPRESSIONS
- LAB 18 SOLUTIONS: REGULAR EXPRESSIONS
CHAPTER 19 ANALYTICS
- THE WITH CLAUSE
- REPORTING AGGREGATE FUNCTIONS
- ANALYTICAL FUNCTIONS
- USER-DEFINED BUCKET HISTOGRAMS
- THE MODEL CLAUSE
- PIVOT AND UNPIVOT
- TEMPORAL VALIDITY
- LAB 19: ANALYTICS
- LAB 19 SOLUTIONS: ANALYTICS
CHAPTER 20 ANALYTICS II
- RANKING FUNCTIONS
- RANK
- DENSE_RANK
- CUME_DIST
- PERCENT_RANK
- ROW_NUMBER
- WINDOWING AGGREGATE FUNCTIONS
- RATIO_TO_REPORT
- LAG / LEAD
- LINEAR REGRESSION FUNCTIONS
- INVERSE PERCENTILE FUNCTIONS
- HYPOTHETICAL RANKING FUNCTIONS
- PATTERN MATCHING
|
