Receive an introduction to Oracle’s relational database and get hands-on practice learning to add, update, extract, and organize information from the database. You will cover how to format reports, and you will learn how to export data to ODBC files. You will work with Oracle’s Structured Query Language (SQL), SQL*Plus.

What You’ll Learn

  • Format reports using SQL*Plus commands
  • Extract and organize information from the database
  • Insert, update, and delete information in database tables
  • Basics of SQL

Who Needs to Attend

Anyone, including application designers, developers, database administrators, and end users, who needs to extract and organize information from an Oracle database, performing tasks such as formatting reports, exporting data to ODBC files, and adding, updating, and deleting data

Prerequisites

An understanding of relational database design concepts

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Database Management System Overview

  • What a Database Is
  • Types of DBMSs

2. Introduction to Entity Relationship Modeling

  • What a Data Model Is
  • Why Use an Entity Relationship Model

3. Fundamentals of Relational Database Management Systems

  • What a Relational Database Management System (RDBMS) Is
  • Physical Data Storage
  • Relationships Between Tables
  • Domains
  • Data Integrity

4. SQL Basics and SQL*Plus

  • Difference Between SQL, SQL*Plus and PL/SQL
  • Invoking and Leaving SQL*Plus
  • Entering and Executing Commands
  • The SELECT Command and Mandatory Clauses

5. Using SQL Developer

  • SQL Developer Environment and Commands

6. The WHERE and ORDER BY Clause

  • Using the Data Dictionary

7. Single Row Functions

  • Functions, Date, and Numeric Format Models
  • Data Conversion
  • Control Statements

8. Joining Data from Multiple Tables in Queries

  • The JOIN Condition
  • The Cartesian Product
  • Outer JOINs
  • ANSI99 Joins

9. Group Functions and the GROUP BY Clause

  • Group Functions
  • Distinct Operator in Group Functions

10. Using Subqueries and CASE Clauses

  • Single-Row, Multi-Row, and Multi-Column Subqueries
  • SELECT, FROM, WHERE, HAVING, ORDER BY, and Correlated Subqueries
  • CASE expressions

11. Analytic Functions (Optional)

  • Single Row and Aggregating Analytic Functions

12. SQL*Plus Reporting

  • SQL*Plus Report Writing Commands
  • COLUMN Command / Titles
  • System Variables
  • Master-Detail Report with TITLE and COLUMN Commands
  • Control Breaks
  • Computing Aggregate Amounts

13. Data Manipulation Language (DML) Commands

  • INSERT, UPDATE, DELETE, and MERGE Command
  • Use of Subqueries
  • Transaction Control / Read Consistency / Locking

14. Getting Data In and Out of Oracle

  • SQL*Loader Basics
  • Importing into Excel
  • Direct Database Queries Using ODBC

Labs

Lab 1: RDBMS Principles

Lab 2: SQL*Plus Basics

Lab 3: SQL Developer

Lab 4: Insert, Update, and Delete Data

Lab 5: SQL Queries

Lab 6: Filtering and Sorting Data

Lab 7: Single Row Functions

Lab 8: Joining Multiple Tables

Lab 9: ANSI SQL99 Joins

Lab 10: Group Clauses and Functions

Lab 11: Subqueries

Lab 12: Analyzing Data (Optional)

Lab 13: SQL*Plus Reporting