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 Customise Output
|
|
- Show the differences between single row and multiple row SQL functions
- Categorise 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
|
|