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
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
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
