Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Datawarehousing
DB2
Desktop Application Software
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle 18c SQL
Oracle Training Overview

This course provides a complete, hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for users of Oracle 11g and higher. A full presentation of the basics of relational databases and their use are also covered.

Oracle Training Audience

This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators.

Oracle Training Prerequisites

Basic computer skills are needed. A basic knowledge of databases is desired but not required.

Course duration

5 Days

Oracle Training Course outline

CHAPTER 1 – BASIC RDBMS PRINCIPLES

RELATIONAL DESIGN PRINCIPLES

ACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGE              

ENTITY RELATIONSHIP DIAGRAMS

DATA DOMAINS

NULL VALUES

INDEXES

VIEWS

DENORMALIZATION

DATA MODEL REVIEW

LAB 1:  BASIC RDBMS PRINCIPLES

LAB 1 SOLUTIONS:  BASIC RDBMS PRINCIPLES

CHAPTER 2 – THE SQL LANGUAGE AND TOOLS

USING SQL*PLUS

  • Why Use SQL*Plus When Other Tools Are Available?
  • Starting SQL*Plus
  • EZConnect
  • SQL Commands
  • PL/SQL Commands
  • SQL*Plus Commands

The COLUMN Command

  • The HEADING Clause
  • The FORMAT Clause
  • The NOPRINT Clause
  • The NULL Clause
  • TheCLEAR Clause

PREDEFINED DEFINE VARIABLES

LOGIN.SQL

WE HAVE HISTORY!

PERFORMANCE SETTINGS

CSV OPTION FOR MARKUP

FEEDBACK ONLY

SUPPORT  FOR LONG IDENTIFIERS

COPY AND PASTE IN SQL*PLUS

ENTERING SQL COMMANDS

ENTERING PL/SQL COMMANDS               

ENTERING SQL*PLUS COMMANDS

DEFAULT OUTPUT FROM SQL*PLUS

ENTERING QUERIES

WHAT ABOUT PL/SQL? 

INTRODUCING SQLCL:  THE DEATH OF SQL*PLUS?

  • Installation
  • Editing / Autocomplete
  • SQLFORMAT ANSICONSOLE
  • NEW COMMANDS
    • ALIAS
      • Search
    • APEX
    • BRIDGE
    • CD
    • CTAS
    • DDL
    • FIND
    • FORMAT
    • HISTORY
    • INFORMATION
    • LOAD
    • NET
    • OERR
    • REPEAT
    • REST
    • SODA
    • SSHTUNNEL
    • TNSPING
    • WHICH
  • MISCELLANEOUS

LAB 2: SQL LANGUAGE AND TOOLS

LAB 2 SOLUTIONS:  SQL LANGUAGE AND TOOLS               

CHAPTER 3 – USING SQL DEVELOPER

CHOOSING A SQL DEVELOPER VERSION

CONFIGURING CONNECTIONS 

  • Creating A Basic Connection
  • Creating A TNS Connection
  • Connecting

CONFIGURING PREFERENCES

USING SQL DEVELOPER

  • The Columns Tab
  • The Data Tab
  • The Constraints Tab
  • The Grants Tab
  • The Statistics Tab
  • Other Tabs
  • Queries In SQL Developer
  • Saving Typing
  • Query Builder
  • Modifying Your Query With The Drawer Pullout
  • Accessing Objects Owned By Other Users
  • The Actions Pulldown Menu

DIFFERENCES BETWEEN SQL DEVELOPER AND SQL*PLUS

  • Reporting Commands Missing In SQL Developer
  • General Commands Missing In SQL Developer

SPOOLING IN SQL DEVELOPER

DATA DICTIONARY REPORTS

USER DEFINED REPORTS

USING SCRIPTS IN SQL DEVELOPER

LAB 3:  USING SQL DEVELOPER

LAB 3 SOLUTIONS:  USING SQL DEVELOPER

CHAPTER 4 – SQL QUERY BASICS

UNDERSTANDING THE DATA DICTIONARY

  • Exporting Key Data Dictionary Information
  • An Alternative Approach To A Quick ERD

THE DICTIONARY VIEW 

COMPONENTS OF A SELECT STATEMENT

  • The SELECT Clause
  • The FROM Clause
  • The WHERE Clause
  • The GROUP BY Clause
  • The HAVING Clause
  • The ORDER BY Clause
  • The START WITH And CONNECT BY Clauses
  • The FOR UPDATE Clause
  • Set Operators

COLUMN ALIASES

FULLY QUALIFYING TABLES AND COLUMNS

TABLE ALIASES

A NEAT TRICK

USING DISTINCT AND ALL IN SELECT STATEMENTS

LAB 4:  SQL QUERY BASICS

LAB 4 SOLUTIONS:  SQL QUERY BASICS

CHAPTER 5– WHERE AND ORDER BY

WHERE CLAUSE BASICS

COMPARISON OPERATORS

LITERALS AND CONSTANTS IN SQL

SIMPLE PATTERN MATCHING

LOGICAL OPERATORS

THE DUAL TABLE

ARITHMETIC OPERATORS

EXPRESSIONS IN SQL

CHARACTER OPERATORS

PSEUDO COLUMNS

ORDER BY CLAUSE BASICS

ORDERING NULLS

ACCENT AND CASE INSENSITIVE SORTS

DATA BOUND COLLATION

CASE-INSENSITIVE DATABASE

SAMPLING DATA

WHERE AND ORDER BY IN SQL DEVELOPER

ALL, ANY, SOME

LAB 5:  WHERE AND ORDER BY

LAB 5 SOLUTIONS:  WHERE AND ORDER BY

CHAPTER 6 – FUNCTIONS

THE BASICS OF ORACLE FUNCTIONS

NUMBER FUNCTIONS

CHARACTER FUNCTIONS

DATE FUNCTIONS

CONVERSION FUNCTIONS

OTHER FUNCTIONS

LARGE OBJECT FUNCTIONS

ERROR FUNCTIONS

THE RR FORMAT MODEL

LEVERAGING YOUR KNOWLEDGE

LAB 6:  FUNCTIONS

LAB 6 SOLUTIONS: FUNCTIONS

CHAPTER 7 – ANSI 92 JOINS

BASICS OF ANSI 92 JOINS

USING QUERY BUILDER WITH MULTIPLE TABLES

TABLE ALIASES

OUTER JOINS

  • Outer Joins In Query Builder

SET OPERATORS

SELF-REFERENTIAL JOINS

NON-EQUIJOINS

LAB 7: ANSI 92 JOINS

LAB 7:  SOLUTIONS:  ANSI 92 JOINS

CHAPTER 8 – ANSI 99 JOINS

CHANGES WITH ANSI99

CROSS JOIN

NATURAL JOIN

JOIN USING

JOIN ON

LEFT / RIGHT OUTER JOIN

FULL OUTER JOIN

LAB 8:  ANSI 99 JOINS

LAB8 SOLUTIONS:  ANSI 99 JOINS

CHAPTER 9 – GROUP BY AND HAVING

INTRODUCTION TO GROUP FUNCTIONS              

  • Limiting Rows
  • Including NULL
  • Using DISTINCT With Group Functions

GROUP FUNCTION REQUIREMENTS

THE HAVING CLAUSE

OTHER GROUP FUNCTION RULES

USING QUERY BUILDER WITH GROUP CLAUSES

ROLLUP AND CUBE

THE GROUPING FUNCTION

GROUPING SETS

LAB 9:  GROUP BY AND HAVING

LAB 9 SOLUTIONS:  GROUP BY AND HAVING

CHAPTER 10 – SUBQUERIES               

WHY USE SUBQUERIES?               

WHERE CLAUSE SUBQUERIES

FROM CLAUSE SUBQUERIES

HAVING CLAUSE SUBQUERIES  

CORRELATED SUBQUERIES

SCALAR SUBQUERIES

DML AND SUBQUERIES

EXISTS SUBQUERIES

HIERARCHICAL QUERIES               

TOP N AND BOTTOM N QUERIES

CREATING SUBQUERIES USING QUERY BUILDER

LAB 10:  SUBQUERIES

LAB 10 SOLUTIONS:  SUBQUERIES

CHAPTER 11– REGULAR EXPRESSIONS

AVAILABLE REGULAR EXPRESSION FUNCTIONS

REGULAR EXPRESSION OPERATORS

CHARACTER CLASSES

PATTERN MATCHING OPTIONS

REGEX_LIKE

REGEXP_SUBSTR

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_COUNT

LAB 11:  REGULAR EXPRESSIONS              

LAB 11 SOLUTIONS:  REGULAR EXPRESSIONS

CHAPTER 12 – ANALYTICS

THE WITH CLAUSE

REPORTING AGGREGATE FUNCTIONS

ANALYTICAL FUNCTIONS

USER-DEFINED BUCKET HISTOGRAMS

THE MODEL CLAUSE

PIVOT AND UNPIVOT

TEMPORAL VALIDITY

LAB 12:  ANALYTICS

LAB 12 SOLUTIONS:  ANALYTICS               

CHAPTER 13 – ANALYTICS II

RANKING FUNCTIONS

RANK

DENSE_RANK

CUME_DIST

PERCENT_RANK              

ROW_NUMBER               

WINDOWING AGGREGATE FUNCTIONS

RATIO_TO_REPORT

LAG / LEAD

LINEAR REGRESSION FUNCTIONS

INVERSE PERCENTILE FUNCTIONS

HYPOTHETICAL RANKING FUNCTIONS

PATTERN MATCHING

CHAPTER 14 – BASIC REPORTING

BASIC REPORTING

  • The COLUMN Command
  • Setting Column Width

PRINT | NOPRINT

TTITLE | BTITLE

REPHEADER / REPFOOTER

NEW_VALUE / OLD_VALUE

THE COMPUTE COMMAND

COMMENTS IN SCRIPT FILES

SUBSTITUTION VARIABLES

  • Named Substitution Variables
  • Numbered Substitution Variables
  • Dealing With Multiple References
  • Using The DEFINE Command
  • The ACCEPT And PROMPT Commands

RUNNING SCRIPTS UNATTENDED

LAB 14:  BASIC REPORTING         

LAB 14 SOLUTIONS:  BASIC REPORTING

CHAPTER 15 – SECURITY

BASIC SECURITY               

  • SYSTEM Privileges
  • OBJECT Privileges

SCHEMA ONLY ACCOUNTS

THE DATA DICTIONARY AND SECURITY

USING ROLES FOR PRIVILEGE MANAGEMENT

USING PROFILES

  • Kernel Limits
  • Password Limits
  • Creating And Using Profiles

LAB 15:  SECURITY

LAB 15 SOLUTIONS:  SECURITY

CHAPTER 16– DATA IMPORT AND EXPORT

USING SQL*LOADER WITH FIELD DELIMITED DATA

USING SQL*LOADER WITH COMMA DELIMITED DATA

DATA LOADING USING SQL DEVELOPER

EXPORTING ORACLE DATA INTO EXCEL

DOING AN ODBC QUERY

EXPORTING DATA SETS

DATA VALIDATION

A WORD ABOUT DATA PUMP

LAB 16:  DATA IMPORT AND EXPORT

LAB 16 SOLUTIONS:  DATA IMPORT AND EXPORT

CHAPTER 17 – DATA MANIPULATION

THE DATA MANIPULATION LANGUAGE

  • The INSERT Command
  • The UPDATE Command
  • The DELETE Command
  • Using The DEFAULT Keyword With Updates And Inserts

USING SQL DEVELOPER FOR DML

THE TRANSACTION CONTROL LANGUAGE (TCL)

IMPLICIT TCL

LAB 17:  DATA MANIPULATION

LAB 17 SOLUTIONS:  DATA MANIPULATION

CHAPTER 18– ADVANCED DATA MANIPULATION

THE MERGE COMMAND              

MULTIPLE COLUMN SUBQUERY UPDATES AND DELETES

DML AGAINST VIEWS

TRANSACTIONS AND READ CONSISTENCY

DML LOCKS

FLASHBACK TECHNOLOGIES

INSERTING LARGE OBJECTS

CHANGED DATA TRACKING

  • Flashback Versions Query
  • Log Miner
  • Change Data Capture
  • Flashback Data Archive

LAB 18:  ADVANCED DATA MANIPULATION

LAB 18 SOLUTIONS:  ADVANCED DATA MANIPULATION

CHAPTER 19– INTRODUCTION TO DATA DEFINITION

INTRODUCTION TO DDL COMMANDS

KEY OBJECTS

OBJECT NAMING RULES               

THE DATA DICTIONARY

AVAILABLE DATATYPES

  • Using Extended Datatypes

THE CREATE TABLE STATEMENT

NAMING CONSTRAINTS              

INTEGRITY CONSTRAINTS

  • Primary Keys
  • Foreign Keys
  • NOT NULL Constraints
  • UNIQUE Constraints
  • CHECK Constraints
  • DEFAULT Values

IDENTITY Columns

CONSTRAINTS AND CREATE TABLE… AS SELECT

CONSTRAINT LIMITATIONS

JSON SUPPORT

CREATING TABLES IN SQL DEVELOPER

OTHER DDL ACTIONS IN SQL DEVELOPER

THE ALTER TABLE COMMAND

DROPPING OBJECTS

RENAMING OBJECTS

THE TRUNCATE COMMAND

THE COMMENT COMMAND

CREATING SIMPLE VIEWS

LAB 19:  INTRODUCTION TO DATA DEFINITION

LAB 19 SOLUTIONS:  INTRODUCTION TO DATA DEFINITION

CHAPTER 20 – ADVANCED DATA DEFINITION

DDL AND THE DATA DICTIONARY

DISABLING CONSTRAINTS

ENABLING CONSTRAINTS

HANDLING CONSTRAINT EXCEPTIONS

USING DEFERRABLE CONSTRAINTS

SEQUENCES

  • Scalable Sequences

EXTERNAL TABLES FOR DATA STORAGE

  • Why Are External Tables Useful
  • Privileges Needed
  • Syntax For Creating External Tables
  • Inline External Tables

EXTERNAL TABLES AND THE ORACLE_DATAPUMP DRIVER

INDEXES

  • Guidelines
  • Index Creation Syntax
  • Rebuilding Indexes
  • Function Based Indexes

COMMENTS

SYNONYMS

  • CREATE SYNONYM Syntax

COMPLEX VIEWS             

  • Syntax For Views

VIRTUAL COLUMNS

COMPRESSED TABLES

INVISIBLE INDEXES

ONLINE DDL ENHANCEMENTS

INVISIBLE COLUMNS

CREATING MULTIPLE INDEXES ON COLUMNS

LAB 20:  ADVANCED DATA DEFINITION

LAB 20 SOLUTIONS: ADVANCED DATA DEFINITION


 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy