Oracle Database: Introduction to SQL Ed 2

Suggested Prerequisite

• Familiarity with data processing concepts and techniques
• Data processing

Audience

• Application Developers
• System Analysts
• Business Analysts
• Forms Developer
• Developer
• Data Warehouse Administrator
• PL/SQL Developer

Course Topics

1. Introduction

• Overview of Oracle Database 12c and Related Products
• Introduction to SQL and its development environments
• Course Objectives, Course Agenda and Appendixes Used in this Course
• The Human Resource (HR) Schema
• Overview of relational database management concepts and terminologies
• What is Oracle SQL Developer?
• Tables used in the Course
• Starting SQL*Plus from Oracle SQL Developer

2. Working with Oracle Cloud Exadata Express Cloud Service

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

3. Retrieving Data using the SQL SELECT Statement

• Column aliases
• Use of the DESCRIBE command
• Capabilities of the SELECT statement
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Arithmetic expressions and NULL values in the SELECT statement

4. Restricting and Sorting Data

• Rules of precedence for operators in an expression
• Limiting the Rows
• Substitution Variables
• Using the DEFINE and VERIFY command

5. Using Single-Row Functions to Customize Output

• Manipulate strings with character function in the SELECT and WHERE clauses
• Perform arithmetic with date data
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Describe the differences between single row and multiple row functions
• Manipulate dates with the date functions

6. Using Conversion Functions and Conditional Expressions

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

7. Reporting Aggregated Data Using the Group Functions

• Restricting Group Results
• Creating Groups of Data
• Group Functions

8. Displaying Data from Multiple Tables Using Joins

• Self-join
• Types of Joins
• Introduction to JOINS
• Non equijoins
• OUTER join
• Natural join

9. Using Subqueries to Solve Queries

• Single Row Subqueries
• Multiple Row Subqueries
• Introduction to Subqueries

10. Using the SET Operators

• Matching the SELECT statements
• Set Operators
• Using ORDER BY clause in set operations
• INTERSECT operator
• MINUS operator
• UNION and UNION ALL operator

11. Managing Tables using DML statements

• Database Transactions
• Data Manipulation Language

12. Introduction to Data Definition Language

• Data Definition Language

13. Introduction to Data Dictionary Views

• Describe the Data Dictionary Structure
• Introduction to Data Dictionary
• Querying the Data Dictionary Views
• Using the Data Dictionary views

14. Creating Sequences, Synonyms, Indexes

• Overview of indexes
• Overview of synonyms
• Overview of sequences

15. Creating Views

• Overview of views

16. Managing Schema Objects

• Creating and using external tables
• Creating and using temporary tables
• Managing constraints

17. Retrieving Data by Using Subqueries

• Working with Multiple-Column subqueries
• Using Scalar subqueries in SQL
• Correlated Subqueries
• Retrieving Data by Using a Subquery as Source
• Working with the WITH clause

18. Manipulating Data by Using Subqueries

• Using the WITH CHECK OPTION Keyword on DML Statements
• Inserting by Using a Subquery as a Target
• Using Subqueries to Manipulate Data
• Using Correlated Subqueries to Update and Delete rows

19. Controlling User Access

• Revoking object privileges
• Creating a role
• Object privileges
• System privileges

20. Manipulating Data

• Using the MERGE statement
• Using multitable INSERTs
• Performing flashback operations
• Overview of the Explicit Default Feature
• Tracking Changes in Data

21. Managing Data in Different Time Zones

• Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
• Working with INTERVAL data types