Introduction
- Course Prerequisites
- Course Agenda
- Human Resources (HR) Schema
- Review of Using Oracle SQL Developer
- SQL Statements in SQL*Plus
- Review of some basic concepts of SQL
Control User Access
- Identify the System and Object Privileges
- Create Users
- Grant System Privileges
- Create and Grant Privileges to a Role
- Change Your Password
- Grant Object Privileges
- How to Pass On Your Privileges?
- Revoke Object Privileges
Management of Schema Objects
- Add, Modify, and Drop a Column
- Add, Drop, and Defer a Constraint
- Enable and Disable a Constraint
- Create and Remove Indexes
- Create a Function-Based Index
- Perform Flashback Operations
- Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
- Query External Tables
Manage Objects with Data Dictionary Views
- What is the Data Dictionary?
- How to Use the Dictionary Views?
- USER_OBJECTS and ALL_OBJECTS Views
- View Table and Column Information
- Query the dictionary views for constraint information
- Query the dictionary views for view, sequence, index and synonym information
- Add a comment to a table
- Query the dictionary views for comment information
Manipulate Large Data Sets
- Use Subqueries to Manipulate Data
- Retrieve Data Using a Subquery as Source
- Insert data Using a Subquery as a Target
- Use the WITH CHECK OPTION Keyword on DML Statements
- List the types of Multitable INSERT Statements
- Identify Multitable INSERT Statements
- Merge rows in a table
- How to track Changes in Data over a period of time?
Data Management in Different Time Zones
- What are Time Zones?
- The CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP functions
- Compare Date and Time in a Session’s Time Zone
- Describe DBTIMEZONE and SESSIONTIMEZONE
- List the differences between DATE and TIMESTAMP
- Identify the INTERVAL Data Types
- Usage of EXTRACT, TZ_OFFSET and FROM_TZ functions
- Use TO_TIMESTAMP, TO_YMINTERVAL, and TO_DSINTERVAL
Retrieve Data Using Subqueries
- Multiple-Column Subqueries
- Pairwise and Nonpairwise Comparison
- Usage of Scalar Subquery Expressions
- Solve problems with Correlated Subqueries
- Update and Delete Rows Using Correlated Subqueries
- Use the EXISTS and NOT EXISTS operators
- Use the WITH clause
- Use Recursive WITH clause
Regular Expression Support
- Invoke Regular Expressions Functions and Conditions in SQL
- Implement Meta Characters with Regular Expressions
- Perform a Basic Search using the REGEXP_LIKE function
- Find patterns using the REGEXP_INSTR function
- Extract Substrings using the REGEXP_SUBSTR function
- Replace Patterns Using the REGEXP_REPLACE function
- How to use Sub-Expressions with Regular Expression Support?
- The REGEXP_COUNT function