Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Raw Beginner Oracle SQL and PL/SQL

A three day intensive Overview

© 2009 by Burleson Consulting

 

This course is taught at your Company site with up to 20 students. 

Click here for on-site course prices

Optional supplemental mentoring

 

Key Features

* Learn the Oracle architecture in plain English.

* Write sophisticated reports in SQL*Plus.

* Learn how to join tables

* Learn PL/SQL structures.

* Understand PL/SQL logic

  

Course Description

Introduction to SQL and PL/SQL is an intensive three day course that is designed to give the student maximum exposure to Oracle SQL and PL/SQL.  The student learns by doing, and this class has dozens of in-class exercises and the student will be guided from very simple SQL commands to increasingly complex PL/SQL coding techniques.

This course is designed for raw beginners and end users and required no previous exposure. 

The topics start with the basics of SQL and progress into increasingly complex queries, including table joins, subqueries and creating Oracle views.  The PL/SQL section begins with simple concepts and the student gradually masters PL/SQL through increasingly challenging classroom exercises.

Book Required

  Easy Oracle SQL

John Garmany

ISBN 0-9727513-7-8
  Easy Oracle PL/SQL Programming:

John Garmany

ISBN 0-9759135-7-3

Audience

This course is designed for end users and beginning Oracle professionals who have no experience with SQL and the use of a relational database.

Curriculum Design

By the end of this course the student will be able to create reports using SQL*Plus and formulate advanced SQL queries including correlated subqueries and outer joins.  The student will also learn basic Oracle PL/SQL and learn the basic language constructs.



 

Introduction to Oracle SQL and PL/SQL for end users and beginners

Syllabus

© 2009 by Burleson Consulting

Day 1:  Getting Started:

 

1 - Intro to class

·         Class goals

·         Introduction to topics

2 – Introduction to Oracle database management

·         Introduction to relational database

·         Oracle product suite

·         Oracle terminology

3 –Oracle schema concepts

·         Understanding data relationships

·         Entity relation modeling

·         Primary and foreign keys

·         Schema diagrams

4 – Connecting to Oracle SQL*Plus

·         Running SQL statements

·         Entering SQL*Plus

·         Schema navigation with user tables

·         Describing tables

5 - Review of SQL*Plus Commands

·         Adjusting line output

·         Column wrapping

·         Creating breaks and summaries

·         Creating dynamic SQL for reports

·         Adding prompts to queries

6 – Running basic queries

·         Selecting from tables

·         Spooling report output

 

DAY 2 Writing SQL & Making Reports:

 

1 - Introduction to SQL constructs

·         Review of Basic SQL statements
·        
Select, Project, Join
·        
Describing Oracle tables
·        
Restricting row returns

2 - Making basic reports in SQL*Plus 

·         Creating basic reports
·        
Using the set commands
·        
Column wrapping
·        
Creating breaks and summaries
·        
Adding prompts to queries


3 - Joining Oracle tables

·         Equi-join
·        
Outer join
·        
Hiding joins by creating views
·        
Using IN, NOT IN, EXISTS and NOT EXISTS
·        
Subqueries
·        
Correlated subquery
·        
Non-correlated subqueries


4 - Advanced SQL operators

·         Between operator
·        
IN and NOT In operators
·        
Sub-queries
·        
EXISTS clause
·        
Using wildcards in queries (LIKE operator)

5 - Aggregation in SQL
·         Count(*)
·         Sum
·         Avg
·         Min and max
·         Using the group by clause

6 - SQL access methods
·         Review of Basic joining methods
·         Merge join
·         Hash Join
·         Nested Loop join
·         Advanced SQL operators
·         Between operator

 

Day 3: Writing programs in PL/SQL

 

1 - Basics of PL/SQL

·         PL/SQL architecture
·        
PL/SQL and SQL*Plus
·        
PL/SQL Basics, Variables, Constants, data types & error handling
·        
PL/SQL wrapper utility

2 - PL/SQL structures 

·         Simple blocks
·        
Control structures
·        
PL/SQL records
·        
Recognizing the Basic PL/SQL Block and Its Sections
·        
Describing the Significance of Variables in PL/SQL
·        
Distinguishing Between PL/SQL and Non-PL/SQL Variables
·        
Declaring Variables and Constants
·        
Executing a PL/SQL Block

3 - Error checking – exception handling

·         Defining exceptions
·        
Using the when others clause
·        
Ensuring complete error checking
·        
Passing error messages to calling routine

4 - Boolean logic in PL/SQL

·         Identifying the Uses and Types of Control Structures
·        
Constructing an IF Statement
·        
Constructing and Identifying Different Loop Statements
·        
Controlling Block Flow Using Nested Loops and Labels
·        
Using Logic Tables
·        
If-then-else structure
·        
Testing for numbers characters and Booleans

5 - Cursors in PL/SQL

·         Cursor basics
·        
Using a cursor for a multi-row SQL query

6 - Iteration in PL/SQL

·         For loop
·        
While loop

 

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for Oracle certification exams, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.