Oracle18c SQL

Course:  OR18SQL
Duration:  5 Days
Level:  I
Course Summary

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 Oracle11g and higher. A full presentation of the basics of relational databases and their use are also covered.

« Hide The Details
Topics Covered In This Course

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
  • The CLEAR 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

    LAB 8 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

What You Can Expect

To cover a complete, hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus. presentation of the basics of relational databases and their use. This includes anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality.

Who Should Take This Course

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.

Recommended Prerequisites

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

Training Style

Lecture and Hands on Labs

« 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
OR18DBA1
Oracle 18c DBA I
5 Days
I
Details
OR11D1
Oracle 11g/12c Developer: Introduction to PL/SQL
2 Days
I
Details
OR11E2
Oracle 11g/12c Express Introduction to SQL, SQL*Plus, and PL/SQL
3 Days
I
Details
OR11D
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
I
Details
ORAPEX
Oracle APEX Version 5.0.4
5 Days
II
Details
OR11SQL2
Transitioning to Oracle 11g/12c SQL
3 Days
II
Details
OR11D2
Oracle 11g/12c Developer: Intermediate PL/SQL
3 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
2/25/2019
Birmingham, AL
3/11/2019
Live on the Web
3/11/2019
Wilmington, DE
3/18/2019
Madison, WI
3/18/2019
New Orleans, LA
3/25/2019
Des Moines, IA
4/1/2019
Basking Ridge, NJ
5/27/2019
New Orleans, LA
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