INTRODUCTION TO ORACLE9I SQL®
Writing a Basic SQL Statement
• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
• Differentiate between SQL statements and iSQL*Plus commands
Restricting and Sorting Data
• Limit the rows retrieved by a query
• Sort the rows retrieved by a query
Single Row Functions
• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements
• Use conversion functions
Displaying Data from Multiple Tables
• Write SELECT statements to access data from more than one table using equality and nonequality joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself using a self-join
Aggregating Data Using Group Functions
• Identify the available group functions
• Use group functions
• Group data using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause
Subqueries
• Describe the types of problems that subqueries can solve
• Define subqueries
• List the types of subqueries
• Write single-row and multiple-row subqueries
Producing Readable Output with iSQL*Plus
• Produce queries that require a substitution variable
• Produce more readable output
• Create and execute script files
Manipulating Data
• Describe each DML statement
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Merge rows in a table
• Control transactions
Creating and Managing Tables
• Describe the main database objects
• Create tables
• Describe the datatypes that can be used when specifying column definition
• Alter table definitions
• Drop, rename and truncate tables
Including Constraints
• Describing Constraints
• Create and maintain constraints
Creating Views
• Create, maintain and use sequences
• Create and maintain indexes
• Create private and public synonyms
ORACLE DATABASE 11G: ADMINISTRATION I

Exploring the Oracle Database Architecture

• Explain the Memory Structures
• Describe the Process Structures
• Overview of Storage Structures

Preparing the Database Environment

• Identify the tools for Administering an Oracle Database
• Plan an Oracle Database installation
• Install the Oracle software by using Oracle Universal Installer (OUI)
• Creating an Oracle Database

• Create a database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instante

• Setting database initialization parameters
• Describe the stages of database startup and shutdown
• Using alert log and trace files
• Using data dictionary and dynamic performance views

Configuring the Oracle Network Environment

• Configure and Manage the Oracle Network
• Using the Oracle Shared Server architecture

Managing Database Storage Structures

• Overview of tablespace and datafiles
• Create and manage tablespaces
• Space management in tablespaces

Administering User Security

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles

Managing Data and Concurrency

• Monitor and resolve locking conflicts

Managing Undo Data

• Overview of Undo
• Transactions and undo data
• Managing undo

Implementing Oracle Database Security

• Database Security and Principle of Least Privilege
• Work with Standard Database Auditing

Database Maintenance

• Use and manage optimizer statistics
• Use and manage Automatic Workload Repository (AWR)
• Use advisory framework
• Manage Alerts and Thresholds

Performance Management

• Use Automatic Memory Management
• Use Memory Advisors
• Troubleshoot invalid and unusable objects

Intelligent Infrastructure Enhancements

• Use the Enterprise Manager Support Workbench
• Managing Patches

Backup and Recovery Concepts

• Identify the types of failure that can occur in an Oracle database
• Describe ways to tune instance recovery
• Identify the importance of checkpoints, redo log files, and archived log files
• Overview of flash recovery area
• Configure ARCHIVELOG mode

Performing Database Backups

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Manage backups, view backup reports and monitor the flash recovery area

Performing Database Recovery

• Overview of Data Recovery Advisor
• Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Moving Data

• Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases