Oracle 11g and 12c Foundations: SQL and SQL*Plus

Course:   OR11F
Duration:   5 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/OR11F.html
 
Course Summary

You will receive a hands-on introduction to SQL Basics and SQL*Plus including a basic knowledge of relational databases and how to access them through SQL and SQL*Plus. Topics necessary to the development and basic administration of an Oracle 11g or Oracle 12c database are also covered.

This course can also be presented with content specific to Oracle 10g.

Topics Covered In This Course

In the Beginning?Database Management System Overview

  • What is a Database?
  • Types of DBMSs

Introduction to Entity Relationship Modeling

  • What is a Data Model?
  • Why Use an Entity Relationship Model?
  • Input and Components of an Entity Relationship Model
  • Reading the Entity Relationship Model
  • Guidelines for a Model Review

Fundamentals of Relational Database Management Systems

  • What is an RDBMS?
  • History of SQL
  • Physical Data Storage
  • Relationships between Tables
  • Domains - Data Integrity Constraint

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

The WHERE and ORDER BY Clause

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

Single Row Functions

  • Single Row vs. Group Functions
  • Date and Numeric Format Models
  • Data Conversion
  • Control Statements
  • Regular Expressions

Joining Data from Multiple Tables in Queries

  • The JOIN Condition / The Cartesian Product
  • Outer JOINs
  • SET Operators
  • ANSI SQL99 Joins

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 Clause

Analytic Functions (Optional)

  • Single Row Analytic Functions
  • Aggregating Analytic Functions
  • PIVOT / UNPIVOT

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

Security

  • Users, Privileges, Roles, Changing Passwords

Getting Data In And Out Of Oracle

  • SQL*Loader Basics
  • Importing Oracle Data Into Excel
  • Direct Database Queries Using ODBC
What You Can Expect

Upon successfully completing this course, participants will be able to:

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.

Training Style

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

Related Courses
Code Course Title Duration Level
ORDSCD
ORACLE 10g/11g DISCOVERER DESKTOP For End Users
2 Days
I
Details
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
OR11A1
Oracle Database 11g Administration I
5 Days
I
Details
ORDSCP
ORACLE 10g / 11g BI DISCOVERER PLUS for End Users
2 Days
I
Details
OR11D
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
I
Details
OR11SQL2
Transitioning to Oracle 11g/12c SQL
3 Days
II
Details
OR11D2
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
II
Details
OR12NEW
Oracle12c New Features For Administrators
5 Days
II
Details
OR12MT
Oracle12c Release 1 Multitenant Architecture
1 Day
III
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.