ORACLE DATABASE 12c R1: SQL FUNDAMENTALS – GTD18

Course Description

This course is a common starting point in the Oracle database curriculum for administrators, developers and business users.

^^

Duration

5 days

^^

Target Audience

The target audience for this training course is all Oracle professionals, both business and systems professionals, who need a “how to” guide to acquire the knowledge of SQL. Among the specific groups for whom this course will be helpful are:

• Application designers and developers

• Database administrators

• Business users and non-technical senior end users

^^

Course Prerequisites

A basic knowledge of computer programming concepts is required along with an understanding of the general Oracle architecture and relational database design and modelling.

If you have any questions or doubts as to whether you meet the pre-requisites for this course, or indeed are wondering which course best suits you, please consult with us to discuss your suitability for course attendance.

^^

Suggested Follow on Courses

Please contact us for further information.

^^

Course Content

INTRODUCTION TO SQL

RELATED DOCUMENTATION AND RESOURCES

THE ORACLE CERTIFIED ASSOCIATE

Preparing For The Test

Registering For The Test

Test Specifics

Sample Questions

Your Test Results

REDOING THE CLASS LABS

REVIEW OF LAB ENVIRONMENT

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 (login sql In Linux / UNIX)

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?

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

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

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

LAB 4: SQL QUERY BASICS

LAB 4 SOLUTIONS: SQL QUERY BASICS

CHAPTER 5 – 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 5: DATA MANIPULATION

LAB 5 SOLUTIONS: DATA MANIPULATION

CHAPTER 6 — 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

SAMPLING DATA

WHERE AND ORDER BY IN SQL DEVELOPER

ALL, ANY, SOME

LAB 6: WHERE AND ORDER BY

LAB 6 SOLUTIONS: WHERE AND ORDER BY

CHAPTER 7 – FUNCTIONS

CHAPTER OVERVIEW

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 7: FUNCTIONS

LAB 7 SOLUTIONS: FUNCTIONS

CHAPTER 8 – 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 8: ANSI 92 JOINS

LAB 8 SOLUTIONS: ANSI 92 JOINS

CHAPTER 9 – ANSI 99 JOINS

CHANGES WITH ANSI99

CROSS JOIN

NATURAL JOIN

JOIN USING

JOIN ON

LEFT / RIGHT OUTER JOIN

FULL OUTER JOIN

LAB 9: ANSI 99 JOINS

LAB 9 SOLUTIONS: ANSI 99 JOINS

CHAPTER 10 – GROUP BY AND HAVING

INTRODUCTION TO GROUP FUNCTIONS

Limiting Rows

Including NULLs

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 10: GROUP BY AND HAVING

LAB 10 SOLUTIONS: GROUP BY AND HAVING

CHAPTER 11 – 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 11: SUBQUERIES

LAB 11 SOLUTIONS: SUBQUERIES

CHAPTER 12 – BASIC REPORTING

BASIC REPORTING

The COLUMN Command

Setting Column Width

PRINT | NOPRINT

TTITLE | BTITLE

REPHEADER / REPFOOTER

NEW_VALUE / OLD_VALUE

Using Substitution Variables

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 12: BASIC REPORTING

LAB 12 SOLUTIONS: BASIC REPORTING

CHAPTER 13 – 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

A WORD ABOUT DATA PUMP

LAB 13: DATA IMPORT AND EXPORT

LAB 13 SOLUTIONS: DATA IMPORT AND EXPORT

CHAPTER 14 – SECURITY

BASIC SECURITY

SYSTEM Privileges

Object Privileges

THE DATA DICTIONARY AND SECURITY

USING ROLES FOR PRIVILEGE MANAGEMENT

USING PROFILES

Kernel Limits

Password Limits

Creating And Using Profiles

LAB 14: SECURITY

LAB 14 SOLUTIONS: SECURITY

CHAPTER 15 – 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 15: ADVANCED DATA MANIPULATION

LAB 15 SOLUTIONS: ADVANCED DATA MANIPULATION

CHAPTER 16 – 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

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 16: INTRODUCTION TO DATA DEFINITION

LAB 16 SOLUTIONS: INTRODUCTION TO DATA DEFINITION

CHAPTER 17 – ADVANCED DATA DEFINITION

DDL AND THE DATA DICTIONARY

DISABLING CONSTRAINTS

ENABLING CONSTRAINTS

HANDLING CONSTRAINT EXCEPTIONS

USING DEFERRABLE CONSTRAINTS

SEQUENCES

EXTERNAL TABLES FOR DATA STORAGE

Why Are External Tables Useful

Privileges Needed

Syntax For Creating 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 17: ADVANCED DATA DEFINITION

LAB 17 SOLUTIONS: ADVANCED DATA DEFINITION

CHAPTER 18 – 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 18: REGULAR EXPRESSIONS

LAB 18 SOLUTIONS: REGULAR EXPRESSIONS

CHAPTER 19 – ANALYTICS

CHAPTER OVERVIEW

THE WITH CLAUSE

REPORTING AGGREGATE FUNCTIONS

ANALYTICAL FUNCTIONS

USER-DEFINED BUCKET HISTOGRAMS

THE MODEL CLAUSE

PIVOT AND UNPIVOT

TEMPORAL VALIDITY

LAB 19: ANALYTICS

LAB 19 SOLUTIONS: ANALYTICS

CHAPTER 20 – 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

APPENDIX A: THE EL DATA MODEL

THE EL SAMPLE SCHEMA

The Taxes Table

The ZipCodes Table

The Jobs Table

The Departments Table

The Employees Table

Objects In The EL Model

Analysis

Join Conditions

THE ENTITY RELATIONSHIP MODEL

APPENDIX B: THE ELL DATA MODEL

THE ELL SAMPLE SCHEMA

The Nations Table

The Customers Table

The Items Table

The Promotions Table

The SalesData Table

Objects In The ELL Model

Analysis

Join Conditions

THE ENTITY RELATIONSHIP MODEL

THE CHANNEL FUNCTION

^^

See more Database Technologies courses