Overview of Relational Databases, SQL and PL/SQL

1. Discuss the theoretical and physical aspects of a relational database
2. Describe the Oracle implementation of the RDBMS and ORDBMS
3. Describe the use and benefits of PL/SQL

Writing Basic SQL Statements

1. List the capabilities of SQL SELECT statements
2. Execute a basic SELECT statement
3. Differentiate between SQL statements and SQL*Plus commands

Restricting and Sorting Data

1. Limit the rows retrieved by a query
2. Sort the rows retrieved by a query

Single Row Functions

1. Describe various types of functions available in SQL
2. Use character, number, and date functions in SELECT statements
3. Describe the use of conversion functions

Displaying Data from Multiple Tables

1. Write SELECT statements to access data from more than one table using equality and nonequality joins
2. View data that generally does not meet a join condition by using outer joins
3. Join a table to itself

Aggregating Data Using Group Functions

1. Identify the available group functions
2. Describe the use of group functions
3. Group data using the GROUP BY clause
4. Include or exclude grouped rows by using the HAVING clause

Subqueries

1. Describe the types of problems that subqueries can solve
2. Define subqueries

Manipulating Data

1. Describe each DML statement
2. Insert rows into a table
3. Update rows in a table
4. Delete rows from a table
5. Control transactions

Creating and Managing Tables

1. Describe the main database objects
2. Create tables
3. Describe the datatypes that can be used when specifying column definition
4. Alter table definitions
5. Drop, rename, and truncate tables

Including Constraints

1. Describe constraints
2. Create and maintain constraints

Creating Views

1. Describe a view
2. Create a view
3. Retrieve data through a view
4. Insert, update, and delete data through a view
5. Drop a view

Oracle Data Dictionary

1. Describe the data dictionary views a user may access
2. Query data from the data dictionary

Other Database Objects

1. Describe database objects and their uses
2. Create, maintain, and use sequences
3. Create and maintain indexes
4. Create private and public synonyms

Controlling User Access

1. Create users
2. Create roles to ease setup and maintenance of the security model
3. Use the GRANT and REVOKE statements to grant and revoke object privileges

Declaring Variables

1. List the benefits of PL/SQL
2. Describe the basic PL/SQL block and its sections
3. Describe the significance of variables in PL/SQL
4. Declare PL/SQL variables
5. Execute a PL/SQL block

Writing Executable Statements

1. Describe the significance of the executable section
2. Write statements in the executable section
3. Describe the rules of nested blocks
4. Execute and test a PL/SQL block
5. Use coding conventions
6. List the types of subqueries
7. Write single-row and multiple-row subqueries

Multiple-Column Subqueries

1. Write multiple-column subqueries
2. Describe and explain the behavior of subqueries when null values are retrieved
3. Write subqueries in a FROM clause

Producing Readable Output with SQL*Plus

1. Produce queries that require an input variable
2. Customize the SQL*Plus environment
3. Produce more readable output
4. Create and execute script files
5. Save customizations

Interacting with the Oracle Server

1. Write a successful SELECT statement in PL/SQL
2. Declare the datatype and size of a PL/SQL variable dynamically
3. Write DML statements in PL/SQL
4. Control transactions in PL/SQL
5. Determine the outcome of SQL DML statements

Writing Control Structures

1. Identify the uses and types of control structures
2. Construct an IF statement
3. Construct and identify different loop statements
4. Use logic tables
5. Control block flow using nested loops and labels

Working with Composite Datatypes

1. Create user-defined PL/SQL records
2. Create a record with the %ROWTYPE attribute
3. Create a PL/SQL table
4. Create a PL/SQL table of records
5. Describe the difference between records, tables, and tables of records

Writing Explicit Cursors

1. Distinguish between an implicit and an explicit cursor
2. Use a PL/SQL record variable
3. Write a cursor FOR loop

Advanced Explicit Cursor Concepts

1. Write a cursor that uses parameters
2. Determine when a FOR UPDATE clause in a cursor is required
3. Determine when to use the WHERE CURRENT OF clause
4. Write a cursor that uses a subquery

Handling Exceptions

1. Define PL/SQL exceptions
2. Recognize unhandled exceptions
3. List and use different types of PL/SQL exception handlers
4. Trap unanticipated errors
5. Describe the effect of exception propagation in nested blocks
6. Customize PL/SQL exception messages