# CTE Resource Center - Verso - Database Design and Management with PL/SQL (Oracle) Task/Competency List

CTE Resource Center - Verso

Virginia’s CTE Resource Center

2019/2020 Competency-Based Task/Competency List for Database Design and Management with PL/SQL (Oracle) (6662/36 weeks)

Tasks/competencies bordered in blue are considered optional when marked as such; they and/or additional tasks/competencies may be taught at the discretion of the school division. All other tasks are considered essential statewide and are required of all students.

Expand all

Ajax loader

Demonstrating Personal Qualities and Abilities

  1. Demonstrate creativity and innovation.
  2. Demonstrate critical thinking and problem solving.
  3. Demonstrate initiative and self-direction.
  4. Demonstrate integrity.
  5. Demonstrate work ethic.

Demonstrating Interpersonal Skills

  1. Demonstrate conflict-resolution skills.
  2. Demonstrate listening and speaking skills.
  3. Demonstrate respect for diversity.
  4. Demonstrate customer service skills.
  5. Collaborate with team members.

Demonstrating Professional Competencies

  1. Demonstrate big-picture thinking.
  2. Demonstrate career- and life-management skills.
  3. Demonstrate continuous learning and adaptability.
  4. Manage time and resources.
  5. Demonstrate information-literacy skills.
  6. Demonstrate an understanding of information security.
  7. Maintain working knowledge of current information-technology (IT) systems.
  8. Demonstrate proficiency with technologies, tools, and machines common to a specific occupation.
  9. Apply mathematical skills to job-specific tasks.
  10. Demonstrate professionalism.
  11. Demonstrate reading and writing skills.
  12. Demonstrate workplace safety.

Examining All Aspects of an Industry

  1. Examine aspects of planning within an industry/organization.
  2. Examine aspects of management within an industry/organization.
  3. Examine aspects of financial responsibility within an industry/organization.
  4. Examine technical and production skills required of workers within an industry/organization.
  5. Examine principles of technology that underlie an industry/organization.
  6. Examine labor issues related to an industry/organization.
  7. Examine community issues related to an industry/organization.
  8. Examine health, safety, and environmental issues related to an industry/organization.

Addressing Elements of Student Life

  1. Identify the purposes and goals of the student organization.
  2. Explain the benefits and responsibilities of membership in the student organization as a student and in professional/civic organizations as an adult.
  3. Demonstrate leadership skills through participation in student organization activities, such as meetings, programs, and projects.
  4. Identify Internet safety issues and procedures for complying with acceptable use standards.

Exploring Work-Based Learning

  1. Identify the types of work-based learning (WBL) opportunities.
  2. Reflect on lessons learned during the WBL experience.
  3. Explore career opportunities related to the WBL experience.
  4. Participate in a WBL experience, when appropriate.

Introducing PL/SQL Programming Concepts

  1. Explain PL/SQL.
  2. Differentiate between SQL and PL/SQL.
  3. Explain the need for PL/SQL.
  4. Describe the structure of a PL/SQL block.
  5. Identify the different types of PL/SQL blocks.

Using PL/SQL Program Data

  1. Use variables in PL/SQL.
  2. Describe valid and invalid identifiers in PL/SQL.
  3. Describe reserved words, delimiters, literals, and comments in PL/SQL.
  4. List data types used in PL/SQL.
  5. Identify the benefits of anchoring data types with the %TYPE attribute.
  6. Use built-in SQL functions in PL/SQL.
  7. Differentiate between implicit and explicit conversions.
  8. Demonstrate how functions can be used to explicitly convert data types.

Using SQL in PL/SQL

  1. Evaluate the SQL statements that can be directly included in a PL/SQL executable block.
  2. Retrieve data in PL/SQL.
  3. Manipulate data with DML statements in PL/SQL.
  4. Use SQL cursor attributes.
  5. Use transaction control statements in PL/SQL.

Constructing PL/SQL Program Structures to Control Execution Flow

  1. Identify the uses and types of conditional control structures.
  2. Construct and use an IF statement.
  3. Construct and use an IF-THEN-ELSE statement.
  4. Use basic loops with EXIT conditions.
  5. Use basic loops with EXIT WHEN conditions.
  6. Use WHILE loops.
  7. Use FOR loops.
  8. Use nested loops.

Using Cursors

  1. Distinguish between an implicit and an explicit cursor.
  2. Declare and control explicit cursors.
  3. Use cursor simple LOOP to FETCH data.
  4. Use cursor FOR LOOP to fetch data.
  5. Declare and use cursors with parameters.
  6. Lock rows using the FOR UPDATE clause.
  7. Reference the current row with the WHERE CURRENT clause.
  8. Use multiple cursors.

Using Exception Handling

  1. Describe the function of exceptions.
  2. Describe the function of an exception handler.
  3. Handle exceptions in PL/SQL programs.
  4. Trap predefined Oracle server exceptions.
  5. Trap non-predefined Oracle server errors.
  6. Trap user-defined errors.
  7. Describe scope of exceptions.

Examining PL/SQL Composite Data Types

  1. Describe user-defined PL/SQL records.
  2. Create a user-defined PL/SQL record.
  3. Use PL/SQL INDEX BY Table and INDEX BY Table of Records.

Creating and Managing Procedures

  1. Identify the characteristics and benefits of a stored procedure.
  2. Invoke a stored procedure.
  3. List the steps for creating a procedure.
  4. Create a procedure with parameters.
  5. Invoke a procedure that has parameters.
  6. List the types of parameter modes.
  7. Describe the DEFAULT option for parameters.
  8. Describe the method for propagating exceptions.
  9. Remove a procedure.
  10. Identify how to view and manage procedures.

Creating and Managing Functions

  1. Define stored function.
  2. Create a function.
  3. List the procedures for invoking a function.
  4. List the advantages of user-defined functions in SQL statements.
  5. List circumstances in which user-defined functions can be called from within a SQL statement.
  6. Describe the restrictions on calling functions from SQL statements.
  7. Remove a function.
  8. Identify how to view stored objects in the data dictionary.
  9. Identify differences between invoker and definer rights.

Designing Packages

  1. Identify a package specification and body.
  2. Create packages (e.g., related variables, cursors, constants, exceptions, procedures, and functions).
  3. Invoke a package construct.
  4. Designate package constructs as public or private.
  5. Drop packages.
  6. Identify benefits of packages.
  7. Create packages that use the overloading feature.
  8. Identify restrictions on using packaged functions in SQL statements.
  9. Invoke packaged functions from SQL.
  10. Identify persistent states in package variables and cursors.
  11. Describe the use and application of some Oracle server-supplied packages (e.g., DBMS_SQL, DBMS_OUTPUT, and UTL_FILE).
  12. Use EXECUTE IMMEDIATE.
  13. Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for native dynamic SQL.

Creating and Removing Triggers

  1. Describe database triggers and types and the uses of each.
  2. Create a DML trigger.
  3. List the DML trigger components.
  4. Create a statement-level trigger.
  5. Describe the trigger-firing sequence options.
  6. Use conditional predicates in a DML trigger.
  7. Create a row-level trigger.
  8. Use the OLD and NEW qualifiers in a database trigger.
  9. Create an INSTEAD OF trigger.
  10. Describe events that cause database triggers to fire.
  11. Create a trigger for a DDL statement.
  12. Create a trigger for a system event.
  13. Describe the functionality of the CALL statement.
  14. Describe the effect of a mutating table.
  15. View trigger information in the dictionary views.
  16. Alter a trigger status.
  17. Remove a trigger.

Using Advanced Data Types

  1. Describe the function of LOB (large object) data types.
  2. Migrate from LONG to LOB.
  3. Manage BFILES.

Understanding Procedural Dependencies

  1. Describe the implications of procedural dependencies.
  2. Describe dependent objects and referenced objects.
  3. View dependency information in the dictionary views.
  4. Use the UTLDTREE script.
  5. Use the IDEPTREE and DEPTREE views.
  6. List the procedures for minimizing dependency failures.

Using the PL/SQL Compiler

  1. Describe PL/SQL initialization parameters.
  2. Use PL/SQL initialization parameters.
  3. Identify compiler warning messages.
  4. Describe conditional compilation.
  5. Describe the hiding of source code.

Preparing for Industry Certification

  1. Describe the process and requirements for obtaining industry certifications related to the Database Design and Management with PL/SQL (Oracle) course.
  2. Identify testing skills and strategies for a certification examination.
  3. Demonstrate ability to successfully complete selected practice examinations (e.g., practice questions similar to those on certification exams).
  4. Complete an industry certification examination representative of skills learned in this course (e.g., MOS, MTA, IC3).

Developing Employability Skills

  1. Investigate job opportunities, using the Internet and other sources.
  2. Investigate careers in the information technology field.
  3. Research certification and educational opportunities.
  4. Compose or update a printed résumé.
  5. Prepare an electronic résumé.
  6. Create a letter of application.
  7. Complete an electronic application form.
  8. Create an interview follow-up letter.
  9. Identify the steps to follow in resigning from a position.
  10. Develop a portfolio containing representative samples of student's work (e.g., program design, technical documentation and design, source code, and output).
  11. Deliver an oral presentation of portfolio.
  12. Identify potential employment barriers for nontraditional groups and ways to overcome barriers.