Oracle 12c: SQL & SQL*Plus Basics Express Introduction

COURSE OUTLINE:

Description

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

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

An understanding of relational database design concepts

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

1. Introduction to the Oracle Database

  • Why Use an Entity Relationship Model?
  • Physical Data Storage
  • Relationships between Tables
  • Domains and Data Integrity Constraints
  • Data Model Review

2. SQL Basics and SQL*Plus

  • 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

3. Using SQL Developer

  • Understanding SQL Developer
  • SQL Developer Setup
  • Advanced Functionality

4. The WHERE and ORDER BY Clause

  • WHERE, ORDER BY Clause
  • Using the Data Dictionary
  • Hierarchical Queries
  • Pattern Matching

5. Single Row Functions

  • 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

6. Joining Data from Multiple Tables in Queries

  • ANSI SQL 92 and SQL99 Joins
  • The JOIN Condition / The Cartesian Product
  • Outer JOINs

7. Group Functions and the GROUP BY Clause

  • Group Functions
  • Distinct Operator in Group Functions
  • GROUP BY, WITH Clause, ROLLUP, CUBE
  • GROUPING SETS

8. Using Subqueries

  • 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

9. Data Manipulation Language (DML) Commands

  • INSERT, UPDATE, DELETE Command

10. Getting Data In And Out Of Oracle

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