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
+1 (415)-873 0245