Course Code: GES336
Begin the course by learning about relational database basics, simple SELECTs, and more.
Move on to understand advanced SELECTs, subqueries, joins, and unions.
Learn about users and schemas, pseudo columns and functions, using subqueries, joining tables, and more.
Understand the difference between SQL character and non-character functions, and how to effectively use the two.
Explore the environment for PL/SQL development, as well as PL/SQL basics. Learn how to declare variables and work within the block.
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.
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
C. Using Joins
D. Outer Joins
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
C. Exercise: Use the SYSCONTEXT function
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
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
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
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
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
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
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
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.
There are no prerequisites for this course; however, some prior programming language knowledge is helpful.
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
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
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:
5. Now that you're connected to HR, you're ready to test.
The instructional materials required for this course are included in enrollment and will be available online.
Yes, ed2go courses are online, so you never have to actually travel to the school. Most schools offer telephone or online registration.
This course is self-paced and open enrollment, so you can start when you want and finish at your own pace. When you register, you'll receive six (6) months to complete the course.
The time allotted for course completion has been calculated based on the number of course hours. However, if you are unable to complete the course, contact your Student Advisor to help you work out a suitable completion date. Please note that an extension fee may be charged.
ed2go courses are non-credit, so they do not qualify for federal aid, FAFSA and Pell Grant. 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. Financing is available from select schools. Learn more: https://www.ed2go.com/career/financial-assistance
Upon successful completion of the course, you will be awarded a Certificate of Completion.
This course will provide you with the skills you need to obtain an entry-level position in most cases. We do not offer direct job placement services, but our instructors and career counselors will help you build your resume and advise you on starting your career. Instructors can also be used as a professional reference upon course completion. However, you should always research the job market in your area before registering.
You may be assigned with an instructor or team of industry experts for one-on-one course interaction. Your support will be available (via e-mail) to answer any questions you may have and to provide feedback on your performance. All of our instructor are successful working professionals in the fields in which they teach. You will be assigned to an Advisor for academic support.
If you have questions that are not answered on our website, 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.
This course is open enrollment, so you can register and start the course as soon as you are ready. Please note: Once the course curriculum is accessed online or through submission of a material shipment confirmation, refunds cannot be issued. Access to your course can take 24-48 business hours.
This program does not prepare you for a certification but prepares you to enter the job market as an entry-level Oracle SQL Developer.