ALPHAPRIMETECH
112 New South Road,
Hicksville, NY
11801
+1 (212) 629 - 7366
admin@alphaprimetech.org

• Identify the connection between an ERD and a Relational Database
• Explain the relationship between a database and SQL
• Describe the purpose of DDL
• Describe the purpose of DML
• Build a SELECT statement to retrieve data from an Oracle Database table

• Use the ORDER BY clause to sort SQL query results
• Limit the rows that are retrieved by a query
• Use ampersand substitution to restrict and sort output at runtime
• Use SQL row limiting clause

• Use various types of functions available in SQL
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements

• Describe various types of conversion functions that are available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Apply general functions and conditional expressions in a SELECT statement

• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

• Describe the different types of joins and their features
• Use 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

• Define subqueries
• Describe the types of problems subqueries can solve
• Describe the types of subqueries
• Query data using correlated subqueries
• Update and delete rows using correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause
• Use single-row and multiple-row subqueries

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

• Truncate data
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

• Describe data types that are available for columns
• Create a simple table
• Create constraints for tables
• Drop columns and set column UNUSED
• Create and use external tables

• Query various data dictionary views

• Differentiate system privileges from object privileges
• Grant privileges on tables and on a user
• Distinguish between privileges and roles

• Describe how schema objects work
• Create simple and complex views with visible/invisible columns
• Create, maintain and use sequences
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• Perform flashback operations

• Describe the features of multitable INSERTs
• Merge rows in a table

• 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, nonpredefined 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

• 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 subprograms, 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