|
Course Details |
|
|
|
Oracle 11g SQL |
Introduction |
|
|
|
List the Oracle Database 11g Main Features
An Overview of: components, internet platform, apps server and developer suite
Describe Relational and Object Relational Database Designs
Review the System Development Life Cycle
Define the term Data Models
Describe different means of Sorting Data
Show how Multiple Tables can be related
Describe how SQL Communicates to the Database
|
|
|
|
Writing SQL SELECT Statements |
|
|
|
Define projection, selection, and join terminology
Review the basic SQL SELECT statement syntax
Select all columns using a wildcard notation from a table
State simple rules and guidelines for writing SQL statements
Write a query containing the arithmetic operators
Create a character expression with the concatenation operator
Using the iSQL*Plus Environment
SQL statements versus iSQL*Plus commands
|
|
|
|
Restricting and Sorting Data |
|
|
|
Limit rows using a selection
Using the WHERE clause to retrieve specific rows
Using the comparison conditions in the WHERE clause
Use the LIKE condition to compare literal values
List the logical conditions AND, OR, NOT
Describe the rules of precedence for the conditions
Sort rows with the ORDER BY clause
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time
|
|
|
|
Using Single-Row Functions to Customize Output |
|
|
|
Show the differences between single row and multiple row SQL functions
Categorize the character functions into case manipulation and character
manipulation types
Use the character manipulation functions in the SELECT and WHERE clauses
Explain and use the DATE and numeric functions
Use the SYSDATE function to retrieve the current date in the default format
Introduce the DUAL table as a means to view function results
List the rules for applying the arithmetic operators on dates
Use the arithmetic operators with dates in the SELECT clause
|
|
|
|
Reporting Aggregated Data Using the Group Functions |
|
|
|
Describe and categorize the group functions
Use the group functions
Utilize the DISTINCT keyword with the group functions
Describe how nulls are handled with the group functions
Create groups of data with the GROUP BY clause
Group data by more than one column
Avoid illegal queries with the group functions
Exclude groups of data with the HAVING clause
|
|
|
|
Displaying Data From Multiple Tables |
|
|
|
Identify Types of Joins
Retrieve Records with Natural Joins
Use Table Aliases to write shorter code and explicitly identify columns from
multiple tables
Create a Join with the USING clause to identify specific columns between tables
Use the ON clause to specify arbitrary conditions or specify columns to Join
Create a Three-way join with the ON clause to retrieve information from 3 tables
List the Types of Outer Joins LEFT, RIGHT, and FULL
Generating a Cartesian Product
|
|
|
|
Using Sub Queries to solve Queries |
|
|
|
List the syntax for sub queries in a SELECT statements WHERE clause
List the guidelines for using sub queries
Describe the types of sub queries
Execute single row sub queries and use the group functions in a sub query
Identify illegal statements with sub queries
Execute multiple row sub queries
Analyze how the ANY and ALL operators work in multiple row sub queries
|
|
|
|
Using the SET Operators |
|
|
|
Use the UNION operator to return all rows from multiple tables and eliminate any
duplicate rows
Use the UNION ALL operator to return all rows from multiple tables
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator
|
|
|
|
Manipulating Data |
|
|
|
Write INSERT statements to add rows to a table
Copy rows from another table
Create UPDATE statements to change data in a table
Generate DELETE statements to remove rows from a table
Use a script to manipulate data
Save and discard changes to a table through transaction processing
Show how read consistency works
Describe the TRUNCATE statement
|
|
|
|
Using DLL Statement to create and Manage Tables |
|
|
|
List the main database objects and describe the naming rules for database
objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement and Rename a table
|
|
|
|
Creating the Schema Objects |
|
|
|
Categorize simple and complex views and compare them
Create a view
Retrieve data from a view
Explain a read-only view
List the rules for performing DML on complex views
Create a sequence
List the basic rules for when to create and not create an index
Create a synonym
|
|
|
|
Managing Objects with Data Dictionary Views |
|
|
|
Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema
objects
Use the COMMENT command to document objects
|
|
|
|
Controlling User Access |
|
|
|
Controlling User Access
System versus Objects Privileges
Using Roles to define user groups
Changing Your Password
Granting Object Privileges
Confirming Privileges Granted
Revoking Object Privileges
Using Database Links
|
|
|
|
Manage Schema Objects |
|
|
|
Using the ALTER TABLE statement
Adding a Column
Modifying a Column
Dropping a Column, Set Column UNUSED
Adding, Enabling and Disabling Constraints
Creating Function-Based Indexes
Performing FLASHBACK operations
External Tables
|
|
|
|
Manipulating Large Data Sets |
|
|
|
Using the MERGE Statement
Performing DML with Sub queries
Performing DML with a RETURNING Clause
Overview of Multi-table INSERT Statements
Tracking Changes in DML
|
|
|
|
Generating Reports by Grouping Related Data |
|
|
|
Overview of GROUP BY Clause
Overview of Having Clause
Aggregating data with ROLLUP and CUBE Operators
Determine subtotal groups using GROUPING Functions
Compute multiple groupings with GROUPING SETS
Define levels of aggregation with Composite Columns
Create combinations with Concatenated Groupings
|
|
|
|
Managing Data From Diffrent Time Zone |
|
|
|
Time Zones
Using date and time functions
Identifying TIMESTAMP Data Types
Differentiating between DATE and TIMESTAMP
Performing Conversion Operations
|
|
|
|
Hierarchical Retrieval |
|
|
|
Sample Data from the EMPLOYEES Table
The Tree Structure of Employee data
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses
|
|
|
|
Regular Expression Support |
|
|
|
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data
|
|
|