Introduction to SQL
o Listing the features of Oracle Database 11g
o Discussing the basic design, theoretical and physical aspects of a relational database
o Describing the development environments for SQL
o Describing Oracle SQL Developer
o Describing the data set used by the Package
Retrieving Data Using the SQL SELECT Statement
o Listing the capabilities of SQL SELECT statements.
o Generating a report of data from the output of a basic SELECT statement
o Using arithmetic expressions and NULL values in the SELECT statement
o Using Column aliases
o Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
o Displaying the table structure using the DESCRIBE command
Restricting and Sorting Data
o Writing queries with a WHERE clause to limit the output retrieved
o Using the comparison operators and logical operators
o Describing the rules of precedence for comparison and logical operators
o Using character string literals in the WHERE clause
o Writing queries with an ORDER BY clause to sort the output
o Sorting output in descending and ascending order
o Using the Substitution Variables
Using Single-Row Functions to Customize Output
o Differentiating between single row and multiple row functions
o Manipulating strings using character functions
o Manipulating numbers with the ROUND, TRUNC and MOD functions
o Performing arithmetic with date data
o Manipulating dates with the date functions
Using Conversion Functions and Conditional Expressions
o Describing implicit and explicit data type conversion
o Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
o Nesting multiple functions
o Applying the NVL, NULLIF, and COALESCE functions to data
o Using conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group Functions
o Using the aggregation functions in SELECT statements to produce meaningful reports
o Using AVG, SUM, MIN, and MAX function
o Handling Null Values in a group function
o Creating queries that divide the data in groups by using the GROUP BY clause
o Creating queries that exclude groups of date by using the HAVING clause
Displaying Data From Multiple Tables
o Writing SELECT statements to access data from more than one table
o Joining Tables Using SQL:1999 Syntax
o Viewing data that does not meet a join condition by using outer joins
o Joining a table by using a self join
o Creating Cross Joins
Using Sub-queries to Solve Queries
o Using a Subquery to Solve a Problem
o Executing Single-Row Sub-queries
o Using Group Functions in a Sub-query
o Using Multiple-Row Subqueries
o Using the ANY and ALL Operator in Multiple-Row Sub-queries
Using the SET Operators
o Describing the SET operators
o Using a SET operator to combine multiple queries into a single query
o Using UNION, UNION ALL, INTERSECT, and MINUS Operator
o Using the ORDER BY Clause in Set Operations
Manipulating Data
o Adding New Rows to a Table Using the INSERT statement
o Changing Data in a Table Using the UPDATE Statement
o Using DELETE and TRUNCATE Statements
o Saving and discarding changes with the COMMIT and ROLLBACK statements
o Implementing Read Consistency
o Using the FOR UPDATE Clause
Using DDL Statements to Create and Manage Tables
o Categorizing Database Objects
o Creating Tables using the CREATE TABLE Statement
o Describing the data types
o Describing Constraints
o Creating a table using a subquery
o Altering and Dropping a table
Creating Other Schema Objects
o Creating, modifying, and retrieving data from a view
o Performing Data manipulation language (DML) operations on a view
o Dropping a view
o Creating, using, and modifying a sequence
o Creating and dropping indexes
o Creating and dropping synonyms
Exploring the Oracle Database Architecture
o Oracle Database Architecture Overview
o Interacting with an Oracle Database
o Process Architecture
o Database Storage Architecture
o Logical and Physical Database Structures
o Tablespaces and Data Files
o SYSTEM and SYSAUX Tablespaces
o Segments, Extents, and Blocks
Preparing the Database Environment
o Tasks of an Oracle Database Administrator
o Tools Used to Administer an Oracle Database
o Installation: System Requirements
o Optimal Flexible Architecture (OFA)
o Setting Environment Variables
o Oracle Universal Installer (OUI)
o Database Configuration Options
o Advanced Installation Options
Creating an Oracle Database
o Planning the Database
o Configuring the Listener
o Using the DBCA to Create a Database
o Password Management
o Creating a Database Design Template
o Using the DBCA to Delete a Database
o Using DBCA For Additional Tasks
Managing the Oracle Instance
o Management Framework
o Oracle Enterprise Manager
o Initialization Parameter
o Database Startup and Shutdown
o Shutdown Modes
o Viewing the Alert History
o Using Trace Files
o Data Dictionary: Overview
Configuring the Oracle Network Environment
o Oracle Net Services & Listener
o Establishing Net Connections
o Tools for Configuring and Managing the Oracle Network
o Net Services Administration Pages
o Naming Methods
o Directory Naming
o SGA and PGA
o When Not to Use a Shared Server
Managing Database Storage Structures
o Storage Structures
o How Table Data Is Stored
o Anatomy of a Database Block
o Space Management in Tablespaces
o Tablespaces in the Preconfigured Database
o Actions with Tablespaces
o Oracle Managed Files (OMF)
o ASM: Concepts
Administering User Security
o Database User Accounts
o Predefined Accounts: SYS and SYSTEM
o Administrator Authentication
o Benefits of Roles
o Predefined Roles
o Implementing Password Security Features
o Assigning Quota to Users
Managing Schema Objects
o What Is a Schema?
o Accessing Schema Objects
o Specifying Data Types in Tables
o Creating and Modifying Tables
o Understanding Data Integrity
o Indexes & Views
o Sequences
o Temporary Tables
Managing Data and Concurrency
o Manipulating Data Through SQL
o PL/SQL
o Administering PL/SQL Objects
o Functions, Procedures, Packages & Triggers
o Data Concurrency
o Enqueue Mechanism
o Resolving Lock Conflicts Using SQL
o Deadlocks
Managing Undo Data
o Data Manipulation
o Transactions and Undo Data
o Undo Data Versus Redo Data
o Configuring Undo Retention
o Guaranteeing Undo Retention
o Using the Undo Advisor
o Viewing System Activity
Implementing Oracle Database Security
o Industry Security Requirements
o Principle of Least Privilege
o Monitoring for Compliance
o Value-Based Auditing
o Fine-Grained Auditing
o DBA Auditing
o Security Updates
o Applying Security Patches
Database Maintenance
o Introducing Terminology
o Optimizer Statistics
o Automatic Workload Repository (AWR)
o Automatic Database Diagnostic Monitor (ADDM)
o Advisory Framework
o Automated Maintenance Tasks
o Server-Generated Alerts
o Reacting to Alerts
Performance Management
o Performance Monitoring
o Managing Memory Components
o Enabling Automatic Memory Management (AMM)
o Automatic Shared Memory Advisor
o Using Memory Advisors
o Dynamic Performance Statistics
o Troubleshooting and Tuning Views
o Invalid and Unusable Objects
Backup and Recovery Concepts
o Part of Your Job
o Statement Failure
o User Error
o Understanding Instance Recovery
o Phases of Instance Recovery
o Using the MTTR Advisor
o Media Failure
o Archive Log Files
Performing Database Backups
o Backup Solutions: Overview
o Oracle Secure Backup
o User-Managed Backup
o Terminology
o Recovery Manager (RMAN)
o Configuring Backup Settings
o Backing Up the Control File to a Trace File
o Monitoring the Flash Recovery Area
Performing Database Recovery
o Opening a Database
o Data Recovery Advisor
o Loss of a Control File
o Loss of a Redo Log File
o Data Recovery Advisor
o Data Failures
o Listing Data Failures
o Data Recovery Advisor Views
Moving Data
o Moving Data: General Architecture
o Directory Object: Overview
o SQL*Loader: Overview
o Data Pump: Overview
o Performance Initialization Parameters
o Using Enterprise Manager to Monitor Data Pump Jobs
o Data Dictionary
Enhancing Database Capabilities
o Using EM Support Workbench
o Create a Service Request
o Package and upload diagnostic data to Oracle Support
o Track the SR and Implement Repairs
o Incident Packaging Configuration
o Working with Oracle Support
o MetaLink Integration
o Managing Patches
