Oracle 12c Foundations: SQL & SQL*PLUS

Overview
Course Outline

Overview

Course Description

Thank you for reading this post, don't forget to subscribe!

You will receive a hands-on introduction to SQL Basics and SQL*Plus and learn about relational databases and how to access them through SQL and SQL*Plus.

Audience Profile

Application designers, developers, and database administrators

Prerequisites

An understanding of relational database design concepts

Learning Objectives

Relational database terminology.
Structured Query Language (SQL) basics.
Format reports using SQL*Plus commands.
Extract and organize information from the database.
Insert, update, and delete information in database tables.
Create and drop tables, views, synonyms, and indexes

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: Data Model Review

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 and Mandatory Clauses
Lab: SQL Basics

Module 3: Using SQL Developer
Lessons
• Understanding SQL Developer
• SQL Developer Setup
• Advanced Functionality
Lab: SQL Developer

Module 4: The WHERE and ORDER BY Clauses
Lessons
• WHERE, ORDER BY Clauses
• Using the Data Dictionary
• Hierarchical Queries
• Pattern Matching
Lab: Basic Queries

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: Data Definition
Lab: Data Manipulation
Lab: Filtering and Sorting Data
Lab: Single Row 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: ANSI 92 Joins
Lab: ANSI99 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

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: Subqueries

Module 9: SQL Set Queries
Lessons
• Multiple Select Statement Rules
• Union vs. Union All of Select Statements
• INTERSECTion of Two Select Statements
• MINUS one select from another
Lab: SQL SET Queries

Module 10: Multi-Table Inserts
Lessons
• Unconditional All Insert
• Conditional Insert with ELSE
• Conditional ALL
• Conditional FIRST
• Pivoting Insert
Lab: Multi-Tables Inserts

Module 11: Analytical Functions
Lessons
• Single Row Analytic Functions
• Aggregating Analytic Functions
• PIVOT / UNPIVOT
• The MODEL Clause
Lab: Analytical Functions

Module 12: SQL*Plus Reporting
Lessons
• SQL*Plus Report Writing Commands
• COLUMN Command/Titles, System Variables
• Master-Detail Report with TITLE and COLUMN Commands
• Control Breaks, Computing Aggregate Amounts
• Using SET Variables in SQL*Plus, SQL to Generate SQL
Lab: SQL Plus Reporting

Module 13: Data Manipulation Language (DML) Commands
Lessons
• INSERT, UPDATE, DELETE, MERGE Command
• Use of Subqueries
• Transaction Control Commands/Read Consistency/Locking
• The MERGE Command
• Flashback Queries

Module 14: Data Definition Language (DDL) Commands
Lessons
• DDL Commands, Object Naming, Dropping Objects
• TRUNCATE, COMMENT, RENAME Command
• Creating Tables/Integrity Constraints
• CREATE TABLE, ALTER TABLE Command
• Datatypes, Types of Declarative Constraints
• Default Values
• Creating, Deferrable, Dropping, Disabling/Enabling Constraints/Displaying Constraint Information
• Handling Exceptions
• Views, Synonyms, Sequences, Indexes, Virtual Columns
• Temporary Tables, External Tables
Lab: Advanced DDL
Lab: Advanced DML
Lab: Database Security
Lab: Getting Data In and Out