Oracle Foundations Associate, Database (1Z0-006)

Course wise syllabus

1. Database Foundations (90)

S. No. Topics
Introduction
1 Introduction to the Course
2 Introduction to Databases
3 Types of Database Models
4 Business Requirements
Databases and Data Modeling
5 Relational Databases
6 Conceptual and Physical Data Models
7 Entities and Attributes
8 Unique Identifiers
9 Relationships
10 Entity Relationship Modeling (ERDs)
Refining the Data Model
11 More with Relationships
12 Tracking Data Changes
13 Normalization and Business Rules
14 Data Modeling Terminology and Mapping
Oracle SQL Developer Data Modeler
15 Oracle SQL Developer Data Modeler
16 Convert a Logical Model to a Relational Model
Mapping to the Physical Model
17 Mapping Entities and Attributes
18 Mapping Primary and Foreign Keys
Introduction to SQL
19 Introduction to Oracle Application Express
20 Structured Query Language (SQL)
21 Data Definition Language (DDL)
22 Data Manipulation Language (DML)
23 Transaction Control Language (TCL)
24 Retrieving Data Using SELECT
25 Restricting Data Using WHERE
26 Sorting Data Using ORDER BY
27 Joining Tables Using JOIN
2. Database Design and Programming with SQL (180)
S. No Topics
Introduction
1 Oracle Application Express
2 Relational Database Technology
3 Anatomy of a SQL Statement
SELECT and WHERE
4 Columns, Characters, and Rows
5 Limit Rows Selected
6 Comparison Operators
WHERE, ORDER BY, and Intro to Functions
7 Logical Comparisons and Precedence Rules
8 Sorting Rows
9 Introduction to Functions
Single Row Functions Part I
10 Case and Character Manipulation
11 Number Functions
12 Date Functions
Single Row Functions Part II
13 Conversion Functions
14 NULL Functions
15 Conditional Expressions
JOINs Part I
16 Cross Joins and Natural Joins
17 Join Clauses
18 Inner versus Outer Joins
19 Self-Joins and Hierarchical Queries
JOINs Part II
20 Oracle Equijoin and Cartesian Product
21 Oracle Non-equijoins and Outer Joins
Group Functions Part I
22 Group Functions
23 COUNT, DISTINCT, NVL
Group Functions Part II
24 Using Group By and Having Clauses
25 Using Rollup and Cube Operations, and Grouping Sets
26 Using Set Operators
Subqueries
27 Fundamentals of Subqueries
28 Single-Row Subqueries
29 Multiple-Row Subqueries
30 Correlated Subqueries
Ensuring Quality Queries Part I
31 Ensuring Quality Query Results
DML
32 INSERT Statements
33 Updating Column Values and Deleting Rows
34 DEFAULT Values, MERGE, and Multi-Table Inserts
DDL
35 Creating Tables
36 Using Data Types
37 Modifying a Table
Constraints
38 Intro to Constraints; NOT NULL and UNIQUE Constraints
39 PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
40 Managing Constraints
Views
41 Creating Views
42 DML Operations and Views
43 Managing Views
Sequences and Synonyms
44 Working With Sequences
45 Indexes and Synonyms
Privileges and Regular Expressions
46 Controlling User Access
47 Creating and Revoking Object Privileges
48 Regular Expressions
TCL
49 Database Transactions
Final Project and Exam Review
50 Testing
51 Final Project Database Creation
52 Final Exam Review
Ensuring Quality Queries Part II
53 Ensuring Quality Query Results - Advanced Techniques

3. Database Programming with PL/SQL (180)

S. No. Topics
Fundamentals
1 Introduction to PL/SQL
2 Benefits of PL/SQL
3 Creating PL/SQL Blocks
Defining Variables and Datatypes
4 Using Variables in PL/SQL
5 Recognizing PL/SQL Lexical Units
6 Recognizing Data Types
7 Using Scalar Data Types
8 Writing PL/SQL Executable Statements
9 Nested Blocks and Variable Scope
10 Good Programming Practices
Using SQL in PL/SQL
11 Review of SQL DML
12 Retrieving Data in PL/SQL
13 Manipulating Data in PL/SQL
14 Using Transaction Control Statements
Program Structures to Control Execution Flow
15 Conditional Control: IF Statements
16 Conditional Control: Case Statements
17 Iterative Control: Basic Loops
18 Iterative Control: While and For Loops
19 Iterative Control: Nested Loops
Using Cursors and Parameters
20 Introduction to Explicit Cursors
21 Using Explicit Cursor Attributes
22 Cursor FOR Loops
23 Cursors with Parameters
24 Using Cursors for Update
25 Using Multiple Cursors
Using Composite Datatypes
26 User-Defined Records
27 Indexing Tables of Records
Exception Handling
28 Handling Exceptions
29 Trapping Oracle Server Exceptions
30 Trapping User-Defined Exceptions
31 Recognizing the Scope of Exceptions
Using and Managing Procedures
32 Creating Procedures
33 Using Parameters in Procedures
34 Passing Parameters
Using and Managing Functions
35 Creating Functions
36 Using Functions in SQL Statements
37 Review of the Data Dictionary
38 Managing Procedures and Functions
39 Review of Object Privileges
40 Using Invoker's Rights and Autonomous Transactions
Using and Managing Packages
41 Creating Packages
42 Managing Package Concepts
43 Advanced Package Concepts
Getting the Best out of Packages
44 Persistent State of Package Variables
45 Using Oracle-Supplied Packages
Improving PL/SQL Performance
46 Using Dynamic SQL
47 Improving PL/SQL Performance
Using and Managing Triggers
48 Introduction to Triggers
49 Creating DML Triggers: Part I
50 Creating DML Triggers: Part II
51 Creating DDL and Database Event Triggers
52 Managing Triggers
Recognizing and Managing Dependencies
53 Introduction to Dependencies
54 Understanding Remote Dependencies
Using the PL/SQL Compiler
55 Using PL/SQL Initialization Parameters
56 Displaying Compiler Warning Messages
57 Using Conditional Compilation
58 Hiding your Source Code
Course wise – detailed syllabus

1. Database Foundations
S. No. Topics
Introduction
1 Introduction to the Course
o Identify the course goals and objectives
o Describe the course learning strategy
o Understand the course environment
2 Introduction to Databases
o Differentiate between data and information
o Define database
o Describe the elements of a database management system (DBMS)
o Identify the transformations in computing
o Identify business and industry examples where database applications are used
3 Types of Database Models
o Describe the database development process
o Explain the common types of database models:
Flat file model
Hierarchical model
Network model
Object-oriented model
Relational model
4 Business Requirements
o Explain the need for a database solution
o Describe the importance of business rules
o Identify guidelines and examples of writing business rules
o Explain the importance of clearly communicating and accurately capturing information
requirements
Databases and Data Modeling
5 Relational Databases
o Describe the features of a single table
o Describe the features and rules of a relational database
o Describe the advantages and disadvantages of database types
o Define relational tables and key terms
6 Conceptual and Physical Data Models
o Describe a conceptual data model
o Describe a logical data model
o Describe a physical data model
o Analyze the similarities and differences between conceptual and physical data models
7 Entities and Attributes
o Identify entities
o Identify attributes
o Identify mandatory, optional, volatile, and nonvolatile attributes
o Describe the Barker, Bachman, and Information Engineering notations
8 Unique Identifiers
o Identify unique identifiers (UIDs)
o Identify artificial unique identifiers
o Identify composite unique identifiers
o Identify candidate and secondary unique identifiers
o Define primary keys
9 Relationships
o Define and recognize examples of relationships and corresponding foreign keys
o Identify the optionality of relationships
o Identify the cardinality of relationships
o Types of relationships
o Relationship matrix
10 Entity Relationship Modeling (ERDs)
o Describe data modelling
o Explain “implementation-free” as it relates to data models and database design implementation
o List the four goals of entity relationship modeling
o Identify an entity relationship diagram (ERD)
o Mapping relationships using ERDish
o Construct ERD components that represent entities and attributes according to diagramming
conventions
Refining the Data Model
11 More with Relationships
o Resolve M:M relationships
o Identifying barred relationships
o Identify and illustrate nontransferable relationships
o Identify and draw supertype and subtype entities
o Identify hierarchical, recursive, and arc relationships
12 Tracking Data Changes
o Keep track of data that changes over time
13 Normalization and Business Rules
o Explain normalization
o Describe the normal forms
o Use normalization to validate data
o Describe business rules
14 Data Modeling Terminology and Mapping
o Apply terminology mapping between the logical and physical models
o Understand and apply the Oracle naming conventions for tables and columns used in physical
models
o Apply the rules of relationship mapping to correctly transform relationships
Oracle SQL Developer Data Modeler
15 Oracle SQL Developer Data Modeler
o Use Oracle SQL Developer Data Modeler to create:
Entities, attributes, and UIDs with correct optionality and cardinality
Supertype and subtype entities
Arc, hierarchical, barred, and recursive relationships
16 Convert a Logical Model to a Relational Model
o Describe how to convert a logical model to a relational model in Oracle SQL Developer Data
Modeler
o List the steps to convert a logical model to a relational model
o List the steps to convert a relational model to a logical model in Oracle SQL Developer Data
Modeler
Mapping to the Physical Model
17 Mapping Entities and Attributes
o Explain the naming conventions used in a relational database.
o Use Oracle SQL Developer Data Modeler to create a glossary and apply naming standards to
Map entities to table names
Map attributes to column names
18 Mapping Primary and Foreign Keys
o Decide on naming conventions for:
Primary key constraint names
Foreign key constraint names
Foreign key column names
Introduction to SQL
19 Introduction to Oracle Application Express
o Distinguish between application software and system software and give an example of each
o Log-in to the Oracle Application Express practice environment
o Execute a simple query to retrieve information from the Database
o Apply the rules of SQL to display all columns and a subset of columns specified by criteria
20 Structured Query Language (SQL)
o Describe how data is organized in a relational database
o Explain the various relational database terminologies
o Define the structured query language and its functions
o Describe how SQL processing takes place
o Identify the tools used to access the relational database
21 Data Definition Language (DDL)
o Identify the steps needed to create database tables
o Describe the purpose of the data definition language (DDL)
o List the DDL operations needed to build and maintain a database's tables
22 Data Manipulation Language (DML)
o Describe the purpose of the data manipulation language (DML)
o Explain the DML operations that are required to manage a database's table data:
Insert
Update
Delete
23 Transaction Control Language (TCL)
o Describe the purpose of the Transaction Control Language (TCL)
o Explain the TCL operations that are required to manage a transaction:
COMMIT
SAVEPOINT
ROLLBACK
o Describe the need for read consistency
24 Retrieving Data Using SELECT
o List the capabilities of SQL SELECT statements
o Write and execute a SELECT statement that:
Returns all rows and columns from a table
Returns specific columns from a table
Uses column aliases to display descriptive column headings
Uses arithmetic and concatenation operators
Uses literal character strings
Eliminates duplicate rows
o Describe the structure of a table
25 Restricting Data Using WHERE
o Limit rows with:
WHERE clause
Comparison operators using =, <=, >=, <>, >, <, !=, ^=, BETWEEN, IN, LIKE and
NULL conditions
Logical conditions using AND, OR and NOT operators
o Describe the rules of precedence for operators in an expression
26 Sorting Data Using ORDER BY
o Use the ORDER BY clause to sort SQL query results
o Identify the correct placement of the ORDER BY clause within a SELECT statement
o Using ROWNUM for Top-N-Analysis
o Use substitution variables in the WHERE clause
27 Joining Tables Using JOIN
o Write SELECT statements to access data from more than one table using equijoins and non-
equijoins
o Use a self-join to join a table to itself
o Use OUTER joins view data that generally does not meet a join condition
o Generate a Cartesian product (cross join) of all rows from two or more tables

2. Database Design and Programming with SQL

S. No Topics
Introduction
1 Oracle Application Express
o Distinguish between application software and system software and give an example of each
o Log-in to the Oracle Application Express practice environment
o Execute a simple query to retrieve information from the Database
o Apply the rules of SQL to display all columns and a subset of columns specified by criteria
2 Relational Database Technology
o Define and give an example of a relational database
o Identify table-key terms, including row, column, field, primary key, and foreign key
o Relate the importance of databases to everyday life
3 Anatomy of a SQL Statement
o Match projection and selection with their correct capabilities
o Create a basic SELECT statement
o Use the correct syntax to display all rows in a table
o Use the correct syntax to select specific columns in a table, modify the way data is
displayed, and perform calculations
using arithmetic expressions and operators
SELECT and WHERE
4 Columns, Characters, and Rows
o Apply the concatenation operator to link columns to other columns, arithmetic expressions,
or constant values to create a
character expression
o Use column aliases to rename columns in the query result
o Enter literal values of type character, number, or date into a SELECT statement
o Define and use DISTINCT to eliminate duplicate rows
o Edit, execute, and save SQL statements in Oracle Application Express
5 Limit Rows Selected
o Apply SQL syntax to restrict the rows returned from a query
o Demonstrate application of the WHERE clause syntax
o Explain why it is important, from a business perspective, to be able to easily limit data
retrieved from a table
o Construct and produce output using a SQL query containing character strings and date
values
6 Comparison Operators
o Apply the proper comparison operator to return a desired result
o Demonstrate proper use of BETWEEN, IN, and LIKE conditions to return a desired result
o Distinguish between zero and NULL, the latter of which is unavailable, unassigned,
unknown, or inapplicable
o Explain the use of comparison conditions and NULL
WHERE, ORDER BY, and Intro to Functions
7 Logical Comparisons and Precedence Rules
o Evaluate logical comparisons to restrict the rows returned based on two or more conditions
o Apply the rules of precedence to determine the order in which expressions are evaluated
and calculated
8 Sorting Rows
o Construct a query to sort a result set in ascending or descending order
o State the order in which expressions are evaluated and calculated based on the rules of
precedence
o Construct a query to order a result set using a column alias
o Construct a query to order a result set for single or multiple columns
9 Introduction to Functions
o Identify appropriate applications of single-row functions in query statements
o Classify a function as a single-row or multi-row function
o Differentiate between single-row functions and multi-row functions and the results returned
by each
Single Row Functions Part I
10 Case and Character Manipulation
o Select and apply single-row functions that perform case conversion and/or character
manipulation
o Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in
a SQL query
o Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH,
INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query
o Write flexible queries using substitution variables
11 Number Functions
o Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL
query
o Distinguish between the results obtained when TRUNC is applied to a numeric value and
ROUND is applied to a
numeric value
o State the implications for business when applying TRUNC and ROUND to numeric values
12 Date Functions
o Demonstrate the use of SYSDATE and date functions
o State the implications for world businesses to be able to easily manipulate data stored in
date format
Single Row Functions Part II
13 Conversion Functions
o Provide an example of an explicit data-type conversion and an implicit data-type conversion
o Explain why it is important, from a business perspective, for a language to have built-in data-
conversion capabilities
o Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER, and TO_DATE
single-row functions to produce
a desired result
o Apply the appropriate date and/or character format model to produce a desired output
o Explain and apply the use of YY and RR to return the correct year as stored in the database
14 NULL Functions
o Demonstrate and explain the evaluation of a nested function
o List at least four general functions that work with any data type and relate to handling null
values
o Explain the use of the COALESCE and the NVL functions
o Explain the use of general functions to deal with null values in data
o Construct and execute a SQL query that correctly applies NVL, NVL2, NULLIF, and
COALESCE single-row functions
14 Conditional Expressions
o Compare and contrast the DECODE and CASE functions
o Construct and execute a SQL query that correctly uses the DECODE and CASE functions
o Construct and execute two methods for implementing IF-THEN-ELSE conditional logic
JOINs Part I
16 Cross Joins and Natural Joins
o Construct and execute a natural join using ANSI-99 SQL join syntax
o Create a cross join using ANSI-99 SQL join syntax
o Explain the importance of having a standard for SQL as defined by ANSI
o Describe a business need for combining information from multiple data sources
17 Join Clauses
o Construct and execute a join with the ANSI-99 USING Clause
o Construct and execute a join with the ANSI-99 ON Clause
o Construct and execute an ANSI-99 query that joins three tables
18 Inner versus Outer Joins
o Compare and contrast an inner and an outer join
o Construct and execute a query to use a left outer join
o Construct and execute a query to use a right outer join
o Construct and execute a query to use a full outer join
19 Self-Joins and Hierarchical Queries
o Construct and execute a SELECT statement to join a table to itself using a self-join
o Interpret the concept of a hierarchical query
o Create a tree-structured report
o Format hierarchical data
o Exclude branches from the tree structure
JOINs Part II

20 Oracle Equijoin and Cartesian Product

o Name the Oracle proprietary joins and their ANSI/ISO SQL: 99 counterparts
o Construct and execute a SELECT statement that results in a Cartesian product
o Construct and execute SELECT statements to access data from more than one table using
an equijoin
o Construct and execute SELECT statements that add search conditions using the AND
operator
o Apply the rule for using table aliases in a join statement
21 Oracle Non-equijoins and Outer Joins
o Construct and execute a SELECT statement to access data from more than one table using a
non-equijoin
o Create and execute a SELECT statement to access data from more than one table using an
Oracle outer join
Group Functions Part I
22 Group Functions
o Define and give an example of the seven group functions: SUM, AVG, COUNT, MIN,
MAX, STDDEV, VARIANCE
o Construct and execute a SQL query using group functions
o Construct and execute group functions that operate only with numeric data types
23 COUNT, DISTINCT, NVL
o Construct and execute a SQL query using the COUNT group function
o Use DISTINCT and the NVL function with group functions
Group Functions Part II

24 Using Group By and Having Clauses

o Construct and execute a SQL query using GROUP BY
o Construct and execute a SQL query using GROUP BY … HAVING
o Construct and execute a GROUP BY on more than one column
o Nest group functions
25 Using Rollup and Cube Operations, and Grouping Sets
o Use ROLLUP to produce subtotal values
o Use CUBE to produce cross-tabulation values
o Use GROUPING SETS to produce a single result set
o Use the GROUPING function to identify the extra row values created by either a
ROLLUP or CUBE operation
26 Using Set Operators
o Define and explain the purpose of Set Operators
o Use a set operator to combine multiple queries into a single query
o Control the order of rows returned using set operators
Subqueries
27 Fundamentals of Subqueries
o Define and explain the purpose of subqueries for retrieving data
o Construct and execute a single-row subquery in the WHERE clause
o Distinguish between single-row and multiple-row subqueries
28 Single-Row Subqueries
o Construct and execute a single-row subquery in the WHERE clause or HAVING clause
o Construct and execute a SELECT statement using more than one subquery
o Construct and execute a SELECT statement using a group function in the subquery
29 Multiple-Row Subqueries
o Correctly use the comparison operators IN, ANY, and ALL in multiple-row subqueries
o Construct and execute a multiple-row subquery in the WHERE clause or HAVING clause
o Describe what happens if a multiple-row subquery returns a null value
o Understand when multiple-row subqueries should be used, and when it is safe to use a
single-row subquery
o Distinguish between pair-wise and non-pair-wise subqueries
30 Correlated Subqueries
o Identify when correlated subqueries are needed.
o Construct and execute correlated subqueries.
o Create a query using the EXISTS and NOT EXISTS operators to test for returned rows
from the subquery
o Construct and execute named subqueries using the WITH clause
Ensuring Quality Queries Part I
31 Ensuring Quality Query Results
o Create a query to produce specified data
o Modify a query to produce specified data
DML
32 INSERT Statements
o Explain the importance of being able to alter the data in a database
o Construct and execute INSERT statements which insert a single row using a VALUES
clause
o Construct and execute INSERT statements that use special values, null values, and date
values
o Construct and execute INSERT statements that copy rows from one table to another using
a subquery
33 Updating Column Values and Deleting Rows
o Construct and execute an UPDATE statement
o Construct and execute a DELETE statement
o Construct and execute a query that uses a subquery to update and delete data from a table
o Construct and execute a query that uses a correlated subquery to update and delete from a
table
o Explain how foreign-key and primary-key integrity constraints affect UPDATE and
DELETE statements
o Explain the purpose of the FOR UPDATE Clause in a SELECT statement
34 DEFAULT Values, MERGE, and Multi-Table Inserts
o Understand when to specify a DEFAULT value
o Construct and execute a MERGE statement
o Construct and execute DML statements using subqueries
o Construct and execute multi-table inserts
DDL
35 Creating Tables
o List and categorize the main database objects
o Review a table structure
o Describe how schema objects are used by the Oracle database
o Create a table using the appropriate data type for each column
o Explain the use of external tables
o Query the Data Dictionary to obtain the names and other attributes of database objects
36 Using Data Types
o Create a table using TIMESTAMP and TIMESTAMP WITH TIME ZONE column data
types
o Create a table using INTERVAL YEAR TO MONTH and INTERVAL DAY TO
SECOND column data types
o Give examples of organizations and personal situations where it is important to know to
which time zone a date-time
value refers
o List and provide an example of each of the number, date, and character data types
37 Modifying a Table
o Explain why it is important to be able to modify a table
o Explain and provide an example for each of the DDL statements—ALTER, DROP,
RENAME, and TRUNCATE—and the
effect each has on tables and columns
o Construct a query and execute the ALTER TABLE commands ADD, MODIFY, and
DROP
o Explain and perform FLASHBACK QUERY on a table
o Explain and perform FLASHBACK table operations
o Track the changes to data over a period of time
o Explain the rationale for using TRUNCATE versus DELETE for tables
o Add a comment to a table using the COMMENT ON TABLE command
o Name the changes that can and cannot be made to modify a column
o Explain when and why the SET UNUSED statement is advantageous
Constraints
38 Intro to Constraints; NOT NULL and UNIQUE Constraints
o Define the term "constraint" as it relates to data integrity
o State when it is possible to define a constraint at the column level, and when it is possible
at the table level
o State why it is important to give meaningful names to constraints
o State which data integrity rules are enforced by NOT NULL and UNIQUE constraints
o Write a CREATE TABLE statement which includes NOT NULL and UNIQUE
constraints at the table and column levels
o Explain how constraints are created at the time of table creation
39 PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
o Define and give an example of a PRIMARY KEY, FOREIGN KEY, and CHECK
constraint
o Explain the purpose of defining PRIMARY KEY, FOREIGN KEY, and CHECK
constraints
o Demonstrate the creation of constraints at the column level and table level in a CREATE
TABLE statement
o Evaluate a business problem requiring the addition of a PRIMARY KEY and FOREIGN
KEY constraint and write the
code to execute the change
40 Managing Constraints
o List four different functions that the ALTER statement can perform on constraints
o Write ALTER TABLE statements to add, drop, disable, and enable constraints
o Name a business function that would require a DBA to drop, enable, and/or disable a
constraint or use the CASCADE
syntax
o Query the data dictionary for USER_CONSTRAINTS and interpret the information
returned
Views
41 Creating Views
o List three uses for views from the standpoint of a database administrator
o Explain, from a business perspective, why it is important to be able to create and use logical
subsets of data derived
from one or more tables
o Create a view with and without column aliases in the subquery using a single base table
o Create a complex view that contains group functions to display values from two tables
o Retrieve data from a view
42 DML Operations and Views
o Write and execute a query that performs DML operations on a simple view
o Name the conditions that restrict your ability to modify a view using DML operations
o Write and execute a query using the WITH CHECK OPTION clause
o Explain the use of WITH CHECK OPTION as it applies to integrity constraints and data
validation
o Apply the WITH READ ONLY option to a view to restrict DML operations
43 Managing Views
o Create and execute a SQL statement that removes a view
o Create and execute a query using an inline view
o Create and execute a top-n-analysis query
Sequences and Synonyms
44 Working With Sequences
o List at least three useful characteristics of a sequence
o Write and execute a SQL statement that creates a sequence
o Query the data dictionary using USER_SEQUENCES to confirm a sequence definition
o Apply the rules for using NEXTVAL to generate sequential unique numbers in a table
o List the advantages and disadvantages of caching sequence values
o Name three reasons why gaps can occur in a sequence
45 Indexes and Synonyms
o Define an index and its use as a schema object
o Name the conditions that cause an index to be created automatically
o Create and execute a CREATE INDEX and DROP INDEX statement
o Create and execute a function-based index
o Create private and public synonyms
Privileges and Regular Expressions
46 Controlling User Access
o Compare the difference between object privileges and system privileges
o Construct the two commands required to enable a user to have access to a database
o Construct and execute a GRANT… ON …TO statement to assign privileges to objects in
their schema to other users
and/or PUBLIC
o Query the data dictionary to confirm privileges granted
47 Creating and Revoking Object Privileges
o Explain what a ROLE is and what its advantages are
o Construct a statement to create a ROLE and GRANT privileges to it
o Construct a GRANT .. ON .. TO.. WITH GRANT OPTION statement to assign privileges
on objects in your schema to
other users and/or PUBLIC
o Construct and execute a statement to REVOKE object privileges from other users and/or
from PUBLIC
o Distinguish between privileges and roles
o Explain the purpose of a database link
48 Regular Expressions
o Describe regular expressions
o Use regular expressions to search, match, and replace strings in SQL statements
o Construct and execute regular expressions and check constraints
TCL
49 Database Transactions
o Define the terms COMMIT, ROLLBACK, and SAVEPOINT as they relate to data
transactions
o List three advantages of the COMMIT, ROLLBACK, and SAVEPOINT statements
o Explain why it is important, from a business perspective, to be able to control the flow of
transaction processing
Final Project and Exam Review
50 Testing
o Develop and apply a strategy for testing that a database functions as designed
51 Final Project Database Creation
o Apply SQL concepts to create a functional database appropriate for a small business
52 Final Exam Review
o Review the key points about case and character manipulation
o Review number, date, conversion, and general functions
o Review conditional expressions
o Review Cartesian product and join operations
o Review non-equijoins, outer joins, self joins, cross joins, natural joins, and join clauses
o Review group functions, group by syntax, and having clauses
o Review single-row and multiple row subqueries
o Review pair-wise and non-pair-wise subqueries
o Review correlated subqueries
o Review DML statements insert, update, delete, merge, and multi-table inserts
o Review DDL statements CREATE, ALTER, RENAME, TRUNCATE, FLASHBACK
TABLE, DROP, and FLASHBACK
QUERY
o Review DCL statements CREATE and REVOKE object privileges
Ensuring Quality Queries Part II
53 Ensuring Quality Query Results - Advanced Techniques
o Create an advanced query to produce specified data
o Modify an advanced query to produce specified data
3. Database Programming with PL/SQL
S. No. Topics
Fundamentals
1 Introduction to PL/SQL
o Describe PL/SQL
o Differentiate between SQL and PL/SQL
o Explain the need for PL/SQL
2 Benefits of PL/SQL
o List and explain the benefits of PL/SQL
o List differences between PL/SQL and other programming languages
o Give examples of how PL/SQL can be used in other Oracle products
3 Creating PL/SQL Blocks
o Describe the structure of a PL/SQL block
o Identify the different types of PL/SQL blocks
o Identify PL/SQL programming environments
o Create and execute an anonymous PL/SQL block
o Output messages in PL/SQL
Defining Variables and Datatypes
4 Using Variables in PL/SQL
o List the uses of variables in PL/SQL
o Identify the syntax for variables in PL/SQL
o Declare and initialize variables in PL/SQL
o Assign new values to variables in PL/SQL
5 Recognizing PL/SQL Lexical Units
o List and define the different types of lexical units available in PL/SQL
o Describe identifiers and identify valid and invalid identifiers in PL/SQL
o Describe and identify reserved words, delimiters, literals, and comments in
PL/SQL
6 Recognizing Data Types
o Define data type and explain why it is needed
o List and describe categories of data types
o Give examples of scalar and composite data types
7 Using Scalar Data Types
o Declare and use scalar data types in PL/SQL
o Define guidelines for declaring and initializing PL/SQL variables
o Identify the benefits of anchoring data types with the %TYPE attribute
8 Writing PL/SQL Executable Statements
o Construct accurate variable assignment statements in PL/SQL
o Construct accurate statements using built-in SQL functions in PL/SQL
o Differentiate between implicit and explicit conversions of data types
o Describe when implicit conversions of data types take place
o List drawbacks of implicit data type conversions
o Construct accurate statements using functions to explicitly convert data types
o Construct statements using operators in PL/SQL
9 Nested Blocks and Variable Scope
o Understand the scope and visibility of variables
o Write nested blocks and qualify variables with labels
o Describe the rules for variable scope when a variable is nested in a block
o Recognize a variable scope issue when a variable is used in nested blocks
10 Good Programming Practices
o List examples of good programming practices
o Accurately insert comments into PL/SQL code
o Create PL/SQL code that follows formatting guidelines to produce readable code
Using SQL in PL/SQL
11 Review of SQL DML
o Insert data into a database table
o Update data in a database table
o Delete data from a database table
o Merge data into a database table
12 Retrieving Data in PL/SQL
o Recognize the SQL statements that can be directly included in a PL/SQL
executable block
o Construct and execute an INTO clause to hold the values returned by a single-row
SQL SELECT statement
o Construct statements to retrieve data that follow good practice guidelines
o Construct statements that apply good practice guidelines for naming variables
13 Manipulating Data in PL/SQL
o Construct and execute PL/SQL statements that manipulate data with DML
statements
o Describe when to use implicit or explicit cursors in PL/SQL
o Create PL/SQL code to use SQL implicit cursor attributes to evaluate cursor
activity
14 Using Transaction Control Statements
o Define a transaction and provide an example
o Construct and execute a transaction control statement in PL/SQL
o Since Oracle Application Express automatically commits changes, the following
information will be presented as if
you were issuing the commands in an installed/local environment with the ability to
use COMMIT and ROLLBACK
Program Structures to Control Execution Flow
15 Conditional Control: IF Statements
o Describe a use for conditional control structures
o List the types of conditional control structures
o Construct and use an IF statement
o Construct and use an IF-THEN-ELSIF-ELSE statement
o Create PL/SQL to handle the null condition in IF statements
16 Conditional Control: Case Statements
o Construct and use CASE statements in PL/SQL
o Construct and use CASE expressions in PL/SQL
o Include the correct syntax to handle null conditions in PL/SQL CASE statements
o Include the correct syntax to handle Boolean conditions in PL/SQL IF and CASE
statements
17 Iterative Control: Basic Loops
o Describe the need for LOOP statements in PL/SQL
o Recognize different types of LOOP Statements
o Create PL/SQL containing a basic loop and an EXIT statement
o Create PL/SQL containing a basic loop and an EXIT statement with conditional
termination
18 Iterative Control: While and For Loops
o Construct and use the WHILE looping construct in PL/SQL
o Construct and use the FOR looping construct in PL/SQL
o Describe when a WHILE loop is used in PL/SQL
o Describe when a FOR loop is used in PL/SQL
19 Iterative Control: Nested Loops
o Construct and execute PL/SQL using nested loops
o Label loops and use the labels in EXIT statements
o Evaluate a nested loop construct and identify the exit point
Using Cursors and Parameters
20 Introduction to Explicit Cursors
o Distinguish between an implicit and an explicit cursor
o Describe why and when to use an explicit cursor in PL/SQL code
o List two or more guidelines for declaring and controlling explicit cursors
o Create PL/SQL code that successfully opens a cursor and fetches a piece of data
into a variable
o Use a simple loop to fetch multiple rows from a cursor
o Create PL/SQL code that successfully closes a cursor after fetching data into a
variable
21 Using Explicit Cursor Attributes
o Use the %ROWTYPE attribute to define a record based on a cursor
o Create PL/SQL code to process the row of an active set using record types in
cursors
o Retrieve information about the state of an explicit cursor using cursor attributes
22 Cursor FOR Loops
o List and explain the benefits of using cursor FOR loops
o Create PL/SQL code to declare a cursor and manipulate it in a FOR loop
o Create PL/SQL code containing a cursor FOR loop using a subquery
23 Cursors with Parameters
o List the benefits of using parameters with cursors
o Create PL/SQL code to declare and manipulate a cursor with a parameter
24 Using Cursors For Update
o Create PL/SQL code to lock rows before an update using the appropriate clause
o Explain the effect of using NOWAIT in a update cursor declaration
o Create PL/SQL code to use the current row of the cursor in an UPDATE or
DELETE statement
25 Using Multiple Cursors
o Explain the need for using multiple cursors to produce multilevel reports
o Create PL/SQL code to declare and manipulate multiple cursors within nested
loops
o Create PL/SQL code to declare and manipulate multiple cursors using parameters
Using Composite Datatypes
26 User-Defined Records
o Create and manipulate user-defined PL/SQL records
o Define a record structure using the %ROWTYPE attribute
27 Indexing Tables of Records
o Create an INDEX BY table
o Create an INDEX BY table of records
o Describe the difference between records, tables, and tables of records
Exception Handling
28 Handling Exceptions
o Describe several advantages of including exception handling code in PL/SQL
o Describe the purpose of an EXCEPTION in a PL/SQL block
o Create PL/SQL code to include an EXCEPTION section
o List several guidelines for exception handling
29 Trapping Oracle Server Exceptions
o Describe and provide an example of an error defined by the Oracle server
o Describe and provide an example of an error defined by the PL/SQL programmer
o Differentiate between errors that are handled implicitly and explicitly by the
Oracle Server
o Write PL/SQL code to trap a predefined Oracle Server error
o Write PL/SQL code to trap a non-predefined Oracle Server error
o Write PL/SQL code to identify an exception by error code and by error message
30 Trapping User-Defined Exceptions
o Write PL/SQL code to name a user-defined exception
o Write PL/SQL code to raise an exception
o Write PL/SQL code to handle a raised exception
o Write PL/SQL code to use RAISE_APPLICATION_ERROR
31 Recognizing the Scope of Exceptions
o Describe the scope of an exception
o Recognize an exception-scope issue when an exception is within nested blocks
o Describe the effect of exception propagation in nested blocks
Using and Managing Procedures
32 Creating Procedures
o Differentiate between anonymous blocks and subprograms
o Identify benefits of subprograms
o Define a stored procedure
o Create a procedure
o Describe how a stored procedure is invoked
o List the development steps for creating a procedure
o Create a nested subprogram in the declarative section of a procedure
33 Using Parameters in Procedures
o Describe how parameters contribute to a procedure
o Define a parameter
o Create a procedure using a parameter
o Invoke a procedure that has parameters
o Differentiate between formal and actual parameters
34 Passing Parameters
o List the types of parameter modes
o Create a procedure that passes parameters
o Identify three methods for passing parameters
o Describe the DEFAULT option for parameters
Using and Managing Functions
35 Creating Functions
o Define a stored function
o Create a PL/SQL block containing a function
o List ways in which a function can be invoked
o Create a PL/SQL block that invokes a function that has parameters
o List the development steps for creating a function
o Describe the differences between procedures and functions
36 Using Functions in SQL Statements
o List the advantages of user-defined functions in SQL statements
o List where user-defined functions can be called from within an SQL statement
o Describe the restrictions on calling functions from SQL statements
37 Review of the Data Dictionary
o Describe the purposes of the Data Dictionary
o Differentiate between the three types of Data Dictionary view
o Write SQL SELECT statements to retrieve information from the Data Dictionary
o Explain the use of DICTIONARY as a Data Dictionary search engine
38 Managing Procedures and Functions
o Describe how exceptions are propagated
o Remove a function and a procedure
o Use Data Dictionary views to identify and manage stored programs
39 Review of Object Privileges
o List and explain several object privileges
o Explain the function of the EXECUTE object privilege
o Write SQL statements to grant and revoke object privileges
40 Using Invoker's Rights and Autonomous Transactions
o Contrast invoker's rights with definer's rights
o Create a procedure that uses invoker's rights
o Create a procedure that executes an Autonomous Transaction
Using and Managing Packages
41 Creating Packages
o Describe the reasons for using a package
o Describe the two components of a package: specification and body
o Create packages containing related variables, cursors, constants, exceptions,
procedures, and functions
o Create a PL/SQL block that invokes a package construct
42 Managing Package Concepts
o Explain the difference between public and private package constructs
o Designate a package construct as either public or private
o Specify the appropriate syntax to drop packages
o Identify views in the Data Dictionary that manage packages
o Identify guidelines for using packages
43 Advanced Package Concepts
o Write packages that use the overloading feature
o Write packages that use forward declarations
o Explain the purpose of a package initialization block
o Create and use a bodiless package
o Invoke packaged functions from SQL
o Identify restrictions on using packaged functions in SQL statements
o Create a package that uses PL/SQL tables and records
Getting the Best out of Packages
44 Persistent State of Package Variables
o Identify persistent states of package variables
o Control the persistent state of a package cursor
45 Using Oracle-Supplied Packages
o Describe two common uses for the DBMS_OUTPUT server-supplied package
o Recognize the correct syntax to specify messages for the DBMS_OUTPUT
package
o Describe the purpose for the UTL_FILE server-supplied package.
o Recall the exceptions used in conjunction with the UTL_FILE server-supplied
package.
o Describe the main features of the UTL_MAIL server-supplied package
Improving PL/SQL Performance
46 Using Dynamic SQL
o Recall the stages through which all SQL statements pass
o Describe the reasons for using dynamic SQL to create an SQL statement
o List four PL/SQL statements supporting Native Dynamic SQL
o Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for
Dynamic SQL
47 Improving PL/SQL Performance
o Identify the benefits of the NOCOPY hint and the DETERMINISTIC clause
o Create subprograms that use the NOCOPY hint and the DETERMINISTIC clause
o Use Bulk Binding FORALL in a DML statement
o Use BULK COLLECT in a SELECT or FETCH statement
o Use the Bulk Binding RETURNING clause
Using and Managing Triggers
48 Introduction to Triggers
o Describe database triggers and their uses
o Define a database trigger
o Recognize the difference between a database trigger and an application trigger
o List two or more guidelines for using triggers
o Compare and contrast database triggers and stored procedures
49 Creating DML Triggers: Part I
o Create a DML trigger
o List the DML trigger components
o Create a statement level trigger
o Describe the trigger firing sequence options
50 Creating DML Triggers: Part II
o Create a DML trigger that uses conditional predicates
o Create a row-level trigger
o Create a row-level trigger that uses OLD and NEW qualifiers
o Create an INSTEAD OF trigger
o Create a Compound Trigger
51 Creating DDL and Database Event Triggers
o Describe events that cause DDL and database event triggers to fire
o Create a trigger for a DDL statement
o Create a trigger for a database event
o Describe the functionality of the CALL statement
o Describe the cause of a mutating table
52 Managing Triggers
o View trigger information in the Data Dictionary
o Disable and enable a database trigger
o Remove a trigger from the database
Recognizing and Managing Dependencies
53 Introduction to Dependencies
o Describe the implications of procedural dependencies
o Contrast dependent objects and referenced objects
o View dependency information in the data dictionary
o Use the UTLDTREE script to create the objects required to display dependencies
o Use the IDEPTREE and DEPTREE views to display dependencies
o Describe when automatic recompilation occurs
o List how to minimize dependency failures
54 Understanding Remote Dependencies
o Describe remote dependencies
o List how remote dependencies are controlled
o Describe when a remote dependency is unsuccessfully recompiled
o Describe when a remote dependency is successfully recompiled
Using the PL/SQL Compiler
55 Using PL/SQL Initialization Parameters
o Describe how PLSQL_CODE_TYPE can improve execution speed
o Describe how PLSQL_OPTIMIZE_LEVEL can improve execution speed
o Use USER_PLSQL_OBJECT_SETTINGS to see how a PL/SQL program was
compiled
56 Displaying Compiler Warning Messages
o Explain the similarities and differences between a warning and an error
o Compare and contrast the warning levels which can be set by the
PLSQL_WARNINGS parameter
o Set warning levels by calling the DBMS_WARNING server-supplied package
from within a PL/SQL program
57 Using Conditional Compilation
o Describe the benefits of conditional compilation
o Create and conditionally compile a PL/SQL program containing selection, inquiry
and error directives
o Create and conditionally compile a PL/SQL program which calls the
DBMS_DB_VERSION server-supplied package
58 Hiding your Source Code
o Describe the benefits of obfuscated PL/SQL source code
o Use the DBMS_DDL.CREATE_WRAPPED server-supplied procedure
o Describe how to use the Wrapper utility to obfuscate PL/SQL source code