Loading....

SQL/PL-SQL

Objective of the SQL and PL/SQL course

The SQL and PL/SQL course gives you hands-on practice to use skills to extract and analyze data from databases and create reports. It also incorporates how to build advanced queries including adding, updating, and deleting data, views, tables and indexes.

Modules Include:

SQL Concepts

Database Design

• Database Models
• Beginnings
• Oracle Architecture
• Some Introductory Terminology
• Normalization
• First Normal Form
• Higher Order Normal Forms

The Sample Database
• The Entity Relationship Model
• Entity Relationship Diagrams
• The Sample Database
• ER Diagram for Sample Database
• Creating the Sample Data
• Viewing Sample Data
• Data Types of the Sample Data

Data Definition Language
• Categories of SQL Statements
• Oracle Datatypes
• The CREATE Statement
• The DROP Command
• The ALTER Command
• Integrity Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Modifying Table to Use Constraints
• Checking Constraints
• The Data Dictionary

Data Manipulation Language
• DML Statements
• The SELECT Statement
• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
• More SQL*Plus Commands

Transaction Control – 1 Hour
• Transactions
• Command Classification
• Savepoints
• The SET TRANSACTION Command

SQL Operators
• Simple Selects
• Comparison Operators
• >,<,<>,>=,<=
• IN and NOT IN Operators
• BETWEEN Operator
• The LIKE Operator
• Logical Operators
• IS NULL and IS NOT NULL
• ANY
• ALL

SQL Functions
• Introduction
• The DISTINCT Keyword
• Aliases
• Miscellaneous Functions
• Mathematical Functions
• String Functions
• Date Functions
• Conversion Functions
• Pseudo Columns ROWID,ROUNM

Joining Tables
• Joins
• Cartesian Product
• Inner Joins
• Equi-Join
• Table Aliases
• Non-Equi Join
• Non-Key Join
• Reflexive Join
• Natural Join
• Outer Joins
• Right Outer Join
• Left Outer Join
• Full Outer Join

Set Operators
• Introduction
• Selection Criteria
• Union
• Union All
• Intersect
• Minus

SQL SubQueries
• Introduction
• Using a Sub query with a DML Statement
• Typical Sub queries
• Sub query Operators
• Standard vs Correlated Sub queries
• Correlated Sub query Example
• Predicate Operators

Groups
EMP NO NAME Sal DEPT NO
Select * from EMP where DEPTNO = ‘10’;
• SQL Statements
• GROUPBY
• HAVING
• WHERE
• ORDER BY
• GROUP BY Clause
• HAVING Clause
• Order of a SELECT Statement

More Database Objects
• More Database Objects
• Relational Views
• Updating a View
• Create or Replace
• Forcing a View
• The Data Dictionary Revisited
• Indexes
• Synonyms

Click here to contact us and learn about all our trainings!

PL-SQL Concepts

Introduction
• Declarations
• Standard Data Types
• Initialization
• Variable Names
• Specialized Data Types – %TYPE
• Specialized Data Types – %ROWTYPE
• Building Your Own Data Types – Records

Loops
• Introduction
• Assignments
• Decision Making Statements
• Simple Loops
• Loops – for
• Loops – indefinite
• Loops – while
• Simple Loops
• Nested Loops
• Boolean Variables
• PL/SQL Relational Operators
• PL/SQL Logical Operators
• The CASE Construct

Cursors
• Introduction
• Cursors
• Using the Cursor
• For Loops Cursors
• Cursor Attributes
• Cursor Parameters
• Nested Cursors
• Cursor Exceptions
• REF Cursors

Functions and Procedures
• Introduction
• Views & MV’s
• Creating a Procedure
• Example Procedure
• Procedure Using Parameters
• Functions
• Procedures and Exceptions
• Triggers
• Packages

Exceptions
• Errors in Programs
• Run Time Exceptions
• Oracle Built In Exceptions
• Unnamed Exceptions
• Built in Exception Functions
• Creating Your Own Exceptions
• Building Non Terminating Exceptions

Collections
• Types of Collections
• Index by table
• Nested Tables
• arrays

Learn more about our training program.

Get in touch today!

Back To Top