Oracle 11g/12c: Introduction to SQL

On Demand

 

Course Description

 

In this course students learn the basics of writing Oracle SQL statements. This course provides the SQL skills that allow developers, database administrators and end-users to write queries against single and multiple tables, manipulate data in tables and take advantage of Oracle supplied row functions.

 

In This Course You Will Learn How To:

  • Access database data with a basic SELECT statement
  • Access data from more than one table
  • Manipulate database data
  • Decide where best to use Oracle-supplied functions in place of user-written code

 

Prerequisites

 

You need to have equivalent experience or have taken the following course:

  • Introduction to Programming and Coding for Everyone with JavaScript

Outline

 

Module 00: Oracle 11g/12c: Introduction to SQL Online Training - Course Introduction

 

Module 01: Writing SQL SELECT Statements

  • Review the basic SQL SELECT statement syntax
  • Select all columns using a wildcard notation from a table
  • Valid SELECT statement expressions
  • Write a query containing the arithmetic operators
  • Use aliases to customize column headings
  • Create a character expression with the concatenation operator
  • Use the CASE Expression
  • Lab Exercise

 

Module 02: Writing SQL SELECT Statements Demo

 

Module 03: Restricting Data

  • Use the WHERE Clause
  • WHERE clause operators
  • =, <>, >, < operators
  • BETWEEN, IN, LIKE, IS NULL operators
  • Adding ANY or ALL to an operator
  • List the Logical Conditions AND, OR, NOT
  • Use Multiple Conditions in the WHERE clause
  • Describe the Rules of Precedence
  • Lab Exercise

 

Module 04: Restricting Data Demo

 

Module 05: Sorting Data

  • Discuss default order of result sets
  • Sort Rows with the ORDER BY Clause
  • ORDER BY valid expressions
  • DISTINCT and UNIQUE clauses
  • Lab Exercise

 

Module 06: Sorting Data Demo

 

Module 07: Displaying Data from Multiple Tables

  • Identify Types of Joins
  • ANSI standard join operators vs. non-standard join methods
  • Using the non-standard join methods
  • Inner join vs. Outer join
  • Generating a Cartesian Product
  • ANSI standard join operators
  • NATURAL JOIN, JOIN USING, JOIN ON, LEFT/RIGHT/FULL OUTER joins
  • Lab Exercise

 

Module 08: Displaying Data from Multiple Tables Demo

 

Module 09: Character Row Functions

  • Categories of row functions
  • Statement behavior of row functions
  • Useful / common character row functions
  • Nest Functions to perform multiple tasks in one statement
  • Lab Exercise

 

Module 10: Character Row Functions Demo

 

Module 11: Numeric Row Functions

  • Where numeric row functions can and cannot be used
  • Helpful numeric row functions in SQL statements
  • Functionalities of common numeric row functions
  • Explain Implicit and Explicit conversion
  • Lab Exercise

 

Module 12: Numeric Row Functions Demos

 

Module 13: Date/Time Row Functions

  • Where date/time row functions can and cannot be used
  • Helpful date/time row functions in SQL statements
  • Functionalities of common date/time row functions
  • Lab Exercise

 

Module 14: Date/Time Row Functions Demo

 

Module 15: Miscellaneous Row Functions

  • Using conversion functions
  • Using conversion functions to format date/time data
  • Environment and Data specific row functions
  • The Decode function versus the Case Option
  • Lab Exercise

 

Module 16: Miscellaneous Row Functions Demo

 

Module 17: Aggregating Data Using Group Functions

  • Categorize the Types of Group Functions
  • Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
  • Utilize the DISTINCT Keyword with the Group Functions
  • Describe how Nulls are handled with the Group Functions
  • Create Groups of Data with the GROUP BY Clause
  • Group Data by more than one column
  • Exclude Groups of Data with the HAVING Clause
  • Differences between the HAVING clause and the WHERE clause
  • Lab Exercise

 

Module 18: Aggregating Data Using Group Functions Demo

 

Module 19: Extensions to the GROUP BY Clause

  • Using the CUBE option
  • Using the ROLLUP option
  • CUBE vs. ROLLUP output
  • The GROUPING BY option
  • Lab Exercise

 

Module 20: Extensions to the GROUP BY Clause Demo

 

Module 21: Manipulating Data I

  • Write INSERT statements to add rows to a table
  • Insert Special Values
  • Copy Rows from Another Table
  • Update Rows in a Table
  • Use DELETE statements to remove rows from a table
  • Delete Rows Based on Another Table
  • The COMMIT and ROLLBACK statements
  • Using the SAVEPOINT statement
  • Lab Exercise

 

Module 22: Manipulating Data I Demo

 

Module 23: Manipulating Data II

  • Describe the TRUNCATE Statement
  • TRUNCATE vs. DELETE
  • Using the Multi-table INSERT statement
  • Using the MERGE statement
  • Show how Read Consistency works
  • Lab Exercise

 

Module 24: Manipulating Data II Demo

 

 

Module 25: 12c New Features

  • Top-n Query
  • Cascading Truncate Statement
  • Native Left Outer Join