12c Database and Cloud Features

Course: 9 Minutes

• Course Introduction
• Features of Oracle Database 12c
• Oracle Cloud and Cloud Control 12c

Relational Database Management Concepts

Course: 24 Minutes

• Relational and Object Relational Database
• Relational Database Concepts
• Data Models
• Entity Relationship Model
• Relating Multiple Tables
• Relational Database Terminology

Implementation of RDBMS and ORDMS

Course: 9 Minutes

• Using SQL to Query a Database
• Human Resources (HR) Schema

Using SQL Developer

Course: 30 Minutes

• Introduction to SQL Developer
• Creating a Database Connection
• Browsing Database Objects and Structures
• Creating a New Schema Object
• Using the SQL Worksheet
• Working with SQL Statements and Scripts
• Executing Saved Script Files

Additional Features of SQL Developer

Course: 32 Minutes

• Using Snippets
• Using the Recycle Bin
• Debugging Procedures and Functions
• Database Reporting
• Creating a User-defined Report
• Linking to External Tools and Search Engines
• Setting Preferences within SQL Developer
• Data Modeler in SQL Developer

Practice: Working with the HR Schema

Course: 5 Minutes

• Exercise: Browse the HR Schema Using SQL Developer

Using SQL*Plus

Course: 27 Minutes

• Course Introduction
• 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

Writing SQL Statements and Arithmetic Expressions

Course: 22 Minutes

• The Basic SELECT Statement
• Writing SQL Statements
• Arithmetic Expressions
• Operator Precedence in Arithmetic Expressions
• Null Values

Column Aliases, Strings, and Duplicate Rows

Course: 18 Minutes

• Column Aliases
• Concatenation Operator
• Literal Character Strings
• Alternative Quote Operator
• Duplicate Rows
• Displaying Table Structure

Practice: Using SQL to Retrieve Data

Course: 6 Minutes

• Exercise: Retrieving Data Using SQL Statements

Restricting and Sorting Data

Course: 49 Minutes

• Course Introduction
• 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

Course: 20 Minutes

• Single-ampersand Substitution Variable
• Substituting Column Names, Expressions, and Text
• Double-ampersand Substitution Variable
• DEFINE and VERIFY Commands

Practice: Substitution Variables with WHERE

Course: 5 Minutes

• Exercise: Use WHERE and Substitution Variables

Single-row Functions

Course: 35 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

Using Date Functions

Course: 23 Minutes

• Working with Dates and the RR Date Forma
• SYSDATE, CURRENT_DATE, and CURRENT_TIMESTAMP
• Arithmetic Operators in Dates
• Date-manipulation Functions and using Date Functions
• Numeric ROUND and TRUNC Functions with Dates

Practice: Character, Number, and Date

Course: 6 Minutes

• Exercise: Use Character, Number, and Date Functions

Conversion and Conditional Functions

Course: 55 Minutes

• Course Introduction
• Implicit and Explicit Data Type Conversion
• Using the TO_CHAR Function with Dates
• Date Format Model
• Using the TO_CHAR Function with Numbers
• TO_NUMBER and TO_DATE Functions
• Using 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

Aggregated Data and Group Functions

Course: 21 Minutes

• Group Functions
• AVG and SUM Functions
• MIN and MAX Functions
• COUNT and COUNT DISTINCT Function
• Group Functions and null Values
• Creating Groups of Data

Using GROUP BY

Course: 25 Minutes

• GROUP BY Clause
• GROUP BY on Multiple Columns
• ILLEGAL GROUP Function
• Restricting Group Results
• Nesting Group Functions

Practice: Functions and Conditional Expressions

Course: 4 Minutes

• Exercise: Use Functions and Conditional Expressions

Practice: Working with Group Functions

Course: 4 Minutes

• Exercise: Use the Group By and Having Functions

Multiple Tables and Joins

Course: 43 Minutes

• Course Introduction
• Obtaining Data from Multiple Tables
• Creating and Retrieving Records with Natural Joins
• Creating Joins with the USING Clause
• Ambiguous Column Names and Table Aliases
• Creating Joins with the ON Clause
• Creating Three-way Joins
• Applying Additional Conditions to a Join
• Creating Self-joins using the ON Clause
• Retrieving Records from Non-equijoins

Multiple Tables and Outer Joins

Course: 22 Minutes

• Retrieving Records with No Direct Match
• INNER and OUTER Joins
• Left OUTER Join
• Right OUTER Join
• Full OUTER Join
• Generating Cartesian Product
• CROSS Joins

Using Subqueries to Solve a Problem

Course: 6 Minutes

• Using Subquery to Solve a Problem
• Guidelines for Subqueries

Single-row Subqueries

Course: 16 Minutes

• Executing Single-row Subqueries
• Using Group Functions in Subqueries
• Using the HAVING Clause with Subqueries
• Troubleshooting Inner Query

Multiple-row Subqueries

Course: 16 Minutes

• Multiple-row Subqueries
• ANY and ALL Operators in Multiple-row Subqueries
• Multiple-column Subqueries
• Null Values in Subquery

Practice: Multiple Tables and Joins

Course: 5 Minutes

• Exercise: Joining Multiple Tables

Practice: Using Subqueries to Solve a Problem

Course: 5 Minutes

• Exercise: Use subqueries

Managing Tables Using DML Statements

Course: 9 Minutes

• Course Introduction
• Data Manipulation Language
• Adding New Rows to Tables using the INSERT Statement

Inserting Rows

Course: 23 Minutes

• Inserting Rows with Null Values
• Inserting Rows with Special Values
• Inserting Specific Date and Time Values
• Creating a Script
• Copying Rows from Another Table

Updating Rows

Course: 18 Minutes

• Updating Rows in a Table
• Updating Two Columns with a Subquery
• Updating Rows Based on Another Table

Deleting Rows

Course: 13 Minutes

• Deleting Rows from a Table
• Deleting Rows Based on Another Table
• TRUNCATE Statement

Practice: Working with Data in Tables

Course: 7 Minutes

• Exercise: Manipulate Data in a Table

Database Transactions

Course: 40 Minutes

• Database Transactions
• COMMIT and ROLLBACK Statements
• Explicit Transaction Control Statements
• Using ROLLBACK
• Implicit Transaction Processing
• Database State Before and After COMMIT
• Database State Before and After ROLLBACK
• Implementing Read Consistency
• FOR UPDATE Clause in a SELECT Statement

Practice: Working with Database Transactions

Course: 6 Minutes

• Exercise: Process a Database Transaction

Using the SET Operators

Course: 25 Minutes

• Course Introduction
• 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

Practice: Using SET Operators

Course: 5 Minutes

• Exercise: Write Reports Using the SET Operators

Introduction to Data Definition Language

Course: 18 Minutes

• Database Objects and their Naming Rules
• Creating Tables
• Data Types
• Specifying DEFAULT Option for Columns in a Table

Working with Constraints in DDL

Course: 30 Minutes

• Constraints and Constraint Guidelines
• Defining Constraints
• NOT NULL and PRIMARY KEY Constraints
• UNIQUE Constraint
• FOREIGN KEY Constraint
• CHECK Constraint
• Violating Constraints

Additional Commands in DDL

Course: 25 Minutes

• Creating a Table Using a Subquery
• ALTER TABLE Statement
• Working with Columns
• SET UNUSED Option
• Read-only Tables
• Dropping a Table

Practice: Using DDL Statements

Course: 5 Minutes

• Exercise: Create and Work with Tables Using DDL