Introduction
- The Human Resources (HR) Schema
- Review of Using Oracle SQL Developer
- Review of some basic concepts of SQL
Controlling User Access
- System versus Object Privileges
- Creating Users
- Granting System Privileges
- Creating and Granting Privileges to a Role
- Changing Your Password
- Granting Object Privileges
- Passing On Your Privileges
- Revoking Object Privileges
Managing Schema Objects
- Adding, Modifying and Dropping a Column
- Adding, Dropping and Deferring a Constraint
- Enabling and Disabling a Constraint
- Creating and Removing Indexes
- Creating a Function-Based Index
- Performing Flashback Operations
- Creating an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
- Querying External Tables
Managing Objects with Data Dictionary Views
- The Data Dictionary
- How to Use the Dictionary Views
- USER_OBJECTS and ALL_OBJECTS Views
- Table and Column Information
- Querying the dictionary views for constraint information
- Querying the dictionary views for view, sequence, index and synonym information
- Adding a comment to a table
- Querying the dictionary views for comment information
Manipulating Large Data Sets
- Using Subqueries to Manipulate Data
- Retrieving Data Using a Subquery as Source
- Inserting Using a Subquery as a Target
- Using the WITH CHECK OPTION Keyword on DML Statements
- Types of Multitable INSERT Statements
- Using Multitable INSERT Statements
- Merging rows in a table
- Tracking Changes in Data over a period of time
Managing Data in Different Time Zones
- Time Zones
- CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
- Comparing Date and Time in a Session’s Time Zone
- DBTIMEZONE and SESSIONTIMEZONE
- Difference between DATE and TIMESTAMP
- INTERVAL Data Types
- Using EXTRACT, TZ_OFFSET and FROM_TZ
- Using TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieving Data Using Subqueries
- Multiple-Column Subqueries
- Pairwise and Nonpairwise Comparison
- Using Scalar Subquery Expressions
- Solving problems with Correlated Subqueries
- Updating and Deleting Rows Using Correlated Subqueries
- Using the EXISTS and NOT EXISTS operators
- Using the WITH clause
Regular Expression Support
- Using the Regular Expressions Functions and Conditions in SQL
- Using Meta Characters with Regular Expressions
- Performing a Basic Search using the REGEXP_LIKE function
- Finding patterns using the REGEXP_INSTR function
- Extracting Substrings using the REGEXP_SUBSTR function
- Replacing Patterns Using the REGEXP_REPLACE function
- Using Sub-Expressions with Regular Expression Support
- Using the REGEXP_COUNT function