A company’s success hinges on responsible, accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers to setting financial goals. Data professionals like analysts, developers and architects are tasked with creating, optimizing, managing and analyzing data from databases with little room for error. When databases aren’t built or maintained correctly, it’s easy to mishandle or lose valuable data. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured, modern and secure databases. A full presentation of the basics of relational databases and their use are also covered.

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

Working in a hands on learning environment led by our expert practitioner, attendees will explore:
Basic RDBMS Principles
The SQL Language and Tools
Using SQL Developer
SQL Query Basics
WHERE and ORDER BY
Functions
ANSI 92 Joins
ANSI 99 Joins
Subqueries
Regular Expressions
Analytics

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


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

    Command history

    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?


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

    Query Builder

    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

    Data Dictionary report

    User Defined reports

    Using scripts in SQL Developer


4
  • SQL Query Basics

  • Understanding the data dictionary 

    Exporting Key Data 

    Dictionary Information 

    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 

    Using DISTINCT and ALL in SELECT statements


5
  • WHERE AND ORDER BY

  • WHERE clause basics

    Comparison operators

    Literals and Constants in SQL

    Simple pattern matching

    Logical operations

    The DUAL table

    Arithmetic operations

    Expressions in SQL

    Character operators

    Pseudo columns

    Order by clause basics

    Ordering Nulls

    Accent and case sensitive sorts

    Sampling data

    WHERE and ORDER BY in SQL Developer

    All, Any, Some


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 mode;

    Leveraging your knowledge


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


8
  • ANSI 99 JOINS

  • Changes with ANSI99

    CROSS Join

    NATURAL Join

    JOIN USING

    JOIN ON

    LEFT / RIGHT OUTER JOIN

    FULL OUTER JOIN


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


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


11
  • REGULAR EXPRESSIONS

  • Available Regular Expressions

    Regular Expression Operators

    Character Classes

    Pattern matching options

    REGEX_LIKE

    REGEXP_SUBSTR

    REGEXP_INSTR

    REGEXP_REPLACE

    REGEXP_COUNT


12
  • ANALYTICS

  • The WITH clause

    Reporting aggregate functions

    Analytical functions

    User-Defined bucket histograms

    The MODEL clause

    PIVOT and UNPIVOT

    Temporal validity


13
  • MORE ANALYTICS

  • 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


Audience

This is an introductory level SQL course, 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.

Language

English

Prerequisites

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

$1,795

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
13
Nov
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...