Introduction

• Introduction to SQL and its development environments
• The HR schema and the tables used in this course
• Overview of Oracle Database 12c and related products
• Overview of relational database management concepts and terminologies
• Oracle Database documentation and additional resources

Working with Oracle Cloud Exadata Express Cloud Service

• Accessing Cloud Database using SQL Workshop
• Connecting to Exadata Express using Database Clients
• Introduction to Oracle Database Exadata Express Cloud Service

Retrieve Data using the SQL SELECT Statement

• Invoke Column aliases
• Generate a report of data from the output of a basic SELECT statement
• Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use arithmetic expressions and NULL values in the SELECT statement
• List the capabilities of SQL SELECT statements
• Display the table structure using the DESCRIBE command

Restricted and Sorted Data

• Describe the comparison operators and logical operators
• Write queries with a WHERE clause to limit the output retrieved
• Write queries with an ORDER BY clause
• Sort the output in descending and ascending order
• Describe the rules of precedence for comparison and logical operators
• Usage of character string literals in the WHERE clause
• Substitution Variables

Usage of Single-Row Functions to Customize Output

• Manipulate dates with the DATE functions
• Perform arithmetic with date data
• List the differences between single row and multiple row functions
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Manipulate strings using character functions

Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting multiple functions
• Usage of conditional IF THEN ELSE logic in a SELECT statement
• Apply the NVL, NULLIF, and COALESCE functions to data

Aggregated Data Using the Group Functions

• Divide the data in groups by using the GROUP BY clause
• How to handle Null Values in a group function?
• Exclude groups of date by using the HAVING clause
• Usage of the aggregation functions in SELECT statements to produce meaningful reports
• Describe the AVG, SUM, MIN, and MAX function

Display Data From Multiple Tables

• Join a table to itself by using a self join
• Join Tables Using SQL:1999 Syntax
• Create Cross Joins
• View data that does not meet a join condition by using outer joins
• Write SELECT statements to access data from more than one table

Usage of Subqueries to Solve Queries

• Use the ANY and ALL Operator in Multiple-Row Subqueries
• Use a Subquery to Solve a Problem
• Group Functions in a Subquery
• Single-Row Subqueries
• Multiple-Row Subqueries
• Use the EXISTS Operator

SET Operators

• Use the ORDER BY Clause in Set Operations
• Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
• Use a SET operator to combine multiple queries into a single query
• Describe the SET operators

Data Manipulation

• Add New Rows to a Table
• Change the Data in a Table
• How to save and discard changes with the COMMIT and ROLLBACK statements
• Describe the FOR UPDATE Clause
• Use the DELETE and TRUNCATE Statements
• Implement Read Consistency

DDL Statements to Create and Manage Tables

• Describe the data types
• Create Tables
• How to drop a table?
• Understand Constraints
• How to alter a table?
• Create a table using a subquery
• Categorize Database Objects

Other Schema Objects

• How to drop a view?
• Create and drop synonyms
• Create and drop indexes
• Create, use, and modify a sequence
• Create, modify, and retrieve data from a view
• Perform Data manipulation language (DML) operations on a view

Introduction to PL/SQL

• Create a Simple Anonymous Block
• Generate the Output from a PL/SQL Block
• List the benefits of PL/SQL Subprograms
• PL/SQL Overview
• Overview of the Types of PL/SQL blocks

PL/SQL Identifiers

• List the different Types of Identifiers in a PL/SQL subprogram
• Bind Variables
• %TYPE Attribute
• Usage of the Declarative Section to Define Identifiers
• Sequences in PL/SQL Expressions
• Scalar Data Types
• Use of variables to store data

Write Anonymous PL/SQL blocks

• SQL Functions in PL/SQL
• Basic PL/SQL Block Syntax Guidelines
• How to comment code?
• Nested Blocks
• Operators in PL/SQL
• Data Type Conversion