Oracle Database 12c R2 SQL E-Learning

Oracle Database 12c R2 SQL: Using Structured Query Language (SQL)

Course: 50 Minutes

Focus Areas
Features
Database Management Systems
Relational Databases
Data Models
Querying Databases Using SQL
Oracle Live SQL
SQL Developer
Database Cloud Service
Exercise: Work with the SQL Language

Oracle Database 12c R2 SQL: Data Manipulation Language and Transaction Control Language

Course: 1 Hour

Data Manipulation Language
INSERT Statement
Inserting Rows with NULL Values
Inserting Special Values
UPDATE Statement
TRUNCATE Statement
Transactions
SAVEPOINT
Statement-level Rollback
Read Consistency
Manual Data Locking
Exercise: Manipulate Data in a Table

Oracle Database 12c R2 SQL: Using Basic SELECT statements

Course: 1 Hour

The Basic SELECT Statement
Writing SQL Statements
Arithmetic Expressions
Operator Precedence in Arithmetic Expressions
NULL Values
Column Aliases
Concatenation Operator
Literal Character Strings
WHERE Clause
Duplicate Rows
Displaying Table Structure
Exercise: Retrieving Data Using SQL Statements

Oracle Database 12c R2 SQL: Conversion Functions and Conditional Expressions

Course: 1 Hour, 8 Minutes

Implicit and Explicit Data Type Conversion
Use the TO_CHAR Function with Dates
Date Format Model
Use the TO_CHAR Function with Numbers
TO_NUMBER and TO_DATE Functions
Use TO_CHAR and TO_DATE with RR Date Format
General Functions
NVL and NVL2 Functions
NULLIF and COALESCE Functions
Conditional Expressions
CASE Expression
Using the DECODE Function
Exercise: Apply Conversions

Oracle Database 12c R2 SQL: Displaying Data from Multiple Tables

Course: 1 Hour, 7 Minutes

Obtaining Data from Multiple Tables
Types of Joins
Creating Natural Joins
USING Clause
ON Clause
Self-Joins
Nonequijoins
OUTER Joins
Cartesian Product
Cross Joins
Exercise: Access Data from Tables

Oracle Database 12c R2 SQL: Using the Set Operators

Course: 40 Minutes

Types of Set Operators
Oracle Server and Set Operators
UNION and UNION ALL Operators
INTERSECT Operator
MINUS Operator
Matching SELECT Statements
Using the ORDER BY Clause in Set Operations
Exercise: Combine Multiple Queries

Oracle Database 12c R2 SQL: Managing Objects with Data Dictionary Views

Course: 35 Minutes

Introduction to Data Dictionary Views
USER_OBJECTS
ALL_OBJECTS
Querying Dictionary Views
Adding Comments to a Table
Exercise: Work with Dictionary Views

Oracle Database 12c R2 SQL: Managing Schema Objects

Course: 1 Hour, 12 Minutes

Adding a Constraint
Dropping a Constraint
Deleting Child Rows
Cascading Constraints
Disabling and Enabling Constraints
Indexes
Creating Indexes
Index Information
Temporary Tables
External Tables
CREATE TABLE Statement
Querying External Tables
Exercise: Work with Database Objects

Oracle Database 12c R2 SQL: Data Definition Language (DDL)

Course: 1 Hour, 3 Minutes

Database Objects and their Naming Rules
Creating Tables
Data Types
Specify the DEFAULT Option for Columns in a Table
Overview of Constraints
Working with Constraints
Creating Tables Using Subqueries
ALTER TABLE Statement
Marking Columns as Unused
Removing Unused Columns
DROP TABLE Statement
Exercise: Create and Work with Tables Using DDL

Oracle Database 12c R2 SQL: SQL*Plus

Course: 46 Minutes

Interacting with SQL*Plus
SQL*Plus Editing Commands
LIST, APPEND, and CHANGE Commands
SQL*Plus File Commands
SAVE and START Commands
SERVEROUTPUT and the SPOOL Commands
AUTOTRACE Commands
Exercise: Using SQL*Plus

Oracle Database 12c R2 SQL: Restricting and Sorting Data

Course: 1 Hour

Limiting Selected Rows and Using the WHERE Clause
Character Strings and Dates
Comparison Operators
BETWEEN and IN Operators
LIKE Operator and Combining Wildcard Characters
NULL and Logical Operator Conditions
AND, OR, and NOT Operators
Rules of Precedence
ORDER BY Clause
SQL Row Limiting Clause
Substitution Variables
Exercise: Manipulating Data

Oracle Database 12c R2 SQL: Using Single-Row Functions to Customize Output

Course: 1 Hour, 11 Minutes

Types of SQL Functions and Single-Row Functions
Character Functions
Case Conversion Functions
Character Manipulation Functions
Nesting Functions
Numeric ROUND and TRUNC Functions
MOD Function
Working with Dates and the RR Date Format
SYSDATE, CURRENT_DATE, and CURRENT_TIMESTAMP
Arithmetic Operators in Dates
Date Manipulation Functions
Using Numeric ROUND and TRUNC Functions with Dates
Exercise: Use Character, Number, and Date Functions

Oracle Database 12c R2 SQL: Reporting Aggregated Data Using the Group Functions

Course: 1 Hour, 7 Minutes

Group Functions
AVG and SUM Functions
MIN and MAX Functions
COUNT and COUNT DISTINCT Functions
Group Functions and NULL Values
Creating Groups of Data
GROUP BY Clause
GROUP BY on Multiple Columns
Illegal Queries
Restricting Group Results Using the HAVING Clause
Nesting Group Functions
Exercise: Use GROUP BY and HAVING

Oracle Database 12c R2 SQL: Using Subqueries to Solve Queries

Course: 1 Hour, 23 Minutes

Using Subqueries to Solve a Problem
Single-Row Subqueries
Multiple-Row Subqueries
Multiple-Column Subqueries
EXISTS and NOT EXISTS Operators
WITH Clause
Correlated UPDATE
Correlated DELETE
WITH CHECK OPTION
NULL Values in a Subquery
Exercise: Manipulate Data Using Subqueries

Oracle Database 12c R2 SQL: Creating Other Schema Objects

Course: 50 Minutes

Views
Simple and Complex Views
Creating a View
Modifying a View
Viewing Information
Visible and Invisible Columns
Sequences
Pseudocolumns
Using a Sequence
Modifying a Sequence
Sequence Information
Exercise: Create Other Schema Objects

Oracle Database 12c R2 SQL: Controlling User Access

Course: 38 Minutes

Controlling User Access
Privileges
Granting Privileges
Roles2
Granting Privileges to a Role
ALTER USER Statement
Object Privileges
WITH GRANT OPTION clause
Confirming Granted Privileges
REVOKE Statement
Exercise: Implement User Access

Oracle Database 12c R2 SQL: Manipulating Data Using Advanced Queries

Course: 1 Hour, 8 Minutes

Explicit Default
Multitable INSERT Statements
Unconditional INSERT
Conditional INSERT ALL
Conditional INSERT FIRST
Pivoting INSERT
MERGE Statement
FLASHBACK TABLE Statement
Tracking Changes in Data
VERSIONS BETWEEN Clause
Exercise: Perform Advanced Queries