Overview
Course Description
Thank you for reading this post, don't forget to subscribe!In this course, you will receive an introduction to Oracle’s relational database, and a hands-on introduction in how to add, update, extract, and organize information from the database. You will work with Oracle’s Structured Query Language (SQL), SQL*Plus, and SQL Developer.
Audience Profile
Anyone who needs to extract and organize information from an Oracle database, including formatting reports, adding, updating, and deleting data, and exporting data to ODBC files, including:
- Application designers
- Developers
- Database administrators
- End users
Prerequisites
There are no prerequisites for this course.
Learning Objectives
- Format reports using SQL*Plus commands.
- Extract and organize information from the database.
- Insert, update, and delete information in database tables.
- Understand Structured Query Language basics
Course Outline
Course Outline
Module 1: Introduction to the Oracle Database
Lessons
• Why Use an Entity Relationship Model?
• Physical Data Storage
• Relationships between Tables
• Domains and Data Integrity Constraints
• Data Model Review
Lab: RDBMS Principles
Lab: SQL*Plus Basics
Module 2: SQL Basics and SQL*Plus
Lessons
• Understanding the Difference Between SQL, SQL*Plus and PL/SQL
• Invoking and Leaving SQL*Plus
• Entering and Executing Commands
• The SQL Buffer, SQL*Plus Default Output
• LOGIN.SQL / GLOGIN.SQL
• The SELECT Command & Mandatory Clauses
Lab: SQL Developer
Lab: Insert, Update and Delete Data
Module 3: Using SQL Developer
Lessons
• Understanding SQL Developer
• SQL Developer Setup
• Advanced Functionality
Lab: SQL Queries
Lab: Filtering and Sorting Data
Module 4: The WHERE and ORDER BY Clause
Lessons
• WHERE, ORDER BY Clause
• Using the Data Dictionary
• Hierarchical Queries
• Pattern Matching
Lab: Number Functions
Lab: Character Functions
Module 5: Single Row Functions
Lessons
• Arithmetic Operators
• Operator Precedence
• Single Row vs. Group Functions
• Date and Numeric Format Models
• Data Conversion / Date Manipulation / Time Zones
• Control Statements
• IF THEN ELSE Logic
• Regular Expressions
Lab: Date Functions
Lab: Misc. Functions
Module 6: Joining Data from Multiple Tables in Queries
Lessons
• ANSI SQL 92 and SQL99 Joins
• The JOIN Condition / The Cartesian Product
• Outer JOINs
Lab: Joining Multiple Tables
Lab: ANSI SQL99 Joins
Module 7: Group Functions and the GROUP BY Clause
Lessons
• Group Functions
• Distinct Operator in Group Functions
• GROUP BY, WITH Clause, ROLLUP, CUBE
• GROUPING SETS
Lab: Group Clauses and Functions
Lab: Subqueries
Module 8: Using Subqueries
Lessons
• Single-Row, Multi-Row, Multi-Column Subqueries
• Subqueries in a WHERE / FROM / HAVING Clauses
• Subqueries In the SELECT Clause – Scalar Subqueries
• Correlated Subqueries / Hierarchical Queries
• EXISTS / NOT EXISTS / WITH / Recursive WITH
Lab: SQL Loader
Module 9: Data Manipulation Language (DML) Commands
Lessons
• INSERT, UPDATE, DELETE Command
Module 10: Getting Data In and Out of Oracle
Lessons
• SQL*Loader Basics
• Importing Into Excel
• Direct Database Queries Using ODBC