Retrieving Data Using the SQL SELECT Statement · List the capabilities of SQL SELECT statements · Execute a basic SELECT statement Restricting and Sorting Data · Limit the rows that are retrieved by a query · Sort the rows that are retrieved by a query · Use ampersand substitution to restrict and sort output at runtime Using Single-Row Functions to Customize Output · Describe various types of functions available in SQL · Use character, number, and date functions in SELECT statements Using Conversion Functions and Conditional Expressions · Describe various types of conversion functions that are available in SQL · Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions · Apply conditional expressions in a SELECT statement Reporting Aggregated Data Using the Group Functions · Identify the available group functions · Describe the use of group functions · Group data by using the GROUP BY clause · Include or exclude grouped rows by using the HAVING clause Displaying Data from Multiple Tables · Write SELECT statements to access data from more than one table using equijoins and nonequijoins · Join a table to itself by using a self-join · View data that generally does not meet a join condition by using outer joins · Generate a Cartesian product of all rows from two or more tables Using Subqueries to Solve Queries · Define subqueries · Describe the types of problems that the subqueries can solve · List the types of subqueries · Write single-row and multiple-row subqueries Using the Set Operators · Describe set operators · Use a set operator to combine multiple queries into a single query · Control the order of rows returned Manipulating Data · Describe each data manipulation language (DML) statement · Insert rows into a table · Update rows in a table · Delete rows from a table · Control transactions Using DDL Statements to Create and Manage Tables · Categorize the main database objects · Review the table structure · List the data types that are available for columns · Create a simple table · Explain how constraints are created at the time of table creation · Describe how schema objects work Creating Other Schema Objects · Create simple and complex views · Retrieve data from views · Create, maintain, and use sequences · Create and maintain indexes · Create private and public synonyms | Oracle Database 11g: PL/SQL Fundamentals · Introduction to PL/SQL · Explain the need for PL/SQL · Explain the benefits of PL/SQL · Identify the different types of PL/SQL blocks · Output messages in PL/SQL · Declaring PL/SQL Variables · Recognize valid and invalid identifiers · List the uses of variables, declare and initialize variables, use bind variables · List and describe various data types using the %TYPE attribute · Writing Executable Statements · Identify lexical units in a PL/SQL block · Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions · Describe when implicit conversions take place and when explicit conversions have to be dealt with · Write nested blocks and qualify variables with labels · Write readable code with appropriate indentation · Interacting with the Oracle Database Server · Create PL/SQL executable blocks using DML and transaction control statements · Make use of the INTO clause to hold the values returned by a SQL statement · Writing Control Structures · Identify the uses and types of control structures (IF, CASE statements and expressions) · Construct and identify loop statements · Apply guidelines when using conditional control structures · Working with Composite Data Types · Create user-defined PL/SQL records · Create a record with the %ROWTYPE attribute · Create an INDEX BY table and INDEX BY table of records · Describe the differences among records, tables, and tables of records · Using Explicit Cursors · Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes · Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data · Declare and use cursors with parameters · Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause · Handling Exceptions · Define PL/SQL exceptions · Recognize unhandled exceptions · Handle different types of exceptions (pre-defined exceptions, non-predefined exceptions and user-defined exceptions) · Propagate exceptions in nested blocks and call applications · Creating Stored Procedures and Functions · Differentiate between anonymous blocks and subprograms · Create a simple procedure and invoke it from an anonymous block · Create a simple function · Create a simple function that accepts a parameter · Differentiate between procedures and functions Oracle Database 11g: Develop PL/SQL Program Units · Creating Procedures · Differentiate between anonymous blocks and subprograms, use a modularized and layered subprogram design, and identify the benefits of subprograms · Create a simple procedure and invoke it from an anonymous block · Work with procedures · Handle exceptions in procedures, remove a procedure, and display a procedure's information · Creating Functions · Differentiate between a procedure and a function · Describe the uses of functions · Work with functions (create, invoke and remove functions) · Creating Packages · Identify the benefits and the components of packages · Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information) · Working with Packages · Overload package subp rograms, use forward declarations · Create an initialization block in a package body · Manage persistent package data states for the life of a session and use PL/SQL tables and records in packages · Using Oracle-Supplied Packages in Application Development · Describe how the DBMS_OUTPUT package works · Use UTL_FILE to direct output to operating system files · Describe the main features of UTL_MAIL · Using Dynamic SQL · Describe the execution flow of SQL statements · Use Native Dynamic SQL (NDS) · Use the DBMS_SQL package · Design Considerations for PL/SQL Code · Create standard constants and exceptions · Write and call local subprograms · Control the run-time privileges of a subprogram · Perform autonomous transactions · Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause · Use bulk binding and the RETURNING clause with DML · Creating Triggers · Describe different types of triggers and their uses · Create database triggers · Manage triggers · Creating Compound, DDL, and Event Database Triggers · Create triggers on DDL statements · Create triggers on system events · Using the PL/SQL Compiler · Describe the new PL/SQL compiler and features · Use the new PL/SQL compiler initialization parameters · Use the new PL/SQL compile time warnings · Managing PL/SQL Code · Describe and use conditional compilation · Hide PL/SQL source code using dynamic obfuscation and the Wrap utility · Managing Dependencies · Track and manage procedural dependencies | Oracle11g: Advanced PL/SQL · Introduction to PL/SQL PL/SQL Programming Concepts: Review · List restrictions on calling functions from SQL expressions · Hand exceptions · Manage dependencies · Use Oracle-supplied packages Designing PL/SQL Code · Identify guidelines for cursor design · Use cursor variables · Create subtypes based on existing types Working with Collections · Create collections (nested table, array and associative arrays, PL SQL tables) · Use collections methods · Manipulate collections · Distinguish between the different types of collections and their uses Using Advanced Interface Methods · Execute external C programs from PL/SQL · Execute Java programs from PL/SQL Implementing Fine-Grained Access Control for VPD · Explain the process of fine-grained access control · Implement and test fine-grained access control Manipulating Large Objects · Create and manage LOB data types · Use the DBMS_LOB PL/SQL package · Use of temporary LOBs Administering SecureFile LOBs · Describe SecureFile LOB features · Enable SecureFile LOB deduplication, compression, and encryption · Migrate BasicFile LOBs to the SecureFile LOB format Performance and Tuning · Use native and interpreted compilation methods · Tune PL/SQL code · Enable intraunit inlining Improving Performance with Caching · Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package · Write queries that use the result cache hint · Set up PL/SQL functions to use PL/SQL result caching Analyzing PL/SQL Code · Run reports on source code · Determine identifier types and usages · Use DBMS_METADATA to retrieve object definitions Profiling and Tracing PL/SQL Code · Trace PL/SQL program execution · Profile PL/SQL applications Safeguarding Your Code Against SQL Injection Attacks · Describe SQL injections · Reduce attack surfaces · Use DBMS_ASSERT · Design immune code · Test code for SQL injection flaws |
