Courses Search

(search any course. example: Sap Bw, Java etc.)

Register for a Demo



















helpdesk@sscyient.com    +1 (415)-873 0244

SQL & PL/SQL Online Training

 

Fundamentals of Database

  • Introduction to DBMS
  • File and File system management
  • Disadvantages of file system management
  • Database Models
  • Hierarchical Model
  • Network Model
  • Relational Model

Introduction to RDBMS

  • Advantages of RDBMS over FMS and DBMS
  • The 12 Rules (E. F. Codd Rules)
  • Types of RDBMS
  • OLTP
  • OLAP
  • Data Model for OLTP
  • E-R MODEL
  • Necessity of Normalization Process
  • Client/Server Technology
  • Oracle versions
  • Database Application Tools
  • SQL & SQL * PLUS

SQL

  • What is SQL?
  • Role of SQL in RDBMS
  • Sub-languages in SQL
  • What is SQL, SQL*PLUS and ISQL*PLUS

Classification of SQL Commands

  • Types of commands
  • Data Definition Language Commands(DDL)
  • Data Manipulation Language Commands(DML)
  • Transaction Control Language Commands(TCL)
  • Database Security and Privileges(DCL)

Data Definition Language (DDL) Commands

  • How to create a table?
  • How to modify table structure?
  • How to delete a table?

DML Commands

  • INSERT
  • Inserting records into a table
  • NULL VALUES
  • How to insert NULL VALUES
  • DQL--SELECT: Fetching data from the tables
  • SELECT with WHERE CLAUSE
  • SELECT with DISTINCT clause
  • SELECT with ORDER BY clause

OPERATORS

  • ARITHEMATIC OPERATORS
  • RELATIONAL OPERATORS
  • RELATION NEGATION OPERATORS
  • LOGICAL OPERATORS

UPDATE

  • Updating all VALUES in a column
  • Updating column values conditionally
  • Updating multicolumn values

DELETE

  • Deleting all the data from a table
  • Deleting data conditionally

TRUNCATE

  • Deleting data permanently

Defining DATA INTEGRITY CONSTRAINTS on tables:

  • KEY CONSTRAINTS
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY and its types
  • Applying DEFAULT option on columns
  • DOMAIN CONSTRAINTS
  • CHECK constraint with BETWEEN and IN Operators
  • REFERENTIAL INTEGRITY CONSTRAINTS
  • FOREIGN KEY constraint
  • Understanding ON DELETE CASCADE
  • Dropping of constraints
  • Enabling/Disabling constraints
  • Getting Information from Constraints system table
  • USER_CONSTRAINTS
  • USER_CONS_COLUMNS

SET OPERATORS

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

DATA JOINING (JOINS)

  • What is data joining?
  • When we use joins?
  • Types of Joins
  • Cross join, Equi join , Inner join, self join AND OUTER JOINS
  • LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN

Working with Sub Queries

  • What is the purpose of sub query?
  • Types of sub queries
  • Single row sub-queries
  • Multi-row sub-queries
  • Correlated Sub-Queries

VIEWS

  • What is a view?
  • Importance of View
  • Types of Views
  • Simple views
  • Composite views
  • Inline views
  • Materialized views

INDEXES

  • Necessity of Indexes
  • Creation of index
  • Types of Indexes
  • Developers Perspective
  • DBA's Perspective

PSEUDO COLUMNS IN ORACLE

  • Understanding pseudo columns
  • Types of pseudo columns
  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM

SYNONYMS

  • Advantages of using Synonyms
  • Types of Synonyms
  • How and when to create synonyms?

CLUSTERS

  • Use of cluster
  • Creation of cluster
  • Advantages of clusters

SEQUENCE

  • Creation and Use of sequences

TRANSACTION CONTROL LANGUAGE

  • COMMIT/ROLLBACK/SAVEPOINT

ORACLE FUNCTIONS

  • CATEGORIES
  • GROUP OR AGGREGATE FUNCTIONS
  • SCALAR OR SINGLE ROW FUNCTIONS

Types of functions

  • NUMERIC FUNCTIONS
  • STRING FUNCTIONS
  • CONVERSION FUNCTIONS
  • DATE FUNCTIONS

DATA PARTITIONS AND PARALLEL PROCESSING

  • Need for Partitioning
  • Types of Partitions
  • Range, Hash, list Partitions

LOCKS

  • Row level locks
  • Table level Locks
  • Shared Lock

SQL * LOADER

  • SQL*LOADER Architecture
  • Data File
  • Control File
  • Bad File
  • Discard File
  • Log File

PL-SQL

  • Introduction to PL/SQL
  • What is a Program block?
  • Advantages of PL/SQL
  • Architecture of PL/SQL Engine
  • Features, Structure of Program Block
  • Data types, Executable Stmts
  • What are the blocks in the program structure?
  • DECLARE Block
  • BEGIN Block
  • EXCEPTION Block
  • Types of Programs
  • i) Static Programs ii) Dynamic Programs

Type Compatibility keywords

  • Declaring variables Dynamically --%TYPE
  • Declaring Table Type Record Variables--%ROWTYPE

SUB PROGRAMS

  • 1) Stored Procedures 2) PL/SQL Functions

Stored Procedures

  • What is a procedure? And How to create it?
  • What are the advantages of procedures?
  • Types of stored procedures
  • i) Static Procedures (without Arguments)
  • ii) Dynamic Procedures (with Arguments)
  • Arguments or Parameters Mode
  • IN OUT INOUT

CONDITIONAL STATEMENTS

  • Simple IF
  • IF—THEN—ELSE
  • COMPOUND IF
  • ELSIF Construct
  • Worked Examples and Assignments

LOOPS

  • Simple Loop
  • While Loop
  • For Loop

CURSORS

  • CURSOR TYPES
  • IMPLICIT CURSORS
  • EXPLICIT CURSORS
  • Declaring and using Cursor Variables
  • REF cursor

Exceptions

  • i) Predefined Exceptions ii) User defined Exceptions
  • Pragma_ Exception_init
  • RISE_APPLICATION_ERROR
  • SQL Error code values

PL/SQL COLLECTIONS

  • RECORD TYPE COLLECTIONS
  • ARRAY TYPE COLLECTIONS

PL/SQL RECORDS AND TABLES

  • Composite Data Types
  • PL/SQL Records & Tables
  • Nested Records
  • Using Record in a Table
  • NAnd Examples

FUNCTIONS

  • What is a Function?
  • When we have to create a function?
  • How to call it?
  • With and without arguments

TRIGGERS

  • What is a Trigger Program?
  • When we need a Trigger?
  • Parts of a Trigger
  • Types of Triggers
  • Database Triggers
  • Row Triggers with Ex
  • Statement Triggers
  • Instead-of Triggers - Views with Ex

PACKAGES

  • Structure
  • Implementation
  • Accessing procedures and functions from a package
  • EXPLANATION ABOUT THE FOLLOWING

DB Tuning and Performance Optimization

  • SQL tuning
  • Optimization tools & techniques
  • Explain plans
  • AWRs, ADDM, OEM
  • Parallel processing techniques
  • Troubleshooting performance issues
Other Courses
© Copyrights SSCyient 2014. All Rights Reserved. Sitemap