Introduction to Oracle SQL and PL/SQL for end users and beginners (Syllabus)

Introduction to Oracle SQL and PL/SQL for end users and beginners (Syllabus)

Course Fee:   200 $        

 Training Mode:  Online

 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

·         Outer join / Equi 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

 Course Fee:   200 $

 Training Mode:  Online