Oracle 11g SQL Certification training Bangalore| Oracle SQL Certification course| OCA OCP training


Oracle 11g SQL Certification training in Bangalore


Oracle 11g SQL Certification Training- (Exam Code: 1Z0-051)
Click here to download the detailed Syllabus

What do you learn in this course?

This is a 100% job oriented course. In this course students learn the practical concepts of relational databases and essential SQL writing skills that allow developers to write queries against single and multiple tables extensively. Aim of this course is to enable students in getting 100% practical knowlegde and make them ready to work on real projects and also attend interviews.

Course Highlights:

Get trained by well experienced IT professionals
Real time training with live examples and practice
100% job oriented training with lots of Practice
Learn how to convert business logic to SQL Queries
Understand how queries run in the background
10 Sessions, 10 days, 2-3 hrs per day
Full training and support for Certification Exam
Premium support at our Authorized Exam center
Interview questions discussion

About the Instructor
DTecH Certified & experienced real time trainer. 
100% money back guarantee if you don’t like the training.

Language of Instruction: English

Duration: 10 days, 10 Sessions, 2-3 hours per day

Training mode: Live Instructor led training

Course materials: Hard copies of both course & lab materials provided.

Software Setup & Lab: 

Will provide access our Oracle Server for Lab sessions.
Software provided to install on student laptops.

Pre-requisites:

Good logical thinking skills or knowledge of any programming language

Course Outline:

Course Topics

Course Topics

1. Introduction to DBMS
What is a Database?
File based Database
How is data stored in excel?
Introduction to DBMS
Oracle DBMS

2. SQL Basics
Types of Data
Datatypes in Oracle DB
How to Create a table?
Example: Create Statement
How to insert data into a table?
How to view table after data is inserted?

3. SQL SELECT Statement
Structured Query Language (SQL)
Basic SELECT Statement – Syntax
Arithmetic Operators
Define NULL value
Using Column Aliases
Concatenation Operator
Duplicate Rows
Displaying the Table Structure

4. WHERE Clause, Operators and Sorting (Order by)
Limiting the Rows that Are Selected
Character Strings and Dates
Comparison Operators
Using Comparison Operators
Range Conditions Using the BETWEEN Operator
The IN Operator
Handling the NULL Conditions
Logical Operators
AND Operator
OR Operator
Using the NOT Operator
Using the ORDER BY Clause

5. Using SQL Functions
Classification of Functions
Single-Row Functions
Character Functions
Case-Conversion Functions
Character-Manipulation Functions
Number Functions
Dates Arithmetic
Date-Manipulation Functions
Date Functions: Illustration

6. Conversion Functions and Conditional Expressions
Conversion Functions
Explicit Data Type Conversion
Using the TO_CHAR Function with Numbers
Using the TO_CHAR Function with Dates
Elements of the Date Format Model
TO_NUMBER function
TO_DATE function
General Functions
NVL Function
Using the NVL2 Function
Using the NULLIF Function
Conditional Expressions
CASE Expression

7. Group Functions & Group by Clause
What Are Group Functions?
Types of Group Functions
Group Functions: Syntax
AVG Function
MIN and MAX Function
Using the COUNT Function
GROUP BY Clause Rules
Restricting Group Results
Restricting Group Results with the HAVING Clause

8. SQL Join Operations
Obtaining Data from Multiple Tables
Oracle Joins
Qualifying Ambiguous Column Names
INNER or SIMPLE Join
Creating Three-Way Joins
Joining a Table to Itself – Self Join
Self Join
Simple Join - Equijoins
Simple Join – Nonequijoins
Returning Records with No Direct Match with Outer Joins
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Cartesian Products

9. Using Sub queries
Subquery : Scenario
Using a Subquery
Subqueries – Definition
Single-Row Subqueries
ORA-01427: Single-row subquery returns more than one row
Multiple-Row Subqueries
Using the ANY Operator
Using the ALL Operator

10. Set Operators
Set Operators
Set Operator Guidelines
UNION Operator
Using the UNION Operator
UNION ALL Operator
Using the UNION ALL Operator
INTERSECT Operator
Using the INTERSECT Operator
MINUS Operator
Using MINUS Operator
Using the ORDER BY Clause in Set Operations

11. DMLs and TCLs
Data Manipulation Language
Adding a New Row to a Table
INSERT Statement Syntax
Inserting Rows with Null Values
UPDATE Statement Syntax
DELETE Statement
TRUNCATE Statement
Database Transactions
TCL commands (COMMIT/ROLLBACK)
COMMIT and ROLLBACK
Data Before COMMIT or ROLLBACK
State of the Data After COMMIT and Rollback
State of the Data After ROLLBACK: Example

12. DDLs – CREATE, ALTER, DROP
CREATE TABLE
Referencing Another User’s Tables
DEFAULT Option
Creating Tables with sub query
Including Constraints
Defining constraint
NOT NULL Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
CHECK Constraint
ALTER TABLE Statement
Dropping a Table

13. Views, Synonyms, Sequences & Indexes
Database Objects
What Is a View?
Creating a View
Describing and Retrieving Data from a View
Altering and Modifying a View
Performing DML Operations on Simple View
Removing a View
Sequences
CREATE SEQUENCE Statement: Syntax
Using a Sequence
Alter a Sequence
Indexes
Removing an Index
Synonyms
Creating a Synonym for an Object
Synonym – Illustration
Removing and Altering Synonyms

------------------------------------------------------------------------- Advanced SQL ----------------------------------------------

14. Capabilities of SELECT Statement
Operator Precedence
Example - Operator Precedence
Literal Character Strings
Quote (q) Operator
Pattern Matching Using the LIKE Operator
ESCAPE Identifier
Rules of Precedence
ORDER BY For NULLs
Positional Sorting
Substitution Variables
Demo of Substitution Variable in SQL developer
Specifying Column Names, Expressions, and Text
DEFINE Command
Using the VERIFY Command
Character-Manipulation Functions – Advanced
Number Functions – Advanced
RR Date Format – Century Calculation Rules
Date-Manipulation Functions – Advanced
Using the TO_CHAR Function with Dates – Advanced
Nesting Functions
General Functions – Advanced
Conditional Expressions
Advanced – Searched Case Expression
DECODE Function
Group Functions and Null Values
GROUP BY Clause Usage notes – I
GROUP BY Clause Usage notes – II
Where vs Having
Nesting Group Functions

15. ANSI Joins
Creating Natural Joins
Creating Joins with the USING Clause
Using Table Aliases with the USING Clause
Creating Joins with the ON Clause
Creating Three-Way Joins with the ON Clause
Joining a Table to Itself – Self Join
Self-Joins Using the ON Clause
Simple Join - Equijoins
Simple Join – Nonequijoins
Returning Records with No Direct Match with Outer Joins
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Creating Cross Joins
The HAVING Clause with Subqueries
INLINE VIEWS
Advanced Subqueries
Correlated Sub Query
Multiple Column Sub Query
Set Operators - Matching the SELECT Statement
Arithmetic vs Set operators
16. Inserting Special Values into table
Copying Rows from Another Table
Updating Two Columns with a Subquery
Updating Rows Based on Another Table
Deleting Rows Based on Another Table
Savepoint and Partial Rollbacks
Explicit Transaction Control Statements
Rolling Back Changes to a Marker
Read Consistency
FOR UPDATE Clause in a SELECT Statement
Examples of different Database Objects
DDL- Naming Rules
Creating Tables with sub query without copying data
Data Types
Defining constraints at Table Level
FOREIGN KEY Constraints – Advanced
Alter - Read-Only Tables
Dropping a Table – Advanced
Simple Views and Complex Views
Creating a Complex View
Performing DML Operations on a Complex View
Using the WITH CHECK OPTION Clause
WITH READ ONLY clause
SEQUENCE – Advanced
When to use CURRVAL
Index – Notes
Index Creation Guidelines
Index - Advanced

 

Click here to download the detailed Syllabus

Click here to fill up the course Enquiry Form!