This course is designed for application developers with basic SQL language skills. In this course, students learn to control privileges at the object and system level. This course covers creating indexes and constraints, and altering existing schema objects. Students also learn to create and query external tables and use the advanced features of SQL to query and manipulate data within the database.
Students learn to use the dictionary views to retrieve metadata and create reports about their schema objects. Students learn some of the date time functions available in the Oracle 11g Database and use the regular expression support in SQL.
In this course, students use Oracle SQL Developer as the main tool and SQL*Plus is introduced as an optional tool.
Learn To:
Control database access to specific objects
Manage schema objects and use data dictionary views
Manipulate large data sets in the Oracle database by using subqueries
Manage data in different time zones
Retrieve data by using advanced subqueries
Use the regular expression support in SQL to search, match, and replace strings in terms of regular expressions
Skills Gained
Manage schema objects
Manage objects with data dictionary views
Manipulate large data sets in the Oracle database by using subqueries
Manage data in different time zones
Write multiple-column subqueries
Use scalar and correlated subqueries
Use the regular expression support in SQL
Add new users with different levels of access privileges
Control database access to specific objects
Who Can Benefit
Application Developers
Database Administrators
Business Intelligence Developer
System Analysts
Forms Developer
Support Engineer
PL/SQL Developer
Developer
Prerequisites
Required Prerequisites:
Oracle Database 11g: SQL Fundamentals I
Suggested Prerequisites:
Basic Knowledge of SQL
Familiarity with Oracle SQL Developer and SQL*Plus
Suggested Next Courses:
Basic Knowledge of SQL
Familiarity with Oracle SQL Developer and SQL*Plus
Code:
11gDB-SQL-FUND-II
Length:
2 days
Type:
Instructor-Led
Certified By:
Oracle
Tuition:
$1,200
This course is taught by Certified Oracle instructors. There is a difference. Learn More
This course is in the following categories. Click the categories to find similar courses and topics of interest.
There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below. We can also contact you when the course is scheduled in your area.
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
When you take a certified course with ExitCertified, you are learning from
the creators of the products you use. Our commitment to your IT
community, along with our authorization to deliver certified courses,
ensures you receive a premium training experience.
