Transitioning to Oracle 11g/12c SQL

Course:  OR11SQL2
Duration:  3 Days
Level:  II
Course Summary

You will receive a hands-on introduction to advanced SQL concepts, especially as they relate to Oracle's implementation of the SQL Language. Topics necessary to the development and basic administration of an Oracle 11g or 12c database are also covered.

This course is specifically designed for those who know generic SQL but are not familiar with SQL*Plus and more importantly, Oracle's implementation of SQL.

A two-day version of this course can be presented by dropping just a few of the sub-topics that cover lesser-used capabilities of Oracle's SQL tools.  The course can also be presented with content specific to Oracle 10g.

« Hide The Details
Topics Covered In This Course

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
  • The SELECT Command & Mandatory Clauses

Using SQL Developer

  • Understanding the SQL Developer Environment and Commands

Single Row Functions

  • Functions, Date and Numeric Format Models/Data Conversion
  • Control Statements
  • Regular Expressions
  • SQL MODEL Statements

Joining Data from Multiple Tables in Queries

  • ANSI SQL99 Joins
  • ANSI SQL92 Joins
  • Outer Joins
  • SET Operators

Group Functions and the GROUP BY Clause

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

Using Subqueries and CASE Clauses

  • Single-Row, Multi-Row, Multi-Column Subqueries
  • Subqueries in a WHERE Clause
  • Subqueries in the FROM Clause ? In-Line Views
  • Subqueries In the SELECT Clause ? Scalar Subqueries
  • Correlated Subqueries
  • CASE Clauses

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

Data Manipulation Language (DML) Commands

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

Data Definition Language (DDL) Commands

  • 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
  • Temporary Tables, External Tables

Security

  • Users
  • Privileges
  • Roles
  • Authentication Methods

Getting Data In And Out Of Oracle

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

Course Labs:

  • Creating Tables And Constraints
  • Creating Views
  • Data Manipulation
  • Single Row Functions
  • Advanced Joins
  • Subqueries
  • Group Functions
  • SQL*Plus Reporting
  • Advanced DDL
  • Advanced DML
  • Privileges And Roles
What You Can Expect

On successfully completing this course, participants can expect to be able to:

  • Converse in relational database terminology
  • Understand Structured Query Language
  • Format reports using SQL*Plus commands
  • Extract and organize information from the database
  • Insert, update & delete information in database tables
  • Create and drop tables, views, synonyms and indexes
Who Should Take This Course

Application Designers, Developers, & Database Administrators, as well as those seeking the OCA certification.

Recommended Prerequisites

An understanding of relational database design concepts and general knowledge of SQL for Oracle or other DBMS.

Training Style

Short lecture sessions (60%) along with hands-on lab sessions (40%).

« Hide The Details
Related Courses
Code Course Title Duration Level
OR11E1
Oracle 11g/12c: Express Introduction to SQL and SQL*Plus
3 Days
I
Details
OR11E2
Oracle 11g/12c Express Introduction to SQL, SQL*Plus, and PL/SQL
3 Days
I
Details
OR11F
Oracle 11g and 12c Foundations: SQL and SQL*Plus
5 Days
I
Details
OR12NEW
Oracle12c New Features For Administrators
5 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com