Oracle SQL, PL/SQL

Oracle 11g - SQL & PL/SQL is the entry point to start your career on Oracle Technologies. This course also serves as the basis for other Oracle technology courses like Oracle DBA, Oracle Developer, and Oracle Applications Technical Consultant etc

Download Brochure

What you will learn?

This course provides students with comprehensive understanding of Oracle Technologies in general and SQL, PL/SQL Languages in specific. Students will learn how to use SQL language to interact with Databases and PL/SQL to write more complex and data intensive programs.

Who can attend?

  • IT beginners
  • Developers
  • Any graduate planning to start career in database technologies

Suggested Pre-requisites

  • This is a starter course and hence no specific prerequisites
  • Basic computer operating knowledge is desirable

Training Highlights

  • Training covers Oracle11g & Oracle12c versions
  • Extensive syllabus, compared to any other institute
  • In-depth coverage with real world examples and scenarios
  • Top Quality course material for self-study and future reference
  • Dedicated System for practice
  • Interview questions & Guidance on Oracle certifications
  • Assistance on resume preparation
  • Free Linux & Oracle software installation on your personal Laptops
  • 100% Placement assistance by full-fledged in-house placement cell
  • 100% satisfaction guaranteed
1. Introduction to DBMS

  • Approach to Data Management, File and File System
  • Disadvantages of file base data managemen
  • Database Terminology
  • Database Models – Hierarchal Model
  • Network Model, Relational Model
2. Introduction to RDBMS

  • Features of RDBMS
  • Advantages of RDBMS over FMS & DBMS
  • E.F Codd Rules for RDBMS
  • Normalization Process
  • Client server Technology
  • Oracle Corporation Products
  • Oracle Versions about SQL & SQL*PLUS
  • Difference between OLTP & OLAP
3. Sub Language Commands

  • Data Definition Language(DDL)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)
4. Introduction to SQL

  • Schema & Schema Objects
  • Oracle Data Types
  • DDL Commands
  • DML Command
  • DRL Commands
  • Operators
  • Arithmetic and Logical Operators
5. Built-in Functions

  • Arithmetic Functions
  • Character Functions
  • Date functions
  • Conversion Functions
  • Aggregate Functions
  • OLAP Functions & General Functions
6. Grouping the result of a query

  • Using Group by and Having Clause of DRL Statement
  • Using Order by clause
7. Working with Integrity Constraints

  • Importance of Data integrity
  • Working with different types of integrity constraints
  • NOT NULL constraint, UNIQUE constraint, PRIMARY KEY constraint, REF constraint
  • Understanding ON DELETE clause in referential integrity constraint
  • Working with multiple constraints upon a column
  • Adding constraints to a table
  • Dropping of constraints, Enabling / disable constraints
  • Querying for constraints information
8. Querying Multiple Tables (Joins)

  • Equi Join/Inner Join/Simple Join, Cartesian Join, Non-Equi Join, Self Join
9. Working with Sub Queries

  • Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
  • Usage of Sub Queries.
  • The Impact of Having Clause in Sub Queries, IN, ANY SOME, ALL Operators in Sub Queries
  • PAIR WISE and NON PAIR WISE Comparison in Sub Queries, Be... Aware of NULL’S, Correlated Sub
  • Queries, Handling Date Retrieval with EXISTS and NOT EXISTS Operators
10. Working with DCL, TCL Commands

  • Grant, Revoke,
  • Commit, Rollback, Savepoint
  • Read Consistency
11. Maintaining Database Objects VIEWS IN ORACLE

  • Understanding the Standards of VIEWS in Oracle
  • Types of VIEWS: Relational Views, Practical approach of SIMPLE VIEWS and COMPLEX VIEWS, Column definitions in VIEWS, Using VIEWS for DML Operations, forced Views, Putting CHECK Constraint upon VIEWS, Creation of READ ONLY VIEWS, Understanding the IN LINE VIEWS
  • TOP-N Analysis, About Materialized Views, View Triggers
  • Working with, Sequences, Working with Synonyms, Working with Index and Clusters, Creating Cluster Tables, implementing Locks, Working with roles
12. Pseudo Columns in Oracle

  • Understanding Pseudo Columns in Oracle
  • Type of Pseudo Columns in Oracle
13. Data Partitions & Parallel Process

  • Range Partitions, Hash Partition
  • List Partition, Composite Partition, Parallel Query Process
14. Locks

  • Row level Locks
  • Table level Locks
  • Shared Lock
  • Dead Lock
  • Implicit Locking
15. PL/SQL (Procedure Language – SQL)

  • Introduction to Programming Languages
  • Introduction PL/SQL, The Advantages of PL/SQL
  • PL/SQL Architecture, PL/SQL Data types
  • Variable and Constants, Using Built-in Functions
  • Conditional and Unconditional Statement
  • Simple if, if..else. Nested if..else, if.. else Ladder, Selection Case, Simple Case, GOTO Label and EXIT
  • Iterations in PL/SQL, Simple LOOP, WHILE LOOP, FOR LOOP and NESTED LOOPS
  • SQL within PL/SQL, Composite Data types (complete), Cursor Management in PL/SQL
  • Implicit Cursors, Explicit Cursors, Cursor Attributes
  • Cursor with Parameters, Cursors with LOOPs Nested Cursors, Cursors with Sub Queries, Ref. Cursors, Record and PL/SQL Table Types
16. Advanced PL/SQL

  • Procedures in PL/SQL: STORES PROCEDURE, PROCEDURE with Parameters (IN, OUT and IN OUT)
  • POSITIONAL Notation and NAMED Notation, Procedure with Cursors, Dropping a Procedure
  • Functions in PL/SQL: Differences between Procedures and Functions
  • User Defined Functions, Nested Functions, using stored function in SQL statements
  • Purity Levels, User _Source Dictionary table Packages in PL/SQL: Creating PACKAGE Specification and PACKAGE Body, Private and Public Objects in PACKAGE
  • EXCEPTIONS in PL/SQL: Types of Exceptions: User Defined Exceptions, Pre Defined Exceptions, Pragma Exception_Unit, RAISE_APPLICATION_ERROR, PRAGMA_AUTONOMOUS_TRANSACTION
  • SQL Error Code Values
17. Database Triggers in PL/SQL

  • Types of Triggers, Row Level Triggers, Statement Level Triggers, DML Triggers, DDL Triggers, Schema Triggers
  • Using OLD and NEW References, Trigger Auditing, Enabling / Disabling Triggers, Dropping Triggers
18. Implementing Object Technology

  • What is Object Technology?
  • OOPS-Object instances, Creation of objects
  • Creating user Defined Date Types
  • Creating Object Tables
  • Inserting rows in a table using object
  • Retrieving date from Object based Tables
  • Calling a Method, Indexing Abstract Data type Attributes
19. Using Collections

  • Advantages of collection
  • Ref Cursor (Dynamic Cursor), Weak Ref Cursor, Strong Ref Cursor, Nested Tables
  • Associative Arrays, VARRAYS or VARYING arrays
  • Creating tables using nested tables
  • Inserting, Updating & Deleting Nested, Table Records
20. Advanced Features

  • New Date function, Rename column, Inner Join/Natural Join, Left Outer Join/Right Outer Join, Full Outer Join
  • Multiple inserts, Insert All Command, Merge statement, NVL2(), NULLIF(), COALESCE(), CASE expression of Select Command, Temporary Tables/Global Tables
  • New Function EXTRACT (), Autonomous Transaction
  • Pragma_Autonomous_Transaction(),
  • Returning into clause, Bulk Collect, For All, Definer/Invoker Rights
  • About flash Back Queries, Dynamic SQL, Flash back command, Purge Command, Recycle bin, Regular expressions, DML Error Logging, Virtual Columns, Read only tables, Cross tab Views using, Pivot/Unpivot Operators Follows Clause, Compound Triggers, New data types
  • Working with LARGE Objects
21. Oracle Built-in Packages

  • DBMS_OUTPUT Package
  • DBMS_DDL Package
  • DBMS_JOB Package
  • UTL_FILE Package
  • UTL_MAIL Package
22. Introduction to Oracle Database

  • Introduction to Oracle Database Architecture
  • Physical Structures Logical structures
  • DB Memory Structures
23. Oracle Utilities

  • Oracle Data Pump
  • SQL * Loader
  • External Tables

UCLID IT School is the pioneer in Oracle DBA training.  At UCLID, you are assured to get pure and clean Oracle DBA training. We have mastered the art of teaching Oracle DBA and changed many lives for good.  At UCLID, we follow an easy, simple and no-nonsense approach towards making you a master in Oracle DBA.

With it’s enviable & impeccable track record of training and placing thousands of students as Oracle DBAs in many IT organizations, Uclid has the following unique advantages:

  • Training by former employees of Oracle India and real time IT professionals from various top IT firms
  • Training on Real Time scenarios and case studies
  • Most in-depth and comprehensive training on every topic of the course
  • Well structured Training Material for future reference
  • Training on Latest Versions – Oracle 12c
  • Individual attention and group discussions for Interview preperation
  • Dedicated systems for Lab practice supported by qualified Lab Administrators and UPS for power backup
  • Unlimited Lab Access till you get job
  • 100% Placement Assistance backed by many top IT organizations (around 16 Top IT companies hire fresher DBAs from Uclid)
  • 100% Satisfaction guarenteed