998 - Oracle 12c Foundations: SQL & SQL*PLUS


998 - Oracle 12c Foundations: SQL & SQL*PLUS


Price
Length : 5 days
$2,695.00

You will receive a hands-on introduction to SQL Basics and SQL*Plus and learn about relational databases and how to access them through SQL and SQL*Plus.

What You'll Learn

  • Relational database terminology
  • Structured Query Language (SQL) basics
  • Format reports using SQL*Plus commands
  • Extract and organize information from the database
  • Insert, update, and delete information in database tables
  • Create and drop tables, views, synonyms, and indexes

Who Should Attend

Application designers, developers, and database administrators

Course outline

1. Introduction to the Oracle Database
Why Use an Entity Relationship Model?
Physical Data Storage
Relationships between Tables
Domains and Data Integrity Constraints
Data Model Review

2. 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 / GLOGIN.SQL
The SELECT Command and Mandatory Clauses

3. Using SQL Developer
Understanding SQL Developer
SQL Developer Setup
Advanced Functionality

4. The WHERE and ORDER BY Clauses
WHERE, ORDER BY Clauses
Using the Data Dictionary
Hierarchical Queries
Pattern Matching

5. Single Row Functions
Arithmetic Operators
Operator Precedence
Single Row vs. Group Functions
Date and Numeric Format Models
Data Conversion / Date Manipulation / Time Zones
Control Statements
IF THEN ELSE Logic
Regular Expressions

6. Joining Data from Multiple Tables in Queries
ANSI SQL 92 and SQL99 Joins
The JOIN Condition / The Cartesian Product
Outer JOINs

7. Group Functions and the GROUP BY Clause
Group Functions
Distinct Operator in Group Functions
GROUP BY, WITH Clause, ROLLUP, CUBE
Grouping Sets

8. Using Subqueries
Single-Row, Multi-Row, Multi-Column Subqueries
Subqueries in a WHERE / FROM / HAVING Clauses
Subqueries In the SELECT Clause - Scalar Subqueries
Correlated Subqueries / Hierarchical Queries
EXISTS / NOT EXISTS / WITH / Recursive WITH

9. SQL Set Queries
Multiple Select Statement Rules
Union vs. Union All of Select Statements
INTERSECTion of Two Select Statements
MINUS one select from another

10. Multi-Table Inserts
Unconditional All Insert
Conditional Insert with ELSE
Conditional ALL
Conditional FIRST
Pivoting Insert

11. Analytical Functions
Single Row Analytic Functions
Aggregating Analytic Functions
PIVOT / UNPIVOT
The MODEL Clause

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

13. Data Manipulation Language (DML) Commands
INSERT, UPDATE, DELETE, and MERGE Commands
Use of Subqueries
Transaction Control Commands / Read Consistency / Locking
The MERGE Command
Flashback Queries

14. Data Definition Language (DDL) Commands
DDL Commands, Object Naming, Dropping Objects
TRUNCATE, COMMENT, RENAME Commands
Creating Tables / Integrity Constraints
CREATE TABLE, ALTER TABLE Commands
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

Labs

Lab 1: Data Model Review

Lab 2: SQL Basics

Lab 3: SQL Developer

Lab 4: Basic Queries

Lab 5: Data Definition

Lab 6: Data Manipulation

Lab 7: Filtering and Sorting Data

Lab 8: Single Row Functions

Lab 9: ANSI 92 Joins

Lab 10: ANSI 99 Joins

Lab 11: Group Clauses and Functions

Lab 12: Subqueries

Lab 13: SQL SET Queries

Lab 14: Multi-Table Inserts

Lab 15: Analytical Functions

Lab 16: SQL Plus Reporting

Lab 17: Advanced DDL

Lab 18: Advanced DML

Lab 19: Database Security

Lab 20: Getting Data In And Out

Class Dates:

Virtual

May 20, 2019 - May 24, 2019

Register Now...

On Site Class Available

ExpertONE Learning Solutions Provider Microsoft Certified Partner Logo GSA Advantage