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
© 2016 by Burleson Corporation
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.
