Oracle Database: SQL Workshop I (D80190) – 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
  • Sorting rows using ORDER BY clause
  • SQL row limiting clause in a query
  • 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
  • Nesting Functions
  • 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
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Conditional Expressions
Reporting Aggregated Data Using the Group Functions
  • Group Functions
  • Grouping Rows (GROUP BY and HAVING clause)
  • Nesting Group Functions
Displaying Data From Multiple Tables Using Joins
  • Introduction to JOINS
  • Types of Joins
  • Natural join
  • Self-join
  • Nonequijoins
  • OUTER join
  • Cartesian product
Using Subqueries to Solve Queries
  • Introduction to Subqueries
  • Single Row Subqueries
  • Multiple Row Subqueries
  • Null values in a subquery
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
  • Manual Data Locking
Introduction to Data Definition Language
  • Database Objects
  • CREATE TABLE statement
  • Data types
  • Overview of constraint
  • Creating a table using subquery
  • Use the ALTER TABLE statement to add, modify, and drop a column
  • DROP TABLE statement