Courses Courses Arts and Design Arts and Design Animation Graphic Arts Photography Web Design Business Business Accounting Administrative Communication Finance Marketing and Sales Operations Project Management Small Business Soft Skills Career Online High School Career Online High School Online High School Diploma Computer Applications Computer Applications Autodesk Microsoft QuickBooks Windows Computer Programming Computer Programming Programming Classes Construction and Trades Construction and Trades Construction Environmental and Energy Trades Health and Fitness Health and Fitness Alternative Medicine Dental Fitness Medical Veterinary Wellness Hospitality Hospitality Hospitality Service Information Technology Information Technology Cloud Computing Networking Other Security Language Language Languages Legal Legal Legal Studies Math and Science Math and Science Mathematics and Science Teacher Professional Development Teacher Professional Development Child Development Classroom Technology Math and Science Reading and Writing Test Prep Test Prep Exam Prep Writing Writing Writing and Editing Skill Map How It Works How It Works About ed2go Externship Programs Financial Assistance Corporate Training Find a School Military

CONTACT US

(855) 520-6806

See all results for ""

See All Courses

ed2go Computer Programming Programming Classes Oracle SQL and PL/SQL Developer
Return to Programming Classes
oracle-sql-developer

Oracle SQL and PL/SQL Developer

The Oracle SQL Developer program teaches you how to use SQL to build applications or generate business reports and helps you master SQL using an Oracle database. You will also learn PL/SQL extension language to write sophisticated queries against an Oracle database. 

This program prepares you to enter the job market as an entry-level Oracle SQL Developer or take on more responsibility within your current career. Designed for those new to writing SQL queries, this program is perfect for those who love to learn and like working with data.

6 Months / 120 Course Hrs
Open enrollment

Offered in Partnership with your Preferred School

Association of Executive and Administrative Professionals

Why this school? It's been chosen based on your location or if you've visited this school's website. Change School

Finance Options Available*

Learning Method

Instructor-led Self-Paced: Study on your own schedule.

Contact Us

Oracle SQL and PL/SQL Developer

Learning Method

Instructor-led Self-Paced: Study on your own schedule.

Contact Us

Details + Objectives

Course Code: GES336

You Will Learn
  • Build applications or generate business reports using Structured Query Language (SQL)
  • Master SQL using an Oracle database
  • Use PL/SQL extension language to write sophisticated queries against an Oracle database
How the course is taught
  • Self-paced, online course
  • 12 months to complete
  • Open enrollment, begin anytime
  • 120 course hours
How you will benefit
  • Turn your passion for working with data into a profession by learning to write SQL queries
  • Prepare to enter the job market as an entry-level Oracle SQL Developer or to take on more responsibility in your current role
  • Set yourself apart from competitors by taking a challenging course in PL/SQL development
Outline
Expand All
Introduction to SQL

Begin the course by learning about relational database basics, simple SELECTs, and more.

Advanced SELECTs

Move on to understand advanced SELECTs, subqueries, joins, and unions.

Advanced Oracle SQL Queries

Learn about users and schemas, pseudo columns and functions, using subqueries, joining tables, and more.

SQL Functions

Understand the difference between SQL character and non-character functions, and how to effectively use the two.

Oracle PL/SQL Training

Explore the environment for PL/SQL development, as well as PL/SQL basics. Learn how to declare variables and work within the block.

Uses of PL/SQL

Complete the course by mastering a detailed list of uses for PL/SQL. Master an understanding of nested blocks, subprograms, stored procedures and functions, packages, database triggers, data retrieval techniques, and specialized topics.

Program Details

Introduction to SQL
    I.    Relational Database Basics
            A.    Brief History of SQL
            B.    Relational Database
            C.    Popular Databases
            D.    SQL Statements
    II.    Simple SELECTs
            A.    Introduction to the Northwind Database
            B.    Some Basics
            C.    SELECTing All Columns in All Rows
            D.    Exploring the Tables
            E.    SELECTing Specific Columns
            F.    Sorting Records
            G.    The WHERE Clause and Operator Symbols
            H.    Using the WHERE clause to check for equality or inequality
            I.    Using the WHERE clause to check for greater or less than
            J.    Checking for NULL
            K.    Using WHERE and ORDER BY Together
            L.    The WHERE Clause and Operator Words
            M.    More SELECTs with WHERE
            N.    Checking Multiple Conditions
            O.    Writing SELECTs with Multiple Conditions
    III.    Advanced SELECTs
            A.    Calculated Fields
            B.    Calculating Fields
            C.    Aggregate Functions and Grouping
            D.    Working with Aggregate Functions
            E.    Built-in Data Manipulation Functions
            F.    Data Manipulation Functions
    IV.    Subqueries, Joins and Unions
            A.    Subqueries
            B.    Joins
            C.    Using Joins
            D.    Outer Joins
            E.    Unions
            F.    Working with Unions

    V.    Introduction to SQL Exam

Advanced Oracle SQL Queries
    I.    Users and Schemas
            A.    Introduction to Users and Schemas
            B.    About the HR Schema
            C.    Creating An Application Schema
            D.    Exercise: Create a User
    II.    Pseudo Columns & Functions
            A.    Using The Dual Table
            B.    Functions
            C.    Exercise: Use the SYSCONTEXT function
            D.    Pseudo-Columns
            E.    Using Rowid
            F.    Using Rownum
            G.    Exercise: Use ROWNUM
    III.    Using Subqueries
            A.    Simple Subqueries
            B.    Inline Views
            C.    Correlated Subqueries
            D.    Scalar Subquery
            E.    Exercise: Subqueries
    IV.    Joining Tables
            A.    Review of Joins
            B.    Equijoins
            C.    Cross Joins
            D.    Reflexive Join
            E.    Non-Key Join
            F.    Natural Joins
            G.    Semijoins and Antijoins
            H.    Using Named Subqueries
            I.    Exercise: Join Exercises
    V.    Rollup & Cube
            A.    About Group Processing
            B.    EMP_DETAILS_VIEW
            C.    Simple GROUP BY
            D.    ROLLUP Function
            E.    GROUPING Function
            F.    Using Cube
            G.    Exercise: Practice GROUP BY, ROLLUP and CUBE
    VI.    Using Set Operators
            A.    Set Operators Defined
            B.    Relationship to Mathematical Set Theory
            C.    Restrictions on Set Operators
            D.    Exercise: Set Operators Exercise
    VII.    Conditional Processing
            A.    The DECODE Function
            B.    The Case Expression
            C.    Exercise: Conditional Processing
    VIII.    SQL Functions (Character)
            A.    What Are SQL Functions?
            B.    Character Functions
            C.    Regular Expressions
            D.    Exercise: Character Functions
    IX.    SQL Functions (Non-Character)
            A.    Numeric Functions
            B.    Date/Time Functions
            C.    Date Format Functions
            D.    Date Arithmetic Functions
            E.    Null Value Functions
            F.    Exercise: SQL Non-Character Functions
    X.    SQL Data Manipulation Language
            A.    The INSERT Statement
            B.    The UPDATE Statement
            C.    The DELETE Statement
            D.    Transactions
            E.    Complex Table References
            F.    The MERGE statement
            G.    Exercise: Data Manipulation Language (DDL)
    XI.    Advanced Oracle SQL Queries Exam

Oracle PL/SQL Training
    I.    The Environment for PL/SQL Development
            A.    Prerequisites
            B.    Connecting to Oracle
            C.    Executing PL/SQL Code
            D.    SQL Developer Configuration
            E.    The PL/SQL Development Cycle
    II.    PL/SQL Basics
            A.    PL/SQL Block
            B.    Anonymous Block Structure
            C.    Named Block Structure
            D.    Executing Blocks
            E.    Calling PL/SQL Functions
            F.    Executing PL/SQL Blocks and Functions
    III.    Declaring Variables
            A.    Variable Usage
            B.    Variable Data Types
            C.    Variable Naming
            D.    Variable Assignment
            E.    Complex Variable Types
            F.    More Information
            G.    Variable Declaration, Initialization and Display
    IV.    Within the Block
            A.    Conditional Processing t
            B.    Iterative Processing
            C.    Salary Classification using Conditional Statements
            D.    Salary Increases using Loops
    V.    Handling Exceptions
            A.    Overview of Exceptions
            B.    Causing System Generated Exceptions
            C.    Handling System Generated Exceptions
            D.    Identifying System Generated Exceptions
            E.    OTHERS Exception Handler
            F.    User Defined Exceptions
            G.    Exception Handling for Invalid Salary
    VI.    Use of SQL in PL/SQL
            A.    Implicit Cursors
            B.    %TYPE and %ROWTYPE Attributes
            C.    EXECUTE IMMEDIATE statement
            D.    Cursors Attributes
            E.    Implicit Cursor
            F.    Explicit Cursors
            G.    Explicit Cursor
            H.    Cursor FOR loop
    VII.    Nested Blocks
            A.    Nesting Blocks
            B.    Scope of Variables
            C.    Scope of Exceptions
            D.    Nested Subprograms
            E.    Exception Propagation in Nested Blocks
            F.    Block Variable Visibility
    VIII.    Introducing Subprograms
            A.    Anonymous blocks
            B.    Types of Subprograms
            C.    Finding Subprograms through SQL Developer
            D.    Finding Subprograms in the Oracle Data Dictionary
            E.    Object Dependencies
            F.    Subprogram information in the Oracle Data Dictionary
    IX.    Stored Procedures and Functions
            A.    Creating Subprograms
            B.    Modifying Subprograms
            C.    Removing Subprograms
            D.    Application Maintenance
            E.    Exercise Title
            F.    Parameters
            G.    Cursors as Parameters
            H.    Subprogram Development Techniques
            I.    Addressing Compilation Errors
            J.    Directives for Debugging
            K.    Issues with Booleans
            L.    Integrated Development Environments
            M.    PL/SQL Subprograms with Parameters
    X.    Packages
            A.    Structure of Packages
            B.    Purpose of Packages
            C.    Wrap Utility
            D.    Positional vs Named Parameter Notation
            E.    Subprograms Omitted from Specifications
            F.    Using PL/SQL Packages
    XI.    Database Triggers
            A.    Purpose of Triggers
            B.    Invocation of Triggers
            C.    Coding Triggers
            D.    Validation Trigger
            E.    Modifying Triggers
            F.    Viewing Triggers
            G.    Enabling/Disabling Triggers
            H.    Trigger Errors
            I.    System and User Event Triggers
            J.    Logon Counter Trigger
    XII.    Data Retrieval Techniques
            A.    Cursor Review
            B.    Cursor Parameters
            C.    Cursor Variables
            D.    Dynamic SQL
            E.    Employee Report by State/Province
    XIII.    Using Oracle Supplied Packages
            A.    Oracle Supplied Packages
            B.    Identifying Available Packages
            C.    Identifying Package Subprograms
            D.    Selected Oracle Supplied Package Demos
            E.    Packages related to Input/Output
            F.    Packages related to Networking
            G.    DBMS_SQL: Dynamic SQL
            H.    DBMS_URL: Working with URLS
            I.    The DBMS_APPLICATION_INFO
    XIV.    Specialized Topics
            A.    Application Partitioning
            B.    Subprograms in other Languages
            C.    Oracle Object Features
            D.    Statistical Analysis
            E.    Data Mining
            F.    Use Oracle Object Features

    XV.    Oracle PL/SQL Training Exam

    XVI.    Final Exam

    XVII.    Oracle SQL Developer Final Project

View All Sections
Instructors
Roger Sakowski

Roger Sakowski has over 35 years of experience in technical training, programming, data management, network administration, and technical writing for companies such as NASA, Sun Microsystems, Bell Labs, GTE, GE, and Lucent among other Fortune 100 companies. Roger has been an instructor for Webucator for 6 years and previously trained for other prominent technical training companies.

Note: This course is supported by multiple instructors at Webucator, a national training and courseware development company, all of whom are experts in their fields.

Prerequisites / Requirements

Prerequisites:
There are no prerequisites for this course; however, some prior programming language knowledge is helpful.

Requirements:
Hardware Requirements:

  • This course can be taken on either a PC or Mac.
  • Dual monitors are helpful but are not required.

Software Requirements:

  • PC: Windows XP or later.
  • Mac: OS X Snow Leopard 10.6 or later.
  • Browser: The latest version of Internet Explorer, Firefox, Chrome, or Safari browsers. (Although Firefox or Chrome is preferred).
  • Adobe Flash Player. Click here to download the Flash Player.
  • Adobe Acrobat Reader. Click here to download the Acrobat Reader.
  • Other: Email capabilities and access to a personal email account.
  • Software must be installed and fully operational before the course begins. 

Instructional material requirements: 

  • The instructional materials required for this course are included in enrollment.

Oracle

Oracle Database 10g/11g

You need to have full administrative access to Oracle 10g or 11g. If you do not, you can use Oracle 11g Express Edition:

1. Download Oracle Database Express Edition for either 32 or 64 bit OS: http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html?ssSourceSiteId=ocomen. Note: you will need to register for a free account to gain access to the download.

2. Execute the file you downloaded by double-clicking on the folder and then double-clicking on Setup.exe.

3. Run through the Installer accepting all the defaults. Make your password system (all lowercase). Make sure to write down and keep your password.

Note: for any IT Department personnel who are setting up a work computer for a student: (1) This class requires the use of the Oracle supplied HR demo schema, with the permissions and objects in the state in which Oracle created them. The HR schema is created by default during an install but is not always unlocked. Please unlock the schema, assigning a password. Please make sure the student has the password. (2) If you will be using a work laptop at home, then install Oracle at home. If your IT department is installing it at work, make sure they disconnect from both the internet and the intranet before launching the install. The safest way to do this is to remove the ethernet connection from your laptop and turn off your wireless connection

Oracle Client Options

1. Install Oracle SQL Developer. 1. If you are on a Windows 32 bit platform click here. Download "Windows 32-bit - zip file includes the JDK1.6.0_35".  2. For Windows 64 bit and other platforms, click here. NOTE: For Windows 64 bit platforms download "Windows 64-bit - zip file includes the JDK 7".

2. Create a folder called sqldeveloper in the Oracle folder located on c: and then extract the sqldeveloper.zip to that sqldeveloper folder you just created.

3. Within that folder, open the sqldeveloper folder.

4. Double-click sqldeveloper.exe to start SQL Developer

Class Files

1. Download the class files.

2. After downloading the class files, create a directory on your hard drive named "Webucator" that can be found easily.

3. On Windows it is recommended the new folder have a path of C:\Webucator.*

4. Extract the files to the folder you just created.

* If you are using XAMPP (e.g, for PHP classes), you must place the Webucator folder on the C:\xampp\htdocs folder.

Unlocking the HR Account

1. In SQL Developer, log in using this connection

  • Connection name: system
  • Username: system
  • Password: system (This should be the password you created when you installed Oracle)

2. Open the HR_Unlock_Account.sql file from your class files.

3. Click on the second green play button (F5) to execute the sql query.

4. To continue setup, click on the green + under the Connections tab, and add the criteria below:

  • Connection Name: hr
  • Username: hr
  • Password: hr
  • Click on Connect. A worksheet should open up on a tab that's labeled "hr".

5. Now that you're connected to HR, you're ready to test.

  • Enter the following in the worksheet: select * from regions;
  • Click on the "green arrow" to execute your code.
  • If you get back 2 columns and 4 rows then you are all set.
Frequently Asked Questions
Expand All
Can I register for programs if I am an international student?

Yes, since ed2go programs are online, you never have to actually travel to the school. Most schools offer telephone or online registration.

How long does it take to complete a program?
This program is self-paced and open enrollment, so you can start and finish at your own pace. Upon registering, you're given twelve (12) months to complete this program.
What if I don't have enough time to complete my program within the time frame provided?

The time allotted to complete your program has been calculated based on the number of course hours. If after a concerted effort, you are still unable to complete your program on time, your Student Advisor will help you work out a suitable completion date. Please note that a fee may be charged for an extension.

Can I get financial assistance?

ed2go courses are non-credit courses, so they do not qualify for federal aid. In some states, vocational rehab or workforce development boards will pay for qualified students to take our courses. Additionally, some students may qualify for financial assistance when they enroll, if they meet certain requirements.

What happens when I complete the program?
Upon successful completion of the program, you will be awarded a certificate of completion.
Am I guaranteed a job?

ed2go programs will provide you with the skills you need to obtain an entry-level position in most cases. We don’t provide direct job placement services, but our facilitators and career counselors will help you build your resume and are available to give advice on finding your first job. Facilitators will also be available to use as a professional reference upon completion of the program. Potential students should always do research on the job market in their area before registering.

Who will be my instructor?
Each student is paired with a facilitator for one-on-one interaction. The facilitator will be available (via e-mail) to answer any questions you may have and to provide feedback on your performance. All of our facilitators are successful working professionals in the fields in which they teach.
How can I get more information about this program?

If you have additional questions, please feel free to contact us via LIVE CHAT or by calling us at (855) 520-6806. If you are visiting us during non-business hours, please feel free to send us a question using the Contact Us form.

When can I start the program?

This program is open enrollment. You can register and start the program as soon as you are ready.

Please note: Once the program curriculum is accessed online or through submission of a material shipment confirmation, refunds cannot be issued.

Does this program prepare for a certification?

This program does not prepare you for a certification but prepares you to enter the job market as an entry-level Oracle SQL Developer. 

Browse All