Oracle 12c Foundations: SQL & SQL*PLUS Course Details:

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.

    No classes are currenty scheduled for this course.

    Call (919) 283-1674 to get a class scheduled online or in your area!

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 and Mandatory Clauses

3. Using SQL Developer

  • Understanding SQL Developer
  • SQL Developer Setup
  • Advanced Functionality

4. The WHERE and ORDER BY Clauses

  • WHERE, ORDER BY Clauses
  • 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. SQL Set Queries

  • Multiple Select Statement Rules
  • Union vs. Union All of Select Statements
  • INTERSECTion of Two Select Statements
  • MINUS one select from another

10. Multi-Table Inserts

  • Unconditional All Insert
  • Conditional Insert with ELSE
  • Conditional ALL
  • Conditional FIRST
  • Pivoting Insert

11. Analytical Functions

  • Single Row Analytic Functions
  • Aggregating Analytic Functions
  • PIVOT / UNPIVOT
  • The MODEL Clause

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
  • Using SET Variables in SQL*Plus, SQL to Generate SQL

13. Data Manipulation Language (DML) Commands

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

14. Data Definition Language (DDL) Commands

  • DDL Commands, Object Naming, Dropping Objects
  • TRUNCATE, COMMENT, RENAME Commands
  • Creating Tables / Integrity Constraints
  • CREATE TABLE, ALTER TABLE Commands
  • 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

*Please Note: Course Outline is subject to change without notice. Exact course outline will be provided at time of registration.
  • 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

Lab 1: Data Model Review

Lab 2: SQL Basics

Lab 3: SQL Developer

Lab 4: Basic Queries

Lab 5: Data Definition

Lab 6: Data Manipulation

Lab 7: Filtering and Sorting Data

Lab 8: Single Row Functions

Lab 9: ANSI 92 Joins

Lab 10: ANSI 99 Joins

Lab 11: Group Clauses and Functions

Lab 12: Subqueries

Lab 13: SQL SET Queries

Lab 14: Multi-Table Inserts

Lab 15: Analytical Functions

Lab 16: SQL Plus Reporting

Lab 17: Advanced DDL

Lab 18: Advanced DML

Lab 19: Database Security

Lab 20: Getting Data In And Out

Application designers, developers, and database administrators

Ready to Jumpstart Your IT Career?

CONTACT US NOW!