Contact

LICENSED SCHOOL BY NEW YORK BUREAU OF PROPRIETARY SCHOOL SUPERVISION
FREE TRAINING FOR QUALIFIED CANDIDATES. FOR DETAILS CONTACT: +1(212) 629-7366

Computer System Analyst - SQL

Course Curriculum

Computer System Analyst - SQL
Total # of Hours: 120
Starting Date:

Locations

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

ALPHAPRIMETECH
22 W 34th Street, Suite 604,
New York, NY
10001
+1 (212) 629 - 7366
admin@alphaprimetech.org

Contact Us

Oracle and Structured Query Language (SQL)

  • 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

Restricting and Sorting Data

  • 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

Using Single-Row Functions to Customize Output

  • Use various types of functions available in SQL
  • Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) 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 general functions and conditional expressions in a SELECT statement

Reporting Aggregated Data Using the 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

  • 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

Using Subqueries to Solve Queries

  • 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

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

  • Truncate data
  • 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

  • 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

Managing Objects with Data Dictionary Views

  • Query various data dictionary views

Controlling User Access

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

Managing Schema Objects

  • 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

Manipulating Large Data Sets

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

Oracle Database: 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, 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

Oracle Database : 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 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

***

See the full list of Training courses and find the course that's right for you.

We are Hearing: +1 (212) 629 - 7366

Or you can write to us: admin@alphaprimetech.org Contact Us

***

Have Questions?

Please visit our FAQ section for quick help