May 17, 2022  
2020-2021 Course Catalog 
    
2020-2021 Course Catalog [ARCHIVED CATALOG]

Add to Portfolio (opens a new window)

CIS 338 - SQL/Oracle

Credits: 3
Lecture Hours: 2
Lab Hours: 2
Practicum Hours: 0
Work Experience: 0
Course Type: Voc/Tech
Students will use advanced techniques to retrieve data, format reports and create script files to generate SQL. The course also provides the opportunity to students to write COBOL programs that utilize embedded SQL statements.
Prerequisite: CIS 332  
Competencies
  1. Develop blocks of PL/SQL code
    1. Distinguish between SQL and PL/SQL
    2. Describe the benefits of PL/SQL over SQL
    3. Recognize the components of a PL/SQL block of code
    4. Distinguish between anonymous PL/SQL blocks and PL/SQL subprograms
    5. Describe the significance of variables in PL/SQL
    6. Declare and use variables in PL/SQL
    7. Declare and use constants in PL/SQL
    8. Assign values to PL/SQL variables
    9. Distinguish between PL/SQL and Non-PL/SQL variables
    10. List the guidelines for PL/SQL block syntax
    11. Demonstrate effective commenting in PL/SQL code
    12. Execute an anonymous PL/SQL block
  2. Control PL/SQL flow of execution
    1. Recognize the significance of the executable section of PL/SQL blocks
    2. Describe conditional statements and process flow
    3. Define a cursor
    4. Explain the difference between implicit and explicit cursors
    5. Declare and use cursors.
    6. Write executable statements within the executable section of PL/SQL
    7. Write a successful SELECT statement in PL/SQL
    8. Write DML statements in PL/SQL
    9. Perform transaction control in PL/SQL
    10. Determine the outcome of a SQL DML statement
    11. Describe the rules of nested blocks
  3. Write Control Structures within PL/SQL
    1. Identify the three types of selection structures available in PL/SQL
    2. Identify the three types of repetition structures available in PL/SQL
    3. Construct and IF statement in PL/SQL
    4. Construct looping structures in PL/SQL
    5. Control block flow using nested loops and labels
    6. Use logic tables
  4. Create PL/SQL Procedures and Functions
    1. Describe the use of procedures and functions
    2. Compile a procedure
    3. Compile a function
    4. Debug the compile of a procedure or function
    5. Invoke a procedure from the SQL*Plus command prompt
    6. Invoke a procedure from within a PL/SQL subprogram
    7. Invoke a function from the SQL*Plus command prompt
    8. Invoke a function from within a PL/SQL subprogram
    9. Revise and re-compile a procedure
    10. Revise and re-compile a function
    11. Remove a procedure from the database
    12. Remove a function from the database
  5. Implement error handling in PL/SQL code
    1. Recognize the three basic types of exceptions
    2. Code exception handlers
    3. Discuss the guidelines for trapping exceptions
  6. Create PL/SQL Packages
    1. Describe packages and list their possible components
    2. Create a package to group together related variables, cursors, constructs, exceptions, procedures, and functions
    3. Make a package construct either public or private
    4. Invoke a package construct
    5. Write packages that make use of the overloading feature of PL/SQL
    6. Compile a package
    7. Remove a package from the database
  7. Create Database Triggers
    1. Describe database triggers and their use
    2. Create database triggers
    3. Describe database trigger firing rules
    4. Drop database triggers
  8. Test PL/SQL programs thoroughly



Add to Portfolio (opens a new window)