SQL & PL-SQL
Module 1
DATABASE DESIGN
Module 2
THE SAMPLE DATABASE
Module 3
DATA DEFINITION LANGUAGE
Module 4
DATA MANIPULATION LANGUAGE
Module 5
TRANSACTION CONTROL – 1 Hour
Module 6
SQL OPERATORS
Module 7
SQL FUNCTIONS
Module 8
JOINING TABLES
Module 9
SET OPERATORS
Module 10
SQL SUBQUERIES
Module 11
GROUPS EMP NO NEME Sal DEPT NO Select * from EMP where DEPTNO = ‘10’;
Module 12
MORE DATABASE OBJECTS
PL-SQL Concepts
Introduction
Loops
Cursors
Functions and Procedures
Exceptions
Collections

SQL & PL-SQL

$1500 USD
Categories:
Reviews:
PL-SQL-Course

About this course

Oracle is a combination of both SQL (Structured Query Language) and Pl/SQL (Programming Language SQL). SQL is a data-oriented language that is used to select and manipulate datasets. While PL/SQL is a procedural language that is used to create applications. If you are aiming to become an Oracle Database Administrator, you need to learn both.

 

This course is for anyone that wants to learn the programming language. It is also recommended for Database Administrators, Software Developers, Analytics professionals, and Business Intelligence and Data Warehousing Professionals.

 

Number of jobs in the U.S. – 67,000+ (Source: LinkedIn)

 

U.S. National Average salary – $106,355/year (Source: ZipRecruiter)

 

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.

 

Prerequisite – None required. Basic knowledge of SQL helpful.

Syllabus

Module 1
DATABASE DESIGN

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

Module 2
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

Module 3
DATA DEFINITION LANGUAGE

• Categories of SQL Statements • Oracle Datatypes • The CREA TE Statement • The DROP Command • The AL TER Command • Integrity Constraints • Entity Integrity Constraints • Referential Integrity Constraints • Modifying Table to Use Constraints • Checking Constraints • The Data Dictionary

Module 4
DATA MANIPULATION LANGUAGE

• DML Statements • The SELECT Statement • The INSERT Statement • The DELETE Statement • The UPDA TE Statement • More SQL*Plus Commands

Module 5
TRANSACTION CONTROL – 1 Hour

• Transactions • Command Classification • Savepoints • The SET TRANSACTION Command

Module 6
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

Module 7
SQL FUNCTIONS

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

Module 8
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

Module 9
SET OPERATORS

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

Module 10
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

Module 11
GROUPS EMP NO NEME Sal DEPT NO Select * from EMP where DEPTNO = ‘10’;

• SQL Statements • GROPBY • HAVING • WHERE • ORDER BY • GROUP BY Clause • HAVING Clause • Order of a SELECT Statement

Module 12
MORE DATABASE OBJECTS

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

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