Oracle 11g/12c: Introduction to SQL

 

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