Course Description
Introduction to SQL and PL/SQL 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.
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
Course Topics :-
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
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
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