2019/2020 Competency-Based Task/Competency List for Database Design and Management with PL/SQL (Oracle) (6662/36 weeks)
Tasks/competencies 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.are considered optional
Demonstrating Personal Qualities and Abilities
Demonstrating Interpersonal Skills
Demonstrating Professional Competencies
- Demonstrate big-picture thinking.
- Demonstrate career- and life-management skills.
- Demonstrate continuous learning and adaptability.
- Manage time and resources.
- Demonstrate information-literacy skills.
- Demonstrate an understanding of information security.
- Maintain working knowledge of current information-technology (IT) systems.
- Demonstrate proficiency with technologies, tools, and machines common to a specific occupation.
- Apply mathematical skills to job-specific tasks.
- Demonstrate professionalism.
- Demonstrate reading and writing skills.
- Demonstrate workplace safety.
Examining All Aspects of an Industry
- Examine aspects of planning within an industry/organization.
- Examine aspects of management within an industry/organization.
- Examine aspects of financial responsibility within an industry/organization.
- Examine technical and production skills required of workers within an industry/organization.
- Examine principles of technology that underlie an industry/organization.
- Examine labor issues related to an industry/organization.
- Examine community issues related to an industry/organization.
- Examine health, safety, and environmental issues related to an industry/organization.
Addressing Elements of Student Life
- Identify the purposes and goals of the student organization.
- Explain the benefits and responsibilities of membership in the student organization as a student and in professional/civic organizations as an adult.
- Demonstrate leadership skills through participation in student organization activities, such as meetings, programs, and projects.
- Identify Internet safety issues and procedures for complying with acceptable use standards.
Exploring Work-Based Learning
Introducing PL/SQL Programming Concepts
Using PL/SQL Program Data
- Use variables in PL/SQL.
- Describe valid and invalid identifiers in PL/SQL.
- Describe reserved words, delimiters, literals, and comments in PL/SQL.
- List data types used in PL/SQL.
- Identify the benefits of anchoring data types with the %TYPE attribute.
- Use built-in SQL functions in PL/SQL.
- Differentiate between implicit and explicit conversions.
- Demonstrate how functions can be used to explicitly convert data types.
Using SQL in PL/SQL
Constructing PL/SQL Program Structures to Control Execution Flow
- Distinguish between an implicit and an explicit cursor.
- Declare and control explicit cursors.
- Use cursor simple LOOP to FETCH data.
- Use cursor FOR LOOP to fetch data.
- Declare and use cursors with parameters.
- Lock rows using the FOR UPDATE clause.
- Reference the current row with the WHERE CURRENT clause.
- Use multiple cursors.
Using Exception Handling
Examining PL/SQL Composite Data Types
Creating and Managing Procedures
- Identify the characteristics and benefits of a stored procedure.
- Invoke a stored procedure.
- List the steps for creating a procedure.
- Create a procedure with parameters.
- Invoke a procedure that has parameters.
- List the types of parameter modes.
- Describe the DEFAULT option for parameters.
- Describe the method for propagating exceptions.
- Remove a procedure.
- Identify how to view and manage procedures.
Creating and Managing Functions
- Define stored function.
- Create a function.
- List the procedures for invoking a function.
- List the advantages of user-defined functions in SQL statements.
- List circumstances in which user-defined functions can be called from within a SQL statement.
- Describe the restrictions on calling functions from SQL statements.
- Remove a function.
- Identify how to view stored objects in the data dictionary.
- Identify differences between invoker and definer rights.
- Identify a package specification and body.
- Create packages (e.g., related variables, cursors, constants, exceptions, procedures, and functions).
- Invoke a package construct.
- Designate package constructs as public or private.
- Drop packages.
- Identify benefits of packages.
- Create packages that use the overloading feature.
- Identify restrictions on using packaged functions in SQL statements.
- Invoke packaged functions from SQL.
- Identify persistent states in package variables and cursors.
- Describe the use and application of some Oracle server-supplied packages (e.g., DBMS_SQL, DBMS_OUTPUT, and UTL_FILE).
- Use EXECUTE IMMEDIATE.
- Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for native dynamic SQL.
Creating and Removing Triggers
- Describe database triggers and types and the uses of each.
- Create a DML trigger.
- List the DML trigger components.
- Create a statement-level trigger.
- Describe the trigger-firing sequence options.
- Use conditional predicates in a DML trigger.
- Create a row-level trigger.
- Use the OLD and NEW qualifiers in a database trigger.
- Create an INSTEAD OF trigger.
- Describe events that cause database triggers to fire.
- Create a trigger for a DDL statement.
- Create a trigger for a system event.
- Describe the functionality of the CALL statement.
- Describe the effect of a mutating table.
- View trigger information in the dictionary views.
- Alter a trigger status.
- Remove a trigger.
Using Advanced Data Types
Understanding Procedural Dependencies
Using the PL/SQL Compiler
Preparing for Industry Certification
- Describe the process and requirements for obtaining industry certifications related to the Database Design and Management with PL/SQL (Oracle) course.
- Identify testing skills and strategies for a certification examination.
- Demonstrate ability to successfully complete selected practice examinations (e.g., practice questions similar to those on certification exams).
- Complete an industry certification examination representative of skills learned in this course (e.g., MOS, MTA, IC3).
Developing Employability Skills
- Investigate job opportunities, using the Internet and other sources.
- Investigate careers in the information technology field.
- Research certification and educational opportunities.
- Compose or update a printed résumé.
- Prepare an electronic résumé.
- Create a letter of application.
- Complete an electronic application form.
- Create an interview follow-up letter.
- Identify the steps to follow in resigning from a position.
- Develop a portfolio containing representative samples of student's work (e.g., program design, technical documentation and design, source code, and output).
- Deliver an oral presentation of portfolio.
- Identify potential employment barriers for nontraditional groups and ways to overcome barriers.