Oracle Database 12c R2: SQL Workshop 1 (OCDSQLW1) – Outline

Detailed Course Outline

Introduction
  • Course Objectives, Course Agenda and Appendixes Used in this Course
  • Overview of Oracle Database 12c and Related Products
  • Overview of relational database management concepts and terminologies
  • Introduction to SQL and its development environments
  • What is Oracle SQL Developer?
  • Starting SQL*Plus from Oracle SQL Developer
  • The Human Resource(HR) Schema
  • Tables used in the Course
Retrieving Data using the SQL SELECT Statement
  • Capabilities of the SELECT statement
  • Arithmetic expressions and NULL values in the SELECT statement
  • Column aliases
  • Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Use of the DESCRIBE command
Restricting and Sorting Data
  • Limiting the Rows
  • Rules of precedence for operators in an expression
  • Substitution Variables
  • Using the DEFINE and VERIFY command
Using Single-Row Functions to Customize Output
  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the date functions
Using Conversion Functions and Conditional Expressions
  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Use conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group Functions
  • Group Functions
  • Creating Groups of Data
  • Restricting Group Results
Displaying Data From Multiple Tables Using Joins
  • Introduction to JOINS
  • Types of Joins
  • Natural join
  • Self-join
  • Non equijoins
  • OUTER join
Using Subqueries to Solve Queries
  • Introduction to Subqueries
  • Single Row Subqueries
  • Multiple Row Subqueries
Using the SET Operators
  • Set Operators
  • UNION and UNION ALL operator
  • INTERSECT operator
  • MINUS operator
  • Matching the SELECT statements
  • Using ORDER BY clause in set operations
Managing Tables using DML statements
  • Data Manipulation Language
  • Database Transactions
Introduction to Data Definition Language
  • Data Definition Language